5 ms·
Rls and triggers dont scale either
by polote 1y ago
Rls and triggers dont scale either
- shivasaxena 1y agoYeah, 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.
- GuinansEyebrows 1y agothat, and keeping your business logic in the database makes everything more opaque!
- lelanthran 1y ago> 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?
- GuinansEyebrows 1y agoyou make good points; i'm overcorrecting from past trigger abuses :)
- thisoneisreal 1y agoI 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.
- brikym 1y agoHave you tried deferring them?
- candiddevmike 1y agoHow do you handle trigger logic that compares old/new without having a round trip back to the application?
- SoftTalker 1y agoDo it in a stored procedure not a trigger. Triggers have their place but a stored procedure is almost always better. Triggers can surprise you.
- candiddevmike 1y agoI don't follow how you would do that in a stored procedure outside of a trigger.
- const_cast 1y agoI think instead of performing an INSERT you call a stored proc that does the insert and some extra stuff.
- shivasaxena 1y agoYes, 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.
- lelanthran 1y ago> 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?
- nine_k 1y agoHmm, imho, triggers do scale, they are just slow. But as you add more connections, partitionss, and CPUs, the slowness per operation remains constant.
- ants_a 1y agoTriggers 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.
- Spivak 1y agoNeither 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.
- mulmen 1y agoDoes 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.