Idempotency keys in Go and Postgres: a retry that never charges twice

A client sends POST /orders. The server creates the order and starts writing the response. The connection drops. The client sees a timeout, so it does the only sensible thing: it sends the request again.

Now there are two orders. Nothing failed. Every component behaved correctly, and the customer was still charged twice.

GET, PUT and DELETE are safe to repeat by definition. POST is not. The fix is to let the client name the attempt, so the server can recognise a repeat.

The contract

The client generates a unique value per intent (a UUID is fine) and sends it as a header:

POST /ordersIdempotency-Key: 8f14e45f-ceea-4672-a1c2-2ae5d1a0b1f7

The server promises: same key, same request, same response. The first call does the work. Every later call returns the stored result without doing it again.

Two rules complete the contract. Same key with a different body is a client bug, so answer 422. And a key belongs to one user, so the primary key is (user_id, key), never key alone.

One table

CREATE TABLE idempotency_keys (    user_id      bigint      NOT NULL,    key          text        NOT NULL,    request_hash bytea       NOT NULL,    status       int,    response     bytea,    created_at   timestamptz NOT NULL DEFAULT now(),    PRIMARY KEY (user_id, key));

request_hash is a SHA-256 of the method, path and body. status and response stay NULL until the work is finished.

The trick: the key lives in the same transaction

The tempting design is a check-then-act: look the key up, and if it is missing, do the work and save it. That has a race. Two retries arrive together, both see nothing, both do the work.

Let the database be the lock instead:

func (s Store) Do(	ctx context.Context, userID int64, key string, hash []byte,	work func(tx *sql.Tx) (status int, body []byte, err error),) (int, []byte, error) {	tx, err := s.db.BeginTx(ctx, nil)	if err != nil {		return 0, nil, err	}	defer tx.Rollback()	res, err := tx.ExecContext(ctx, `		INSERT INTO idempotency_keys (user_id, key, request_hash)		VALUES ($1, $2, $3)		ON CONFLICT (user_id, key) DO NOTHING`, userID, key, hash)	if err != nil {		return 0, nil, err	}	if n, _ := res.RowsAffected(); n == 0 {		// Someone already owns this key. Replay what they stored.		var prev, body []byte		var status int		err := tx.QueryRowContext(ctx, `			SELECT request_hash, status, response			FROM idempotency_keys WHERE user_id = $1 AND key = $2`,			userID, key).Scan(&prev, &status, &body)		if err != nil {			return 0, nil, err		}		if !bytes.Equal(prev, hash) {			return 0, nil, ErrKeyReuse // handler maps this to 422		}		return status, body, nil	}	status, body, err := work(tx) // the order INSERT happens in this same tx	if err != nil {		return 0, nil, err // rollback also frees the key: the client may retry	}	if _, err := tx.ExecContext(ctx, `		UPDATE idempotency_keys SET status = $3, response = $4		WHERE user_id = $1 AND key = $2`, userID, key, status, body); err != nil {		return 0, nil, err	}	return status, body, tx.Commit()}

Why this closes the race: ON CONFLICT DO NOTHING on a row another transaction has inserted but not yet committed waits for that transaction to finish. If it commits, the second request gets zero rows affected and reads the stored response. If it rolls back, the second request inserts the key itself and does the work. Nobody sees a half-finished state, so there is no "in progress" status to invent.

And because the key and the order commit together, you cannot end up with an order and no key, or a key pointing at an order that was never created.

Where it stops working

This only protects what happens inside your own database transaction. If work calls a payment provider, the charge happens outside Postgres, and a rollback cannot take it back. For that call, forward the same key to the provider, since most payment APIs accept their own idempotency key for exactly this reason.

Two smaller things. The transaction stays open while work runs, so keep it short and never hold it across a slow network call. And keys should expire: a nightly DELETE ... WHERE created_at < now() - interval '24 hours' is enough for most clients.

A retry should be boring. The whole point of this table is that the second request finds the first one's answer and has nothing left to decide.