PostgreSQL Bloated Tables

Introduction

Bloat refers to the excess, wasted disk space occupied by "dead rows" (tuples) within tables and indexes that no longer hold active data. ​ 

​This occurs because PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage concurrent transactions, which ensures transaction snapshot isolation.​ 

Every UPDATE/DELETE Operation doesn't immediately delete old records from the table(data pages), but marks them with xmin/xmax markers (where xmin is the transaction ID that created the record, and xmax is the transaction ID that deleted or updated it). ​ 

As a result, old version records remain physically in the database; the system doesn't discard anything immediately, it simply marks the last version as "deleted" with the appropriate xmax value. ​ 

​This accumulates multiple versions of the same record, of which ultimately only the newest is active, while the rest are "dead" data requiring future cleanup (VACUUM). ​ 

​So, instead of modifying or deleting data directly in place, updates and deletes leave the original data on disk and create new versions (Updates) elsewhere, meaning the physical file size doesn't shrink. VACUUM removes these dead row versions and makes their space reusable within the table, so it should run regularly (autovacuum) on frequently updated tables. Plain VACUUM allows normal reads and writes and usually retains reclaimed space within the table rather than returning it to the operating system. However, if the last pages contain no live rows, it can shorten the file and return that space to the operating system, while VACUUM FULL will rebuild the table and it’s indexes, returning the unused space to the operating system. 

MVCC, xmin/xmax, and Snapshot Isolation in PostgreSQL 

PostgreSQL uses the Multi-Version Concurrency Control (MVCC) mechanism to prevent parallel working requests (transactions) from interfering with each other's work. The foundation of MVCC is that each transaction sees a consistent "snapshot" of the database, a picture of records at the moment of its start.​ 

Database table bloat

There are multiple factors than can contribute to database table bloat, which can impact queries performance and waste storage making your database grow indefinitely: 

• Long-Running Transactions: Active or idle transactions freeze the visibility horizon (xmin), preventing VACUUM from freeing space occupied by dead rows. ​ 

• Delayed Autovacuum: Aggressive write workloads running faster than default or poorly tuned autovacuum settings leave dead space unrecovered. ​ 

• Replication & Standby Lags: Delayed logical replication slots or hot_standby_feedback on replicas force the primary database to hold old row versions. ​ 

• Mass Batch Deletions: Large single operation deletes free page space that PostgreSQL cannot shrink back from the OS without a heavy maintenance command like VACUUM FULL or tools like pg_repack / pg_squeeze.​ 

The diagram below illustrates how a PostgreSQL healthy table looks like comparing to a bloated table.

 How bloat impacts performance

Disk space consumption: Bloat occupies excess disk space, and reading/writing the same amount of real data requires more I/O operations. Accordingly, disk usage and backup time increase because PostgreSQL has to operate on a large volume, while part of the data is effectively useless:

• Disk space consumption: Bloat occupies excess disk space, and reading/writing the same amount of real data requires more I/O operations. Accordingly, disk usage and backup time increase because PostgreSQL has to operate on a large volume, while part of the data is effectively useless.​
• Increased I/O: The database must scan more physical data from disk to find the actual, active data.​ When PostgreSQL executes a query, it reads the required data pages, or blocks, from storage into memory. When those pages are heavily populated with dead tuples, fewer useful rows fit within each block. As a result, PostgreSQL may need to scan more physical data blocks to retrieve the same amount of active data.This additional scanning increases I/O activity, which can slow down queries and put extra pressure on storage resources. In simple terms, the database has to work through unnecessary data before finding the rows that actually matter.
• CPU overhead: PostgreSQL relies on Multi-Version Concurrency Control (MVCC) to determine which tuple versions are visible to each transaction. During a query, PostgreSQL evaluates tuple visibility according to the transaction's snapshot. When many dead tuples are present, more obsolete tuple versions may need to be examined while retrieving active data. This additional processing consumes CPU resources and increases the overall cost of read operations.
• Reduced cache efficiency: Bloated tables take up more memory space, reducing the amount of data that can be cached for faster access.​ Having data pages in memory does not completely eliminate the performance impact of dead tuples. Dead tuples still consume space within those pages, meaning that fewer active rows can be stored in the same amount of cached memory. As PostgreSQL scans the pages, it must evaluate tuple visibility and may need to examine more pages to find the active rows required by the query. Over time, this reduces the effective capacity of the buffer cache, increases memory pressure, and can contribute to additional cache misses and storage reads.
• Bloat can slow index lookups and scans, which will show up in slowly increasing query times and changing query plans​. The impact of dead tuples is not limited to table data; indexes can become bloated as well. As rows are updated or deleted, obsolete index entries can remain until PostgreSQL's maintenance mechanisms clean them up. As these entries accumulate, the index grows and becomes less efficient. A larger index means PostgreSQL may need to examine more index pages to locate the required rows, increasing cache pressure and potentially causing more storage I/O. Once a matching entry is found, PostgreSQL may still need to visit the corresponding table page to verify tuple visibility. This additional work increases the cost of index scans and can further degrade query performance.
• Slower queries: All of these factors combine to make queries slower and less efficient.

How to Monitor Table Bloat​

One of the most popular and efficient tools for monitoring bloated tables in PostgreSQL is pgstattuple extension. 

Once installed, you can use the pgstattuple function to get an overview of a particular table    

SELECT * FROM pgstattuple('your_table_name'); ​

This query will return several key metrics:​ 

• table_len: Total size of the table/index.​ 

• tuple_count: Number of tuples (rows) in the table.​ 

• tuple_len: Total size of all tuples.​ 

• dead_tuple_count: Number of dead tuples, which contribute to bloat.​ 

• dead_tuple_len: Total size occupied by dead tuples.​ 

 

SELECT * FROM pgstattuple('test'); ​  

Apart from pgstattuple, you can follow other methods to monitor your Postgres bloat, for example, pg_stat_user_tables provides statistics about access to user tables, which can help indirectly identify bloat. If you observe that seq_scan is increasing for a table, it might be an indication that the table's indexes are not performing optimally due to bloat.  

Other very useful query can be found in Show database bloat - PostgreSQL wiki​.

How to fix ​PostgreSQL bloats

Rebuilding tables​ 

The most effective way to reclaim space in PostgreSQL is to rebuild the table, which physically rewrites the table's data and indexes, eliminating unused space and fragmentation. 

To rebuild a table and reorganize its physical space we can use native PostgreSQL feature vacuum full, however it requires exclusive lock on the table which, depending on the table size, can have a significant impact on your application availability.  

A special consideration regarding large object, is a specific vacuumlo process that can be used to remove orphaned large objects from a PostgreSQL database before a vacuum full can be done against pg_largeobjetcs​. 

There are other options that can be used online such as pg_repack and pg_squeeze extensions.  

For both these extensions the target table must have either a PRIMARY KEY or a UNIQUE index on a NOT NULL column for the operation to be successful. 

​

Rebuilding Indexes​ 

In PostgreSQL B-tree index pages that become completely empty can be reclaimed and reused. However, partially emptied pages may still lead to inefficient space utilisation. For example, if most index entries on a page are deleted but a small number remain, the page continues to occupy space. 

As a result, workloads that repeatedly delete most, but not all, index entries across many key ranges can gradually accumulate sparsely populated index pages and lead to index bloat. For these usage patterns, periodic REINDEX should be considered to restore more efficient space utilisation. 

 

Optimizing indexing 

Regularly review and remove unnecessary, overlapping, or redundant indexes to prevent their contribution to bloat.​ We can leverage on Azure PostgreSQL Flexible Server index tuning feature for recommendations.  

 

Partition large tables​ 

 Bulk data loads and deletions can be handled more efficiently through TABLE PARTITIONING, provided the partitioning strategy is designed around the workload. Instead of deleting large volumes of rows, an entire partition can be removed using DROP TABLE or detached with ALTER TABLE DETACH PARTITION. This is significantly faster than performing a bulk DELETE and avoids the additional VACUUM work that large deletions can generate 

 

Fine tuning autovacuum 

If autovacuum does not run frequently or aggressively enough, dead tuples can accumulate, causing PostgreSQL to consume additional disk space and potentially leading to significant table bloat. 

By tuning autovacuum thresholds and execution settings to match your workload, particularly for tables with heavy DML activity, you can ensure that dead space is reclaimed sooner and made available for reuse. 

Difference between dead tuples and reusable free space 

Overhead of Diagnostics and Maintenance 

Bloat maintenance is not free. Both identifying bloat and reclaiming disk space can consume significant database resources, so these operations should be planned carefully on production systems. 

pgstattuple diagnostics: Detailed inspection of large tables can generate considerable read I/O and buffer activity. For routine monitoring, lightweight statistics such as n_dead_tup from pg_stat_user_tables are generally better suited to identifying potential problematic tables, while deeper inspection can be reserved for targeted investigations. 

VACUUM FULL: Reclaiming disk space with VACUUM FULL requires rewriting the table and holding an ACCESS EXCLUSIVE lock. This can make the table unavailable to concurrent workloads and requires additional disk space while the new copy is created. 

pg_repack: For environments where prolonged blocking is unacceptable, pg_repack provides an online alternative that allows normal activity during most of the rebuild. However, the operation still consumes I/O, storage and WAL resources and may require brief locking during its final stages.

Test case  

Let’s run a test case to show how bloated tables and bloated indexes can impact storage usage on a PostgreSQL database, as well as impact on query performance. 

This test was run in an Azure PostgreSQL Flexible Server Version 16.15 with a compute size Standard_D2ds_v5 (2 vCores, 8 GiB memory, 3750 max iops) / 256 GiB. 

postgres=> CREATE DATABASE bloat; 
CREATE DATABASE 

postgres=> \c bloat 
bloat=> SELECT pg_size_pretty(pg_database_size('bloat')) AS db_size; 
db_size  
7719 kB 

bloat=> CREATE TABLE test (col1 INT, col2 INT, col3 INT); 
CREATE TABLE 

bloat=> INSERT INTO test (col1,col2,col3) SELECT i,i,i FROM generate_series(1, 600000000) AS t(i); 
bloat=> select count(*) from test; 
count  
600000000 

bloat=> CREATE UNIQUE INDEX idx1 ON test (col1); 
bloat=> SELECT pg_size_pretty(pg_database_size('bloat')) AS db_size; 
db_size  
37 GB 

bloat=> SELECT pg_size_pretty(pg_total_relation_size('public.test')) AS table_total_size; 
table_total_size  
37 GB 

bloat=> SELECT pg_size_pretty(pg_relation_size('public.test')) AS table_size; 
table_size 
25 GB 

bloat=> SELECT pg_size_pretty(pg_relation_size('public.idx1')) AS index_size; 
index_size  
12 GB 

bloat=> SELECT schemaname, relname, n_dead_tup, n_live_tup, round(n_dead_tup::float/n_live_tup::float*100)dead_pct, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze 
FROM pg_stat_all_tables 
WHERE n_live_tup > 0 
AND schemaname='public' 
AND relname ='test'; 

schemaname | relname | n_dead_tup | n_live_tup | dead_pct | autovacuum_count | last_vacuum | last_autovacuum | last_autoanalyze | last_analyze  
------------+---------+------------+------------+----------+------------------+-------------+-----------------+------------------+-------------- 
public     | test    |          0 |  600000000 |        0 |                0 |             |                 |                  |  

bloat=> SELECT * FROM pgstattuple('test');  

table_len  | tuple_count |  tuple_len  | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent  
-------------+-------------+-------------+---------------+------------------+----------------+--------------------+------------+-------------- 
 26568654848 |   600000000 | 21600000000 |          81.3 |                0 |              0 |                  0 |   77844016 |         0.29	 

 

In above output from pg_stat_all_tables, we can see table test with no dead_tuples (n_dead_tup) and from pgstattuple output we confirm it with dead_tuple_count and see there’s very low free space available as well. 

Server-wide storage usage increases after table test and index idx1 creation

bloat=> CREATE TABLE test2 AS SELECT * FROM test; 
CREATE TABLE 

bloat=> SELECT pg_size_pretty(pg_relation_size('public.test2')) AS table_size; 
table_size 
25 GB 

bloat=> \dt+ 
                                 List of relations 
 Schema | Name  | Type  | Owner | Persistence | Access method | Size  |  Description
  --------+-------+-------+-------+-------------+---------------+-------+------------- 
 public | test  | table | edu   | permanent   | heap          | 25 GB |  
 public | test2 | table | edu   | permanent   | heap          | 25 GB |  
(2 rows) 

bloat=> \d test 
                Table "public.test" 
 Column |  Type   | Collation | Nullable | Default  
--------+---------+-----------+----------+--------- 
 col1   | integer |           |          |  
 col2   | integer |           |          |  
 col3   | integer |           |          |  
Indexes: 
    "idx1" UNIQUE, btree (col1) 

bloat=> \d test2 
               Table "public.test2" 
 Column |  Type   | Collation | Nullable | Default  
--------+---------+-----------+----------+--------- 
 col1   | integer |           |          |  
 col2   | integer |           |          |  
 col3   | integer |           |          |  

 

Server-wide storage usage increases after table test2 creation

 

bloat=> \timing 
Timing is on. 

bloat=> EXPLAIN ANALYZE SELECT t.col1, t2.col2 FROM  test t JOIN test2 t2 ON t.col1 = t2.col1 AND  t2.col1 = 6777731; 

                         QUERY PLAN                                                                
Nested Loop  (cost=1000.58..6369286.70 rows=1 width=8) (actual time=120379.691..120439.792 rows=1 loops=1) 
   ->  Index Only Scan using idx1 on test t  (cost=0.57..2.59 rows=1 width=4) (actual time=0.820..0.824 rows=1 loops=1) 
         Index Cond: (col1 = 6777731) 
         Heap Fetches: 0 
   ->  Gather  (cost=1000.00..6369284.10 rows=1 width=8) (actual time=120378.867..120438.962 rows=1 loops=1) 
         Workers Planned: 2 
         Workers Launched: 2 
         ->  Parallel Seq Scan on test2 t2  (cost=0.00..6368284.00 rows=1 width=8) (actual time=113739.478..120360.332 rows=0 loops=3) 
               Filter: (col1 = 6777731) 
               Rows Removed by Filter: 200000000 
Planning Time: 0.944 ms 
Execution Time: 120439.911 ms 

bloat=> DELETE FROM test2 WHERE col1 < 6777731; 
DELETE 6777730 

bloat=> DELETE FROM test2 WHERE col1 > 6777731; 
DELETE 593222269 

bloat=> SELECT COUNT(*) FROM test​2; 
 count ​ 
-------​ 
1 
Time: 162497.229 ms (02:42.497) 

bloat=> SELECT schemaname, relname,n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze,  last_analyze 
FROM pg_stat_all_tables 
WHERE relname IN ('test2');  

schemaname | relname | n_dead_tup | n_live_tup | autovacuum_count | last_vacuum | last_autovacuum | last_autoanalyze | last_analyze 
------------+---------+------------+------------+------------------+-------------+-------------------------------+-------------------------------+-------------- 
public | test2 | 151260968 | 0 | 2 | | 2026-10-28 15:42:04.591895+00 | 2026-10-28 15:42:08.369063+00 | public | test | 0 | 600000140 | 1 | | 2026-10-19 14:39:09.677403+00 | 2026-10-19 14:39:27.062848+00 | 
(1 row) 

bloat=> SELECT schemaname, relname,n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, last_analyze 
FROM pg_stat_all_tables 
WHERE relname IN ('test2'); 

 schemaname | relname | n_dead_tup | n_live_tup | autovacuum_count | last_vacuum | last_autovacuum | last_autoanalyze | last_analyze
 ------------+---------+------------+------------+------------------+-------------+-------------------------------+-------------------------------+-------------- 
public | test2 | 0 | 0 | 3 | | 2026-10-28 15:52:23.885401+00 | 2026-10-28 15:42:08.369063+00 | 
(1 row) 

bloat=> SELECT * FROM pgstattuple('test2');  
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-------------+-------------+-----------+---------------+------------------+----------------+--------------------+-------------+-------------- 
26568818688 | 1 | 36 | 0 | 0 | 0 | 0 | 26464870540 | 99.61  
(1 row) 
Time: 266381.968 ms (04:26.382) 

bloat=> SELECT pg_size_pretty(pg_relation_size('public.test2')) AS table_size;  
table_size 
25 GB 
(1 row) 
Time: 4.848 ms  

 

As we can see even after rows deletion and vacuum performed to remove dead tuples, storage hasn’t decreased. 

 

bloat=> EXPLAIN ANALYZE SELECT t.col1, t2.col2 FROM  test t JOIN test2 t2 ON t.col1 = t2.col1 AND  t2.col1 = 6777731; 

          QUERY PLAN                                                        
------------------------------------------------------------------------------------------------------------------------ 
 Nested Loop  (cost=0.57..3243266.60 rows=1 width=8) (actual time=99702.040..126103.260 rows=1 loops=1) 
   ->  Index Only Scan using idx1 on test t  (cost=0.57..2.59 rows=1 width=4) (actual time=1.605..1.609 rows=1 loops=1) 
         Index Cond: (col1 = 6777731) 
         Heap Fetches: 0 
   ->  Seq Scan on test2 t2  (cost=0.00..3243264.00 rows=1 width=8) (actual time=99700.432..126101.645 rows=1 loops=1) 
         Filter: (col1 = 6777731) 
 Planning Time: 0.645 ms 
 Execution Time: 126103.317 ms 
(8 rows) 
Time: 126107.410 ms (02:06.107) 

bloat=> VACUUM FULL test2; 
Time: 126024.034 ms (02:06.024)  

bloat=> SELECT pg_size_pretty(pg_relation_size('public.test2')) AS table_size; table_size 
8192 bytes 
(1 row) 
Time: 3.604 ms  

bloat=> SELECT * FROM pgstattuple('test2');  

table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- 
8192 | 1 | 36 | 0.44 | 0 | 0 | 0 | 8120 | 99.12  
(1 row) 
Time: 3.682 ms 

bloat=> EXPLAIN ANALYZE SELECT t.col1, t2.col2 FROM  test t JOIN test2 t2 ON t.col1 = t2.col1 AND  t2.col1 = 6777731; 

                     QUERY PLAN                                                        
------------------------------------------------------------------------------------------------------------------------ 
 Nested Loop  (cost=0.57..3.62 rows=1 width=8) (actual time=0.033..0.036 rows=1 loops=1) 
   ->  Index Only Scan using idx1 on test t  (cost=0.57..2.59 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1) 
         Index Cond: (col1 = 6777731) 
         Heap Fetches: 0 
   ->  Seq Scan on test2 t2  (cost=0.00..1.01 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1) 
         Filter: (col1 = 6777731) 
 Planning Time: 0.143 ms 
 Execution Time: 0.067 ms 
(8 rows) 
Time: 3.663 ms 

 

Now after vacuum full storage has decreased as expected.

Note: Row versions may still be required by active snapshots and cannot be removed. Long-running transactions or other snapshot-retention conditions can therefore limit space reclamation. VACUUM FULL also requires an exclusive lock and may wait for transactions accessing the table to finish. Always investigate these conditions before running maintenance. 

 

test2: Before and after VACUUM FULL 

Both measurements concern test2 with one live row. Execution times are the server-side values reported by EXPLAIN ANALYZE, not the client-reported elapsed times. These individual test runs illustrate the effect of the rebuild in this scenario and are not a general performance benchmark.

Conclusion 

PostgreSQL table bloat is not simply a matter of counting dead tuples. Autovacuum can remove obsolete row versions and make their space available for reuse without substantially reducing the physical table size.  

In this test, test2 contained only one live row and no dead tuples after autovacuum, yet still occupied approximately 25 GB, with 99.61% of its space reported as free.  

The query continued to scan this large physical footprint, demonstrating why a low dead-tuple count alone does not establish that a table is space-efficient. 

“Rewriting” test2 with VACUUM FULL reduced the table to 8192 bytes, and the recorded query execution time fell from 126103.317 ms to 0.067 ms.  

These results illustrate the benefit of reclaiming excessive unused space in this specific scenario as the performance benefit for other workloads will vary. 

The comparison concerns a table reduced to a single row and it does not isolate index bloat or establish a general benchmark. 

The practical approach is to prevent avoidable bloat first: tune autovacuum for the workload, investigate long-running transactions and replication-related retention, and review indexing and partitioning strategies. Use lightweight statistics to identify candidates for investigation, then targeted diagnostics to assess dead tuples, reusable free space and physical size alongside query behaviour.  

When a rebuild is justified, choose VACUUM FULL or a supported online alternative according to locking tolerance, available storage and resource capacity. Successful maintenance should address the cause of bloat as well as its symptoms, and its benefit should be verified through before-and-after measurements.

References 

• https://learn.microsoft.com/azure/postgresql/troubleshoot/how-to-autovacuum-tuning​ 

• https://www.postgresql.org/docs/current/sql-vacuum.html​ 

• https://www.postgresql.org/docs/current/pgstattuple.html ​ 

• https://www.postgresql.org/docs/current/sql-vacuum.html​ 

• https://github.com/reorg/pg_repack​ 

• https://learn.microsoft.com/azure/postgresql/troubleshoot/how-to-perform-fullvacuum-pg-repack

• https://github.com/cybertec-postgresql/pg_squeeze​ 

• https://www.postgresql.org/docs/current/sql-reindex.html  

• https://www.postgresql.org/docs/current/ddl-partitioning.html 

• https://wiki.postgresql.org/wiki/Show_database_bloat​