CONCEPT Cited by 1 source

TTL-based row deletion¶

Definition¶

TTL-based row deletion is the retention pattern where a background job (cron, scheduler, pg_cron, external loop) runs a DELETE statement with an interval predicate on an insertion-time column to keep the table bounded to a recent window:

DELETE FROM video_frames WHERE inserted_at < NOW() - INTERVAL '5 seconds';

It is the simplest retention mechanism available in any SQL database — no dedicated TTL feature required, no per-row expiry tracking, no background-thread tombstoning logic. The table grows as inserts arrive and shrinks on each cron tick.

Why it shows up in system design¶

When a table is used as a short-lived staging / event buffer / real- time fan-out substrate, retaining all history is wasteful. Retention is either:

Use-case-bounded: frames from a live call need only be visible for as long as they might be consumed by a replication subscriber or rendered by a browser.
Throughput-bounded: at 15 fps, a single call would accumulate ~108,000 rows/hour. A 5-second window caps the per-call row count to ~150 at any moment. (Source: sources/2026-02-27-planetscale-video-conferencing-with-postgres.)

Operational knobs¶

Cleanup cadence (how often the cron fires): frequent enough to keep steady-state table size bounded, infrequent enough to avoid excessive lock + vacuum pressure. Van Wiggeren's video-chat post runs the cleanup every 2 seconds against a 5-second retention window, producing a steady 5–7 s of data in the table.
Retention window (the INTERVAL in the predicate): the application-correctness lower bound on how much history must be visible for downstream consumers / subscribers to function.
Deletion batch size: on very high-throughput tables, DELETE … LIMIT N in a loop is a common refinement to avoid long- held row locks.

Trade-offs vs alternatives¶

Versus TRUNCATE + table swap: lower blast radius (no brief empty-table window, no schema lock) but leaves [[concepts/dead- tuples-vacuum|dead tuples]] for vacuum to reclaim.
Versus partitioned tables + DROP PARTITION: more operational machinery to maintain (partition creation + range rotation) but reclaims space instantly. Preferred when the table is large and retention is measured in days / weeks rather than seconds.
Versus native TTL features (Redis, DynamoDB): those are background processes too, but amortised across all rows; on Postgres, you run the same kind of loop yourself.
Versus unlogged tables: unlogged tables truncate on crash but write faster and generate no WAL — but they disappear from logical replication. TTL-delete keeps the table logged + replicated while bounding growth.

Interaction with vacuum¶

High-churn tables with TTL-delete are canonical vacuum pressure cases. Every DELETE creates a dead tuple; autovacuum must keep pace with the delete rate to prevent table bloat. For the Postgres queue- table analogue see concepts/postgres-queue-table and the Keeping a Postgres queue healthy post for the MVCC-horizon failure mode that long-running transactions introduce.

Seen in¶

sources/2026-02-27-planetscale-video-conferencing-with-postgres — canonical wiki first-class TTL-delete framing. Nick Van Wiggeren uses a 2-second cleanup cron against a 5-second retention window on a video_frames + audio_frames pair:

DELETE FROM audio_frames WHERE inserted_at < NOW() - INTERVAL '5 seconds';
DELETE FROM video_frames WHERE inserted_at < NOW() - INTERVAL '5 seconds';

"I didn't want to keep every video frame forever. At 15fps, you'd accumulate about 108,000 rows/hour per active call. So there's a cleanup job that runs every 2 seconds and prunes frames older than 5 seconds… This means that for every call, we'd expect to have about 5-7 seconds of frames in the table at any given time, or about 150 rows total." Verified by a sample aggregation query showing 76 rows per participant over the last 5 s (15.2 fps actual). Canonical minimal-ceremony retention lever.