PostgreSQL internals: Things to know about update statements | Hacker News Reader

PostgreSQL internals: Things to know about update statements

patrick.engineering

238 pointsppati00057 comments

> This behavior may well be interpreted as a violation of the SQL standard.

no it may not

this is fully standard compatible behavior and even expected behavior for _any_ SQL database implementing a read committed transaction insulation level

also it's a problem which on a theoretically level is not solvable with how SQL works, hence why on stricter isolation levels like serializable committing the transaction in such a situation will fail (in any SQL database) and needs to be retired. (Through in some unusual setups the db might be able to translate retry the transaction by itself, through at the cost of a ton of drawbacks)

Anyway reading the postgres documentation is always a good idea, it's not perfect but pretty good.

Yeah, I thought the other two sections were good but the "lost updates" section was just wrong, and in my opinion bizarre. Bizarre because the author does talk about different isolation levels - what does he suppose the purpose of those different isolation levels to actually be if he thinks READ COMMITTED should behave the same as SERIALIZABLE?

In 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.

When you're incrementing by using UPDATE ... SET value = value + 1, the database holds the locks for the minimum time needed. Everything else is less efficient.

In more complex scenarios, FOR UPDATE is the solution.

When there's a big chance of multiple tasks grabbing the same rows, processing them, then updating them, marking them for update since the beginning is better. E.g. a message queue like structure where messages should be processed only once.

Not sure what you mean by "the database holds locks for the minimum time needed." Locks are always held until the transaction commits.

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.

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.

To 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.

#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.

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

Pretty much every RDBMS has this: https://www.postgresql.org/docs/current/explicit-locking.htm...

There are multiple flavors in PostgreSQL with different locking semantics. And the exact locking semantics may differ between database(for instance postgres does not have gap locks, while MySQL does) so you'd have to read the docs if that detail matters to you.

it does exist and I think it was even added to the SQL standard in some update

what FOR UPDATE does is (simplified) to lock a mutex for each row the select statement returns which are released once the transaction commits

this has the effect that parallel running transactions have to wait until the new computed value is visible before reading it and in turn there is no problem with lost updates

just to be clear this is a very simplified explanation in many ways

I don't disagree, but it's so uncommonly used, and I don't know any orm that will do it for you- it'd be easy enough to precompile, but that'd require compiler integration. (Or a hit to every select statement - which probably wouldn't matter for most apps!)

JOOQ handled this nicely.

https://www.jooq.org/doc/latest/manual/sql-execution/crud-wi...

ActiveRecord supports it: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pes...

QueryDsl have it, it also has FOR SHARE.

SELECT FOR UPDATE SKIP LOCKED is amazing

Author here. Thank you (and others) for mentioning SELECT FOR UPDATE. Definitely missing at least a footnote in the article.

Note that "one step" updates (e.g., SET followers_count = followers_count + 1) are still not in place. They are regular transactional updates that rewrite the entire row. Still, they can achieve higher throughput because there is no application/database roundtrip between locking and committing.

If it's not an indexed column (and your page isn't too full), it can do a HOT update, which is effectively the same as an in-place write if you're flushing to disk at page granularity anyway unless you have really wide rows.

Even better, FOR NO KEY UPDATE is probably a good option here, since no key values are updated.

However, SELECT FOR UPDATE doesn't lock not-yet-existing rows in PostgreSQL, like it does in MySQL with gap locks. If for example, the transaction performs either an UPDATE or an INSERT based on whether the SELECT FOR UPDATE found or didn’t find a row, then two concurrent executions of that transaction can run into a conflict or duplicate INSERT in PostgreSQL, whereas in MySQL the second execution would block on the SELECT FOR UPDATE of the first execution even when there are no matching rows (but the first execution then will/might create some).

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.

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.

The example with follower_count shows how to use the +1 technique to do without locking. That's OK IMHO. It's not possible to use that technique when updating other type of data. Example

  select * from issues where id = 123;
  -- do something in app
  update issues set status = 'done' where id = 123;

There you can lose a status update if two people happen to work on the same issue at the same time with two different ideas of how to update it.

Or the classic bank account balance update. Both must be solved with a SELECT FOR UPDATE. That should be in the demo page of every ORM, to be sure that people that don't know SQL don't make that kind of mistakes.

> The example with follower_count shows how to use the +1 technique to do without locking..

Without explicit pessimistic locking. There are always locks. More guarantees, more locks.

That issue example can be tackled with optimistic concurrency controls depending on the constraints. The issue can be checked out with an UPDATE .. WHERE .. RETURNING ..

I'm convinced that this is due to developers over-relying on ORMs instead of delving into raw SQL.

I'll give another, different but somewhat related example: consider a worker that works on a batch of rows, and wants to update each of the rows in the batch, each row with different data for that row. In raw SQL, this is simple enough with the UPDATE FROM VALUES pattern (see e.g. https://stackoverflow.com/a/18799497 ).

There's no support for this in Prisma, for example: https://www.prisma.io/docs/orm/prisma-client/queries/crud#up... . Most developers would wrap prisma.foo.update in an application-level loop, or possibly wrap all the individual updates in a single prisma.$transaction.

I think maybe with Django, you could use a QuerySet with an F expression to do this? [0] Agreed that it's trivial in pure SQL. As a counter though, having worked at places using an ORM (mostly Django), and also where everything was raw SQL, the latter winds up having WILDLY inefficient schema and queries that the DB team (me) has to fix later.

I lost any respect for Prisma when I learned it doesn't do JOINs in the DB. [1]

[0]: https://docs.djangoproject.com/en/5.0/ref/models/expressions...

[1]: https://github.com/prisma/prisma/issues/5184

> raw SQL... winds up having WILDLY inefficient schema and queries that the DB team (me) has to fix later.

I won't dispute this, I'll just point out that (a) premature optimization is the root of all evil, (b) because it's raw SQL, it's easier to reason about and fix. Inefficient queries aren't really a problem in the early days when your whole dataset doesn't even add up to a gigabyte.

The queries aren't necessarily a problem early on (although I've seen OFFSET/LIMIT used for pagination, which is just... no), but schema is sticky. Terrible decisions made early on only become harder to fix as the data set grows.

If you start using locking, you'll encounter many more serialization failures. Similarly for higher isolation levels. This means that you need to retry transactions, and doing that can be quite hard. Many years ago, I wrote some helper code to retry transactions on transient failures for PostgreSQL (which was unnecessarily hard at the time because the error codes that are eligible for transaction retry were not documented clearly). But even with that taken care of, you still had to think carefully about non-transactional side effects (such as sending mail) when writing application logic. A neat side effect was that you could restart the PostgreSQL server without impacting running applications (and today, you could probably even kexec a new kernel before the application timeout kicks in).

I suspect with the current preferences to avoid exceptions, writing for automated retry becomes quite a bit harder. And I couldn't really get automated transaction retry to work for SQLite while still caching statement handles, treating them as prepared statements.

That's why automated retry on concurrency failures should be a built-in feature of the database (or at least of the DB client). It should be easy to register your own commit (for side effects that can be deferred until success) or abort (for side effects that can't be deferred but can be undone) handlers for a given transaction.

I don't think you can have automated retry because if the application logic lives outside the database and is not subject to its transaction processing (which is the common programming pattern today, I think), the database has to arrange for a re-run of that logic if any of the observed database state changes (such as the followers_count returned from the SELECT statement in the example). This means that the code implementing the application code has to be ready to execute multiple times without ill effects, and application programmers need to be aware of that.

That's true, that's why the client API should allow you to submit a callable object of some sort (e.g. a C++ lambda) that is automatically wrapped in a DB transaction and allows you to register commit and abort handlers as I described. Here's such an API that I'm currently working on: https://senderista.github.io/atomik-website/.

One thing I'd really like PostgreSQL to add is LIMIT on update statement as this makes batching easier. E.g.

    UPDATE user_profile
    SET followers_count = 0
    WHERE followers_count IS NULL
    LIMIT 10000

I don't care which rows gets updated, only that no more than 10000 rows get updated. After each UPDATE I will COMMIT and then repeat as long as the UPDATE returns a positive number of rows updated.

This makes it much easier to make small-ish batch updates and avoid locking all rows in the table.

You can already achieve this with CTEs.

Can you really run updates on CTEs that are simple projections and selections? TIL.

No, you run a SELECT … LIMIT in a CTE, and the main query is an UPDATE … JOIN CTE ON mytable.id = CTE.id

But using a Common Table Expression is a workaround, kind of like using a paper towel as a plate. Just give us a plate...

Completely agree, I’d like a LIMIT on UPDATE too

How?

Would you not just be able to do the CTE as you would a correlated sub query? Something like:

    WITH batch AS (
      SELECT id FROM user_profile
      WHERE followers_count IS NULL
      LIMIT 10000
    )
    UPDATE user_profile
    SET followers_count = 0
    FROM batch
    WHERE user_profile.id = batch.id

But with the difference that if you didn’t want to round-trip to the application for each batch you could now make this a recursive CTE?

Do you specifically need a CTE, or wouldn't a nested SELECT also work?

This would also require adding the ORDER BY clause, as there might be applications where the order in which they are updated matters.

Is not any single query in Postgres a transaction? I don't think individual rows would be visible outside that transaction until all are updated.

The question is which rows would be updated by a single execution of a UPDATE...LIMIT query. The order of result rows of a SELECT query is undefined in Postgres unless you add an ORDER BY clause. It is natural to assume that an UPDATE...LIMIT would be similarly affected.

The ”solution” for this is also SELECT FOR UPDATE … LIMIT 1000.

This is one area where MySQL is ahead. UPDATE and DELETE can both use LIMIT.

Database locking can be a bit surprising at times. I recently stumbled over CREATE TABLE IF NOT EXISTS causing client timeouts - you might expect this to be near-instantaneous if the table exists, and it usually is, until it's not... because it always acquires an exclusive table lock, so it's blocked even by readers.

I wouldn’t classify that as “database locking”, but as “idiosyncrasy of a particular database system”. It’s a QoI issue.

I find the following article from some time ago more informative. It contains a very good explanation of isolation levels and how they are implemented in postgresql.

https://news.ycombinator.com/item?id=38684447

> This behavior may well be interpreted as a violation of the SQL standard.

This effectively changed the tone from an informational article to a hit piece. This is a huge accusation which is plain wrong.

It’s not a hit piece, it’s just someone who had the wrong conception of the guarantees (not) provided by transactions, and hasn’t fully come around yet.

Grownups don’t use UPDATE.

Reply on news.ycombinator.com