Your PostgreSQL database is getting slower. Queries that used to be fast now take a bit longer. The size of your backups is growing at a rate that seems disproportionate to the actual growth of your data. Your disk utilization is increasing, even though you are not inserting a massive volume of new information. If this scenario sounds familiar, you may be facing one of the most silent and corrosive enemies of PostgreSQL performance: bloat.
Bloat is the wasted space in your tables and indexes, occupied by “dead tuples” and empty padding. It is not a bug, but a natural consequence of PostgreSQL’s sophisticated concurrency control design, MVCC. However, if not actively managed, bloat can accumulate to the point of severely degrading read performance, inflating storage costs, and making maintenance operations painfully slow.
This technical guide offers a deep dive into the root cause of bloat, provides actionable scripts to diagnose it accurately, and details the strategies, including online tools like pg_repack, to safely remove it in production environments.
The Root Cause: Why Does Bloat Happen?
To combat bloat, it is essential to understand why it exists. The cause lies at the heart of PostgreSQL’s architecture: Multi-Version Concurrency Control (MVCC). MVCC is what allows PostgreSQL to offer fantastic read consistency, where reads (SELECT) do not block writes (UPDATE/DELETE), and vice versa.
The mechanism works by maintaining multiple “versions” of the same row. When you perform an operation, PostgreSQL does not modify the data in place:
- UPDATE is not an UPDATE: An UPDATE in PostgreSQL is actually a combined INSERT + DELETE operation. The database inserts a new version of the row with the updated data and marks the old version as “dead” or “invalid” for all future transactions. The old row is not physically removed from the data file at that moment.
- DELETE is not a DELETE: Similarly, a DELETE does not physically erase the row. It simply marks the current version of the row as “dead.”
These old, dead versions of the rows are called “dead tuples.” They remain in the data files to ensure that long-running transactions, which started before the modification, can still see the consistent version of the data that existed when they began.
The process responsible for eventually cleaning up these dead tuples and making the space they occupied reusable for future INSERTs is VACUUM. If the rate of dead tuple creation (i.e., your write load) is greater than VACUUM’s ability to clean them up, bloat begins to accumulate.
The Impact of Bloat on System Performance
Bloat is not just wasted space; it has a direct and negative impact on performance:
- Slower Seq Scans: When PostgreSQL performs a sequential scan on a bloated table, it needs to read many more pages from the disk to get the same amount of live data. If a table has 50% bloat, a full scan will read twice as many blocks, causing twice as much disk I/O.
- Degraded Index Performance: Indexes also suffer from bloat. A bloated index is “deeper” and “wider,” requiring more I/O to be read and traversed. Even worse, bloat in the table means that even an efficient Index Scan needs to skip a large number of dead tuples when fetching the data from the table, a process known as “heap fetches.”
- Inefficient Cache Usage: Bloat consumes your most precious resource: RAM. Data pages that are 80% full of dead tuples might be occupying valuable space in the shared_buffers (PostgreSQL’s cache), pushing out “hot” and useful data and lowering your cache hit ratio.
- Increased Storage and Backup Costs: The most obvious impact is the increase in the physical size of your database, which leads to higher storage costs and backups that take longer to complete and restore.
Scripts to Find and Quantify Bloat
Guessing that you have bloat is not enough. You need to measure it. The PostgreSQL community has developed robust SQL scripts to estimate the amount of wasted space. They work by comparing the actual size of the table with the expected size based on PostgreSQL’s statistics.
Warning: These scripts can be resource-intensive. Run them during low-load times and test them in a staging environment first.
Script to Estimate Table Bloat
This is one of the most well-known and reliable scripts, adapted from various community sources:
SELECT
current_database(),
schemaname,
tablename,
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
CASE WHEN relpages > otta THEN ROUND((relpages-otta)::FLOAT*bs/1024/1024, 2) ELSE 0 END AS tbsize_mb,
(CASE WHEN otta=0 OR sml.relpages=0 THEN 0.0 ELSE sml.relpages::FLOAT/sml.reltuples END)::NUMERIC AS tuple_bloat,
(CASE WHEN otta=0 OR sml.relpages=0 THEN 0.0 ELSE bs*(sml.relpages-otta)::FLOAT/sml.reltuples END)::NUMERIC AS wasted_bytes_per_tuple
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*sml.avg_width)/ (bs-20)) AS otta
FROM (
SELECT
ma, schemaname, tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr, (maxfracsum*(datawidth-hdr)) AS datafrac
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS hdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::NUMERIC) AS bs,
CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_stat_user_tables su ON cc.oid = su.relid
) AS sml
ORDER BY tbsize_mb DESC;
How to Read the Output:
- tbloat: A bloat factor. 1.0 is ideal. 1.5 means 50% bloat. 2.0 means 100% bloat (the table is twice the size it should be).
- tbsize_mb: The estimated size of the wasted space in megabytes. Sort by this column to find your biggest offenders.
Script to Estimate Index Bloat
Indexes can also bloat. This script helps to identify them:
SELECT
current_database(), nspname AS schemaname, tblname, idxname,
bs*idx_res.relpages/1024/1024 AS "size_mb",
idx_res.idx_scan,
idx_res.idx_tuples,
(
SELECT pg_size_pretty(
(bs*idx_res.relpages - bs*COALESCE(
CEIL(
idx_res.idx_tuples * (
24 + max(v.avg_width) * (1 - max(v.null_frac))
) / (
(100 - i.fillfactor) * bs / 100
)
), 0
))::bigint
)
) AS "wasted_space"
FROM (
SELECT
nspname,
tbl.relname AS tblname,
idx.relname AS idxname,
idx.oid,
idx.reltuples,
idx.relpages,
stat.idx_scan,
stat.idx_tuples
FROM pg_class tbl
JOIN pg_index i ON i.indrelid = tbl.oid
JOIN pg_class idx ON idx.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = tbl.relnamespace
LEFT JOIN pg_stat_user_indexes stat ON stat.indexrelid = idx.oid
WHERE tbl.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
) AS idx_res
LEFT JOIN pg_stats v ON v.tablename = idx_res.tblname AND v.attname = ANY(string_to_array(idx_res.oid::regclass::text, '((, ))'::text)::text[])
CROSS JOIN (SELECT current_setting('block_size')::numeric AS bs) AS constants
GROUP BY 1, 2, 3, 4, 5, 6, 7
ORDER BY "wasted_space"::bigint DESC;
How to Read the Output:
- wasted_space: An estimate of the wasted space in the index. Sort by this column to find the most bloated indexes.
Strategies to Remove Bloat
Once you have diagnosed the bloat, it’s time to remove it.
The Nuclear Option: VACUUM FULL
- What it does: VACUUM FULL rewrites the entire table to a new file on the disk, with no dead space, and then swaps the old file for the new one. It reclaims the maximum possible space.
- Why you should NOT use it in production: It acquires an ACCESS EXCLUSIVE lock on the table, blocking all operations, including SELECTs, for its entire execution. On a large table, this means hours of downtime. It is a viable option only in very generous maintenance windows or for small tables.
The Online Option: The pg_repack Extension
This is the gold-standard tool for removing bloat in 24/7 production environments.
- How It Works: pg_repack is an extension that you need to install on your PostgreSQL. It works intelligently to avoid long-running locks:
- It creates a new, “shadow” table, identical to the original.
- It creates triggers on the original table to capture all changes (INSERT, UPDATE, DELETE) that occur during the process.
- It copies the content of the original table to the shadow table.
- It applies all the changes that were captured by the triggers to the shadow table.
- In a very short transaction at the end, it acquires an exclusive lock, swaps the original table for the shadow one (an almost instantaneous metadata operation), and drops the old table.
- The Result: You reclaim all the wasted space with a block that lasts only a few seconds at the end of the process, making it safe for most production environments. It can also be used to rebuild indexes online or to move tables to another tablespace.
Tuning Autovacuum to Prevent Bloat
Removing bloat is good, but preventing it from accumulating in the first place is even better. The key to this is an aggressive and well-tuned autovacuum.
- The Approach: PostgreSQL’s default autovacuum is configured to be conservative and not interfere with the workload. For tables with a high write rate, these default settings are often insufficient. The best practice is to adjust the autovacuum parameters on a per-table basis.
- Example Command:
ALTER TABLE public.high_write_table SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 1000);
- This instructs autovacuum to trigger a cleanup on this specific table when 5% of its rows have been modified (instead of the default 20%), ensuring that dead tuples are cleaned up much more frequently and preventing the accumulation of bloat.
Continuous and Proactive Hygiene and Debloating
Bloat in PostgreSQL is not a matter of “if,” but of “when.” It is an inevitable consequence of its MVCC architecture. Leaving it unmanaged is a way of accumulating technical debt that will manifest as slowness, rising costs, and performance incidents.
By understanding its causes, using scripts to diagnose it, and employing tools like pg_repack to safely remove it, engineering teams can transform bloat management from a reactive crisis maintenance into a proactive and continuous hygiene practice. Combined with a well-tuned autovacuum, this ensures that your databases remain lean, efficient, and performant in the long run.
Want help diagnosing and solving bloat problems and other silent bottlenecks in your PostgreSQL? Schedule a meeting with our specialist or watch a live demo!
Schedule a demo here.
Learn more about dbsnOOp!
Learn about database monitoring with advanced tools here.
Visit our YouTube channel to learn about the platform and watch tutorials.
Recommended Reading
- How dbsnOOp ensures your business never stops: This article explores the concept of business continuity from the perspective of proactive observability. Learn how predictive anomaly detection and root cause analysis allow engineering teams to prevent performance incidents before they impact the operation, ensuring the high availability of critical systems.
- Industry 4.0 and AI: The Database Performance Challenge and the Importance of Observability: Explore how the demands of Industry 4.0, IoT, and Artificial Intelligence are raising the complexity and volume of data to new heights. This article discusses why legacy monitoring tools are insufficient in this new scenario and how observability becomes crucial to ensure the performance and scalability needed for innovation.
- Performance Tuning: how to increase speed without spending more on hardware: Before approving an instance upgrade, it is crucial to exhaust software optimizations. This guide focuses on performance tuning techniques that allow you to extract the maximum performance from your current environment, solving the root cause of slowness in queries and indexes, instead of just remedying the symptoms with more expensive hardware.