3 ms·
The math on #1 doesn't check out. If you update 2 bytes in the record only 28 are still written. But are only 28 written in either case? Does Postgres not write
by Rapzid 3y ago
The math on #1 doesn't check out. If you update 2 bytes in the record only 28 are still written. But are only 28 written in either case? Does Postgres not write out entire pages?
It gets much more complicated when you consider full page writes, checkpoints, HOT, and multiple rows being updated at once.
For #3 it really depends on a lot of factors what the best approach is and if it's even worth your time.. But a typical approach that isn't mentioned is to just make sure you acquire locks in a deterministic order between transactions you don't want to deadlock each other. This will reduce concurrency(which is the entire point of the deadlock detection feature), but you can push the queue into the DB(behind the lock) which will minimize latency if you are done round-tripping.
- wisty 3y ago8 bytes of new data but 8x28 = 224 bytes = 200 data + 8 date + 16 for id. The 28 is a factor (×).
- Rapzid 3y agoI should have used more precise language; I mean the bytes written to disk don't work out with such a simple formula. Missed the footnote which discusses this, but there are even more things to consider such as how many rows are being updated at once and etc.
- Rapzid 3y agoTo add to this for anyone reading later.. The OS doesn't write bytes to the storage; it writes blocks. For modern drives the sector size is 4k, and the block io size is likely to be 4k unless utilizing 512b emulation. So, the OS will always send 4k minimum writes to the storage device. In this case updating just the 8bytes would still result in a 4k write; at least to the wal then again to update the page plus index updates and etc.