Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How Postgres Triggers Can Simplify Your Back End Development

themythicalengineer.com

Post reply on HN
Print view View original

101–110 of 112 posts

Re: How Postgres Triggers Can Simplify Your Back End Development

#101

Post by majkinetor »

masklinn wrote: post #38
Aqueous wrote: post #27

Why is this the top story? This is a major foot gun. Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex. Instead adopt a solution for structuring your business logic in a sane way, such as using a workflow engine. Your code will become simpler and well organized that way without creating a tangled web of distributed rules, as well as exist…

> Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex. Alternatively, write all the business logic in the database. This way you can better leverage the DB features and ensure that logic only needs to be written once.

Using SQL for business logic is probably worst then using Cobol in 2023 - good luck with automated tests, debug, documentation etc. Yes, I know it all technically can be done but you relly need to be masohistic and mindless bastard in order to do so.

Rather, lets rejoice with latest C#, zig, rust, PoweShell or whatever-beautiful-language-and-ecosystem-perfected-in-latest-decade we have, instead of horrible SQL.

Re: How Postgres Triggers Can Simplify Your Back End Development

#102

Post by saltcured »

OJFord wrote: post #91
simonw wrote: post #88

Earlier quoted context omitted.

You checkout that version, then run the migrations from scratch against a fresh database. In Django that's "./mange.py migrate".

And then inspect the db, exactly. In other words you can't, there is no declarative 'current state' checked in or provided by the migration tooling.

If you are going to use some kind of meta-schema tool to manage migrations, you should store your declarative schema in whatever syntax this tool understands and allow it to generate the schema-mutating migration commands as runtime artifacts (more like a build output than a versioned source).

If not using such a tool, you might adopt a little meta-schema logic in your own DDL. With PostgreSQL, you can try writing idempotent and self-migrating schemas. Using syntax variants like CREATE OR REPLACE for functions and views, DROP ... IF EXISTS, CREATE ... IF NOT EXISTS, and ALTER ... ADD IF NOT EXISTS allows some of your DDL to simply work on an empty DB or a prior version. Wrapping all this within a pl/pgsql block allows conditional statements to run DDL variants. Conditionals are also useful if you need to include some custom logic for data migration alongside your schema migration, i.e. create a new structure, copy+transform data, drop old structure.

For smaller DBs, you may be able to afford some brute-force techniques to simplify the DDL. Things like DROP IF EXISTS for multiple earlier versions of views, indexes, or constraints to clear the slate and then recreating them with the latest definitions. This may add IO costs to execute it, but makes the DDL easier to read as the same DDL statements are used for the clean slate and the migration. Similarly, a little pl/pgsql logic could loop over tables and apply triggers, policies, etc. that you want to use systematically in a project.

If possible, you can also prune your code so that any specific version in your source control only has logic to handle a few cases, i.e. clean slate builds and a migration of N-1 or N-2 to latest. This minimizes the amount of branched logic you might have to understand when maintaining the DDL. The approach here depends a lot on whether you are versioning a product where you control the deployment lifecycle or versioning a product release which may encounter many different prior versions "in the wild".

In any case, you have a more complicated test requirement if you want to validate that your system works from multiple starting points. I.e. clean-slate builds as well as migrations from specific earlier versions. I think this is true whether you are using some higher level migration management tooling or just rolling your own conditional and idempotent DDL.

Re: How Postgres Triggers Can Simplify Your Back End Development

#103

Post by phendrenad2 »

polishdude20 wrote: post #85

One of the reasons we use database triggers is that we have a legacy system running on Rails and a new system in Typescript. The old system has an entity that is similar to the new systems entity but a bit different. While in this limbo of sunsetting the old system, we have triggers on the old entity when it changes to update the new entity. The thing is, these triggers invoke a lambda which does the business logic f…

Using a trigger to kick off a lambda seems like the way to go. You're essentially doing what the NOTIFY command does.

Re: How Postgres Triggers Can Simplify Your Back End Development

#105

Post by hansvm »

thinkingkong wrote: post #90
hansvm wrote: post #80

Earlier quoted context omitted.

It can work great, but if not carefully introduced it's one typo away from a disaster in prod that nobody understands. You just need somebody to introduce a code path with longish transactions interacting with the queue and not have a reasonable prolonged load test in your deployment pipeline. Given how easy other queues are to set up I wouldn't default to Postgres on many teams.

Im sort of confused. Youre mentioning MVCC and deployment pipelines not having load but what portion of an application MVCC has anything to do with a queue, postgres or otherwise? Same with deployments? Maybe theres a specific model or deployment strategy thats in use that I am unaware of and its a blind spot?

Postgres uses multi version concurrency control. The amount of garbage kept around to ensure each transaction can be handled independently is roughly proportional to the length of the transaction multiplied by how much work is happening with any transaction overlap. That's potentially a problem with queues implemented in Postgres because the for-all-intents-and-purposes-dead rows being tracked by MVCC slow down each piece of work on the queue, increasing transaction times, and circularly causing a runaway scenario at lower thresholds than you might expect.

The comment about deployment pipelines was just that some shops explicitly load test with a multiple of prod-like data to find that sort of issue before prod. Doing so for a non-negligible amount of time is important though to catch qualitative shifts in the RDBMS behavior as it approaches a steady or runaway state as a result of any software change.

Re: How Postgres Triggers Can Simplify Your Back End Development

#106

Post by hansvm »

asguy wrote: post #89
hansvm wrote: post #80

Earlier quoted context omitted.

It can work great, but if not carefully introduced it's one typo away from a disaster in prod that nobody understands. You just need somebody to introduce a code path with longish transactions interacting with the queue and not have a reasonable prolonged load test in your deployment pipeline. Given how easy other queues are to set up I wouldn't default to Postgres on many teams.

I think you're projecting an implementation of a queue in Postgres, which isn't how most people implement these things. [0] We're not doing table level locks, or creating contention with multiple queue producers or consumers, and they're not "one typo away from disaster in prod". To do this right, you're using row level locking e.g. SELECT FOR UPDATE/SKIP LOCKED [1], and hopefully you're already using idle_in_transac…

Even that first link explicitly calls out MVCC table bloat with that strategy? Like, you can do it right (and I have no comment on your implementation in particular, let's assume for the sake of argument it's fantastic), but it's easy to write a right-looking solution with the property that innocuous-looking one-line changes cause major issues. I wouldn't want an average development team to pursue that approach without good reason, especially when the alternatives are so easy to do right.

Re: How Postgres Triggers Can Simplify Your Back End Development

#107

Post by OJFord »

simonw wrote: post #96
OJFord wrote: post #91

Earlier quoted context omitted.

And then inspect the db, exactly. In other words you can't, there is no declarative 'current state' checked in or provided by the migration tooling.

I don't understand. What are you looking for here? If you want a plain text SQL file to look at you can have that with a bit of extra automation - for example, every time you tag a release you could have a script that runs "./manage.py migrate" against that checkout and then dumps the schema out to a file somewhere (an asset attached to the release on GitHub as one example).

That's pretty much what I was describing in my initial reply to your top-level comment that would be an improvement, just not built in.

Ideally though I'd like a tool with first class declarative schema as the source of truth - migrations that Django can correctly autogenerate don't need to concern me at all; if I need to vary them or resolve some ambiguity in a possible path (renamed a column vs. dropped and added one, for a simple example) then there can be some way of specifying that migration. Essentially as Django targets its models as the intended result of migrations (and complains of a missing migration if they don't get there, etc.) I'd prefer instead to target SQL schema. Still check ORM consistency - but against the schema, not the migrations of it.

Starting from scratch it seems obvious that you'd have the schema, and the mapping into python models. Django does away with the former by inference, but in so doing limits its scope to the subset of possibly desired SQL that it understands.

If you have SQL and Python, then you can have DSL for the understood subset without losing migrations, tracking in a single place, etc. of the rest. You could also give Django ownership of the entire database/schema, so that any manual experiments/adjustments would be blown away on migration, they'd have to be documented in code to be persisted.

Re: How Postgres Triggers Can Simplify Your Back End Development

#108

Post by asguy »

hansvm wrote: post #106
asguy wrote: post #89

Earlier quoted context omitted.

I think you're projecting an implementation of a queue in Postgres, which isn't how most people implement these things. [0] We're not doing table level locks, or creating contention with multiple queue producers or consumers, and they're not "one typo away from disaster in prod". To do this right, you're using row level locking e.g. SELECT FOR UPDATE/SKIP LOCKED [1], and hopefully you're already using idle_in_transac…

Even that first link explicitly calls out MVCC table bloat with that strategy? Like, you can do it right (and I have no comment on your implementation in particular, let's assume for the sake of argument it's fantastic), but it's easy to write a right-looking solution with the property that innocuous-looking one-line changes cause major issues. I wouldn't want an average development team to pursue that approach witho…

The first link also calls out how to deal with said bloat. It’s part of administering a Postgres DB yourself (which may or may not be something a team should be doing).

> I wouldn't want an average development team to pursue that approach without good reason, especially when the alternatives are so easy to do right.

Agreed. Good reasons I’ve had in the past are:

- wanting transactional guarantees across your DB data and queue (which you don’t get if your queue is external)

- not wanting to add more stack complexity (this has been an issue when you have to support on-prem deployments that you aren’t allowed to interact with).

I’m sure there are others.

Re: How Postgres Triggers Can Simplify Your Back End Development

#109

Post by idlephysicist »

A colleague of mine talks about the Law of Conservation of Complexity. It boils down to "the complexity will have to go somewhere".

You can make the development of your backend more simple, by shoving the complexity into the database, meaning your backend just does less. That in itself does not make your application any simpler.

Re: How Postgres Triggers Can Simplify Your Back End Development

#110

Post by michaelsalim »

OJFord wrote: post #53
simonw wrote: post #22

Earlier quoted context omitted.

I think this problem can be robustly solved if you have the right mechanisms in place: 1. Migrations. Your schema needs to live in version control, and changes to your schema must be applied by an automated system. Django migrations are the gold standard here in my opinion, but you can stitch together a custom system if you need to, one that tracks which migrations have been run already and provides a mechanism to ap…

I don't think migrations (at least as done by Django et al.) solve it - you want a declarative source of truth for what the schema looks like today , not a chain of changes that only tell you that after computing the combined effect. Even if they just created a generated file of the final schema, that sat in version control and errored the makemigrations check (just like a missing migration) if it was out of sync, th…

Had a thought recently. What's stopping someone from separating table migrations from others like functions/triggers?

The standard tables can use the standard process while triggers, etc are run declaratively. When you change something, a tool could simply tear everything down and rebuild it. No need to worry about data since those are handled separately.

Would performance be a concern? Or is there something I'm missing?

Post reply on HN