Postgres Counter Avoiding Race Conditions

Question

I'm working on a project where I have something that can only be used a given number of times. Imagine the postgres table (table_1) is like this:

id | maximum_uses

Where another table (table_2) tracks the uses like below, where the used_by is a user:

id | used_by

Before adding anything to table_2, I'd like to confirm the rows of table_2 with the corresponding ID do not exceed the maximum uses in table_1. The question I have is: how can I ensure I don't go above the maximum uses without bottlenecking my code through isolation mode serialized?

Things I've thought of and potential reasons they might not work:

1. Tracking the items through a queue or something similar, having a "physical" number of items that will be used. This might be problematic because there are fail conditions where I'll have to figure out how to put the item back on the queue.
2. Adding in a query where I lock the second table for updates on the ID that's being used: SELECT * FROM table_2 FOR UPDATE. After performing this query I'd add my row in. My only concern here is locking the table means someone else will have to wait when updating, and performance times might lag.
3. Potentially figuring out a way to add a constraint on the table that will fail queries if there are already too many rows in table_2, but I can't seem to determine how constraints work with race conditions.

Does anybody have any thoughts?

Answer 1

You either have to use SERIALIZABLE or you have to serialize data modifications with locks. The latter would probably mean that you have to keep an array of table_2 in table_1 (which would render the count unnecessary). You would need a trigger to keep the redundant data in the tables synchronized (as you do now).

No matter what, data modifications will be serialized. You can benchmark which solution is better. Usually the result depends on the frequency of collisions.