The waitlist position problem

Position numbers seem simple. They're just integers. But they're one of the more interesting distributed systems problems you'll encounter on a small scale.

#The naive implementation

INSERT INTO signups (email, position)
SELECT 'user@example.com', COALESCE(MAX(position), 0) + 1
FROM signups
WHERE waitlist_id = 'abc';

This works correctly when requests arrive one at a time. When two requests arrive simultaneously, both execute the SELECT before either INSERT commits. Both read the same MAX(position). Both get assigned the same number.

You've now given two different people position 47. Neither knows about the other. One will eventually notice. The other might not, but the integrity of your queue is already gone.

#The race condition, illustrated

t=0ms  Request A: SELECT MAX(position) → 46
t=0ms  Request B: SELECT MAX(position) → 46
t=1ms  Request A: INSERT position = 47 ✓
t=1ms  Request B: INSERT position = 47 ✓  ← duplicate

Both inserts succeed. Both users receive a welcome email telling them they're #47. Your waitlist has two people in the same slot.

#How to fix it

Option 1: Sequence / SERIAL column. Let the database assign the position via an auto-increment. The database guarantees uniqueness. Works well when position is just a row counter, but breaks down if you want positions to be per-waitlist (you'd need a sequence per waitlist, or derive position from row order at read time).

Option 2: SELECT FOR UPDATE. Lock the row or the aggregate before reading it:

BEGIN;
SELECT COUNT(*) + 1 AS next_position
FROM signups
WHERE waitlist_id = 'abc'
FOR UPDATE;

INSERT INTO signups (email, position, waitlist_id)
VALUES ('user@example.com', <next_position>, 'abc');
COMMIT;

The lock prevents concurrent transactions from reading a stale count. The second request waits for the first to commit. Positions come out sequential.

Option 3: Application-level counter. A Redis INCR or a compare-and-swap on a counter row. Fast, but adds a dependency and a consistency boundary between the counter and the database.

#Why the duplicate matters more than you'd think

Duplicate positions break referral logic (who referred whom is ambiguous if positions overlap), make email copy inaccurate ("you're #47" is wrong if someone else is also #47), and make queue management unreliable if you're ever promoting users by position.

More subtly, position duplicates are hard to detect after the fact. The users don't report them. You won't see them in logs. You'll only find them if you run SELECT position, COUNT(*) FROM signups GROUP BY position HAVING COUNT(*) > 1 and wonder why some positions have two rows.

#Enlist's approach

Positions are assigned inside a locked transaction. The response always contains { position, total }, both derived from the committed state of the queue at the moment of insert. Re-submitting the same email within the same transaction returns the existing row, so retries are safe and idempotent.

The practical result: no gaps, no duplicates, no race condition. position means something you can show to a user.