Are triggers really that slow in Postgres? • Hacker News Board

Re: Are triggers really that slow in Postgres?

#71

Post by rleigh » Wed, May 23, 2018, 1:05 PM UTC

arkh wrote: post #59

begriffs wrote: post #52

Earlier quoted context omitted.

> but perhaps not at the expense of keeping business logic in separate places Perhaps the answer is to consolidate business logic in the database with constraints. This also protects data from manipulation outside of an app, like with scripts or ETL. Putting more logic in the db often results in terse error messages though, so the app needs to deal with that and make them nicer for the end user. I wrote about one pos…

> plpgsql is somewhat gnarly, but it is well adapted for the database. You can use python or perl to define your postgres functions. https://www.postgresql.org/docs/10/static/plpython-funcs.htm...

They are slower (by orders of magnitude) than writing them in C or C++, or even PL/pgSQL. Calling into the interpreter for millions of trigger events can quickly become a major bottleneck.

When I originally implemented https://pgxn.org/dist/debversion/ for version numbering, I originally implemented it in Perl, then Python. The implementations were clean, but the performance of both was abysmal. After reimplementing it in C++ with a C interface, it runs like greased lightning. While this is a custom datatype with operators implemented as C functions, the same concerns apply to triggers which are invoked on every affected row.

Re: Are triggers really that slow in Postgres?

#72

Post by protomyth » Wed, May 23, 2018, 1:16 PM UTC

lostapathy wrote: post #27

protomyth wrote: post #11

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.

In my experience - there are a lot of organizations where old-school DBAs resist any modern software engineering practices, such as proper version control. I think some of it is stubborness, but a lot of it is that DBAs in those organizations tend to be more "developers who happen to write DB code" and not really the A in DBA. Letting "their" code go into version control is the first step in breaking the illusion tha…

Or it could be that DBAs who let the database go down get fired. I know a lot of DBAs that are old school that live in fear of any change because they get left holding the bag. They aren't special, they get blamed. It also doesn't help that a lot of new developers don't understand why "move fast, break things" is just not something any DBA of any worth is going to accept.

Re: Are triggers really that slow in Postgres?

#73

Post by MBCook » Wed, May 23, 2018, 1:44 PM UTC

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…

That was what we did in Java with Flyway and it’s stateless migrations. You could easily see the history of stores procedures and every time the app started up it made sure they were all updated and correct.

Worked great, WAY better than having them buried in other SQL files.

Re: Are triggers really that slow in Postgres?

#74

Post by ashearer » Wed, May 23, 2018, 2:13 PM UTC

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…

One approach that works very well is to keep stored functions in separate .sql files in a directory (I use "fixtures"), and execute them all on each deployment. This should happen after triggering migrations, so that table and column dependencies are guaranteed to be present. The .sql files use CREATE OR REPLACE FUNCTION so that their execution is idempotent.

This keeps the stored functions version-controlled along with the source code, and avoids any need to hunt through migration files to find the latest definition. Adding a stored function or modifying its function body just works.

The less common operations of deleting a function or modifying its argument list do require an explicit line in a migration file, but those situations are rare (and potentially backward-incompatible, requiring extra caution regardless).

One subtlety is that a migration that adds a new table with a trigger should define an empty stub function as the trigger. This avoids duplicating code. The real function body will be loaded from the fixture immediately afterwards.

Re: Are triggers really that slow in Postgres?

#75

Post by hobs » Wed, May 23, 2018, 2:14 PM UTC

manquer wrote: post #34

hinkley wrote: post #15

Earlier quoted context omitted.

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…

Not all bugs are logical. Many times CI workflow does not catch poorly written but logically correct queries because sample db has few records in it, emulating large data sets with data patterns like production to get the same kind of query plans is hard.

I would say this is the first objection that is on point in this thread, not that figuring out deployment isn't hard, but the blog posts on how to solve it for your platform are multitudinous.

Getting useful data loads to test queries is much more difficult but you have a few options (at least coming from a SQL Server approach):

* Query hints to emulate larger sets of data (so you can see what type of IO you would get, of course multiplication is your friend)

* Check if your database offers something along the lines of DBCC CLONEDATABASE https://docs.microsoft.com/en-us/sql/t-sql/database-console-... (gets you a copy of statistics from prod without the data)

* Building a data masking process so that the data is somewhat representative (in volume, mocking it such that the histogram of values is the same in your database is WAY harder)

* Building an isolated load test environment with something like distributed replay https://docs.microsoft.com/en-us/sql/tools/distributed-repla... (record workload, replay, measure, make your change, replay, measure, yes - it is tedious but that's a performance regression test for you)

Re: Are triggers really that slow in Postgres?

#76

Post by weavie » Wed, May 23, 2018, 2:19 PM UTC

doh wrote: post #37

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

> 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.

Re: Are triggers really that slow in Postgres?

#77

Post by juliangoldsmith » Wed, May 23, 2018, 2:28 PM UTC

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 worked on a pretty large, very stored proc-heavy app based on SQL Server for several years, and separation of business logic has never been much of an issue.

Keeping logic in stored procedures is, in practice, not much different from putting it in a separate function. If your app is data-driven, like the one I work with, most of your business logic can be done in stored procedures. Most any sort of data transform is likely better done in SQL than another language, unless your data is a poor fit for a relational database.

We have had issues with version control in the past. I'm currently working on implementing a CI and Git workflow using Jenkins and Red Gate tools, which I expect will make keeping our database in source control trivial. To be fair, though, I'm still working out the last of the kinks, so it may end up being more difficult than I thought.

As far as having to learn another language, I've always found that to be an incredibly weak excuse to use JavaScript everywhere. Learning another programming language isn't difficult, and having the right tool for the job is indispensable. Given that JavaScript these days seems to go by "flavor of the week", I don't that think not being able to learn is a problem.

I don't find the argument that it's difficult to switch between languages to be convincing, either. Switching between two programming languages that you know isn't any more difficult than switching between a programming language and a spoken one.

Re: Are triggers really that slow in Postgres?

#78

Post by combatentropy » Wed, May 23, 2018, 2:40 PM UTC

begriffs wrote: post #52

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…

> but perhaps not at the expense of keeping business logic in separate places Perhaps the answer is to consolidate business logic in the database with constraints. This also protects data from manipulation outside of an app, like with scripts or ETL. Putting more logic in the db often results in terse error messages though, so the app needs to deal with that and make them nicer for the end user. I wrote about one pos…

> plpgsql is somewhat gnarly

I agree it doesn't look like Python or whatever, but it does look a lot like SQL. So if you're familiar with SQL, PL/pgSQL is easy to learn. To me it's just SQL with if-statements and loops.

Re: Are triggers really that slow in Postgres?

#79

Post by doh » Wed, May 23, 2018, 3:13 PM UTC

weavie wrote: post #76

doh wrote: post #37

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

> 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.

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.