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

61–70 of 79 posts

Re: Are triggers really that slow in Postgres?

#61

Post by williamscales »

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?

To keep things clean one could keep the trigger code in a file separate from any migrations and then create a migration that references the trigger source so that one doesn't have to go digging through the migration files later to read the trigger or update it.

I think some folks above may literally have meant they put the trigger code in the migration file itself. Or I'm way off base.

Re: Are triggers really that slow in Postgres?

#62

Post by magicalhippo »

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?

We have the DB schema in a custom XML file, and have written a tool to compare the actual schema with that of the XML file. The tool then applies the necessary changes if they're non-destructive (extending column, adding index etc), or fails and lets the user know what needs to be done if run interactively.

By making sure we do as few potentially "destructive" changes to the schema as possible, this tool automatically upgrades our customers database when we release a new version with high reliability.

Adding or altering triggers, stored procs and views are considered "non-destructive" in this context, but of course that requires some discipline from us. One aspect of that is that we try to keep the number of triggers to an absolute minimum. Removing triggers and similar is considered "destructive", however we have a way in the XML to explicitly delete the object if needed.

The XML file lives in our version control repository alongside the source code, and we have triggers which updates test databases automatically when an update is committed etc.

It's a fairly simple idea but has worked quite well for us.

Re: Are triggers really that slow in Postgres?

#63

Post by irishsultan »

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?

Rails migrations are checked in to version control, and then rails keeps track of which "migration" has been run. For tables this works fine, because you want to define them only once and if you need to change columns/add columns etc. you can create a new migration, which contains only the updates you want.

For triggers however you need to replace the trigger wholesale, so you end up with several versions of the trigger on your file system and need to check each migration file to see what the latest definition of the trigger is (for tables rails normally creates a separate file with the current state of the database after running a migration, so you have relatively easy access to the latest state of table definitions).

Re: Are triggers really that slow in Postgres?

#64

Post by blattimwind »

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 good ORM allows you to specify that a column may be changed server-side by triggers etc. on an update (or insert), and it will then make sure to retrieve the correct value after the fact, if necessary.

Re: Are triggers really that slow in Postgres?

#65

Post by Jweb_Guru »

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)

Counterpoint: using writable views was one of the best decisions my company ever made and no one regretted it. It is much easier to comprehend than ordinary triggers (specifically, the automated version).

Re: Are triggers really that slow in Postgres?

#66

Post by rantanplan »

_mischief wrote: post #60
gumballhead wrote: post #41

Earlier quoted context omitted.

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 s…

I understand the appeal and you're not the only person to want it: I've seen people and companies implement db schema to graphql, db schema to models, and models to db schema. Based on personal experience, this is a bad idea though. You do not want a 1:1 correspondence between your database schema, your backend models, and your graphql schema, because the way you organise information in each layer should be different…

Everything you say is so true. In a similar vein, all those ready-made REST libraries that help you shoehorn your business models into 1-to-1 mappings between them and your REST resources/endpoints, have no reason to exist. And yet you still see people battling with them.

REST is still HTTP + more exotic verbs + headers + more serializing/encoding options. You can build a small library, specific for you project's needs in a matter of 3-5 days. And on top of that, you don't forfeit any possible future optimizations, which you most certainly will by choosing any ready made library.

Re: Are triggers really that slow in Postgres?

#67

Post by skibz »

I've been maintaining a large (and quite old) in-play gambling system for the past six months that uses Postgres triggers and pg_notify [1] as the core mechanism for broadcasting websocket frames to SPA clients.

Hilariously, it turned out the system was violating a recommendation in the Postgres official documentation for pg_notify:

> The "payload" string to be communicated along with the notification. This must be specified as a simple string literal. In the default configuration it must be shorter than 8000 bytes. (If binary data or large amounts of information need to be communicated, it's best to put it in a database table and send the key of the record.)

The original triggers were invoking pg_notify with row_to_json(NEW), often causing the pg_notify calls to fail (which also caused the underlying insert/update that fired the trigger to fail too, meaning we DROPPED new or updated records) due to the JSON text being far too large.

[1]: https://www.postgresql.org/docs/9.0/static/sql-notify.html

Re: Are triggers really that slow in Postgres?

#68

Post by lostapathy »

matte_black wrote: post #38
lostapathy wrote: post #36

Earlier quoted context omitted.

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.

This is just a limitation of ORMs. After a certain complexity you really should just be making direct queries to your database and not relying on ORMs.

That’s the thing -what the app tier was doing wasn’t to complex for an ORM. Not by a long ahot.

The triggers that caused us problems generally came about to cover unrelated dba laziness and needed removed for sanity reasons anyway.

I am not arguing there cannot be a good case for triggers, just that when abused they cause a certain level of hell that is not worth it.

Re: Are triggers really that slow in Postgres?

#69

Post by fauigerzigerk »

Someone wrote: post #54

1) I don’t know pgbench , but given the NULL checks in the trigger, I wonder how often they actually change the records in this benchmark. If you want to performance test triggers, wouldn’t it be better to unconditionally change those fields? 2) as the blog post indicates, these are extremely simple triggers that work locally. The IMO interesting cases are moving those last modified columns to a separate table, requi…

>2) as the blog post indicates, these are extremely simple triggers that work locally. The IMO interesting cases are moving those last modified columns to a separate table, requiring an index lookup, or inserting rows into an audit log.

Depends on the question you're asking. Benchmarking extremely simple triggers is the only way to find out whether the triggers invocations themselves cause significant overhead compared to what you would otherwise have to do from application code (such as inserting a row into a separate table or populating two additional columns in the same table).

I think triggers (and stored procedures in general) are more attractive the more roundtrips they help avoid.

Re: Are triggers really that slow in Postgres?

#70

Post by GlennS »

brightball wrote: post #35
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…

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…

So there's a potential problem here.

Yes, you can technically just drop and replace all your stateless things (views, triggers, stored procedures, user defined functions).

But, if one of your migration steps happens to depend on a particular version of one of those things (it's not likely to be a trigger, but who knows?), then it can break.

I'd say - assuming you're doing your migration offline - then do it both ways. At a previous workplace I used a paid for tool called SQL Delta [https://www.sqldelta.com/]. It couldn't handle our complicated data migrations (at some point you need a human in the loop), but it was really useful for checking if there was anything off, and for synchronizing all of the views and so on at the end.

Post reply on HN