Yeah, I'm going to remove triggers in next deploy of a POS system since they are adding 10-50ms to each insert.

Becomes a problem if you are inserting 40 items to order_items table.

> Yeah, I'm going to remove triggers in next deploy of a POS system since they are adding 10-50ms to each insert.

Do you expect it to be faster to do the trigger logic in the application? Wouldn't be slower to execute two statements from the application (even if they are in a transaction) than to rely on triggers?

Hmm, imho, triggers do scale, they are just slow. But as you add more connections, partitionss, and CPUs, the slowness per operation remains constant.

Triggers are not even particularly slow. They just hide the extra work that is being done and thus sometimes come back to bite programmers by adding a ton of work to statements that look like they should be quick.

How do you handle trigger logic that compares old/new without having a round trip back to the application?

Do it in a stored procedure not a trigger. Triggers have their place but a stored procedure is almost always better. Triggers can surprise you.

I don't follow how you would do that in a stored procedure outside of a trigger.

I think instead of performing an INSERT you call a stored proc that does the insert and some extra stuff.

Yes, we already have all of our business logic in postgres functions(create_order, create_partial_payment etc).

Doing the extra work in stored procedures is noticeably faster than relying on triggers.

Have you tried deferring them?

that, and keeping your business logic in the database makes everything more opaque!

> that, and keeping your business logic in the database makes everything more opaque!

Opaque to who? If there's a piece of business logic that says "After this table's record is updated, you MUST update this other table", what advantages are there to putting that logic in the application?

When (not if) some other application updates that record you are going to have a broken database.

Some things are business constraints, and as such they should be moved into the database if at all possible. The application should never enforce constraints such as "either this column or that column is NULL, but at least one must be NULL and both must never be NULL at the same time".

Your database enforces constraints; what advantages are there to code the enforcement into every application that touches the database over simply coding the constraints into the database?

I think the dream is that business requirements are contained to one artifact and everything else responds to that driver. In an ideal world, it would be great to have databases care only about persistence and be able to swap them out based on persistence needs only. But you're right, in the real world the database is much better at enforcing constraints than applications.

you make good points; i'm overcorrecting from past trigger abuses :)

Neither do foreign keys the moment you need to shard. Turns out that there's no free lunch when you ask your database to do "secret extra work" that's supposed to be transparent-ish to the user.

Does that only apply when you need to shard within tenants?

If each tenant gets an instance I would call that a “shard” but in that pattern there’s no need for cross-shard references.

Maybe in the analytics stack but that can be async and eventually consistent.