How do I limit the count of rows in a result set in Postgres without unnecessary locking?

Question

This question is inspired by a 'general admission' variant of the common 'event ticketing' System Design interview question. Critically in this version, the user does not select a seat - they only say they want a ticket.

Given a table Reservations with some columns event_id, user_id, paid, hold_expires_at

I want to know how I can do the following:

Count the number of reservations that are either paid or still on hold
Insert a new reservation row into the table if that count is less than N

I know that with READ_COMMITTED isolation level I can't simply run a SELECT COUNT()... query followed by INSERT INTO Reservation VALUES... in a transaction and expect the limit to be enforced. It is not actually clear to me that setting the transaction's isolation level to SERIALIZABLE would even guarantee this in Postgres since from the DB's perspective, I wouldn't expect there to be any dependency detected on between queries in 1) and 2). Maybe if I combined the two queries into one and had a predicate on the INSERT based on the COUNT, that would work? The documentation has an example where a serialization error occurs as a result of inserting the sum of a column in a table back into that same table in two different transactions.

Ideally if there are, say 1000 tickets for sale and 1200 requests came in, they would not all have to execute serially - that is, they would execute concurrently without causing serialization errors until there was contention for the 1000th unsold/unheld ticket.

Also curious about ways to do this outside of Postgres.

"I wouldn't expect there to be any dependency detected on between queries in 1) and 2)" - why not? — Bergi
– Bergi, Commented Apr 28 at 13:36
@Bergi clearly I don't know the details of how this works, but if the check whether the count is greater than 1000 occurs in application code, I don't see how the database detects that the insertion is conditional on the value of the count, unless it's that it's assumed that all queries are dependent on the previous ones within a single transaction. — asantas93
– asantas93, Commented Apr 28 at 15:27
oh I guess if User1 and User2 both try to do this User1's query 2 will affect the result of User2's query 1, and vice-versa...have been thinking about this problem too long — asantas93
– asantas93, Commented Apr 28 at 15:37

Laurenz Albe · Accepted Answer · 2025-04-28 06:53:21Z

2

Using the SERIALIZABLE isolation level will guarantee that your constraint won't be violated. If a transaction succeeds, you are guaranteed that there is an equivalent serial execution order for all transactions, and with serial execution, no anomaly could ever happen.

An alternative would be a SHARE lock on the table, but that is a bad idea, since that lock conflicts with VACUUM.

As a third suggestion, you could serialize the operations explicitly. If you want to do that with database means, you could have a second table that holds the current reservation count. That table gets updated by a deferred constraint trigger whenever you make a reservation, so that that row is not locked any longer than necessary to guarantee serialization. Then a check constraint on that second table will enforce the limit on the reservations.

answered Apr 28 at 6:53

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I limit the count of rows in a result set in Postgres without unnecessary locking?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related