Actually transactions can be a secomd-layer safety-net for single-responsibility writers to ensure rollback on eg restarts and consistency on loadbalancer redecisions without having much of an impact on performance, and data integrity is usually quite important.
As long as the database is acid restarts should not be a factor. Data integrity is not helped by transactions, you would need error correcting codes for that. Plus the effect on performance is quite notable on all dbs I’ve worked with.
Restarts in a server between dB updates that in a sane world would be txns I meant (e.g update A, crash so don’t update B). Anyway, in postgres they’re pretty cheap in the absence of actual conflict – more expensive if you have actual cinflicts, obvs.
Well it depends how much data integrity is worth to you, and how your system works. Every write in postgres is already a transaction - when you can get away with simple crud stuff, often there’s nothing to do, you have transactionality already. Transaction isolation levels are where db operation costs might change under concurrent conflicting writes but you can tune that by ensuring single-writer-per-partition or whatever in your server logic and it might add a ms or two. OTOH if you have heavy contestation it can be much more expensive. The performance implications are complicated but can certainly kept to a fraction of overall cost depending on your workload!
But adding it to an 80ms operation is. If your operation is 0.5ms it’s either a read on a small table, or maybe a single write – transaction isolation wouldn’t even be relevant there. You’re right that I did mean consistency rather that integrity though, slip of the terminology, but not really worth quibbling over. The point I meant was that I like my data to make sense, a funny quirk of mine.
I get that this is a joke, but…
… ackshually it should almost never be a transaction only when there’s absolutely no other option, because transactions kill your performance.
Actually transactions can be a secomd-layer safety-net for single-responsibility writers to ensure rollback on eg restarts and consistency on loadbalancer redecisions without having much of an impact on performance, and data integrity is usually quite important.
As long as the database is acid restarts should not be a factor. Data integrity is not helped by transactions, you would need error correcting codes for that. Plus the effect on performance is quite notable on all dbs I’ve worked with.
Restarts in a server between dB updates that in a sane world would be txns I meant (e.g update A, crash so don’t update B). Anyway, in postgres they’re pretty cheap in the absence of actual conflict – more expensive if you have actual cinflicts, obvs.
“Pretty cheap” is very subjective…
Well it depends how much data integrity is worth to you, and how your system works. Every write in postgres is already a transaction - when you can get away with simple crud stuff, often there’s nothing to do, you have transactionality already. Transaction isolation levels are where db operation costs might change under concurrent conflicting writes but you can tune that by ensuring single-writer-per-partition or whatever in your server logic and it might add a ms or two. OTOH if you have heavy contestation it can be much more expensive. The performance implications are complicated but can certainly kept to a fraction of overall cost depending on your workload!
Again, not data integrity (Error correction) but consistency (aCid). Adding two milliseconds to a half a millisecond operation is by no means cheap…
But adding it to an 80ms operation is. If your operation is 0.5ms it’s either a read on a small table, or maybe a single write – transaction isolation wouldn’t even be relevant there. You’re right that I did mean consistency rather that integrity though, slip of the terminology, but not really worth quibbling over. The point I meant was that I like my data to make sense, a funny quirk of mine.
Unless you’re using Firebird (3) in which not using transactions kills your performance