Scenarios

File count and size, overlap depth on the filtered column, manifest size and planning time, the age of statistics, and whether a maintenance job was switched off.

Count files and their median size; compare planning time with execution time in the query profile; measure overlap depth at random values.

Millions of files under 1 MB (writers × partitions × commits), planning taking tens of seconds, and wide overlap. Per-file overhead dominates the scan.

Shuffle before writing, or reduce writers per partition; lengthen the commit interval; compact to 256 MB with selection aware of key ranges; compact manifests.

Make maintenance automatic and not optional, with alerts on file count, median file size and overlap depth: work nobody asked for has no one to argue for it. The trade-offs are freshness, background compute, and more history in storage.

Left alone, 5,000 files a minute.

Shuffle by partition so one writer owns each partition, and commit every 5 minutes: at most 100 files per commit, about 20 a minute instead of 5,000.

Compact to 256 MB, triggered per partition by cost (T = √(2C / r)), sorting by the most common filter column. Use narrow conflict detection and jobs of about 500 files, so compaction commits over new appends. Compact manifests on their own schedule. Keep 3 days of history with the rewrite rate measured, so storage stays bounded.

Compaction and writers repeatedly conflicting on the hot partition; a retention window shorter than the longest query; silent corruption (add fingerprint checks).

Maintenance built into the table, not a separately scheduled job.

Storage ≈ live size × (1 + f × R). Hourly compaction might rewrite half the data each day: 40 × (1 + 0.5 × 14) = 320 TB. Cutting history to 3 days and compacting only changed partitions (f = 10%) gives 40 × 1.3 = 52 TB.

Move partitions that have gone unread longer than the break-even (retrieval fee ÷ monthly saving) to a cold tier, but only large files. Remove orphan files, with a grace period of days.

Shorter time travel, a lower limit on how long queries may run, and retrieval fees if old data is read.

The longest real query, so the retention window never falls below it.

Find the files holding the customer's rows from manifest statistics, rewrite them without those rows, expire every snapshot referencing the old files, and verify with counts. The cost follows file size, not row size: with 1 GB files, one row can cost up to 1 GB of rewriting. Time travel can now reach back no further than the regulatory window.

A per-object hold that vacuum, snapshot expiry and orphan removal all check.

For new data, a key per person, so deletion is just deleting the key, accepting that the key granularity is fixed forever. For existing data, batch many deletions into each rewrite to spread the cost.

A snapshot that still references the data; a backup copy; a long-running reader; and a legal hold that conflicts with a deletion deadline, which is an organizational decision, not a technical one.

The job is a long transaction that keeps losing the optimistic commit to short appends; its chance of conflict grows with its duration.

1. Narrow the conflict check to "were my input files touched?".
2. Split the work into short jobs of a few hundred files, so each commits quickly.
3. Run more often, so each job stays small; the interval follows T = √(2C / r) per partition.
4. If deletes touch the same files, retry only that group.

Count and fingerprint before each commit.

More commits mean more metadata, so compact manifests; and jobs that are too small rewrite more in total, since each compaction level rewrites the data again.

The break-even is retrieval fee ÷ monthly saving, about 3.5 months in a typical example. If most data is read again within that time, for example by month-end reports at 60 days, tiering loses money.

Minimum storage-duration charges, and the per-object transition cost: a table of small files pays that per-object price millions of times.

Tier only large, compacted files whose dormancy has been measured. Most teams lack per-file read data, so start by collecting it.

The age of the oldest transaction (pg_stat_activity: xact_start, backend_xmin), abandoned replication slots, and forgotten prepared transactions. A single old reader stops reclamation of every version written during its lifetime.

End or limit the transaction (set idle-in-transaction timeouts), put oldest-reader age on the dashboard, and accept the cost of a manual vacuum afterwards.

Reduce version churn with HOT updates and a lower fill factor.