Idempotent Database Operations

In databases, an idempotent operation is one that, when repeated, produces the same result as a single execution. A common idempotent pattern is the UPSERT (update-or-insert) operation. For example, running INSERT … ON CONFLICT UPDATE with the same values multiple times will not create duplicates – it either inserts a new row or updates the existing row, but repeating it makes no further change. In contrast, a naïve INSERT statement without conflict handling will create duplicate rows on each execution, which is non-idempotent.

Similarly, UPDATE queries that set a field to a fixed value (e.g. SET status = 'active') are idempotent: once the value is set, running the update again has no new effect. Deleting a row by its primary key is idempotent as well – after the first delete, further deletes simply find nothing to remove. Database constraints and keys also help: for instance, a unique constraint can silently ignore or reject duplicate inserts. Transactions contribute by making operations atomic: if a transaction fails and aborts, no partial changes are committed, so retrying the transaction will not cause double effects.

Upserts: In SQL and NoSQL, upsert commands ensure one-time insertion. Re-applying them with the same data yields the same end state.


Fixed updates: Setting a field to a constant (e.g. balance = 100) is idempotent; repeating it doesn’t change anything beyond the first update.


Idempotent deletes: DELETE FROM table WHERE id = X is idempotent – once X is gone, repeating the delete does nothing.


Unique checks: Before inserting, the code can check for an existing key or use unique constraints, so duplicates are prevented (e.g. using a transaction ID to detect a repeated payment request).


Retry-safe transactions: If a transaction is retried (after a rollback or network error) and it commits again, the final state is the same as if it were only run once.

Cockroach Labs points out that some database operations are “naturally idempotent” (like updating customer info) while others (like subtracting funds) are not without safeguards. By using upserts, unique constraints, and careful transaction design, database writes can be made effectively idempotent.