Postgres Bulk Deletes: Use DROP TABLE

Data retention is easy to describe and hard to run. Every platform team eventually needs to remove old events, expired audit data, noisy observability rows, or broken records from a bad deploy. The obvious answer is DELETE FROM table WHERE created_at < ..., but at scale that can turn cleanup into a new production risk.

A recent PlanetScale engineering post, The only scalable delete in Postgres is DROP TABLE, is a useful reminder for SREs: in Postgres, big deletes are write-heavy operations that leave cleanup debt behind.

Why DELETE Hurts

Postgres uses MVCC, so deleted rows do not disappear immediately. They become dead tuples. Autovacuum later marks that space reusable, but the database still has to carry the dead rows, scan around them, and clean related index entries over time.

That matters in production because a bulk delete can:

• generate large write-ahead log volume
• increase replication lag during cleanup
• make read queries inspect more dead row versions
• push autovacuum into emergency mode
• fail to return disk space to the operating system

In other words, a large DELETE often means "work added now, space maybe reused later."

Why DROP TABLE Scales Better

DROP TABLE and TRUNCATE take stronger locks, so they are not invisible operations. But they are mostly metadata and file removal work, not per-row cleanup work. They avoid dead tuples, avoid long index cleanup, and free storage much more directly.

For ongoing retention, the practical design is partitioning. Instead of keeping all rows in one giant table, route data into time-based partitions:

CREATE TABLE events (
  id bigserial,
  created_at timestamptz not null,
  payload jsonb not null
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_06_15
PARTITION OF events
FOR VALUES FROM ('2026-06-15') TO ('2026-06-16');

When data ages out, drop the old partition:

DROP TABLE events_2026_05_15;

That turns a row cleanup job into a predictable table lifecycle operation.

Operational Tips

Design retention before the table becomes huge. Pick partition boundaries that match query patterns and deletion windows, such as daily partitions for high-volume event data or monthly partitions for slower audit logs.

Keep these checks in the runbook:

• verify no application query depends on a dropped time range
• monitor replication lag before and after partition drops
• alert on n_dead_tup, table bloat, and autovacuum backlog
• test lock behavior in staging with production-like shared buffers
• document who can extend retention during investigations

For one-off cleanup after a bug, consider copying rows to keep into a temporary or replacement table, then truncating or swapping tables during a maintenance window. If the table cannot take a write pause, use a trigger or dual-write migration pattern and cut over atomically.

Conclusion

Bulk deletes are not just SQL housekeeping. They are reliability events that affect vacuum, replicas, disk, and query latency. SRE teams should treat large cleanup jobs like migrations: design the table shape first, test the lock behavior, and prefer partition drops when retention is predictable.

Looking to automate your infrastructure operations? Akmatori provides AI-powered agents that help SRE teams manage complex operational workflows with confidence. Built on Gcore's global infrastructure, Akmatori brings intelligent automation to your operational stack.