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

21–30 of 79 posts

Re: Are triggers really that slow in Postgres?

#21

Post by philliphaydon »

yeukhon wrote: post #17
dizzystar wrote: post #10

The best way to discuss any speed thing is with anecdotes, right? /sarcasm 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 wi…

I have no experience in using the json column in postgres (well, sort of did, but it was just a json type, before json column was a real thing). 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,…

Sounds to me like your documents were poorly designed.

Re: Are triggers really that slow in Postgres?

#22

Post by nathanaldensr »

RussianCow wrote: post #18
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.

Based on the experience of co-workers (and my own limited dabbling), you are right that version control is (and should be) used for stored procedures just as it is with application code. The problem is that there isn't a lot of tooling that isn't language- or platform-specific to help maintain these things in the long run. So DBAs tend to write and maintain their own collection of homegrown scripts for deploying chan…

I certainly figured it out at several jobs:

- 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

Re: Are triggers really that slow in Postgres?

#23

Post by sp527 »

hinkley wrote: post #15
stuartaxelowen wrote: post #6

Earlier quoted context omitted.

Interesting, I think that a good CI/CD story is probably the biggest thing holding broader use of stored procedures back.

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…

Who hurt you?

Re: Are triggers really that slow in Postgres?

#24

Post by brongondwana »

zmmmmm wrote: post #8
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…

It's not the whole answer, but Postgres is rather remarkable for its support of using first class programming languages in stored procs - you can even write them in Java if you want: https://tada.github.io/pljava/

I once embedded our entire Perl business logic (closed source, previous job) inside Postgres with its DB routines changed to the Postgres bridge internal stuff, and creating a single interface where you could message it with an XML blob and get back an XML blob of response. It was actually pretty usable, but the speed benefit wasn't enough to justify such a brittle rube goldberg machine!

Re: Are triggers really that slow in Postgres?

#25

Post by michelpp »

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)

Writable views also work perfectly with PostgREST, if you're view is auto-updateable or has an INSTEAD OF trigger than you can POST/PATCH/DELETE via the view to mutate.

Re: Are triggers really that slow in Postgres?

#26

Post by jerrysievert »

dahdum wrote: post #16
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…

For a large node app, we wrote most of the business logic in JS and ran with PLV8. All the procedures were called from a single PLV8 library in shared memory, and we created functions stubs so you could still call with straight SQL (mostly used for ETL scripts).

can you tell me a little more about the methods you used with plv8?

Re: Are triggers really that slow in Postgres?

#27

Post by lostapathy »

protomyth wrote: post #11
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…

> 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 that they are somehow special, and it scares them.

Re: Are triggers really that slow in Postgres?

#28

Post by lostapathy »

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 biggest problem I've had with triggers is that your application tier tells the database to do an update, it says it did it, and then the app shows the user the result.

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.

Re: Are triggers really that slow in Postgres?

#29

Post by megy »

hinkley wrote: post #12
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…

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?

Re: Are triggers really that slow in Postgres?

#30

Post by jerrysievert »

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?

a lot of languages have migrations available, that help manage the state of the database. in addition, postgres allows for you to ALTER or REPLACE functions and triggers, just like you'd ALTER a table.

these in combination make it very easy to manage and deploy.

Post reply on HN