Course contentsShow
← System Design
Lesson 1034 of 1,91926. Idempotency and RetriesFree lesson

Database Patterns for Idempotency

Using unique constraints, upserts, and request tracking tables to implement idempotent database operations.

Database Patterns for Idempotency

What You'll Learn

You'll learn how to use database-level constraints and patterns to guarantee idempotent operations even when requests arrive multiple times.

The Core Challenge

When implementing idempotency, your database schema and operations are your last line of defense. You need patterns that ensure duplicate requests cannot corrupt data or cause unintended side effects, even if application logic fails or requests slip through other checks.

Key Database Patterns

Unique Constraints

Add database-level unique constraints on idempotency keys or natural identifiers. If a duplicate request tries to insert the same key twice, the database itself rejects it. Think of this as a bouncer at a club checking IDs—no duplicates allowed in, period.

Example scenario: An order table with a unique constraint on order_id prevents creating the same order twice, even if two identical requests race to the database.

Upserts (Insert or Update)

Use upsert operations (INSERT ... ON CONFLICT UPDATE in PostgreSQL, MERGE in others) to make writes naturally idempotent. If the record exists, update it to the same state. If not, insert it. Either way, the final state is identical.

This is like saving a document—clicking "Save" multiple times doesn't create multiple files; it just ensures the latest version is stored.

Request Tracking Tables

Maintain a dedicated table storing processed idempotency keys with their results. Before processing any request, check this table. If the key exists, return the cached result instead of re-executing.

Structure: Store idempotency_key, status, result, and timestamp. This table becomes your single source of truth for "have we seen this before?"

Why This Matters

These patterns move idempotency guarantees from application code into the database layer, where ACID properties provide stronger consistency guarantees across concurrent requests.

Key Takeaway: Database-level unique constraints, upserts, and request tracking tables transform idempotency from a coding problem into a data integrity guarantee enforced by your persistence layer.