Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL internals: Things to know about update statements

patrick.engineering

Post reply on HN
Print view View original

11–20 of 61 posts

Re: PostgreSQL internals: Things to know about update statements

#11

Post by Rapzid »

arthurcolle wrote: post #9
koolba wrote: post #5

Earlier quoted context omitted.

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.

Re: PostgreSQL internals: Things to know about update statements

#12

Post by GauntletWizard »

koolba wrote: post #5
GauntletWizard wrote: post #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 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 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!)

Re: PostgreSQL internals: Things to know about update statements

#13

Post by pmontra »

chrisandchris wrote: post #6
GauntletWizard wrote: post #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 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…

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.

Re: PostgreSQL internals: Things to know about update statements

#14

Post by teaearlgraycold »

koolba wrote: post #5
GauntletWizard wrote: post #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 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.

SELECT FOR UPDATE SKIP LOCKED is amazing

Re: PostgreSQL internals: Things to know about update statements

#15

Post by tybit »

GauntletWizard wrote: post #12
koolba wrote: post #5

Earlier quoted context omitted.

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

Re: PostgreSQL internals: Things to know about update statements

#16

Post by ComodoHacker »

rowls66 wrote: post #3

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.

Re: PostgreSQL internals: Things to know about update statements

#17

Post by ppati000 »

koolba wrote: post #5
GauntletWizard wrote: post #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 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.

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.

Re: PostgreSQL internals: Things to know about update statements

#18

Post by silvestrov »

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.

Re: PostgreSQL internals: Things to know about update statements

#19

Post by drdaeman »

koolba wrote: post #5
GauntletWizard wrote: post #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 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.

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

Re: PostgreSQL internals: Things to know about update statements

#20

Post by idorosen »

silvestrov wrote: post #18

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 t…

You can already achieve this with CTEs.
Post reply on HN