PostgreSQL internals: Things to know about update statements
patrick.engineering
PostgreSQL internals: Things to know about update statements
1–10 of 61 posts
Re: PostgreSQL internals: Things to know about update statements
#2#2 is something that nearly no application I know handles, and it saddens me, but not to the extent that it seems to anger many of my fellows. It's perfectly okay to do so for YouTube, it's not worth bothering for many applications of less concurrency than that. Still, it fairly offends me that most applications exist in the middle, where they don't have read isolation, or use logical additions, or have a separate process that cleans up the followers at a point-in-time, so that it's bounded to a day's worth of updates.
Re: PostgreSQL internals: Things to know about update statements
#3In the Lost Updates section, a more straight forward solution is to use the FOR UPDATE clause in the first select statement. This locks the record and prevents concurrent updates.
Re: PostgreSQL internals: Things to know about update statements
#4The 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.
Re: PostgreSQL internals: Things to know about update statements
#5#2 is something that nearly no application I know handles, and it saddens me, but not to the extent that it seems to anger many of my fellows. It's perfectly okay to do so for YouTube, it's not worth bothering for many applications of less concurrency than that. Still, it fairly offends me that most applications exist in the middle, where they don't have read isolation, or use logical additions, or have a separate pr…
This is transaction 101. You simply lock the row via a SELECT … FOR UPDATE when you read the value.
Any application that actually cares about consistency of its data must be doing this.
Or just do it one step so the update increments the value in place. That way you have an implicit lock.
Re: PostgreSQL internals: Things to know about update statements
#6#2 is something that nearly no application I know handles, and it saddens me, but not to the extent that it seems to anger many of my fellows. It's perfectly okay to do so for YouTube, it's not worth bothering for many applications of less concurrency than that. Still, it fairly offends me that most applications exist in the middle, where they don't have read isolation, or use logical additions, or have a separate pr…
While I agree, it's a pretty bad example for demonstrating this issue, because an error on "follower_count" will probably not cause any harm (youtube example). Also, the value can be recalculated at all times (maybe not feasable).
Any other example that comes to my mind will not depend on the "state of the apps memory" for a sensitive value, because that would be bad design too. Then the sensitive value must be the aggregate of other values within the database and with each commit, the equation must be balanced.
Re: PostgreSQL internals: Things to know about update statements
#7The 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 typi…
8 bytes of new data but 8x28 = 224 bytes = 200 data + 8 date + 16 for id.
The 28 is a factor (×).
Re: PostgreSQL internals: Things to know about update statements
#8The 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 typi…
8 bytes of new data but 8x28 = 224 bytes = 200 data + 8 date + 16 for id. The 28 is a factor (×).
I 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.
Re: PostgreSQL internals: Things to know about update statements
#9#2 is something that nearly no application I know handles, and it saddens me, but not to the extent that it seems to anger many of my fellows. It's perfectly okay to do so for YouTube, it's not worth bothering for many applications of less concurrency than that. Still, it fairly offends me that most applications exist in the middle, where they don't have read isolation, or use logical additions, or have a separate pr…
This is transaction 101. You simply lock the row via a SELECT … FOR UPDATE when you read the value. Any application that actually cares about consistency of its data must be doing this. Or just do it one step so the update increments the value in place. That way you have an implicit lock.
I haven't used this, but I see its in Oracle and MySQL - does this exist in Postgresql or is this just the same as wrapping in a transaction where you're selecting on the row by ID or something? Just curious
Re: PostgreSQL internals: Things to know about update statements
#10#2 is something that nearly no application I know handles, and it saddens me, but not to the extent that it seems to anger many of my fellows. It's perfectly okay to do so for YouTube, it's not worth bothering for many applications of less concurrency than that. Still, it fairly offends me that most applications exist in the middle, where they don't have read isolation, or use logical additions, or have a separate pr…
While I agree, it's a pretty bad example for demonstrating this issue, because an error on "follower_count" will probably not cause any harm (youtube example). Also, the value can be recalculated at all times (maybe not feasable). Any other example that comes to my mind will not depend on the "state of the apps memory" for a sensitive value, because that would be bad design too. Then the sensitive value must be the a…
That's what makes it such a perfect example. It's inconsequential, but also simple. A SQL statement `update follower_count = follower_count +1` is incredibly cheap in SQL, even as part of a transaction. Accepting that the follower counts are laggy is incredibly acceptable. Repeatable read is expensive, but cheap enough for many, many applications.
The problem is that most applications that I've worked on do none of the above - they accept the buggy behavior in the concurrency case. As part of the "post reconciliation" solution, that's fine, but it's a bug that will never get fixed.
Perhaps worst is that there's an entire class of people - probably autistic, and often not people I want to work with for one obvious reason or another[1] - who simply have to be kept in the dark about long-standing minor bugs. These are known inconsistencies in the data that don't matter but that they'll pitch a fit to fix because they can't see.the difference in constraints like "the follower count will be an integer" and "The (recorded, but fundamentally cached) follower count will equal `SELECT COUNT(*) FROM followers WHERE account = ${user}`
[1] Really, just one reason - they reject rhetorical logic, focusing on formal logic for anything that catches their attention, to the point they are happier with atrocities than morality.