Clean Up Expired Idempotency Keys

Partition idempotency records by day, drop old partitions on a schedule, and keep a retention window longer than the client retry horizon.

~35 minpostgresqlidempotencymaintenance

Task

Keep the idempotency table small and fast without deleting keys that a client may still retry against.

When to use this

An idempotency table has grown to 400 million rows because nothing ever deletes old keys. Lookups slow down, the index no longer fits in memory, and a bulk DELETE would bloat the table and block writes for hours.

Prerequisites

PostgreSQL 14 or newer with declarative partitioning
An existing idempotency table with a timestamp column
A scheduler such as pg_cron or an external job runner

Steps

1. Recreate the table as range-partitioned by creation day, keeping the unique key composite with the partition column.

CREATE TABLE idempotency_keys (
  key text NOT NULL,
  request_hash text NOT NULL,
  response_status int,
  response_body jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (key, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE idempotency_keys_20261021
  PARTITION OF idempotency_keys
  FOR VALUES FROM ('2026-10-21') TO ('2026-10-22');

Expected output: The table is partitioned by day and the primary key includes created_at as required by PostgreSQL.

2. Create a maintenance function that adds tomorrow's partition and drops partitions older than the retention window.

CREATE OR REPLACE FUNCTION maintain_idempotency_partitions() RETURNS void AS $$
DECLARE
  tomorrow date := current_date + 1;
  cutoff   date := current_date - 7;
BEGIN
  EXECUTE format(
    'CREATE TABLE IF NOT EXISTS idempotency_keys_%s PARTITION OF idempotency_keys FOR VALUES FROM (%L) TO (%L)',
    to_char(tomorrow, 'YYYYMMDD'), tomorrow, tomorrow + 1
  );
  EXECUTE format('DROP TABLE IF EXISTS idempotency_keys_%s',
    to_char(cutoff, 'YYYYMMDD'));
END;
$$ LANGUAGE plpgsql;

Expected output: Calling the function creates one future partition and removes the partition seven days old.

3. Schedule the maintenance daily during low traffic and bound the lock wait for the drop.

psql -U postgres -d appdb -c "SELECT cron.schedule('idem-maintenance', '15 2 * * *', 'SELECT maintain_idempotency_partitions();');"

Expected output: pg_cron runs the function at 02:15 UTC every day and the job appears in cron.job.

4. Confirm no rows remain beyond the retention window and lookups still hit the index.

psql -U app_rw -d appdb -c "SELECT count(*) FROM idempotency_keys WHERE created_at < now() - interval '7 days';"

Expected output: count is 0 and EXPLAIN shows an index scan on the composite primary key for a point lookup.

Troubleshooting

Problem

DROP TABLE waits on a long-running transaction

Solution

Set lock_timeout before the drop in the maintenance function and retry later; a partition drop needs an AccessExclusiveLock on that partition only.

Problem

Foreign keys from other tables block the partition drop

Solution

Avoid referencing idempotency partitions from other tables, or delete dependent rows first; partitions cannot be dropped while referenced.

Limitations

Retention shorter than the client retry window breaks idempotency and produces duplicate side effects.
Dropping a partition is irreversible; ensure the retention policy matches the contractual retry period.

Delete by partition, not by row

An idempotency table is append-heavy and read by exact key. Deleting millions of rows with DELETE creates bloat, updates every index, and holds locks. Dropping an old partition is a metadata operation that returns space immediately and leaves current partitions untouched. Range partitioning by day matches both the access pattern and the retention policy.

Keep the key unique

PostgreSQL requires the partition column in every unique constraint, so the primary key becomes (key, created_at). That is acceptable for idempotency lookups because the application queries by key and a recent time bound. If you need global uniqueness across partitions, enforce it in the application with a short-lived cache or accept the composite key and query the current and previous partitions.

CREATE TABLE idempotency_keys_20261021
  PARTITION OF idempotency_keys
  FOR VALUES FROM ('2026-10-21') TO ('2026-10-22');

Automate before it hurts

Create partitions ahead of time; an insert into a missing partition fails. The maintenance function above creates tomorrow’s partition and drops the one outside retention. Schedule it during low traffic, set lock_timeout, and alert if the job fails two days in a row, because a missed partition means failed writes at midnight.

Choose retention from clients

Retention must exceed the maximum retry horizon of every client, including mobile apps that may retry after days offline. Seven days is common, thirty is safer for partner integrations. Store the retention decision in the runbook and in the function comment so the next engineer does not shorten it casually. Verify after the first scheduled run that tomorrow’s partition exists and that the oldest remaining partition is exactly the retention boundary you intended.