4 ms·

Triggers are one of the best parts of Postgres. We built a large ETL and machine learning operation in PG through triggers, from simple algorithms calculating

by doh 8y ago

Triggers are one of the best parts of Postgres. 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. [0] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex [1] https://cloud.google.com/customers/pex/ https://cloud.google.com/customers/pex/

weavie 8y ago
> There are some downsides. Triggers are very hard to debug, optimize and monitor. That's enough for me. My heart always sinks when in response to a problem someone suggests a solution of "let's just stick a few triggers in".. Its happened too many times in my career where some wierd problem that noone can work out turns out to be caused by a trigger that noone realised was there.
- doh 8y ago
  I do understand. Triggers are ripe for improvements. We have some internal tools built around them, but nothing significant. Most helpful are protocols for humans to follow when dealing with a problem. Always start with the trigger. At the same time, triggers saved us hundreds of thousands of lines of code and extremely complicated logic, that would be required would we wanted to replace them.

by doh 8y ago

weavie 8y ago

> There are some downsides. Triggers are very hard to debug, optimize and monitor. That's enough for me. My heart always sinks when in response to a problem someone suggests a solution of "let's just stick a few triggers in".. Its happened too many times in my career where some wierd problem that noone can work out turns out to be caused by a trigger that noone realised was there.

doh 8y ago
I do understand. Triggers are ripe for improvements. We have some internal tools built around them, but nothing significant. Most helpful are protocols for humans to follow when dealing with a problem. Always start with the trigger. At the same time, triggers saved us hundreds of thousands of lines of code and extremely complicated logic, that would be required would we wanted to replace them.