A unique index prevents concurrent requests from creating multiple rows for the same indexed identity. It is an essential database safeguard, but it does not by itself make an endpoint fully retry-safe: the application must handle conflicts, and often must save and replay the original result for a repeated request.
What a unique index does—and what it does not
A unique index makes the database enforce a rule such as “one operation per caller and request key.” If two requests race to insert the same key, the database’s uniqueness check accounts for concurrent transactions instead of relying on each request to check first. PostgreSQL documents this behavior in its index uniqueness checks.
As an Amazon Associate I earn from qualifying purchases.
This is stronger than application code that first asks whether a row exists and then inserts it. Two requests can both observe no row before either writes. Put the identity rule on the write path with a unique index or constraint, then treat a uniqueness conflict as an expected branch.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallThe index only prevents duplicate values for the indexed identity. It does not ensure that an external payment, email, or other side effect happens once; nor does it automatically return the same HTTP status and response body to a retry. Those behaviors require application-level idempotency.
#1 Best Overall
- API Design Patterns
- ABIS BOOK
- Manning Publications
Designing a retry-safe create endpoint
- Choose a stable operation key. Use an idempotency key supplied by the client or another durable identity for the intended operation. Scope it appropriately, for example by caller and operation type. The client must reuse the same key after a timeout; generating a fresh key for each attempt defeats deduplication.
- Enforce uniqueness in storage. Store the key under a unique constraint or index. Choose the exact business identity deliberately: composite keys, nullable columns, partial indexes, and partitioning can change which rows the database considers duplicates. PostgreSQL documents these index semantics in CREATE INDEX.
- Record the operation and its outcome. Persist the operation state and enough response information to answer a duplicate request. When the local effect and idempotency record live in the same database, write them in one transaction so a crash cannot commit one without the other.
- Handle the conflict by reading the existing operation. Return its saved result if complete, or its current state if still in progress. Do not blindly repeat the effect just because the insert lost the uniqueness race.
- Define behavior for unfinished work. If an operation is still pending, specify whether the retry receives a pending response, waits, or initiates a safe recovery path. The database index prevents a second record, but the application still has to resolve the first operation’s state.
Use the database conflict branch, not a check-then-insert
In PostgreSQL, INSERT ... ON CONFLICT makes the uniqueness conflict explicit. DO NOTHING skips the proposed insert; DO UPDATE provides an atomic insert-or-update outcome, barring independent errors. See the PostgreSQL INSERT documentation for the syntax and conflict-target behavior. That documentation URL is for PostgreSQL 19; verify the documentation for the deployed server version before relying on version-specific details.
Conflict handling is not the same as replaying a response. An upsert can atomically update a row, but a retry-safe API may instead need to fetch the pre-existing operation and return the result originally associated with its key. The correct branch depends on the endpoint’s semantics.
Rank #2
Unique identity and saved idempotency results are different
A unique index answers: “Can there be more than one row with this identity?” A stored idempotency result answers: “What should this retry receive?” Stripe’s documented pattern saves the first result associated with a key and compares parameters when that key is reused. If the parameters differ, Stripe rejects the reuse; some validation failures and concurrent conflicts are not saved because endpoint execution has not begun. See Stripe’s idempotent requests documentation.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Retention is part of the contract. In Stripe’s 2025 documentation snapshot, keys may be pruned automatically once they are at least 24 hours old; a later retry after pruning can be treated as a new request. A service must therefore define how long its own keys and results remain valid, and clients should not assume an expired key still protects against a repeat effect.
When work crosses a database and an external service
A local database transaction cannot atomically include a remote service’s effect. If the endpoint writes a local row and then calls a payment provider or another API, a timeout can leave the caller uncertain whether the remote operation happened. Use the remote service’s idempotency mechanism when available, and reuse exactly the same key for every attempt. AWS’s idempotency and retry guidance likewise emphasizes generating external idempotency tokens once and reusing them.
Also define reconciliation for work whose outcome is ambiguous: retain a pending state, query the remote system when possible, and avoid starting a second operation until the first is resolved. A local unique index protects local identity; it cannot independently guarantee exactly-once behavior across systems.
How the approach differs by storage service
| Concern | PostgreSQL-style relational storage | DynamoDB transactions |
|---|---|---|
| Identity and conflict enforcement | A unique constraint creates a unique B-tree index; an insert can name a conflict target and choose a conflict action. PostgreSQL CREATE INDEX and INSERT. | TransactWriteItems accepts a client token for idempotent transaction requests. The cited transaction documentation does not describe it as a direct equivalent to a general relational unique index. DynamoDB transaction behavior. |
| Conflict or retry outcome | DO NOTHING skips the insert; DO UPDATE atomically inserts or updates. The application may still need to load and replay a saved operation result. |
Reuse the same client token only with identical request parameters during its validity window; after that, the same token is treated as a new request. |
| Deduplication window | Not stated as a built-in time limit for a unique index; the application controls how long it retains idempotency records. | A transaction client token is valid for 10 minutes after the request finishes, according to AWS’s transaction documentation. |
| Transaction scope and size | Not stated as a fixed limit in the cited PostgreSQL sources; depends on the database and application design. | AWS documents a maximum of 100 distinct items and 4 MB per transaction, with transactional guarantees in the Region where the write originates. DynamoDB constraints and transaction behavior. |
| Ambiguous error handling | Handle the database transaction outcome according to the connection and transaction state; the cited sources do not establish a universal retry policy for all errors. | A single-item write returning HTTP 500 may have succeeded or failed. AWS advises checking the resulting state or using a conditional expression before retrying; transactional writes support idempotent retries. DynamoDB error handling. |
Service-specific details that change retry behavior
PostgreSQL index creation and deployment
Building a unique index concurrently can avoid blocking writes for the full build, but it requires multiple scans and may leave an invalid index if the build fails. Check the migration’s completion and index validity, and follow PostgreSQL’s documented recovery steps rather than assuming a failed build left no trace: CREATE INDEX.
Recommended Free Tools
Stripe idempotency keys
Stripe compares parameters when a key is reused and rejects a mismatch. It saves a result once endpoint execution begins, while validation failures and certain concurrent conflicts are not saved and can be retried. Keys may be removed after they are at least 24 hours old, so a key’s protection is not indefinite. Stripe’s documentation.
DynamoDB client tokens and uncertain writes
For TransactWriteItems, AWS documents a 10-minute client-token validity period after the request finishes. The same token must be used with identical request parameters within that window; after it, the request can be treated as new. Transactions are ACID only in the originating Region and are limited to 100 distinct items and 4 MB. DynamoDB transactions and constraints.
For an individual DynamoDB write that returns HTTP 500, the server may have applied the write even though the caller received an error. Read the resulting state or use a conditional expression before retrying rather than assuming failure. AWS error-handling guidance.
Quick Recap
Implementation checklist
- The retry uses the same operation key, scoped so unrelated callers or actions cannot collide.
- A database uniqueness rule enforces the intended identity at write time.
- The operation’s durable local effect, state, and replayable result are committed together where possible.
- A duplicate key loads the existing operation or result instead of repeating a side effect.
- Key retention is explicit and aligned with how long clients may retry.
- Remote calls use the remote service’s idempotency mechanism when available, with the same token reused for each attempt.
- Ambiguous outcomes have a reconciliation path rather than an unconditional retry.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
