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

81–90 of 112 posts

Re: How Postgres Triggers Can Simplify Your Back End Development

#81

Post by prng2021 »

Scubabear68 wrote: post #66
masklinn wrote: post #38

Earlier quoted context omitted.

> 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.

I have worked with systems with nearly 100k lines code in Oracle stored procs, and another legendary place with around 4 million. It was a nightmare. Deployments were very difficult, there was little tooling, reasoning about the system was difficult, and of course running so much code in Oracle required very expensive licenses. And it is much harder to hire hard core PL/SQL devs over Java, C#, Python or whatever. You…

Wow. I was going to say that no one in their right mind would even consider putting all business logic at the database layer. I’m willing to bet there were tons of Oracle-specific features being used too.

Re: How Postgres Triggers Can Simplify Your Back End Development

#84

Post by wgerard »

Heh I vaguely recall at Etsy, predating my time, that a significant amount of business logic was done using stored procedures and triggers.

They migrated away from it at some point, but some of the people who handled that migration were still around when I was there. Didn’t sound fun at all, sounded like a horrific nightmare.

Re: How Postgres Triggers Can Simplify Your Back End Development

#85

Post by polishdude20 »

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 for migrating old row to new row. We could also have the old system maybe make an API call to the new system and skip triggers altogether.

Re: How Postgres Triggers Can Simplify Your Back End Development

#87

Post by Wicher »

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…

One of my pet projects addresses these points you raise about Django + migrations, have a look:

https://pypi.org/project/DBSamizdat

In a nutshell it allows you to keep DB functions, triggers and views around as Python classes, so you can version them together with the rest of your application code. The DB state gets updated for you (in the right dependency order) whenever you change them.

It can also run without Django.

Re: How Postgres Triggers Can Simplify Your Back End Development

#88

Post by simonw »

OJFord wrote: post #79
hermanradtke wrote: post #62

Earlier quoted context omitted.

> 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 Applying a series of migrations to get a final db schema is not much different than a version control system like git.

Ok, so how do I checkout a version and view the file/schema tree? If you like, you can view my comment above as saying 'if they included that tooling, not just the similar tree of changes stored, it would be better'. How to get from one state to the next is interesting to the computer, not to me (not after I've initially written it/done it in prod anyway), I'm interested in the result, where do we stand after all of…

You checkout that version, then run the migrations from scratch against a fresh database.

In Django that's "./mange.py migrate".

Re: How Postgres Triggers Can Simplify Your Back End Development

#89

Post by asguy »

hansvm wrote: post #80
asguy wrote: post #60

Earlier quoted context omitted.

Maybe, but at “mid” production scale I’ve used Postgres plenty as the substrate for queues and managing requests for FSMs and their state changes, and didn’t run into these problems. Like everything, it depends on the application.

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_transaction_session_timeout to deal with total consumer failures. A properly designed queue in Postgres runs more-or-less in parallel, and supports (really fantastic features like) atomic row locks across all resources needed to serve the queue request.

If you need extremely long consumer timeouts, it's also totally fine to use RLLs in addition to state on the job itself.

[0] - https://www.crunchydata.com/blog/message-queuing-using-nativ... [1] - https://www.2ndquadrant.com/en/blog/what-is-select-skip-lock...

Re: How Postgres Triggers Can Simplify Your Back End Development

#90

Post by thinkingkong »

hansvm wrote: post #80
asguy wrote: post #60

Earlier quoted context omitted.

Maybe, but at “mid” production scale I’ve used Postgres plenty as the substrate for queues and managing requests for FSMs and their state changes, and didn’t run into these problems. Like everything, it depends on the application.

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?
Post reply on HN