Idempotence in database changes — interview definition
Idempotence means that executing the same database operation multiple times produces the same final state as executing it once.
In other words:
retrying a DB change is safe and does not corrupt data or create duplicates.
Why idempotence matters (very important to say at interview)
Idempotent DB changes are critical because of:
- Retries (network failures, timeouts, client retries)
- At-least-once delivery (Kafka, RabbitMQ)
- Distributed systems
- Crash recovery
- Deployments & migrations
💡 If your operation is not idempotent, retries may silently corrupt data.
Simple examples
❌ Non-idempotent operation
INSERT INTO orders (id, amount) VALUES (123, 100);
If executed twice → duplicate row or constraint violation.
✅ Idempotent operation (UPSERT)
INSERT INTO orders (id, amount)
VALUES (123, 100)
ON CONFLICT (id) DO UPDATE
SET amount = EXCLUDED.amount;First run → inserts
Second run → no state change
Final DB state is the same ✔
Common idempotent patterns in DB
1. Natural or technical idempotency key
CREATE UNIQUE INDEX ux_order_id ON orders(order_id);
Duplicate requests are rejected or merged
Very common in payments, orders, events
2. Upsert instead of insert
MERGE INTO users u
USING (SELECT 1 AS id) s
ON (u.id = s.id)
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (1, 'Stanley');
3. State-based updates (not incremental)
❌ Non-idempotent:
UPDATE account SET balance = balance - 100;
✅ Idempotent:
UPDATE account SET balance = 900
WHERE id = 1;
4. Idempotent deletes
DELETE FROM sessions WHERE session_id = 'abc';
Deleting twice → same final state
Delete operations are naturally idempotent
5. Processed-events table (very senior pattern)
INSERT INTO processed_events (event_id)
VALUES ('evt-123')
ON CONFLICT DO NOTHING;Then:
-- apply business logic only if insert succeeded
Used in:
- Kafka consumers
- Outbox / Inbox patterns
- Exactly-once semantics (emulated)
Idempotence vs transactions (important distinction)
- Transaction ≠ idempotence
- A transaction guarantees atomicity
- Idempotence guarantees safe retries
You usually need both.
Typical interview pitfalls ❌
Candidates often say:
- “Idempotence means transaction”
- “Just wrap it in a transaction”
- “Database will handle duplicates”
❌ Wrong.
Transactions do not protect you from retries.
Short interview-ready answer (say this)
Idempotence in database changes means that applying the same change multiple times results in the same final state as applying it once.
It’s crucial for retries, message-based systems, and distributed environments.
Common techniques include upserts, unique constraints with idempotency keys, state-based updates, and tracking processed events.