Ordinary PostgreSQL VACUUM can remove dead index entries and make empty pages reusable, but it does not promise to rebuild a B-tree into a smaller file or return space to the operating system. To inspect B-tree page density, use the pgstattuple extension’s pgstatindex function and interpret avg_leaf_density alongside the index’s size, page counts, fragmentation and workload—not as a standalone bloat percentage.
Why ordinary VACUUM does not shrink an index file
PostgreSQL’s routine VACUUM removes dead row versions and supports ongoing database maintenance. It can also remove dead index entries and make completely empty B-tree pages available for reuse. But ordinary vacuuming generally retains reclaimed space within the relation rather than returning it to the operating system, and it does not rewrite every index page into a compact structure.
As an Amazon Associate I earn from qualifying purchases.
That distinction matters: VACUUM does affect indexes, but cleaning entries is not the same operation as rebuilding an index. Pages that still contain a few live keys may remain allocated even when their space utilization is poor. Deleting most, but not all, keys across many key ranges can leave this kind of sparsely populated structure. PostgreSQL’s routine reindexing guidance identifies such deletion patterns as a reason periodic reindexing may be appropriate.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →VACUUM and VACUUM FULL are different
Regular VACUUM makes space reusable inside a relation and ordinarily does not shrink its on-disk allocation. VACUUM FULL rewrites the table and can return space to the operating system, but it is slower, needs extra disk space during the rewrite and takes an ACCESS EXCLUSIVE lock. Do not infer from the table rewrite that VACUUM FULL is a general substitute for rebuilding an index.
#1 Best Overall
Index cleanup can be skipped in some routine vacuums
Current PostgreSQL documentation sets the INDEX_CLEANUP option to AUTO by default. In that mode, vacuuming may 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, it removes dead entries rather than compacting the whole index. See the PostgreSQL 18 VACUUM documentation for the option’s version-specific details.
How to measure B-tree leaf density
The pgstattuple extension provides pgstatindex(regclass), which reports B-tree structure and page statistics, including total size, leaf and internal page counts, empty and deleted pages, avg_leaf_density and leaf_fragmentation. PostgreSQL defines avg_leaf_density as the average density of leaf pages. It is a measure of how full those pages are on average, not a universal percentage of index bloat.
-
Connect to the database where the index exists, using a role permitted to install the extension.
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 & 11Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Install the extension if it is not already available and enabled in that database:
Rank #2
CREATE EXTENSION IF NOT EXISTS pgstattuple; -
Call the function for the target B-tree index, using its schema-qualified name:
SELECT * FROM pgstatindex('schema.index_name'::regclass); -
Record the index size, density, page counts and fragmentation with the measurement time and workload context. If writes are active, repeat measurements under comparable conditions before drawing conclusions.
Consult the pgstattuple documentation for the output columns and function behavior. pgstatindex reads the index page by page, so concurrent changes mean its result is not an instantaneous, simultaneous snapshot of the entire index. The function is documented for B-tree indexes; do not treat it as a generic metric for other index methods.
How to interpret avg_leaf_density
A low density can be a sign that a B-tree has substantial unused space on its leaf pages, but it does not by itself establish that the index should be rebuilt. Density is an average: it does not show whether sparsity is concentrated in certain key ranges, whether future writes will reuse the space, or whether the index’s current size is a problem for the workload.
Read the result together with:
-
Index size and page counts: assess the amount of storage involved, including empty and deleted pages as well as leaf and internal pages.
-
Fragmentation: consider
leaf_fragmentationas another structural observation, not a replacement for workload context. -
Workload history: broad deletes can leave sparsely populated key ranges; inserts and updates can have different effects on page occupancy and future reuse.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Maintenance behavior: determine whether routine vacuuming is cleaning dead index entries, bearing in mind that cleanup and page compaction are distinct.
-
Fillfactor: account for the index’s configured page-packing target. PostgreSQL’s B-tree
fillfactordefault is 90; pages that become completely full can split, and a lower setting may help some insert or update workloads, depending on the workload. See PostgreSQL 18 CREATE INDEX documentation.
There is no official density cutoff that makes reindexing automatically appropriate. Compare measurements over time under similar conditions, especially if concurrent writes could make readings noisy. Judge whether unused space is likely to be reused and whether the index’s physical size is causing a practical storage or performance concern.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.When reindexing may be worth the operational cost
REINDEX builds the index again and is the relevant operation when the goal is to compact its structure. PostgreSQL’s guidance specifically calls out deletion patterns that leave many pages with only a few remaining keys; it does not prescribe a universal threshold based on avg_leaf_density. For non-B-tree index types, the potential for bloat is less well researched, and PostgreSQL recommends monitoring physical size.
Recommended Free Tools
Before scheduling a rebuild, weigh the evidence and operating constraints:
-
Is the index large and sparsely occupied, and does the observed pattern fit broad deletion across key ranges?
-
Are dead index entries being cleaned, or is routine vacuuming skipping index cleanup under its automatic policy?
-
Is there enough free disk space for the rebuild and its temporary storage needs?
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Can the workload tolerate the lock impact and additional write activity for the chosen reindexing method?
-
Is reclaimed space likely to be reused by future writes, making a rebuild less valuable?
According to the PostgreSQL 17 REINDEX documentation, the default REINDEX requires an ACCESS EXCLUSIVE lock, while REINDEX CONCURRENTLY requires a SHARE UPDATE EXCLUSIVE lock. Concurrent reindexing reduces lock severity, but it is not lock-free or cost-free. Confirm syntax and behavior for the server’s PostgreSQL major version and plan around the actual workload rather than relying on a universal density threshold.
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.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problems
