> 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?
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.
> 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?
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.
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?
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.
3 replies →
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.
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?
2 replies →
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.