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

Are triggers really that slow in Postgres?

cybertec-postgresql.com

Post reply on HN
Print view View original

41–50 of 79 posts

Re: Are triggers really that slow in Postgres?

#41

Post by gumballhead »

wyc wrote: post #3

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've often wanted to write a compiler that would accept a schema definition like protobufs or graphql as input and generate the required SQL to update the database if a schema change was committed. It would also be read by the application layer so its model definitions were kept up to date.

As you say, there's a tremendous advantage in using triggers to remove data invariants, but then there's also the issue of the schema's state, and it can be prone to error depending on the complexity of the trigger. It's definitely recommended to use SQL's schema inspection to verify and test it. Perhaps even based on schema definitions from protobufs or whatever.

Re: Are triggers really that slow in Postgres?

#42

Post by halostatue »

stuartaxelowen wrote: post #6
wyc wrote: post #3

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…

Interesting, I think that a good CI/CD story is probably the biggest thing holding broader use of stored procedures back.

This is where [sqitch](http://sqitch.org) and [pgtap](http://pgtap.org) are useful.

We aren’t currently using stored procedures, but we have a fairly substantial pgtap set for our schema definitions (where we have a number of complex constraints).

Re: Are triggers really that slow in Postgres?

#43

Post by gaius »

mustardo wrote: post #4
jerrysievert wrote: post #2

short 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)

Writing through a view is totally unremarkable and has been for a long time. It’s quite common in Oracle and SQL Server lands. You can update via a CTE too, very easy, very elegant.

Re: Are triggers really that slow in Postgres?

#44

Post by ta5676543244 »

megy wrote: post #29
hinkley wrote: post #12

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?

Liquibase is one example of a tool that integrates with your project and can deploy or rollback version controlled scripts. Bit similar to Rails migrations too from memory.

Re: Are triggers really that slow in Postgres?

#45

Post by mozumder »

bobwaycott wrote: post #40
brightball wrote: post #35

Earlier quoted context omitted.

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…

Funny. I was just having a conversation with my partner about this very issue and this was the approach I was thinking of. I’ve found keeping database functions, triggers, etc. painful as part of standard migrations—finding the most recent version is particularly annoying. They’re the kind of thing it’s best to treat like the rest of a project’s code—version controlled outside of migrations.

Isn't that what you'd do anyways?

How would you create triggers if you didn't create an SQL file with the trigger code, and running that at some point? And since it's in a file, wouldn't you check that into version control?

Or are you actually typing in triggers manually in the Postgres command line?

Re: Are triggers really that slow in Postgres?

#46

Post by paulryanrogers »

mozumder wrote: post #45
bobwaycott wrote: post #40

Earlier quoted context omitted.

Funny. I was just having a conversation with my partner about this very issue and this was the approach I was thinking of. I’ve found keeping database functions, triggers, etc. painful as part of standard migrations—finding the most recent version is particularly annoying. They’re the kind of thing it’s best to treat like the rest of a project’s code—version controlled outside of migrations.

Isn't that what you'd do anyways? How would you create triggers if you didn't create an SQL file with the trigger code, and running that at some point? And since it's in a file, wouldn't you check that into version control? Or are you actually typing in triggers manually in the Postgres command line?

Not the OP but I did use triggers as suggested in the past. IME the issue is that DDL like triggers are transactional, so when you deploy them executing queries could be using a mix of old and new.

Granted, this is an issue for any system comprised of distributed components. Though I think the pain is more acute when it's the source of truth.

One way around is downtime or going all in on stored procedures for all work, or at least all writes.

The other wrinkle is that the trigger has to be rolled back when reverting code. And some shops are not diligent about maintaining down procedures. There is also the possiblity of rolling back migrations out of the order they were run, so you might not get back to a consistent state.

Re: Are triggers really that slow in Postgres?

#47

Post by kbenson »

wyc wrote: post #3

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…

> and a new programming language to the stack.

This was actually one of the interesting directions Drizzle SQL was exploring back in the day. It was a fork of MySQL that focused on removing old cruft, including lots of older platform support, modernizing the source, and adding modularity in a lot more places, including the use of different languages for SQL programming. Other languages like Perl, Python, Ruby, etc.

Re: Are triggers really that slow in Postgres?

#48

Post by hboon »

kbenson wrote: post #47
wyc wrote: post #3

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…

> and a new programming language to the stack. This was actually one of the interesting directions Drizzle SQL was exploring back in the day. It was a fork of MySQL that focused on removing old cruft, including lots of older platform support, modernizing the source, and adding modularity in a lot more places, including the use of different languages for SQL programming. Other languages like Perl, Python, Ruby, etc.

Oracle DB supports Java stored procedures. I wrote a couple back in the day. I'm not sure we want to go there :P

Re: Are triggers really that slow in Postgres?

#49

Post by axelfontaine »

stuartaxelowen wrote: post #6
wyc wrote: post #3

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…

Interesting, I think that a good CI/CD story is probably the biggest thing holding broader use of stored procedures back.

Flyway repeatable migrations make this trivial. Keep your stored procs under version control and Flyway will only recreate the ones that changed. See https://flywaydb.org/documentation/migrations#repeatable-mig...

Re: Are triggers really that slow in Postgres?

#50

Post by fredrikfornwall »

lostapathy wrote: post #36
CuriouslyC wrote: post #32

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.

A properly written trigger shouldn't throw away data silently.

It should raise an exception, which indeed would cause the query to fail, and roll back the whole transaction (so no side effect is committed at all).

https://www.postgresql.org/docs/current/static/plpgsql-error...

Post reply on HN