Are triggers really that slow in Postgres?
cybertec-postgresql.com
Are triggers really that slow in Postgres?
1–10 of 79 posts
Re: Are triggers really that slow in Postgres?
#2short answer: no, not slow.
longer answer: you can do some really cool things with triggers in postgres, my favorite is what I like to refer to as "writeable views" - https://legitimatesounding.com/blog/stupid_postgresql_tricks... (2010)
Re: Are triggers really that slow in Postgres?
#3The 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 to stored procs (reminiscent of LINQ's mapping to queries), maybe we could have the best of both worlds.
Re: Are triggers really that slow in Postgres?
#4short answer: no, not slow. longer answer: you can do some really cool things with triggers in postgres, my favorite is what I like to refer to as "writeable views" - https://legitimatesounding.com/blog/stupid_postgresql_tricks... (2010)
Interesting...
I can imagine uttering some four letter words while trying to figure out what was going on if I was to take over maintaining a system that used this (principal of least surprise)
Re: Are triggers really that slow in Postgres?
#5Triggers in postgres are amazing - I've got a full automated phrase-extraction system built in postgres triggers, such that salient phrases are automatically detected in marked text fields.
Re: Are triggers really that slow in Postgres?
#6The 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…
Interesting, I think that a good CI/CD story is probably the biggest thing holding broader use of stored procedures back.
Re: Are triggers really that slow in Postgres?
#7short answer: no, not slow. longer answer: you can do some really cool things with triggers in postgres, my favorite is what I like to refer to as "writeable views" - https://legitimatesounding.com/blog/stupid_postgresql_tricks... (2010)
Interesting... I can imagine uttering some four letter words while trying to figure out what was going on if I was to take over maintaining a system that used this (principal of least surprise)
probably the same four letter words that were uttered when it was determined that something wild and crazy was needed in order to support the requirements.
documentation is key.
Re: Are triggers really that slow in Postgres?
#8The 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…
It's not the whole answer, but Postgres is rather remarkable for its support of using first class programming languages in stored procs - you can even write them in Java if you want:
Re: Are triggers really that slow in Postgres?
#9The 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…
That is my biggest concern.
My second biggest concern is performance overhead. Sure, they are individually fast. However the one thing that is hardest to scale is the database. Loading the database up front with overhead means that you'll hit that limit sooner rather than later.
Re: Are triggers really that slow in Postgres?
#10The best way to discuss any speed thing is with anecdotes, right? /sarcasm
I had a system that required the parsing of large json chunks. The system pulled the json from an API, pushed the data into a json-type column, then sorted the data into normal form.
I originally tried using straight Python to pull the data, but decided that I ought to keep the original data for record keeping, plus testing was a lot faster without constantly calling the API.
When all was said and done, the whole operation took about a minute to complete. I decided to try trigger, which caused the entire process, from call, to printing "done," to take less than a second.
In this case, the trigger was signficantly faster.
The danger of this anectdote, and all stories with databases, is that all things have to be posted with "in this case." Any time you read triggers, aggregation, CTEs, etc, are fast or slow, consider that this is almost always told in a vacuum. There are so many variables, that the term "fast" is wholly useless