Brave Search

May 14, 2026 - The default is AUTO, which allows VACUUM to skip index vacuuming when appropriate. If INDEX_CLEANUP is set to ON, VACUUM will conservatively remove all dead tuples from indexes. This may be useful for backwards compatibility with earlier releases of PostgreSQL where this was the standard behavior.

Medium

tomasz-gintowt.medium.com › vacuum-in-postgresql-not-just-cleaning-up-c4dc154c6320

VACUUM in PostgreSQL: Not Just Cleaning Up | by Tomasz Gintowt | Medium

September 2, 2025 - INDEX_CLEANUP AUTO lets PostgreSQL balance performance and cleanup.

Stack Overflow

stackoverflow.com › questions › 78414419 › index-cleanup-vaccum

postgresql - Index cleanup vaccum - Stack Overflow

-- Before vacuum SELECT indexrelname AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan AS index_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched FROM pg_stat_user_indexes WHERE indexrelname = 'idx_demo_table_data'; -- After vacuum SELECT indexrelname AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, idx_scan AS index_scans, idx_tup_read AS tuples_read, idx_tup_fetch AS tuples_fetched FROM pg_stat_user_indexes WHERE indexrelname = 'idx_demo_table_data'; ... Add to your question as text: 1) Postgres version.

How to use Vaccum full in PostgreSQL

rdbms - PostgreSQL Long VACUUM

sql - How to efficiently vacuum analyze tables in Postgres

postgresql - autovacuum blocking index recreation

sql - How can I perform the vacuum using the PostgreSQL?

More results from stackoverflow.com

Amazon Web Services

docs.aws.amazon.com › amazon rds › user guide › amazon rds for postgresql › common dba tasks for amazon rds for postgresql › working with postgresql autovacuum on amazon rds for postgresql › managing autovacuum with large indexes

Managing autovacuum with large indexes - Amazon Relational Database Service

As a result, pg_stat_all_tables.n_dead_tup increases until autovacuum or a manual VACUUM with index cleanup runs. As a best practice, use this procedure only to prevent transaction ID wraparound. ... However, in RDS for PostgreSQL 11 and lower versions, the only way to allow vacuum to complete ...

Thebuild

thebuild.com › blog › vacuum-index_cleanup-off-considered-harmful

VACUUM (INDEX_CLEANUP OFF) Considered Harmful — The Build

January 28, 2025 - INDEX_CLEANUP OFF reduces the amount of cleanup that vacuuming can do. It does release the space that dead tuples take up, but leaves the line pointers in place (it has to, so that the indexes are still pointing at the right location). (The documentation does explain this, but uses terminology that can be unfamiliar to a lot of PostgreSQL ...

Depesz

depesz.com › 2019 › 05 › 01 › waiting-for-postgresql-12-allow-vacuum-to-be-run-with-index-cleanup-disabled

Waiting for PostgreSQL 12 – Allow VACUUM to be run with index cleanup disabled. – select * from depesz;

May 1, 2019 - =$ psql -Xc "VACUUM ( index_cleanup true, verbose true ) test" 2>&1 | grep INFO INFO: vacuuming "public.test" INFO: index "test_pkey" now contains 0 row versions in 1 pages INFO: index "test_x_key" now contains 0 row versions in 1 pages INFO: index "z" now contains 0 row versions in 1 pages INFO: "test": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages INFO: vacuuming "pg_toast.pg_toast_18582" INFO: index "pg_toast_18582_index" now contains 0 row versions in 1 pages INFO: "pg_toast_18582": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

EnterpriseDB

enterprisedb.com › blog › postgresql-vacuum-and-analyze-best-practice-tips

PostgreSQL VACUUM Guide and Best Practices | EDB

To specifically target an index for cleanup, you would use the REINDEX command instead. Can I run VACUUM while the database is in use? chevron_right · Yes, regular VACUUM can run concurrently with other database operations. However, VACUUM FULL requires exclusive access to tables and considerable system resources. Is it necessary to run VACUUM manually? chevron_right · It is unnecessary to run VACUUM manually because PostgreSQL performs autovacuum operations based on table activity.

boringSQL

boringsql.com › visualizers › vacuum

PostgreSQL VACUUM at the Page Level Visualized | boringSQL

Hit Page-prune to fire opportunistic cleanup: the bytes come back and the line pointers go LP_DEAD, but the slots stay pinned because the index still points at them, and pruning can't reach the index or the other pages.

PostgreSQL Wiki

wiki.postgresql.org › wiki › Index_Maintenance

Index Maintenance - PostgreSQL wiki

From PostgreSQL wiki · Jump to navigationJump to search · One day, you will probably need to cope with routine reindexing on your database, particularly if you don't use VACUUM aggressively enough. A particularly handy command in this area is CLUSTER, which can help with other types of cleanup. Avoid using VACUUM FULL in versions 8.4 and earlier. 1 Index summary ·

Find elsewhere

Google Bing Mojeek

Crunchy Data Blog

crunchydata.com › blog › cleaning-up-your-postgres-database

Cleaning Up Your Postgres Database | Crunchy Data Blog

The first thing you're going to want to look at is your cache hit ratio and index hit ratio. Your cache hit ratio is going to give the percentage of time your data is served from within memory vs. having to go to disk. Generally serving data from memory vs. disk is going to orders of magnitude faster, thus the more you can serve from memory the better.

PostgreSQL

postgresql.org › docs › current › sql-reindex.html

PostgreSQL: Documentation: 18: REINDEX

May 14, 2026 - This can occur with B-tree indexes in PostgreSQL under certain uncommon access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages.

TigerData

tigerdata.com › learn › postgresql-performance-tuning-optimizing-database-indexes

PostgreSQL Tuning: Database Indexes | Tiger Data

February 11, 2026 - Hypertables for Postgres PartitioningTop PostgreSQL Drivers for PythonAn Intro to Data Modeling on PostgreSQLGuide to PostgreSQL Database OperationsUnderstanding PostgreSQL TablespacesWhat Is Audit Logging and How to Enable It in PostgreSQLGuide to Postgres Data ManagementHow to Index JSONB Columns in PostgreSQLHow to Monitor and Optimize PostgreSQL Index PerformanceA Guide to pg_restore (and pg_restore Example)Explaining PostgreSQL EXPLAINA PostgreSQL Database Replication GuideHow PostgreSQL Data Aggregation WorksHow to Use Psycopg2: The PostgreSQL Adapter for PythonBuilding a Scalable DatabaseGuide to PostgreSQL Database Design

PostgreSQL

postgresql.org › docs › 8.4 › sql-reindex.html

PostgreSQL: Documentation: 8.4: REINDEX

July 24, 2014 - This can occur with B-tree indexes in PostgreSQL under certain uncommon access patterns. REINDEX provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages.

Medium

medium.com › @anasanjaria › how-to-determine-unused-index-in-postgresql-6af846686a3

How to determine unused index in PostgreSQL? | Medium

November 23, 2025 - Hence, the most undesirable type of index is the one that remains unused. Another reason is minimalism. In software engineering, the principle of minimalism works the best. If it’s not used, what’s the point of keeping them. PostgreSQL has a view named pg_stat_user_indexescontaining index statistics.

Andyatkinson

andyatkinson.com › blog › 2021 › 07 › 30 › postgresql-index-maintenance

PostgreSQL Indexes: Prune and Tune | Software Engineer, Author, High Performance PostgreSQL for Rails

July 30, 2021 - We added PgHero which helps make unused indexes more visible to team members by showing an Unused label in the UI. ... In MVCC when a row is updated, a new row version (called a tuple) is created behind the scenes. The former row version becomes a “dead tuple” when no transactions reference it. This is part of the concurrency design of PostgreSQL.

PostgreSQL

postgresqlco.nf › en › doc › param › vacuum_cleanup_index_scale_factor

PostgreSQL Documentation: vacuum_cleanup_index_scale_factor parameter

vacuum_cleanup_index_scale_factor: Min: 0, Max: 1e+10, Default: 0.1, Context: user, Needs restart: false, Deprecated since: 13 • Number of tuple inserts prior to index cleanup as a fraction of reltuples.

Postgres Professional

postgrespro.com › list › thread-id › 2488063

Thread: xid wraparound danger due to INDEX_CLEANUP false : Postgres Professional

June 12, 2021 - It also adds a new option to the VACUUM > command, INDEX_CLEANUP, which can be used to override the > reloption. If neither the reloption nor the VACUUM option is > used, the default is true, as before. > > Masahiko Sawada, reviewed and tested by Nathan Bossart, Alvaro > Herrera, Kyotaro Horiguchi, Darafei Praliaskouski, and me. > The wording of the documentation is mostly due to me. > > Discussion: http://postgr.es/m/CAD21AoAt5R3DNUZSjOoXDUY=naYPUOuffVsRzuTYMz29yLzQCA@mail.gmail.com > > made the index scan that is part of vacuum optional.

reddit.com › r/postgresql › vacuum full analyze much better than vacuum analyze + reindex

r/PostgreSQL on Reddit: VACUUM FULL ANALYZE much better than VACUUM ANALYZE + REINDEX

June 24, 2024 -

About 99% articles i read states that VACUUM FULL is a bad practise and can even results into slower DB.

More recommended way articles states is to VACUUM ANALYZE and REINDEX afterwards.

For sure, let's consider for a while that AUTOVACUUM is not enough for some reason, for example - optimal settings are not tuned up at the moment.

What i found is that during our performance tests on regular basis - VACUUM FULL cause better throughput due to much lower and at the same time with better latency - disk IO.

My theory is that VACUUM FULL cause OS to perform more sequental reads rather than random reads. Also, maybe, it allows Page cache to be more saturated due to lower tables size.

Can someone please point me the right way where and what can i do to investigate the real cause of VACUUM FULL ANALYZE performance gain comparing to VACUUM ANALYZE + REINDEX ?

Thanks in advance !

PG 14 in production.

UPDATE

Found an answer:

https://severalnines.com/blog/tuning-io-operations-postgresql/

section "VACUUM FULL".

The reason is disk io of course, but mainly - such operations like seq scan traverse dead tuples too. Maybe it should check if the tuple is dead or the space was reused for new records, btw, if you read the article - the query time (synthetic, doesn't include indexes) dropped from 1.9s to 1.4s after VACUUM FULL ANALYZE comparing to VACUUM ANALYZE.