PostgreSQL Idempotency Keys: Stop Email Retry Duplicates?

Yes, using idempotency keys in PostgreSQL can stop email retries from sending duplicates—but only if you design the table and the retry logic correctly. The key idea is to store a unique identifier for each logical email request, and before sending, check whether that identifier already exists. If it does, you return the previous result instead of sending again.

What Idempotency Keys Actually Do

An idempotency key is a unique token that represents a single logical operation—in this case, one email send. When your application first attempts to send an email, it generates a key (often a UUID) and stores it in a database table along with the email details and the result. If the request fails due to a network timeout or a server crash, the retry uses the same key. The database then tells you whether that key was already processed. If it was, you know the email was already sent (or at least already attempted), and you don’t send it again.

This pattern is common in payment APIs (Stripe uses it) and works just as well for emails. The crucial part is that the check and the insert must be atomic—otherwise two concurrent requests with the same key could both pass the check and both send.

How to Implement Idempotency Keys in PostgreSQL

Here is a concrete implementation using a table and a unique constraint:

1. Create a table to store email attempts:

CREATE TABLE email_attempts (
    idempotency_key UUID PRIMARY KEY,
    recipient_email TEXT NOT NULL,
    subject TEXT NOT NULL,
    body TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

2. Before sending, attempt to insert the key with status ‘pending’:

INSERT INTO email_attempts (idempotency_key, recipient_email, subject, body)
VALUES ($1, $2, $3, $4)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;

If the insert returns a row, the key is new and you proceed to send the email. If it returns no row, the key already exists—so you fetch the existing row and return its status instead of sending again.

3. After a successful send, update the status to ‘sent’:

UPDATE email_attempts SET status = 'sent' WHERE idempotency_key = $1;

If the send fails, you can either delete the row or leave it with status ‘failed’ and retry with the same key—but then you need to handle the case where the email was actually sent but the response was lost. That is a classic distributed-systems problem.

Why a Unique Constraint Alone Isn’t Enough

A unique constraint on the idempotency key prevents duplicate inserts, but it doesn’t prevent duplicates if your application logic has a race condition. For example, if two retry requests arrive at the same time, both might run the SELECT check, both see no existing key, and both send. The fix is to use the atomic INSERT … ON CONFLICT DO NOTHING as shown above, which guarantees that only one request gets the green light to send.

Another common mistake is to use a key that isn’t truly unique—like the recipient’s email address alone. If you send two different emails to the same person, they would collide. The key must be unique per logical email, not per recipient. A UUID generated at the time of the request is the safest choice.

What About the “At Least Once” Problem?

Even with idempotency keys, you can still get duplicates in one specific scenario: the email provider receives your send request, sends the email, but the response is lost (network timeout). Your application sees a failure, retries with the same key, and the idempotency check says “already processed” because you stored the result. But you stored the result as ‘failed’ because you never got the success response. If you then retry and the check sees ‘failed’, you might send again—causing a duplicate.

To handle this, you have two options:

• Treat ‘failed’ as final and do not retry with the same key. This avoids duplicates but risks losing the email entirely.
• Use a status like ‘unknown’ when the send times out, and have a manual reconciliation process to check with the email provider whether the email was actually sent.

Most email APIs (like SendGrid or SES) provide a message ID that you can use to query the provider’s logs. If you store that ID, you can resolve the ambiguity.

Idempotency Keys vs. Deduplication Tables

Sometimes people use a separate deduplication table that stores only the keys of already-processed requests. That works, but it’s essentially the same thing as the email_attempts table. The advantage of storing the full email attempt is that you can return the previous result (e.g., “sent at 12:34”) to the retrying client, which is useful for user-facing feedback.

A deduplication table is simpler but loses that context. If you only need to prevent duplicates, a simple table with a unique key and a timestamp is enough. If you need to know the outcome, store the full record.

Performance and Cleanup Considerations

Storing every email attempt forever can bloat your table. You should add a cleanup job that deletes rows older than, say, 30 days—or move them to an archive table. The idempotency key only needs to exist as long as retries are possible, which is usually minutes or hours, not months.

For high-throughput systems, the primary key lookup on a UUID is fast, but if you have millions of rows, consider partitioning by created_at or using a hash index. In practice, for most email workloads (thousands per day), a simple primary key index is fine.

Frequently Asked Questions

Do I need idempotency keys if my email provider already deduplicates?

Most email providers do not deduplicate by default. They may offer a “deduplication” feature, but it often works on exact content match, not on your logical request. If you send the same email twice intentionally (e.g., two different campaigns with identical content), the provider might suppress the second one if you enable that feature, but that’s not reliable. Idempotency keys give you control at the application level, which is more predictable.

What should I use as the idempotency key?

A UUID (version 4) generated at the time of the request is the standard choice. It’s unique, random, and doesn’t leak information. If you have a natural key like a user ID plus a timestamp, that can work too, but be careful about collisions and about reusing the same key for different emails.

Can I use a unique constraint on a non-primary key column?

Yes, you can add a UNIQUE constraint on any column. But it’s simpler to make the idempotency key the primary key, since you’ll be looking it up frequently. If you need to reference the email attempt from other tables, use a separate integer primary key and put the unique constraint on the idempotency key.

What happens if my database is down during a retry?

If the database is unavailable, you cannot check the idempotency key. In that case, you have to decide whether to fail the send (and not retry) or to proceed without the check. Proceeding without the check can cause duplicates. A common pattern is to fail fast and retry later when the database is back, since email sending is usually not time-critical.

Is this pattern specific to PostgreSQL?

No, the concept works in any relational database that supports unique constraints and atomic upsert. PostgreSQL’s ON CONFLICT syntax is convenient, but you can do the same in MySQL with INSERT … ON DUPLICATE KEY UPDATE, or in SQL Server with MERGE. The key is the atomicity of the insert.

Should I store the email content in the idempotency table?

Yes, storing the full content allows you to return the exact details of what was sent if a retry comes in. It also gives you an audit trail. If you’re concerned about storage, you can store a hash of the content instead, but then you lose the ability to show the recipient what was sent.

Related

• Pokemon Legends Z-A Game Card: Mega Dimension?
• Where Winds Meet Trophy Guide: Unlock Every Achievement
• RG35XX H vs Other Handhelds for Classic Halo