Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Ordinary PostgreSQL VACUUM can remove dead index entries and make empty pages reusable, but it does not promise to rebuild an index into a smaller, compact file or return its space to the operating system. To assess a B-tree, use the pgstattuple extension’s pgstatindex function and interpret avg_leaf_density alongside the index’s size, page counts, fragmentation, workload, and fillfactor—not as a standalone bloat percentage.
Why ordinary VACUUM does not shrink an index file
PostgreSQL’s routine VACUUM handles dead row versions and other maintenance work. For a B-tree index, vacuuming can remove dead index entries and reclaim completely empty pages for reuse. It does not generally rewrite all the remaining pages into a compact structure, so pages with only a few surviving keys can remain allocated. The relation typically retains reclaimed storage for future reuse rather than returning it to the operating system. PostgreSQL’s routine vacuuming documentation describes the distinction between reclaiming space for reuse and returning it to the operating system.
That is why “VACUUM never changes an index” is too broad: cleanup can change its contents and make empty pages reusable. The narrower point is that ordinary vacuuming is not a whole-index compaction operation.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitchesVACUUM FULL is a different operation
VACUUM FULL rewrites a table and can return space to the operating system. It is slower than routine vacuuming, needs extra disk space while rewriting, and takes an ACCESS EXCLUSIVE lock. Do not infer from its table rewrite that ordinary VACUUM compacts indexes; index rebuilding is addressed separately with REINDEX. PostgreSQL’s vacuuming documentation covers these operational differences.
#1 Best Overall
Index cleanup can be skipped automatically
Current PostgreSQL documentation sets INDEX_CLEANUP to AUTO by default. In that mode, vacuuming can skip index cleanup when there are very few dead tuples. Setting INDEX_CLEANUP ON forces conservative cleanup, subject to the wraparound failsafe behavior. Even when cleanup runs, removing dead entries is not the same as rebuilding all index pages. The VACUUM command reference documents the option and failsafe qualification.
Measure B-tree density with pgstatindex
Install the pgstattuple extension if it is permitted in your database, then pass the target B-tree index as a regclass:
Rank #2
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('schema.index_name'::regclass);The function reports B-tree details including total size, leaf and internal page counts, empty and deleted pages, avg_leaf_density, and leaf_fragmentation. The PostgreSQL documentation defines avg_leaf_density as the average density of leaf pages. It is an average measure of how full those pages are; it is not a universal percentage of index space that can be removed. The pgstattuple documentation lists the output columns and their meanings.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Read density in context
Pair density with the index’s physical size, page counts, fragmentation, configured fillfactor, and history of inserts, updates, and deletes. A low average may warrant investigation, but PostgreSQL documentation does not establish a universal density cutoff at which every index should be rebuilt. The function is documented for B-tree indexes; do not treat its results as a generic measure for other index methods.
pgstatindex gathers information page by page. If writes occur during the scan, the returned values should not be treated as an instantaneous snapshot of the entire index. For comparisons, repeat measurements under similar workload conditions and account for concurrent activity. The function’s documentation explains its page-by-page behavior.
When index bloat is worth investigating
Look at the workload pattern, not just one density reading. PostgreSQL calls out a specific case: deleting most, but not all, keys from many key ranges can leave pages with only a few surviving keys. Empty pages can be reclaimed for reuse, but these partly occupied pages may remain, reducing space utilization. PostgreSQL recommends periodic reindexing for that pattern; it does not prescribe a single avg_leaf_density threshold. The routine reindexing guidance describes the sparse-page deletion pattern.
For non-B-tree index types, the potential for bloat has not been well researched in the cited PostgreSQL guidance. Monitor their physical size rather than applying B-tree density interpretation to them.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Decide whether to rebuild the index
Use the evidence and the operational constraints together. The following questions help distinguish an index that may benefit from rebuilding from one whose pages are likely to be reused by its ongoing workload.
- Size and density: Is the index large enough for the wasted space to matter, and do density, empty/deleted page counts, and fragmentation support the concern?
- Workload shape: Has the index seen broad deletes that left sparse key ranges, or is it serving an active insert/update workload that may reuse space?
- Cleanup behavior: Is vacuum index cleanup occurring, or might
INDEX_CLEANUP AUTObe skipping it because there are very few dead tuples? - Rebuild capacity: Is enough free disk available for a rebuild, and can the workload tolerate its resource use and lock impact?
- Expected reuse: Is the freed space likely to be useful to the existing relation, or is reducing the relation’s physical size an important operational goal?
Choose a REINDEX mode around lock impact
In PostgreSQL 17 documentation, the default REINDEX requires an ACCESS EXCLUSIVE lock. REINDEX CONCURRENTLY uses a less restrictive SHARE UPDATE EXCLUSIVE lock. The concurrent form reduces lock severity; it is not lock-free or cost-free. Confirm the exact syntax and behavior for the server’s major version and plan around workload and acceptable impact. The PostgreSQL 17 REINDEX reference documents the lock modes.
How fillfactor affects B-tree page packing
B-tree fillfactor influences how full pages are when an index is built. PostgreSQL documents a default of 90. Pages that become completely full can split as rows are inserted or updated; a lower fillfactor can help some insert/update workloads by leaving more room, but whether it helps depends on the workload. It is a page-packing setting, not a general-purpose cure for every source of index bloat. The CREATE INDEX documentation describes B-tree fillfactor and its default.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
