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

51–60 of 79 posts

Re: Are triggers really that slow in Postgres?

#51

Post by yen223 »

btilly wrote: post #9
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…

That is my biggest concern. 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.

Agreed. I like thinking of the database as a dumb data store, optimised for data integrity, fast reads and writes, and nothing more.

Re: Are triggers really that slow in Postgres?

#52

Post by begriffs »

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

Re: Are triggers really that slow in Postgres?

#53

Post by bobwaycott »

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?

You can easily add functions, triggers, etc. as SQL in migrations. It’s just not a good idea when you realize you might want to change them. Putting them in migrations seems nice at first because it omits an extra step when upgrading your db. But then you have the SQL multiple times in your migrations, which makes finding the correct version annoying.

Re: Are triggers really that slow in Postgres?

#54

Post by Someone »

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, requiring an index lookup, or inserting rows into an audit log.

Re: Are triggers really that slow in Postgres?

#55

Post by PeterisP »

gumballhead wrote: post #41
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 s…

It's feasible to make such a tool to update the database schema so that it would match a changed definition, but I don't think that it's feasible for it to update the actual database.

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.

Re: Are triggers really that slow in Postgres?

#56

Post by krylon »

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.

Microsoft SQL Server allows one to write server-side code (triggers, sprocs, functions) in any language that compiles to .Net IL. There are some serious restrictions, though, as most of the .Net Framework becomes unavailable[1]. More recent version also provide for server-side R, but our SQL Server is 2014, so I have not had the option to play with that (plus, I don't know R).

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

Re: Are triggers really that slow in Postgres?

#57

Post by aeontech »

jasonmp85 wrote: post #13
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)

FWIW since 9.6 this behavior is automatic for views with simple column references against a single relation

What?! That is amazing!

Re: Are triggers really that slow in Postgres?

#58

Post by aeontech »

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)

This exactly what I need this week. Thank you!

Re: Are triggers really that slow in Postgres?

#59

Post by arkh »

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

Re: Are triggers really that slow in Postgres?

#60

Post by _mischief »

gumballhead wrote: post #41
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 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.

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

Post reply on HN