Are triggers really that slow in Postgres?
cybertec-postgresql.com
cybertec-postgresql.com
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.
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.
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.
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.
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 approach to the problem though.
Worked great, WAY better than having them buried in other SQL files.
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?
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).
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.
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.
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.
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 possible approach here - https://begriffs.com/posts/2017-10-21-sql-domain-integrity.h...
> weak version control/deploy solutions
There are solutions that allow you to store migrations in version control, apply them to different database targets, and run consistency checks. For instance http://sqitch.org/ but maybe you're familiar with it already and regard it as one of the weak tools. Thought I'd point it out though.
> a new programming language to the stack
plpgsql is somewhat gnarly, but it is well adapted for the database. You know what it's going to do, compared with managing mappings from some other language. I never tried LINQ though so maybe the mapping would be more pleasant than I'm imagining.
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.
You can use python or perl to define your postgres functions.
https://www.postgresql.org/docs/10/static/plpython-funcs.htm...
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.
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.
these in combination make it very easy to manage and deploy.
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.
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 that they are somehow special, and it scares them.
I'm sure some companies have this figured out, though, so YMMV.
- Scripts are numbered for quick order determination by humans (e.g., 0001-initial-schema.sql, 0002-create-foo-table.sql)
- A scripts.json file containing an array of string filenames declares the exact order of scripts, just in case numbers are shared for some reason (say, two branches being merged at the same time with a new script in them)
- A custom PowerShell module knows how to read scripts.json and the files and execute the changes
- When possible, all scripts are executed transactionally by the CI tool (some commands in some RDBMSes can't be executed in transactions, however)
- Optionally, backups/restores may be performed to handle failures
I have a dev box with 16 GB of RAM that sits unused all day because I work with people who think this way. Such a waste of resources.
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.
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.
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.
But in the background, the trigger somehow overrode your changes, so the next time the user comes back they see different data than the app just told them was there.
Granted, this is arguably the wrong way to use triggers, but once it's become an invasive problem in your codebase it's incredibly hard to deal with. You can't remove the triggers for fear of what still relies on the side effects, and you don't want to query back the data for every update either. It ends up being like the polar opposite of functional programming - side effects everywhere.
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.
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...
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.
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.
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.
Database schema needs to be performant for expected queries. That means de/normalisation decisions; sometimes the same data will be stored in multiple locations.
Backend models need to express the domain, because this is where your business logic is. (There's a reason people bitch about ORMs: when you get to complex enough usecases they're not flexible enough in either direction and you need extra models wrapping THAT.)
Graphql schema is a view of your backend; sometimes several fields will be fulfilled using the same model, sometimes your model should not have a reflection in graphql schema (because you do not want to expose this data to frontend/the world), and sometimes your graphql schema will be full of deprecated fields because client apps have not been updated (see Facebook policy of never removing anything.)
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.
To do that, you need knowledge about how and why the schema was altered, and schema definitions like protobufs or graphql don't contain that. For example, how would you distinguish between renamed column (where you need to keep the data) and deletion of a column plus adding a different one?
There's a reason why migrations are the standard level of abstraction, packaging changes to schema with scripts ensuring consistency of data throughout the whole version history.
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.
[1] I was looking into writing a stored procedure for use with the Service Broker in C#, but the fact that most of the .Net Framework is unavailable (not to speak of third-party libraries) quickly put an end to my investigation.
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 your schema, indexes and triggers would be under version control, and a data dump is somewhere people can get it.
Once you have this your CI system runs Postgres locally or in a container, runs the drop create scripts, and then runs your integration and end to end tests on the canned data.
In addition to getting a CI solution for next to free you get rid of the concurrent access Wild West and this particularly painful conversation:
Why did this break and why didn’t you notice it before you pushed? Oh I saw that problem the other day but I thought someone else was changing data (and not my code being broken).
If the data is on your machine and it gets broken, then it is only your machine that could have broken it. You can’t delude yourself into thinking it was someone else mucking around. The problem is either in your code or in your latest pull from master. You are responsible for determining the cause, not me, not the release manager, not QA. You.
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)
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).
if you're moving logic into your database, it's very important to be able to test and understand your use cases.
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
For me, I try to do as much on the database side like sorting, which does require a good schema and table designs. This is why folks often criticize MongoDB. One of the reasons was the convincence of “schemaless”.
When Mongo was first introduced, I think a lot of developers, including me, saw Mongo as an excuse to move away from relational databases. So we began dumping all kinds of shit. Doing fancy stuff on Mongo side is not possible without a good design either.
What people probably did was just pulling data from multiple collections, and do filtering and “joins” on the server (client) side. I would find myself writing a for loop over doing a bunch of stuff. Yikes.
Of course there are other criticisms against MongoDB, but ultimately developers like myself did not (and probably still) have any decent clues how to use databases well. Learnig to use databases right is something I really want to be good at.
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)
For instance, you want to rename a column? Add the new column, rename the existing table, add a view in its place with both the old column name and the new column name, with an INSTEADOF trigger to update the base table. Postgres lets you do this all in a transaction, so the entire operation is atomic.
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)
documentation is key.
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
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.