What is actually consuming space in your Postgres database?
This checklist is for backend and platform engineers whose Postgres storage keeps growing, or who are paying for an allocated volume much larger than the data they think they have. Measure before you delete anything, because the biggest consumer is often not the one you expect.
Rank relations by footprint
Start with pg_total_relation_size(relid) across user tables, ordered descending. It returns everything attributed to a table: the heap, all of its indexes, and its TOAST data. Then break each large table into parts:
- Heap:
pg_relation_size(relid)returns the main data fork only. - Indexes:
pg_indexes_size(relid)returns the combined size of every index on the table. - TOAST and maps: total minus heap minus indexes. This remainder is mostly out-of-line storage for large
text,jsonbandbyteavalues, plus the small free space and visibility maps.
The split tells you which lever to pull. If indexes exceed the heap, look for unused and redundant indexes. If the heap dominates on a table with heavy updates or deletes, suspect bloat. If TOAST dominates, look at retention of large payloads.
Check the consumers outside tables
Compare the sum of all relations with pg_database_size() and with the volume's actual usage. A large gap usually points to one of these:
- Replication slots. A slot forces the server to keep every WAL segment after its
restart_lsn. If the consumer is gone, WAL accumulates indefinitely, and no amount of table cleanup will offset it. Checkpg_replication_slotsfor rows whereactiveis false. - Temporary files. Large sorts and hashes that exceed
work_memspill to disk.temp_bytesinpg_stat_databaseshows the cumulative volume. - Logs. On some managed services, server logs share the same storage allocation as your data.
-- 1. Top 10 tables by total footprint, split into heap, indexes and TOAST/maps.
SELECT
relid::regclass AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total,
pg_size_pretty(pg_relation_size(relid)) AS heap,
pg_size_pretty(pg_indexes_size(relid)) AS indexes,
-- Remainder: TOAST data plus the free space and visibility maps
pg_size_pretty(pg_total_relation_size(relid)
- pg_relation_size(relid)
- pg_indexes_size(relid)) AS toast_and_maps
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
-- 2. Compare with the whole database. A large gap means space is going elsewhere.
SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size;
-- 3. WAL held back by each replication slot (run on the primary).
-- Inactive slots with large retained_wal are the usual culprit.
SELECT
slot_name,
slot_type,
active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;
-- 4. Cumulative temp-file spill per database since stats were last reset.
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_spilled
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;
Diagnostic queries that rank tables by heap, index and TOAST size, then check database size, WAL retained by replication slots, and cumulative temporary file spill.
Who should use this checklist?
It is written for the people who own a Postgres database's day-to-day health and its line on the infrastructure bill:
- Backend engineers who own the schema and the queries, and can therefore judge whether an index or an old table is still needed.
- Platform and SRE teams who run managed or self-hosted Postgres fleets and see storage alarms, autoscaling events or volume increases first.
- Engineers asked to cut database spend who need to separate what cleanup can actually save from what only a migration can.
You need enough privileges to read the statistics views (pg_stat_user_tables, pg_stat_user_indexes, pg_stat_database, pg_replication_slots), to install pgstattuple if you want exact bloat figures, and to run DDL such as DROP INDEX and REINDEX. If you do not own the application, involve the team that does before dropping indexes or retiring data. They know about the month-end job that the statistics have not seen yet.
When should you run this cleanup?
Run through it when one or more of these is true:
- Storage keeps growing faster than the business data. Row counts are roughly flat, but table, index or WAL size keeps rising. That pattern points to bloat, retained WAL or an abandoned replication slot rather than real growth.
- You are about to increase allocated storage. On services where allocation cannot shrink in place, every increase is effectively permanent. Reclaiming space first can postpone or avoid it.
- You are planning a migration anyway. A major-version upgrade, an instance class change or a move to a new volume is the moment when a smaller allocation becomes possible. Cleaning up beforehand means less data to copy and a smaller target.
- Indexes rival or exceed the heap in size. That is the clearest signal that unused, redundant or bloated indexes are worth investigating.
- Backups, restores or replica builds are getting slow. Less data means faster base backups and faster recovery, which can matter more than the storage bill.
- Retention rules exist but are not enforced. If policy says data older than a given age should be gone and it is still in the database, the work is overdue regardless of cost.
How does Postgres storage reclamation work?
Postgres uses multi-version concurrency control (MVCC). An UPDATE does not overwrite a row in place. It writes a new version of the tuple and marks the old one as no longer current. A DELETE only marks the tuple. The old versions stay on their pages until no running transaction can still see them.
That has four consequences for storage:
- Dead tuples need a cleanup pass.
VACUUM, usually run by autovacuum, removes dead tuples that are no longer visible to any transaction and records the freed space in the free space map. Later inserts and updates in the same table reuse that space. The file itself does not shrink, apart from empty pages at the very end. - Long-running transactions block cleanup. A transaction or session that stays open (including
idle in transactionsessions, and replicas reporting feedback to the primary) holds back the point up to which dead tuples can be removed. While it is open, bloat accumulates even if autovacuum runs constantly. - Indexes multiply the cost of updates. Each new tuple version normally needs new entries in every index on the table. The exception is a heap-only tuple (HOT) update: if no indexed column changed and the new version fits on the same page, the indexes are not touched. Tables with many indexes, or updates to indexed columns, therefore bloat their indexes quickly, and B-tree pages split by that churn stay sparse until the index is rebuilt.
- Only file-level operations return space to the filesystem. Dropping an index or partition deletes files.
VACUUM FULL,REINDEXand online rewrite tools write new, compact files and delete the old ones. Everything else keeps the space inside the existing files.
On top of the files sits the volume, and on most managed services the bill follows the allocated volume, not what the files use. The next section shows how each action reaches, or fails to reach, that layer.
Why doesn't deleting rows or dropping indexes lower your storage bill?
Postgres stores each table and index as files of fixed-size pages. Deleting a row does not remove it. It marks the tuple dead, and the page stays the same size on disk. Your bill depends on three layers, and each cleanup action reaches a different one:
┌─ RDS allocated volume (billed) ───────────────────────┐
│ ┌─ Filesystem ────────────────────────────────────┐ │
│ │ ┌─ Table/index files (8 KB pages) ──────────┐ │ │
│ │ │ live tuples | dead tuples | free space │ │ │
│ │ └───────────────────────────────────────────┘ │ │
│ │ ▲ VACUUM: dead → reusable, file same size │ │
│ │ ▲ VACUUM FULL / DROP INDEX: files shrink or │ │
│ │ disappear, filesystem gains free space │ │
│ └─────────────────────────────────────────────────┘ │
│ ▲ Only a migration to a new, smaller instance │
│ lowers allocated storage │
└───────────────────────────────────────────────────────┘
Plain VACUUM marks dead tuples as reusable for future inserts and updates within the same table. It can truncate empty pages at the very end of a file. Free space in the middle of a file stays there, so the file rarely shrinks.
VACUUM FULL rewrites the table and its indexes into new files, then deletes the old ones. That returns space to the filesystem, with two costs:
- It holds an
ACCESS EXCLUSIVElock for the whole operation, which blocks reads and writes. - It needs free disk roughly equal to the live data, because the old and new copies exist at the same time.
DROP INDEX deletes the index files outright, so that space also goes back to the filesystem.
On RDS, none of this changes the bill. You pay for allocated storage, not used storage, and allocated storage cannot be decreased in place. Reclaimed space lets you postpone the next storage increase. It does not shrink the volume. To actually pay less, you have to move the data to a new instance with a smaller allocation.
┌─ RDS allocated volume (billed per GB allocated) ─────────────────────────┐
│ │
│ ┌─ Filesystem ───────────────────────────────────────────────────────┐ │
│ │ ┌─ Table/index files (8 KB pages) ─────────────────────────────┐ │ │
│ │ │ [live][dead][live][free][dead][live]...[empty][empty] │ │ │
│ │ └──────────────────────────────────────────────────────────────┘ │ │
│ │ ▲ VACUUM: dead tuples become reusable; file stays same size │ │
│ │ ▲ (only trailing empty pages can be truncated) │ │
│ │ │ │
│ │ ▲ VACUUM FULL: rewrites table + indexes, deletes old files │ │
│ │ (ACCESS EXCLUSIVE lock; needs free disk ~ live data size) │ │
│ │ ▲ DROP INDEX: deletes the index files │ │
│ │ => freed space returns to the filesystem only │ │
│ └────────────────────────────────────────────────────────────────────┘ │
│ │
│ ▲ Allocated size is unchanged, so the bill is unchanged │
│ ▲ Only migrating to a new instance with smaller allocated storage │
│ lowers what you pay (allocation cannot shrink in place) │
└──────────────────────────────────────────────────────────────────────────┘
The three storage layers behind a Postgres bill on RDS, showing that VACUUM frees space only inside table files, VACUUM FULL and DROP INDEX return it to the filesystem, and only a migration to smaller allocated storage reduces what you pay.
How do you find unused and redundant indexes safely?
Start with pg_stat_user_indexes and look for indexes where idx_scan = 0, sorted by pg_relation_size so the largest candidates come first. A zero only means something if statistics have been accumulating for long enough. Check stats_reset in pg_stat_database for the current database. If counters were reset last week, a zero tells you very little.
Usage statistics are local to each node. A standby tracks scans from the queries it serves, not the primary's. An index that looks idle on the primary may serve every read on a replica, so query each node separately before deciding.
Exclude any index that backs a primary key, unique constraint or exclusion constraint. Check indisprimary, indisunique and indisexclusion in pg_index, or join pg_constraint on conindid. These indexes enforce correctness even if nothing ever scans them.
Next, look for redundant indexes. These are exact duplicates, or indexes whose column list is a leading prefix of another index on the same table. An index on (a) is usually covered by one on (a, b). Compare predicates, expressions and operator classes before you treat them as equivalent.
Expect false positives from:
- month-end or quarterly jobs
- indexes kept for failover replicas
- rare admin or support queries
- short observation windows
To remove an index safely:
- Save its definition with
pg_get_indexdef. - Drop it on one environment or during a quiet period.
- Watch query latency.
- Run
DROP INDEX CONCURRENTLY, outside a transaction block, so writes are not blocked.
-- Run on the primary AND on each replica: usage counters are local to each node.
-- 1. How long have counters been accumulating? A recent reset makes idx_scan = 0 meaningless.
SELECT datname, stats_reset, now() - stats_reset AS observed_for
FROM pg_stat_database
WHERE datname = current_database();
-- 2. Never-scanned indexes, largest first, excluding constraint-backed indexes.
SELECT s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
s.idx_scan,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
pg_get_indexdef(s.indexrelid) AS definition -- save this before dropping
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
AND NOT i.indisprimary
AND NOT i.indisunique
AND NOT i.indisexclusion
-- belt and braces: skip anything a constraint depends on
AND NOT EXISTS (SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;
-- 3. After a staged trial and a full business cycle (incl. month-end jobs),
-- drop without blocking writes. Must run outside a transaction block.
DROP INDEX CONCURRENTLY IF EXISTS public.orders_status_idx;
-- To roll back, re-run the saved definition with CONCURRENTLY, e.g.:
-- CREATE INDEX CONCURRENTLY orders_status_idx ON public.orders USING btree (status);
Checks how long statistics have been accumulating, lists never-scanned indexes that do not back a constraint (largest first, with definitions saved via pg_get_indexdef), and drops a candidate with DROP INDEX CONCURRENTLY.
How do you reclaim space from table and index bloat?
Measure before rebuilding anything. The pgstattuple extension reports exact free space and dead-tuple percentages for tables (pgstattuple) and leaf density for B-tree indexes (pgstatindex). It reads the whole relation, so run it off-peak. Catalog-based estimation queries are cheaper but can be inaccurate.
Rebuilding indexes often recovers more space than working on tables. Plain VACUUM marks dead tuples reusable, but it returns space to the OS only by truncating empty pages at the end of a table. B-tree indexes damaged by update churn and page splits stay large until they are rebuilt. A rebuild also needs free disk only for the one index, not for the whole table.
| Operation | Lock | Extra disk | WAL | Duration |
|---|---|---|---|---|
VACUUM |
Allows reads and writes | None | Low | Scales with dead tuples |
VACUUM FULL |
Blocks all access | Full copy of table plus indexes | Whole table | Long |
REINDEX |
Blocks writes; queries using the index wait | One new index | Index size | Moderate |
REINDEX CONCURRENTLY |
Allows reads and writes | One new index | Index size, plus extra scans | Longer than REINDEX |
Online rewrite extensions (e.g. pg_repack) |
Brief exclusive locks at start and end | Full copy | Whole table | Long; test it before relying on it |
If a concurrent build or reindex fails or is cancelled, it leaves an invalid index behind (indisvalid = false in pg_index, often with a _ccnew suffix). Postgres still updates that index on every write, but the planner never uses it. Find these leftovers and drop them.
Rewrites need temporary disk space and generate a burst of WAL. On a nearly full volume, they can fill the disk and stop writes, and the WAL burst can push replicas far behind.
Skip the rewrite in these cases:
- Not enough headroom: free space is less than the size of the target relation.
- Bloat will return: churn will regrow it to the same steady state.
- Data is leaving soon: the table is about to be archived or partitioned away.
-- 1. Measure (reads the whole relation; run off-peak)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- Table: dead tuples and free space inside the heap
SELECT pg_size_pretty(table_len) AS table_size,
dead_tuple_percent,
free_percent
FROM pgstattuple('public.orders');
-- B-tree index: a low avg_leaf_density means sparse pages worth rebuilding
SELECT pg_size_pretty(index_size) AS index_size,
avg_leaf_density,
leaf_fragmentation
FROM pgstatindex('public.orders_customer_id_idx');
-- 2. Rebuild one index without blocking reads or writes.
-- Needs free disk for one copy of this index. Cannot run inside a transaction block.
REINDEX INDEX CONCURRENTLY public.orders_customer_id_idx;
-- Confirm the new size
SELECT pg_size_pretty(pg_relation_size('public.orders_customer_id_idx'));
-- 3. Find invalid leftovers from failed or cancelled concurrent builds.
-- They are still updated on every write, but the planner never uses them.
SELECT i.indexrelid::regclass AS index_name,
i.indrelid::regclass AS table_name,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS size
FROM pg_index i
WHERE NOT i.indisvalid;
-- Drop a leftover once you have confirmed it is not being built right now
DROP INDEX CONCURRENTLY IF EXISTS public.orders_customer_id_idx_ccnew;
Measuring table and index bloat with pgstattuple, rebuilding a single index with REINDEX CONCURRENTLY, and finding and dropping invalid indexes left by interrupted concurrent builds.
How should you archive old data instead of running large DELETEs?
Start with a written retention policy: how long each class of data must stay queryable in Postgres, how long it must exist anywhere, and when it must be destroyed. Get it agreed before you touch a row, because the constraints usually come from outside engineering:
- Compliance and legal hold: some records must be kept for a fixed period, and records under hold cannot be deleted on schedule.
- Analytics consumers: reports, ETL jobs and replicas may silently depend on "old" rows.
- Erasure obligations: archives must still honour deletion requests, so plan how you would find and remove one person's data in them.
For cold rows, export to object storage in an open format such as CSV or Parquet. That way any tool can read the archive later, not only a matching Postgres version. Then verify the export before deleting anything. Compare row counts and checksums or aggregates against the source, and restore a sample into a scratch database. An archive you have never restored is a guess.
A large DELETE followed by VACUUM is the expensive way to retire data. It generates heavy WAL, leaves dead tuples behind, and frees space for reuse without shrinking the files. Time-based partitioning makes retirement cheap: export the oldest partition, DETACH PARTITION, then DROP it. The files are removed and the space goes back to the filesystem immediately.
Retrofitting partitioning onto a large existing table is a substantial migration, not a quick win:
- Primary keys and unique constraints must include the partition key, which can change your data model.
- Queries that don't filter on that key lose partition pruning and may get slower.
Plan, test and stage the migration like any other schema change of that size.
When is cleanup worth the effort, and what are the alternatives?
Illustrative estimate
Assume 400 GB allocated, 150 GB of droppable indexes and archivable rows, and an assumed price of $0.10 per GB-month. Substitute your own rate.
- Data after cleanup: 400 − 150 = 250 GB
- New allocation with roughly 20% headroom: about 300 GB
- Monthly saving: (400 − 300) × $0.10 = $10, or about $120 per year
RDS does not let you shrink allocated storage on an existing instance. Freed pages stay allocated and keep being billed until you move the data to a smaller volume. Cleanup alone only slows future growth.
Comparing the options
| Option | Cutover risk | Downtime | Engineering time | Payoff |
|---|---|---|---|---|
| Stop growth: cap autoscaling, set retention | None | None | Hours | Avoids future cost; current bill unchanged |
| Clean up in place | Low, mainly lock contention during rebuilds | Minimal if you rebuild concurrently | Days | Faster queries and backups, free room for growth; bill unchanged |
| Migrate via dump/restore | Low to moderate | Long, scales with data size | Days | Bill drops to the new allocation |
| Migrate via logical replication | Moderate: sequences, DDL and replica identity need care | Short cutover window | Weeks | Bill drops, and downtime stays small |
Before you commit, check where the money goes. Storage is often a small share of database spend compared with instance compute and provisioned I/O. A $120-per-year saving rarely justifies a replication-based migration. It can still be worth doing if the move also lets you right-size the instance class, or if you are migrating for other reasons anyway.
When should you avoid or postpone this cleanup?
Some of these operations carry real risk, and some save nothing. Hold off, or do only the read-only measurement steps, when:
- The volume is nearly full.
VACUUM FULL,REINDEXand online rewrites need free disk for a new copy and produce a burst of WAL. On a volume with little headroom they can fill the disk and stop writes. Deal with the cause first, such as an inactive replication slot or runaway temp files, or grow the volume, then reclaim space. - Your usage statistics are too young. If
stats_resetis recent, or a node was recently replaced or failed over,idx_scan = 0does not prove an index is unused. Wait for a full business cycle before dropping anything. - Nobody can confirm what the data and indexes are for. Without the owning team, you risk dropping an index a rare but critical query depends on, or deleting rows that a report, an ETL job or a legal hold requires.
- The bloat is a steady state. On a table with constant update churn, a rebuild buys only temporary relief before the table grows back to the same size. Spend the effort on autovacuum tuning, fewer indexes on hot columns, or finding long-running transactions instead.
- The data is leaving soon. If a table is about to be partitioned, archived or migrated, rewriting it first is wasted I/O and WAL.
- The saving is too small to matter. If storage is a minor part of database spend and you have no migration planned, the bill will not change. In-place cleanup may still be worth it for performance or backup time, but do not justify it with cost.
- The system is under peak load or a change freeze. Concurrent rebuilds add I/O and WAL, and can push replicas behind. Schedule them for a quiet window.
A repeatable checklist to keep Postgres storage costs down
Whatever you decide about the one-off cleanup, the cheaper long-term fix is keeping the waste from coming back. Run through this list on a fixed schedule, monthly for most items and quarterly for the last two.
- Check index usage and bloat on a schedule. Compare
idx_scaninpg_stat_user_indexeswith index size, and track bloat estimates over time. A single snapshot misleads you. Growth between runs shows which tables need tighter autovacuum settings. - Record when statistics were last reset. Note
stats_resetfrompg_stat_databasenext to every usage review. A zero scan count only means something if the counters have covered a full business cycle, including month-end jobs and quarterly reports. - Save index definitions before dropping anything. Store the output of
pg_get_indexdef()in your migration repo or ticket. If a rare query regresses, you can recreate the index withCREATE INDEX CONCURRENTLYin minutes without reconstructing it from memory. - Enforce retention through partitioning. Make dropping or detaching expired partitions a scheduled job, not a manual task. This keeps old data from piling up and avoids the bloat that large
DELETEs cause. - Alert on replication slots. Alert on inactive slots and on retained WAL above a threshold. An abandoned slot can hold WAL indefinitely and fill the disk.
- Watch temp files and logs. Track
temp_bytesinpg_stat_databaseand log volume. Spikes usually point to a bad query plan or verbose logging that someone left on. - Compare allocated and used storage every quarter. Managed services such as RDS generally let you grow allocated storage but not shrink it in place. Catching over-provisioning before the next increase is cheaper than migrating later to reclaim it.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to reduce postgres storage cost, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.