41–50 of 61 posts
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…
#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…
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 ).…
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...
Earlier quoted context omitted.
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
> 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…
Earlier quoted context omitted.
You can already achieve this with CTEs.
How?
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
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.
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.
Earlier quoted context omitted.
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 ).…
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…
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.
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.
> 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…