Preventing duplicate writes during Heroku Router request retries

To prevent duplicate writes during Heroku Router request retries, the most effective strategy is using PostgreSQL native unique constraints combined with `INSERT ... ON CONFLICT` (upserts). This approach ensures idempotency at the database layer, which is the only reliable point of truth in a multi-dyno environment.

The Problem of Router Retries

The Heroku Router may retry a request if it does not receive a response within the timeout period, even if the application has successfully committed the transaction to Heroku Postgres. Without database-level idempotency, the retry will execute the logic again, resulting in duplicate records.

Strategy: Unique Constraints vs. Idempotency Tables

1. **Native Unique Constraints (Recommended):** Using a unique constraint on a business logic key (e.g., `order_id` or a client-generated request UUID) allows you to use `INSERT ... ON CONFLICT DO NOTHING`. This is the most performant method because it leverages the database's internal indexing to check for duplicates in a single round-trip, minimizing the overhead on PgBouncer. 2. **Dedicated Idempotency Table:** A separate table used to track `request_id` and `response_body` is useful if you need to return the exact same response as the first request on subsequent calls. However, this introduces an extra write and lookup for every request, which can become a bottleneck in high-concurrency environments and increase connection-hold-time.

Implementation Steps

• Generate a Client-Side Key: Ensure the client provides a unique idempotency key for every mutating request.
• Define the Constraint: Add a unique index to that key in your PostgreSQL table:

  CREATE UNIQUE INDEX idx_idempotency_key ON orders (idempotency_key);

• Use Upsert Logic:** Use the following pattern to handle retries gracefully:

  INSERT INTO orders (idempotency_key, data) 
  VALUES ('req_123', '...') 
  ON CONFLICT (idempotency_key) DO NOTHING;

Performance and Concurrency Considerations

In high-concurrency environments using PgBouncer, minimizing the duration of a transaction is critical. Native constraints are superior to manual "check-then-insert" patterns because they avoid race conditions and reduce the number of round-trips. If you use a dedicated idempotency table, ensure it is properly indexed and consider a TTL strategy to prevent unbounded table growth.

Diagnostic Question: Does your application currently generate a unique request ID per client, or are you relying on database-generated sequences to identify duplicates?