PostgreSQL Locks Need API Boundaries

Email verification looks like a small feature: create a challenge, send a message, and mark the address as verified. In a busy service, it becomes a concurrency problem. A user can click resend twice, a mobile client can retry after a timeout, and two workers can process the same confirmation at nearly the same time.

The database is usually the last reliable place to enforce the rules. The API should make those rules visible, then keep each transaction short enough that PostgreSQL can do its job without turning normal retries into a lock queue.

Why verification endpoints create contention

A common first implementation reads a user row, checks verified_at, creates a token, and updates the row. That seems reasonable until several requests target the same account. Every request may hold a row lock while doing work that does not belong inside the transaction, such as generating a message payload or calling an email provider.

The result is not always a visible error. More often it is a slow endpoint, a growing connection pool, and retry traffic that makes the original incident worse. A tepm mail com test address can expose the symptom, but the cause is usually the transaction boundary.

The useful question is: what must be true atomically? For a verification challenge, the answer is normally that one active challenge belongs to one account, has one expiration time, and can be consumed once.

Define the database invariant first

Store the challenge separately from the user record. This prevents an ever-growing user row from becoming the coordination point for every email event.

CREATE TABLE email_challenges (
  id uuid PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id),
  token_digest bytea NOT NULL,
  expires_at timestamptz NOT NULL,
  consumed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX one_active_email_challenge
  ON email_challenges (user_id)
  WHERE consumed_at IS NULL;

The partial unique index is a useful guardrail, but expiration is a business rule, not a database clock trigger. The application should decide whether an existing unconsumed challenge is still usable and return a clear result.

For high-volume systems, model resend rate limits independently. Mixing rate-limit counters, provider status, and verification state in one row makes unrelated operations contend with each other.

Keep the transaction small

The transaction should reserve or consume state, then end. It should not call an external provider. A simplified reservation flow is:

1. Begin a transaction with a short statement timeout.
2. Insert the challenge or update the eligible existing record.
3. Commit.
4. Publish an email job using the committed challenge ID.

If the job enqueue must be reliable, use an outbox table in the same transaction. A worker can then deliver the message outside the database lock. This pattern also gives operators a durable record when a provider is unavailable.

Do not keep a transaction open while waiting for SMTP or an HTTP email API. The provider may take seconds; a PostgreSQL row lock should take milliseconds. That difference becomes expensive as soon as traffic arrives in bursts.

Make retries safe at the API boundary

Clients retry because networks fail, not because they understand your state model. Give the resend endpoint an idempotency key and persist the key with the resulting challenge or outbox event.

An idempotency lookup should distinguish three cases:

• The key is new: reserve the operation and create one event.
• The key exists and is complete: return the stored response.
• The key exists but is still running: return a conflict or a short retry response.

For confirmation, consume the challenge with one conditional statement. The affected-row count is the decision:

UPDATE email_challenges
SET consumed_at = now()
WHERE id = $1
  AND consumed_at IS NULL
  AND expires_at > now()
RETURNING user_id;

If no row returns, the token is expired, already consumed, or unknown. Keep those cases externally similar unless support needs a more specific internal reason. This reduces information leakage about valid accounts.

Teams that test the flow in CI can also benefit from replayable email evidence in CI and email timing budgets in browser tests. The important part is testing the state transitions, not relying on a lucky sleep.

Observability and failure handling

Measure lock wait time separately from query duration. A fast query with a long wait is a concurrency problem, while a slow query without waits may need an index or a simpler plan. Useful fields include account ID hash, challenge ID, idempotency key hash, transaction outcome, and provider event ID. Avoid logging raw tokens or email addresses.

Set explicit limits for statement duration and connection acquisition. A request that cannot reserve a challenge quickly should fail predictably; it should not occupy a worker until the client gives up. Add metrics for duplicate idempotency keys, conditional updates affecting zero rows, outbox age, and resend rate-limit decisions.

A practical checklist

• Define the one-active-challenge invariant.
• Put challenges in their own table.
• Use a partial unique index where it expresses the rule.
• Commit before calling an external email provider.
• Use an outbox when enqueue reliability matters.
• Make resend requests idempotent.
• Consume tokens with a conditional update.
• Measure lock waits, not only total latency.
• Never log raw verification tokens.
• Test retries and concurrent confirmations together.

The design is deliberately modest. PostgreSQL handles the atomic decisions, while the API owns retry semantics and the worker owns delivery. Those boundaries keep authentication behavior understandable when the network is slow, the client is impatient, and several requests arrive at once.