WAL Archive Retention: How Long to Keep WAL and How to Clean Up Safely

WAL archives are insurance against the worst day. They also accumulate at a rate that scales with your write throughput, which means a busy database can produce hundreds of GB per week of WAL. The retention policy is a tradeoff: longer is safer, shorter is cheaper. Here is how I set it.

What WAL retention buys you

With WAL archive plus a recent base backup, you can perform point-in-time recovery to any moment within the retention window. Restore to the moment before the bad migration. Recover deleted rows. Roll the database back to known-good state without losing the rest of the work.

Without WAL retention, you can only restore to base backup boundaries. Daily base backups → 24-hour granularity. The deleted row from 2 PM is gone unless you have WAL.

How much WAL accumulates

The rate depends on workload. Rough orders of magnitude:

• OLTP web app, 10 RPS: 1-5 GB/day.
• Active e-commerce, 100 RPS: 10-50 GB/day.
• High-volume events table, 1000 RPS: 100-500 GB/day.
• VACUUM intensive (rebuilds, FREEZE): 2-10x normal during the operation.

Multiply by retention days for total storage. A 30-day retention on a 50GB/day database is 1.5TB of archive.

How to figure out your rate

-- Bytes generated since cluster start
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0');

-- Cluster start time
SELECT pg_postmaster_start_time();

Divide bytes by hours since start. For a more honest number, sample over a few days because WAL rate fluctuates with workload.

The cloud's storage costs are usually $0.02-0.05 per GB-month. A 1.5TB archive costs $30-75/month at S3 standard. Cheap relative to most database costs, but not zero.

A pragmatic retention policy

For most production workloads:

• 7 days: minimum. Recovers from common operational mistakes (bad deploy, accidental delete) where the discovery happens within a week.
• 30 days: comfortable. Covers monthly billing cycles, longer-discovery incidents, regulatory "undo" requests.
• 90 days: enterprise / high-stakes. Required for some compliance regimes.
• 365 days: rare. Usually replaced by periodic logical backups for point-in-time history.

My default for most teams is 30 days. The cost is reasonable, the window is wide enough for almost any operational need.

Cleanup tools

Never manually delete files in pg_wal/ on the primary. Postgres tracks them; deleting the wrong segment corrupts the cluster.

For the archive (S3 or wherever), the safe tool is pg_archivecleanup:

pg_archivecleanup /path/to/wal/archive WAL_FILE_NAME

This deletes WAL segments older than the specified one. The "WAL_FILE_NAME" is typically derived from the most recent base backup minus the retention window.

For S3 archives, lifecycle policies are easier:

# S3 lifecycle for monpg-wal bucket
rules:
  - id: expire-old-wal
    filter: { prefix: wal/ }
    expiration: { days: 30 }
    status: enabled

S3 deletes objects older than 30 days automatically. Set this once, never think about cleanup again.

What can go wrong with retention

Retention shorter than the oldest base backup useful: a 30-day WAL retention with a 60-day base backup retention means your old base backups have no useful WAL to replay alongside. They can only restore to themselves, which is what pg_dump is for.

Rule of thumb: WAL retention = (longest base backup age) + (PITR window).

Manual deletion of WAL on the primary: as noted, never. Postgres recycles WAL itself. Manual deletion corrupts the cluster.

Archive lifecycle deleting too aggressively: a misconfigured S3 lifecycle deletes WAL that is still needed for an active replica. The replica falls off the edge of WAL availability and needs a full re-sync.

To guard against this, monitor that the oldest WAL retained is older than the replica's restart_lsn.

Replica stuck: an active replica's slot is retaining WAL on the primary, but the WAL is being archived faster than the replica can apply. The slot grows. The disk fills.

Fix: investigate why the replica cannot keep up.

Verification that retention is honest

A periodic test:

# Pick a timestamp from N days ago
# Restore the corresponding base backup
# Configure restore_command to fetch WAL from archive
# Recover to that timestamp

If the recovery succeeds and the data reflects that moment, the retention is real. If WAL is missing, the retention is shorter than advertised.

This is the same drill as the PITR drill. Once a quarter is enough.

On managed services

Managed Postgres handles this for you, with caveats:

• RDS: configurable retention up to 35 days. Beyond that, you have to do logical backups.
• Cloud SQL: similar, up to 35 days.
• Azure Flex: 7-35 days configurable.

If your need is longer than the managed service offers, you have to layer:

• Daily logical (pg_dump) backups to your own storage with longer retention.
• Documented recovery procedure that uses logical backups for older points.
• Acceptance that recovery to >35 days ago is logical-only, not point-in-time.

What I commit to

For production:

1. WAL archive enabled to durable, off-instance storage.
2. Retention 30 days (default) or longer if a specific use case demands.
3. Monitoring on archive failure (last_failed_time newer than last_archived_time).
4. Lifecycle policy automated (S3 or equivalent).
5. Quarterly drill to verify a real PITR works against this retention.

This costs less than people expect and prevents the entire "we cannot recover from yesterday's mistake" failure mode.