A RDS PostgreSQL instance ran out of free space and started rejecting writes. I spent hours chasing table growth, temp spill, and WAL before finding the actual problem - which wasn’t data at all. This is a write-up of how to investigate runaway storage on RDS PostgreSQL, and how to avoid the wrong turns I took.
The symptom
The first sign wasn’t a storage alert. It was application failure. A background sync process started dying with:
FATAL: the database system is not accepting connections
Shortly after, our loaders started tripping their storage guardrails because free space had dropped under 1 GiB.
Here’s the confusing part: the logical database size was nowhere near the provisioned storage. The application data looked completely manageable, yet the instance had burned through hundreds of gigabytes. That gap between what PostgreSQL said it was storing and what RDS said was gone is where the whole investigation lived.
Chasing the wrong problem
I went straight for the biggest tables, because that’s what it always is. Except it wasn’t, and I burned a good chunk of time proving it. Then I suspected temp spill. Then WAL. Then index bloat. Every one of those can absolutely fill a database, which is exactly what makes them such convincing red herrings.
The mistake wasn’t checking them - you should check them. The mistake was assuming each one before measuring it, and building theories on top of theories. Measure first, in a fixed order. Here’s the order I’d use now.
Measuring the actual data
Start inside PostgreSQL. Biggest relations first:
SELECT
n.nspname AS schema_name,
c.relname AS relation_name,
c.relkind,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
pg_size_pretty(pg_relation_size(c.oid)) AS heap_size,
pg_size_pretty(
pg_total_relation_size(c.oid) - pg_relation_size(c.oid)
) AS aux_size,
pg_total_relation_size(c.oid) AS total_size_bytes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
AND c.relkind IN ('r', 'm')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 100;
Then indexes:
SELECT
schemaname,
relname,
indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
pg_relation_size(indexrelid) AS index_size_bytes
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 100;
One thing worth calling out: use relname and indexrelname from pg_stat_user_indexes. A lot of quick queries online get this wrong.
Database and schema totals round it out:
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS size,
pg_database_size(datname) AS size_bytes
FROM pg_database
WHERE datname <> 'rdsadmin'
ORDER BY pg_database_size(datname) DESC;
SELECT
n.nspname AS schema_name,
pg_size_pretty(SUM(pg_total_relation_size(c.oid))) AS total_size,
SUM(pg_total_relation_size(c.oid)) AS total_size_bytes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm', 'i')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY n.nspname
ORDER BY SUM(pg_total_relation_size(c.oid)) DESC;
In our case these numbers didn’t come close to explaining the storage consumption. That was the first real signal that this wasn’t ordinary table growth - the data PostgreSQL knew about wasn’t the data eating the disk.
Temp space
Temp spill is a classic space killer, so it’s worth ruling out quickly:
SHOW temp_file_limit;
SHOW log_temp_files;
SHOW work_mem;
SHOW maintenance_work_mem;
SELECT
datname,
temp_files,
pg_size_pretty(temp_bytes) AS temp_bytes_human,
temp_bytes
FROM pg_stat_database
ORDER BY temp_bytes DESC;
Note that temp_bytes is cumulative since stats reset, not current usage - but if it’s tiny, temp spill isn’t your problem. Ours was tiny. Next.
WAL
WAL can explode if replication stalls, a logical slot lags, or a replica goes unhealthy. Current WAL size:
SELECT
slot_name,
active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
Replication slots - wal_status and safe_wal_size are the columns that matter:
SELECT
slot_name,
slot_type,
active,
restart_lsn,
confirmed_flush_lsn,
wal_status,
safe_wal_size
FROM pg_replication_slots;
Replication status:
SELECT
pid,
application_name,
client_addr,
state,
sync_state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn
FROM pg_stat_replication;
And long-running transactions, which can pin WAL and block cleanup:
SELECT
pid,
usename,
state,
xact_start,
query_start,
now() - xact_start AS xact_age,
LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
All healthy. WAL wasn’t the hidden consumer either. At this point I’d exhausted everything visible from inside SQL - which was itself the clue.
Comparing inside to outside
This is where the RDS-specific part starts. Everything above measures what PostgreSQL knows about. But the instance volume holds more than table data, and if you only look inside SQL you’ll never see the rest.
Pull the free storage metric from CloudWatch:
aws cloudwatch get-metric-statistics \
--namespace AWS/RDS \
--metric-name FreeStorageSpace \
--dimensions Name=DBInstanceIdentifier,Value=<db-instance-id> \
--start-time <start> \
--end-time <end> \
--period 300 \
--statistics Minimum Average \
--region <region>
And the allocated storage:
aws rds describe-db-instances \
--db-instance-identifier <db-instance-id> \
--region <region> \
--query 'DBInstances[0].{AllocatedStorage:AllocatedStorage,FreeStorageSpace:FreeStorageSpace,DBInstanceStatus:DBInstanceStatus}'
For us the arithmetic was stark: allocated storage minus free storage was hundreds of gigabytes more than everything I’d measured inside PostgreSQL. Something outside the database was consuming the volume.
The logs
This was the breakthrough. RDS stores database log files on the instance volume, and you can list them from the CLI:
aws rds describe-db-log-files \
--db-instance-identifier <db-instance-id> \
--region <region> \
--query 'sort_by(DescribeDBLogFiles,&Size)[*].[LogFileName,Size,LastWritten]' \
--output text
That one command answered everything. A handful of PostgreSQL log files were enormous - collectively they accounted for the missing hundreds of gigabytes. The question was no longer why the database was using so much storage. It was why the logs were.
The root cause: audit logging
The answer was pgAudit. The audit scope was far broader than the workload needed, and the application does a lot of write-heavy sync activity. Broad audit logging on a write-heavy system turns normal throughput into a log storm: every batch of writes generated a pile of audit entries, the entries accumulated on instance storage, and free space collapsed while the actual table data stayed modest.
This failure mode feels genuinely unfair the first time you hit it, because every “how big is my database” query tells you things are fine while the instance is falling over.
Checking the settings is straightforward:
SHOW log_statement;
SHOW log_min_duration_statement;
SHOW log_duration;
SHOW log_error_verbosity;
SHOW log_min_error_statement;
SHOW log_rotation_age;
SHOW log_rotation_size;
SHOW log_destination;
And if pgAudit is installed:
SHOW pgaudit.log;
SHOW pgaudit.log_catalog;
SHOW pgaudit.log_parameter;
SHOW pgaudit.log_relation;
SHOW pgaudit.log_statement_once;
pgaudit.log is the one to scrutinise. If it includes write or all on a system doing heavy DML, you’re paying for it in log volume whether you read those logs or not.
Stopping the bleeding
The fix has two halves: generate fewer logs, and keep them for less time.
Narrow the audit scope in the parameter group:
aws rds modify-db-parameter-group \
--db-parameter-group-name <parameter-group> \
--parameters "ParameterName=pgaudit.log,ParameterValue=ddl,role,ApplyMethod=pending-reboot" \
--region <region>
Shorten log retention - this one applies immediately:
aws rds modify-db-parameter-group \
--db-parameter-group-name <parameter-group> \
--parameters "ParameterName=rds.log_retention_period,ParameterValue=1440,ApplyMethod=immediate" \
--region <region>
Then reboot for any pending-reboot parameters:
aws rds reboot-db-instance \
--db-instance-identifier <db-instance-id> \
--region <region>
If you’re critically low, you may also need to grow the volume just to keep the database accepting writes while the config changes take effect:
aws rds modify-db-instance \
--db-instance-identifier <db-instance-id> \
--allocated-storage 600 \
--apply-immediately \
--region <region>
Be clear with yourself about what that is: breathing room, not a fix. RDS storage also can’t be shrunk back down afterwards, so don’t reach for it before you’ve addressed the log generation.
One more thing people ask: can you force RDS to delete the log files and reclaim the space right now? Not really. You can reduce future generation, shorten retention, and wait for RDS to clean up on its schedule. There’s no VACUUM for log files - vacuum reclaims table bloat, and this isn’t table storage. That distinction is worth internalising, because the reflexes you’ve built for reclaiming database space simply don’t apply here.
What I’d do differently
Our guardrails were aimed at the failure modes I expected from a loading workload: table growth, temp spill, WAL accumulation. All real risks, all worth guarding. But the actual killer was log volume, and nothing was watching it. The alarms I’d want now: CloudWatch on FreeStorageSpace with enough headroom to react, plus something tracking RDS log file growth - and a standing rule that any workload doing heavy writes gets its audit scope reviewed before it ships.
The investigation order that would have saved me hours:
- Check
FreeStorageSpacein CloudWatch and compare it to allocated storage - Measure total logical DB size
- Measure the largest tables and indexes
- Check temp file stats
- Check WAL size and replication slots
- Check long-running transactions
- List RDS log files and their sizes
- Inspect logging and audit settings
- Reduce log generation and retention
- Grow storage only if you need headroom to recover
The real shift is in step 1: don’t ask “why is my database big?” until you’ve confirmed the database is what’s big. On RDS, the storage that kills you isn’t always the storage you can query.
