AWS RDS Postgres空闲存储空间骤降原因咨询

Let's dive into the most likely causes for that sudden free space plummet in your AWS RDS PostgreSQL 9.5.10 instance—this is a frustrating issue, but there are clear angles to investigate:

1. Uncontrolled WAL (Write-Ahead Log) Accumulation #

This is the top culprit for sudden space hogging in RDS PostgreSQL. Here's why:

• RDS retains WAL logs until your daily automated backup completes successfully. If your backup gets delayed (due to high IO contention from concurrent workloads, network latency, or large data churn), WAL logs will keep piling up rapidly.
• PostgreSQL 9.5 has known edge cases where WAL archiving can stall, especially under heavy write loads, leading to massive log buildup that eats into your free space.

To check this:
Run this query in your Postgres instance to assess WAL volume and retention:

SELECT pg_size_pretty(pg_current_wal_lsn() - pg_start_backup_lsn()) AS pending_wal_size,
       pg_size_pretty(pg_total_wal_size()) AS total_wal_size;

You can also check the Logs & events tab in the RDS console for WAL archiving errors or delays.

2. Lingering Temporary Tables #

Temporary tables created by complex queries (large joins, sorts, bulk data operations) or misbehaving applications can quickly consume free space. Unlike permanent tables, temp tables are only dropped when the session ends—but long-running sessions or failed queries can leave them lingering and taking up space.

To check for leftover temp tables:

SELECT nspname AS temp_schema,
       relname AS temp_table,
       pg_size_pretty(pg_total_relation_size(oid)) AS table_size
FROM pg_class
WHERE relkind = 't' AND nspname LIKE 'pg_temp%';

3. Autovacuum Falling Behind on Dead Tuples #

PostgreSQL 9.5’s autovacuum process can struggle to keep up with large-scale deletes/updates. When you delete or update rows, the old versions (dead tuples) aren’t immediately removed—they stay until autovacuum cleans them up. If you have a sudden burst of deletions (e.g., batch cleanup jobs) and autovacuum uses conservative default thresholds, dead tuples can balloon and eat into your free space.

To check dead tuple volume:

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
       pg_size_pretty(pg_stat_get_dead_tuples(oid)) AS dead_tuple_size
FROM pg_stat_user_tables
ORDER BY dead_tuple_size DESC;

You can also verify RDS autovacuum settings with SHOW autovacuum;—default values might not be optimized for your workload.

4. Excessive Logging #

If you’ve enabled verbose logging (e.g., log_statement = 'all' or audit logging) for debugging, log files can grow rapidly and consume space. RDS typically rotates logs, but if log retention is set too high or rotation fails, logs can pile up unexpectedly.

Check the Logs & events tab in the RDS console to review log file sizes and retention settings.

5. Old PostgreSQL Version Limitations #

Keep in mind PostgreSQL 9.5.10 is end-of-life (AWS stopped supporting it in November 2021). This version has known bugs related to space management, including autovacuum inefficiencies and WAL archiving stalls that were fixed in later releases. Upgrading to a supported version (like 12+) would resolve many of these underlying issues.

内容的提问来源于stack exchange，提问作者Sowmia Naraynan S