As you have already been answered in the comments, it is best to use the standard pgstattuple extension.
If you do not want to use it, for some reason, you can see the approximate bloat values as follows:
-- Perform ANALYZE on your table
ANALYZE <table_name>;
-- Get the number of deadlines in your tables.
select schemaname,
relname,
pg_size_pretty(pg_relation_size(schemaname|| '.' || relname)) as size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0 THEN round((n_dead_tup::float /
n_live_tup::float)::numeric, 4) END AS dead_tup_ratio,
last_autovacuum,
last_autoanalyze
from pg_stat_user_tables
order by dead_tup_ratio desc NULLS LAST;
The higher the dead_tup_ratio, the higher the bloat of your table. But these are approximate data collected in the course of collecting statistics!
The best way to know for sure about bloat is to use the pgstattuple extension.
Kendralittle
kendralittle.com › 2025 › 12 › 01 › index-bloat-postgres-why-it-matters-how-to-identify-and-resolve
Index Bloat in Postgres: Why It Matters, How to Identify, How to Resolve
December 1, 2025 - Here’s the initial size and density - the primary key pages are 90.1% full, the multi column index pages are 70.1% full. /* Delete a large portion of rows */ DELETE FROM test_bloat WHERE id % 2 = 0; /* You can recheck the size using the query above, but nothing will be different until you run VACUUM.
PostgreSQL Wiki
wiki.postgresql.org › wiki › Index_Maintenance
Index Maintenance - PostgreSQL wiki
One of the common needs for a REINDEX ... FULL (with pre 9.0 versions). An estimator for the amount of bloat in a table has been included in the check_postgres script, which you can call directly or incorporate into a larger monitoring system....
Find bloated tables and indexes in PostgreSQL without extensions - Database Administrators Stack Exchange
I suspect that the tables and indexes on those tables might be bloated. I've seen that there are extensions options for PostgreSQL that can check this, but I would like to avoid creating extensions in my database. More on dba.stackexchange.com
Postgres Bloat Minimization : r/PostgreSQL
I reduced bloat factor, but waste in bytes is still too high, do I need to manually run VACCUM FULL?
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/ all queries that show bloat info are estimates. there are not absolute, true, values, just some result from calculation that can be ok, or can be very not related to what is really happening. Having said that - even if you have bloat, it doesn't mean that there is anything wrong with it. Is your db slow? Why is "bloat" (whether it's real, or not) a problem for you? The thing that you have to understand - it is technically not possible to have db/table without bloat, if you're actually using it. Any update or delete, will cause some "bloat". most of the cases it's irrelevant, as long as your autovacuum is configured properly. More on reddit.com
11
1
June 27, 2023Database bloat
What have you researched so far? The question seems a bit let-me-google-that-for-you, but here is 5mins of goodwill mostly because PG is fucking awesome and interesting. Firstly, read Routine Vacuuming in the manual. Autovacuum is probably the main thing to walk away with. There is applicable config tuning details in there too. Then, off the top of my head: Understand the basics of MVCC and the tradeoff for storing a row multiple times as it changes. This has a material impact on the size of your tables with regards to updates/deletes. Tables are better at optimising thier storage than indexes, given there is no order involved. So you will notice indexes bloat a lot faster than thier tables, generally speaking. Look up "Column Alignment". There is dead padding possible between columns of fixed and variable length data type. This has a material impact on big tables. As part of operations, regularly (say 24hr) run and graph table and index sizes + table and index bloat sizes (google these queries). It's really useful to see trends over time. Good table design, like partitioning where suitable, will help making reclaiming bloat a lot easier on "colder" tables. More on reddit.com
6
3
January 5, 202352:24
Bloat in PostgreSQL: a taxonomy - YouTube
27:06
Postgres index bloat | The Backend Engineering Show - YouTube
17:49
70GB of Unused Bloated Index Space Freed on Postgres, Here is how ...
25:26
Understanding & Managing Postgres Table Bloat | Citus Con: An Event ...
13:51
Scaling Postgres Episode 182 Boundless Text | Revoke Permission ...
PostgreSQL Wiki
wiki.postgresql.org › wiki › Show_database_bloat
Show database bloat - PostgreSQL wiki
October 6, 2015 - Also note that before version 9.5, data types that are not analyzable, like xml, will make a table look bloated as the space needed for those columns is not accounted for. For tables, see these queries. Make sure to pick the correct one for your PostgreSQL version. For Btree indexes, pick the ...
GitHub
github.com › ioguix › pgsql-bloat-estimation
GitHub - ioguix/pgsql-bloat-estimation: Queries to mesure statistical bloat in indexes and tables for PostgreSQL · GitHub
Queries to mesure statistical bloat in btree indexes and tables for PostgreSQL.
Starred by 575 users
Forked by 102 users
Medium
medium.com › @jramcloud1 › understanding-and-managing-bloating-in-postgresql-a-complete-guide-for-dbas-95f50670b488
Understanding and Managing Bloating in PostgreSQL: A Complete Guide for DBAs | by Jeyaram Ayyalusamy | Medium
July 1, 2025 - Decreased query performance due to more data being scanned. Less effective use of cache and I/O bandwidth. This is known as table bloat or index bloat. PostgreSQL uses MVCC to ensure consistency in concurrent transactions.
Amazon Web Services
docs.aws.amazon.com › amazon rds › user guide for aurora › working with amazon aurora postgresql › best practices with amazon aurora postgresql › diagnosing table and index bloat
Diagnosing table and index bloat - Amazon Aurora
Observing bloat without interrupting your applicationAvoiding bloat in temporary tablesAvoiding bloat in indexes · You can use PostgreSQL Multiversion Concurrency Control (MVCC) to help preserve data integrity. PostgreSQL MVCC works by saving an internal copy of updated or deleted rows (also ...
MinervaDB
minervadb.xyz › home › analyzing index bloat in postgresql
PostgreSQL Index Bloat: Diagnose and Reduce Table Bloat
April 10, 2025 - Index Fetches: Furthermore, this shows how often the system uses indexes to fetch data. It reveals the index’s efficiency in returning query results. Bloat Percentage: Finally, this value calculates the proportion of index bloat.
Crunchy Data Blog
crunchydata.com › blog › checking-for-postgresql-bloat
Checking for PostgreSQL Bloat | Crunchy Data Blog
Index bloat (as long as it’s not a primary key) is easier to solve because you can either just reindex that one index, or you can concurrently create a new index on the same column and then drop the old one when it’s done. In all cases when you run VACUUM, it’s a good idea to run ANALYZE as well, either at the same time in one command or as two separate commands. This updates the internal statistics that Postgres uses when creating query plans.
OpenSourceDB
opensource-db.com › home › blog › index bloat management in postgresql
Index Bloat Management in PostgreSQL - OpenSourceDB
August 28, 2024 - By addressing index bloat, you not only free up valuable storage resources but also improve system responsiveness and reduce the time required for backups and recovery operations. Efficient index management minimizes resource consumption, prevents fragmentation, and ensures that your database operates smoothly and effectively, ultimately leading to a better user experience and lower operational costs. ... postgres=# SELECT * FROM pgstattuple('idx_test_id'); -[ RECORD 1 ]------+------ table_len | 16384 tuple_count | 40 tuple_len | 640 tuple_percent | 3.91 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 7348 free_percent | 44.85
Ozkanpakdil
ozkanpakdil.github.io › posts › posts from 2025 › understanding and monitoring index and table bloat in postgresql
Understanding and Monitoring Index and Table Bloat in PostgreSQL | Özkan Pakdil Software Engineer
September 8, 2025 - Drop unused indexes: If pg_stat_user_indexes.idx_scan = 0 for months, it’s a candidate for removal. Can easily win 40%. ... The key is to watch trends, not just snapshots. A sudden growth spike is often more telling than a single measurement. Use Boguk’s formula for fast monitoring. Use pgstattuple or ioguix scripts for precise checks. Prevent bloat with autovacuum tuning, reindexing, and dropping unused indexes.
Postgres Professional
postgrespro.com › list › thread-id › 2530126
Thread: index bloat estimation : Postgres Professional
I use https://github.com/ioguix/pgsql-bloat-estimation . It's pretty good at estimating bloat on tables and Btree indexes (though deduplication in v13 makes it harder). check_pgactivitu uses the queries from this repository.
Netdata
netdata.cloud › blog › postgresql-database-bloat
How to monitor and fix Database bloats in PostgreSQL? | Netdata
postgres=# CREATE EXTENSION pgstattuple; CREATE EXTENSION · You can use a query like the one below to show the dead tuples (or table bloat) for every single table / index in your Database.
Postgresscripts
postgresscripts.com › post › find-postgresql-index-bloat-and-wasted-space
Find PostgreSQL Index Bloat and Wasted Space | Postgres Scripts
May 22, 2026 - Use SQL to estimate index bloat in PostgreSQL B-tree indexes. Find wasted bytes per index, spot oversized indexes, and know when to rebuild them.
Mindful Chase
mindfulchase.com › home › explore › troubleshooting tips › fixing table and index bloat in postgresql for faster queries
Fixing Table and Index Bloat in PostgreSQL for Faster Queries - Mindful Chase
February 9, 2025 - SELECT pid, age(clock_timestamp(), query_start), query FROM pg_stat_activity WHERE state != 'idle' AND query_start IS NOT NULL ORDER BY query_start ASC; ... Regularly monitor dead tuples using pg_stat_user_tables. Schedule periodic VACUUM FULL on critical tables. Optimize indexes by using REINDEX when needed. PostgreSQL table and index bloat can lead to significant performance degradation.
Medium
medium.com › @kemalozz › understanding-bloat-in-postgresql-and-how-to-manage-it-0ca1d6db0c78
Understanding Bloat in PostgreSQL and How to Manage It | by Oz | Medium
April 2, 2026 - The first query focuses on identifying bloat in indexes. Indexes can become bloated due to frequent insertions, updates, and deletions.
Citus Data
citusdata.com › blog › 2017 › 10 › 20 › monitoring-your-bloat-in-postgres
Monitoring your bloat in Postgres - Citus Data
October 20, 2017 - WITH constants AS ( -- define some constants for sizes of things -- for reference down the query and easy maintenance SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma ), no_stats AS ( -- screen out table who have attributes -- which dont have stats, such as JSON SELECT table_schema, table_name, n_live_tup::numeric as est_rows, pg_table_size(relid)::numeric as table_size FROM information_schema.columns JOIN pg_stat_user_tables as psut ON table_schema = psut.schemaname AND table_name = psut.relname LEFT OUTER JOIN pg_stats ON table_schema = pg_stats.schemaname AND table_na
OneUptime
oneuptime.com › home › blog › how to build postgresql index maintenance strategy
How to Build PostgreSQL Index Maintenance Strategy
January 30, 2026 - Learn to implement index maintenance strategies with REINDEX, pg_repack, and monitoring for optimal query performance and storage efficiency.