You keep 90 days of event history in a very large table and must expire the oldest day every night. Compare running a DELETE for the expired rows against dropping a whole time-based partition: what does each actually cost the database, and what does the cheap option require of the table's design?

## The job being described A rolling-window retention policy ("keep the last 90 days") is one of the heaviest recurring workloads on a high-volume table, because it repeatedly touches the largest, coldest part of the data. How you implement it is largely decided when you choose the partitioning scheme, not when you write the job. ## What a DELETE actually does In a multi-version concurrency control (MVCC) engine, rows are never overwritten in place while other transactions may still need them. A DELETE therefore: - marks each row version dead (in PostgreSQL, stamps the deleting transaction id) rather than freeing space; - writes a write-ahead-log (WAL) record per row, which is shipped to replicas and to the archive; - dirties every heap page it touches, so those pages must be written back, evicting hot pages from the buffer cache; - leaves every secondary index entry in place, to be cleaned later; - fires row triggers and foreign-key checks per row; - creates vacuum/purge debt: PostgreSQL autovacuum must scan the table and all its indexes to reclaim the dead tuples, InnoDB accumulates undo and purge work. Afterwards the file is still the same size. Free space is reusable by future inserts, but returning it to the operating system needs a rewrite (VACUUM FULL, pg_repack, OPTIMIZE TABLE) that takes a heavy lock. A single 200-million-row delete can also blow up replication lag and, if run as one transaction, hold locks and pin the oldest transaction horizon so that nothing else can be vacuumed either. ## What dropping a partition does If the oldest day lives in its own partition, expiry becomes a DDL statement: remove the partition's catalog entries, drop the indexes that belong to that partition, and unlink its data files at commit. The cost does not depend on how many rows are inside. There is no per-row logging, no index cleanup, no bloat, no vacuum debt, and the disk space comes back immediately. The same trick exists in every partitioning implementation: ALTER TABLE ... DROP PARTITION in Oracle and MySQL, DROP TABLE of the child in PostgreSQL declarative partitioning. TRUNCATE of a partition is equally cheap if you want to keep the empty partition attached. ## The design precondition Dropping only works when the retention predicate lines up with partition boundaries: - partition on the column the policy is expressed in (event timestamp, not tenant id); - pick a granularity so that one expiry step removes whole partitions: daily partitions for a daily window, monthly for a monthly window; - resist mixing policies ("90 days for tenant A, one year for tenant B") unless the partition key can express them, otherwise the long tail still needs deletes. A useful sanity check in an interview: state that partitioning is often adopted for exactly this reason, retention, more than for query speed. ## Locking and safety Dropping or detaching a partition needs an exclusive lock on the parent table. That lock must wait for existing transactions touching the parent, and while it waits, every new query queues behind it, so a single long-running report can turn a millisecond DDL into a site-wide stall. Mitigations: set a short lock_timeout and retry with backoff, run in a low-traffic window, and prefer a concurrent detach (available in newer PostgreSQL) followed by dropping the now-standalone table. ## Archiving instead of discarding If the data must be kept: detach the partition so it becomes an ordinary table, export or move it (COPY to object storage, dump, move to cheaper storage), then drop it. Detach-then-archive keeps the exclusive-lock window short and the export off the live parent. ## When DELETE is still the right tool Row-level erasure that does not align with partitions (deleting one user's data on request), small volumes, or tables that are not partitioned. Then: delete in bounded batches keyed by the primary key, commit each batch, throttle to let vacuum and replication keep up, and expect to schedule a rebuild if the table becomes badly bloated.