Preventing duplicate writes during Heroku Router request retries

Handling Idempotency in Heroku Postgres

When deploying applications on Heroku, the Heroku Router may trigger request retries under specific failure conditions. If a request successfully commits a transaction to Heroku Postgres but the response fails to reach the router, a subsequent retry can lead to duplicate data entries.

While PostgreSQL supports ACID transactions, ensuring idempotency across multiple dynos requires a strategy to identify and ignore repeated requests. Common approaches include utilizing INSERT ... ON CONFLICT (upserts) or maintaining a dedicated idempotency table to track unique request IDs.

Given the distributed nature of dynos and the potential for connection pool exhaustion when using PgBouncer, there is uncertainty regarding the most performant way to validate request uniqueness without introducing significant latency.

• Which PostgreSQL constraint strategy best balances write performance with the need to prevent duplicates during router-initiated retries?
• Does the use of a dedicated idempotency table create a bottleneck in high-concurrency environments compared to native unique constraints?