31–40 of 79 posts
Earlier quoted context omitted.
There’s nothing stopping you from putting a script to create all of your triggers under version control. With your table and index creation scripts...
How easy is it to deploy those changes then?
The biggest concern that engineering teams seem have with stored procs is maintainability. The consistency guarantees are indeed very attractive, but perhaps not at the expense of keeping business logic in separate places, weak version control/deploy solutions, and a new programming language to the stack. If there were a compiler that could take business logic in the project's programming language and manage mappings…
The biggest problem I've had with triggers is that your application tier tells the database to do an update, it says it did it, and then the app shows the user the result. But in the background, the trigger somehow overrode your changes, so the next time the user comes back they see different data than the app just told them was there. Granted, this is arguably the wrong way to use triggers, but once it's become an i…
Earlier quoted context omitted.
> weak version control/deploy solutions, and a new programming language to the stack Are people not using the same version control for stored procedures as their "normal" programs? I thought deployment was pretty much a solved issue with DBAs.
Based on the experience of co-workers (and my own limited dabbling), you are right that version control is (and should be) used for stored procedures just as it is with application code. The problem is that there isn't a lot of tooling that isn't language- or platform-specific to help maintain these things in the long run. So DBAs tend to write and maintain their own collection of homegrown scripts for deploying chan…
Earlier quoted context omitted.
Interesting, I think that a good CI/CD story is probably the biggest thing holding broader use of stored procedures back.
This sort of sentence is uttered by someone who is so far down a hole that all they can see is stars. It’s a big hole that a lot of people are in but it’s the consequence of a nasty trade off. All of this is trivial if your team decides that a shared database for dev work is bad for repeatability and thus bad for scaling the team. You should be able to spool up a local database with good sample data in it. To do that…
The biggest concern that engineering teams seem have with stored procs is maintainability. The consistency guarantees are indeed very attractive, but perhaps not at the expense of keeping business logic in separate places, weak version control/deploy solutions, and a new programming language to the stack. If there were a compiler that could take business logic in the project's programming language and manage mappings…
I don't know if the library is maintained anymore, but I need to find it.
EDIT: Found it and it's not maintained. Solid approach to the problem though.
Earlier quoted context omitted.
The biggest problem I've had with triggers is that your application tier tells the database to do an update, it says it did it, and then the app shows the user the result. But in the background, the trigger somehow overrode your changes, so the next time the user comes back they see different data than the app just told them was there. Granted, this is arguably the wrong way to use triggers, but once it's become an i…
In postgres you can handle this using the "returning" syntax for updates; then as long as the trigger runs before rather than after the update you don't have to lie about anything.
Most of our code ran through an ORM, and the ORM assumes the DB either did what it asked, or the query fails. I don't want to have to abandon my ORM because I can't trust my "DBA" to not sabotage my queries.
We built a large ETL and machine learning operation in PG through triggers, from simple algorithms calculating rate of change for updated datasets, to identifying trends and connecting seemingly unrelated data.
Even at our scale [0][1] the performance tradeoff is absolutely worth it. The best part is the consistency of the data. If the trigger dies, the whole transaction is rolled back and you have to re-run it. This way we never end up with different state of data that has to be fixed after the fact.
There are some downsides. Triggers are very hard to debug, optimize and monitor. Versioning is kind of nightmare and there is no direct performance information that you could capture. In our case it's even more as we are running on citus and we have to deal with data distribution, colocation and other issues.
But it's now two years and we're only adding to it.
Earlier quoted context omitted.
In postgres you can handle this using the "returning" syntax for updates; then as long as the trigger runs before rather than after the update you don't have to lie about anything.
I'm not really sure that would help in the case we had. Most of our code ran through an ORM, and the ORM assumes the DB either did what it asked, or the query fails. I don't want to have to abandon my ORM because I can't trust my "DBA" to not sabotage my queries.
Earlier quoted context omitted.
> weak version control/deploy solutions, and a new programming language to the stack Are people not using the same version control for stored procedures as their "normal" programs? I thought deployment was pretty much a solved issue with DBAs.
Based on the experience of co-workers (and my own limited dabbling), you are right that version control is (and should be) used for stored procedures just as it is with application code. The problem is that there isn't a lot of tooling that isn't language- or platform-specific to help maintain these things in the long run. So DBAs tend to write and maintain their own collection of homegrown scripts for deploying chan…
The biggest concern that engineering teams seem have with stored procs is maintainability. The consistency guarantees are indeed very attractive, but perhaps not at the expense of keeping business logic in separate places, weak version control/deploy solutions, and a new programming language to the stack. If there were a compiler that could take business logic in the project's programming language and manage mappings…
The best implementation I've seen of this was actually in a Rails library, where rather than keep the functions and triggers in migrations, it kept them in individual files so they'd be version controlled just like the rest of the code...and then reloaded them with migrations if they had changed. I don't know if the library is maintained anymore, but I need to find it. EDIT: Found it and it's not maintained. Solid ap…