You can use the following pl/pgsql script (if you only want to analyze, vacuum cannot be executed from a function or multi-command string):
DO $$
DECLARE
tab RECORD;
schemaName VARCHAR := 'your_schema';
BEGIN
for tab in (select t.relname::varchar AS table_name
FROM pg_class t
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE t.relkind = 'r' and n.nspname::varchar = schemaName
order by 1)
LOOP
RAISE NOTICE 'ANALYZE %.%', schemaName, tab.table_name;
EXECUTE format('ANALYZE %I.%I', schemaName, tab.table_name);
end loop;
end
$$;
PostgreSQL
postgresql.org › docs › current › sql-vacuum.html
PostgreSQL: Documentation: 18: VACUUM
May 14, 2026 - July 16, 2026: PostgreSQL 19 Beta 2 Released! ... Unsupported versions: 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1 · VACUUM — garbage-collect and optionally analyze a database · VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ] where option can be one of: FULL [ boolean ] FREEZE [ boolean ] VERBOSE [ boolean ] ANALYZE [ boolean ] DISABLE_PAGE_SKIPPING [ boolean ] SKIP_LOCKED [ boolean ] INDEX_CLEANUP { AUTO | ON | OFF } PROCESS_MAIN [ boolean ] PROCESS_TOAST [ boolean ] TRUNCATE [ boolean ] PARALLEL integer SKIP_DATABASE_STATS [ boolean ] ONLY_DATABASE_STATS [ boolean ] BUFFER_USAGE_LIMIT size and table_and_columns is: [ ONLY ] table_name [ * ] [ ( column_name [, ...] ) ]
Top answer 1 of 8
27
You can use the following pl/pgsql script (if you only want to analyze, vacuum cannot be executed from a function or multi-command string):
DO $$
DECLARE
tab RECORD;
schemaName VARCHAR := 'your_schema';
BEGIN
for tab in (select t.relname::varchar AS table_name
FROM pg_class t
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE t.relkind = 'r' and n.nspname::varchar = schemaName
order by 1)
LOOP
RAISE NOTICE 'ANALYZE %.%', schemaName, tab.table_name;
EXECUTE format('ANALYZE %I.%I', schemaName, tab.table_name);
end loop;
end
$$;
2 of 8
15
The bash function below utilizes the CLI tool psql to vacuum analyze tables in a single schema which can be identified by either passing the name of the schema as the first parameter to the function or setting the environment variable PG_SCHEMA:
vacuum_analyze_schema() {
# vacuum analyze only the tables in the specified schema
# postgres info can be supplied by either passing it as parameters to this
# function, setting environment variables or a combination of the two
local pg_schema="${1:-${PG_SCHEMA}}"
local pg_db="${2:-${PG_DB}}"
local pg_user="${3:-${PG_USER}}"
local pg_host="${4:-${PG_HOST}}"
echo "Vacuuming schema \`${pg_schema}\`:"
# extract schema table names from psql output and put them in a bash array
local psql_tbls="\dt ${pg_schema}.*"
local sed_str="s/${pg_schema}\s+\|\s+(\w+)\s+\|.*/\1/p"
local table_names=$( echo "${psql_tbls}" | psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}" | sed -nr "${sed_str}" )
local tables_array=( $( echo "${table_names}" | tr '\n' ' ' ) )
# loop through the table names creating and executing a vacuum
# command for each one
for t in "${tables_array[@]}"; do
echo "doing table \`${t}\`..."
psql -d "${pg_db}" -U "${pg_user}" -h "${pg_host}" \
-c "VACUUM (ANALYZE) ${pg_schema}.${t};"
done
}
This function can be added to your .bashrc to provide the ability to invoke it from the command line at any time. Like the schema, Postgres connection and database values can be set by either supplying them as function parameters:
# params must be in this order
vacuum_analyze_schema '<your-pg-schema>' '<your-pg-db>' '<your-pg-user>' '<your-pg-host>'
or by setting environment variables:
PG_SCHEMA='<your-pg-schema>'
PG_USER='<your-pg-user>'
PG_HOST='<your-pg-host>'
PG_DB='<your-pg-db>'
vacuum_analyze_schema
or by a combination of both. Values passed as params will take precedence over corresponding environment vars.
VACUUM FULL ANALYZE much better than VACUUM ANALYZE + REINDEX
https://www.depesz.com/2023/02/06/when-to-use-vacuum-full/ More on reddit.com
12
14
June 24, 2024Is it common to need to do regular full vacuum on tables?
The short answer is no - it isn't common to need to do a regular full vacuum. If you're finding that this is necessary, then probably you need to tune autovacuum. In my opinion the autovacuum defaults are far too conservative, especially on larger or busier tables, but as in all things this really depends on your workload. Look into the topic of database bloat (probably starting in the PostgreSQL wiki , but there are many blog posts on this topic), and start monitoring your tables to see if this is what's happening to you. If you're seeing bloat increase, then tuning autovacuum may be necessary - although there are other potential causes of bloat as well. More on reddit.com
33
8
September 20, 2024Vacuum vs Vacuum full - Simple explanation ?
From the docs , Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. It also allows us to leverage multiple CPUs in order to process indexes. This feature is known as parallel vacuum. To disable this feature, one can use PARALLEL option and specify parallel workers as zero. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an ACCESS EXCLUSIVE lock on each table while it is being processed. That answers your first question - it will allow the internally cleared space to be reused for future writes. Do I never have to use Vacuum full command if my rate of deletion & rate of insertion is exact same ? Yes, I believe it's desirable to never have to run full vacuum. Further down in the same docs, it says that vacuum full can reclaim more space, but takes much longer and exclusively locks the table. It implies that vacuum full can't be run in parallel with live writes to the table i.e. essentially a downtime. However, running vacuum to manage db bloat is only one side of the story. The real reason why it must be ensured that vacuum (non full) is run on a regular basis is to prevent XID wraparound. At my previous job, we were badly bitten by it. In my experience the key is to monitor the various metrics around transaction ids, no. of dead tuples and progress of the vacuum operation and setup some kind of alerts in case it's not running effectively. We have written about it in detail which might help understand and appreciate the role of vacuum operation in pg - https://medium.com/helpshift-engineering/auto-vacuum-tuning-in-postgresql-3408f8b62ad8 More on reddit.com
12
5
July 3, 2024Why auto vacuum or auto analyze isn't running on my table?
reltuples: 4.113114e+06 which is a huge number That's 4113114, so a bit over 4 million rows. Not really a huge number. But the n_dead_tuples 4113114 seems to indicate that you delete a huge number of rows from that table. Please show us the output of vacuum (analyze, verbose) "InvoiceEvents"; Maybe the transaction that deleted that many rows is still active and prevents vacuum? More on reddit.com
7
2
April 3, 2024What does the PostgreSQL VACUUM command do?
The VACUUM command reclaims space from deleted or outdated rows (dead tuples) in PostgreSQL tables. It doesn’t shrink disk usage directly but marks space as reusable for new data. Regular vacuuming keeps performance consistent and avoids unnecessary file growth. It’s a fundamental maintenance step for any PostgreSQL DBA.
hevodata.com
hevodata.com › home › learn › database management system
PostgreSQL VACUUM: How and When to Use It, and Examples
How often should I run VACUUM in PostgreSQL?
For most databases, AUTOVACUUM handles this automatically. However, heavily updated or deleted tables may need manual vacuuming daily or weekly, depending on load. Monitor pg_stat_user_tables for dead tuple counts and adjust autovacuum thresholds accordingly to maintain optimal performance.
hevodata.com
hevodata.com › home › learn › database management system
PostgreSQL VACUUM: How and When to Use It, and Examples
What is AUTOVACUUM, and when should I use it?
AUTOVACUUM is PostgreSQL’s background process that automatically triggers vacuuming and analysis when thresholds are met. It ensures space reclamation, statistics updates, and transaction ID safety without manual intervention. Keep it enabled and tune its parameters for your workload size and update frequency.
hevodata.com
hevodata.com › home › learn › database management system
PostgreSQL VACUUM: How and When to Use It, and Examples
09:25
What is the vacuum process in PostgreSQL? | Different Types of ...
04:41
Tutorial 31-Postgresql vacuum full - YouTube
15:18
Vacuuming PostgreSQL -Fundamentals - YouTube
PostgreSQL VACUUM does not shrink my table ...
09:08
Episode 8 - Vacuum - PostgreSQL for MySQL DBAs - YouTube
02:31
Tutorial 32-Postgresql vacuum vs vacuum full - YouTube
Hevo
hevodata.com › home › learn › database management system
PostgreSQL VACUUM: How and When to Use It, and Examples
November 22, 2025 - Use VACUUM (table_name): Targets specific tables to optimize space and performance locally. Enable AUTOVACUUM: Automates periodic cleanup to prevent manual overhead and transaction wraparound. Imagine a multi-user environment in PostgreSQL, having a Table X with millions and billions of rows, edited frequently with new updates and deletions.
PostgreSQL
postgresql.org › docs › current › app-vacuumdb.html
PostgreSQL: Documentation: 18: vacuumdb
May 14, 2026 - When the -a/--all is used, connect to this database to gather the list of databases to vacuum. If not specified, the postgres database will be used, or if that does not exist, template1 will be used. This can be a connection string. If so, connection string parameters will override any conflicting command line options.
TechOnTheNet
techonthenet.com › postgresql › vacuum.php
PostgreSQL: VACUUM Statement
If not specified, all tables in the database will be vacuumed. ... Optional. If specified, these are the columns that will be analyzed. Each time you perform an update on a table, the original record is kept in the database. A vacuum will remove these old records (ie: tuples) and reduce the ...
PostgreSQL
postgresql.org › docs › current › routine-vacuuming.html
PostgreSQL: Documentation: 18: 24.1. Routine Vacuuming
May 14, 2026 - TRUNCATE removes the entire content of the table immediately, without requiring a subsequent VACUUM or VACUUM FULL to reclaim the now-unused disk space. The disadvantage is that strict MVCC semantics are violated. The PostgreSQL query planner relies on statistical information about the contents of tables in order to generate good plans for queries.
Medium
batcat.medium.com › clean-house-clear-mind-keeping-your-postgres-tables-tidy-with-vacuuming-3e13a15b30fe
Why I Vacuum My Tables Regularly. Improve performance and prevent data… | by BatCat | Medium
July 23, 2024 - No downtime was allowed and gigabytes of data needed to be synced. Many rows were updated, however, I saw that the space the table claimed doubled. I ran VACUUM FULL once per day for the most important and most frequently used tables, resulting with the table size going back to normal.
CommandPrompt Inc.
commandprompt.com › education › how-to-use-vacuum-command-in-postgresql
How to Use VACUUM Command in PostgreSQL — CommandPrompt Inc.
October 7, 2022 - The VACUUM command optimizes the performance of the Postgres databases, records, etc. ... Here, the tab_name is optional, if you specify the tab_name, then only that specific table will be vacuumed; however, if you don’t specify the table’s name, then all the tables of the selected database will be vacuumed.
EnterpriseDB
enterprisedb.com › blog › postgresql-vacuum-and-analyze-best-practice-tips
PostgreSQL VACUUM Guide and Best Practices | EDB
The reason for specifying multiple ... up vacuuming smaller tables and user sessions. The autovacuum_max_workers parameter tells PostgreSQL to spin up the number of autovacuum worker threads to do the cleanup. A common practice by PostgreSQL DBAs is to increase the number of maximum worker threads to speed up autovacuum. This doesn’t work as all the threads ...
Redbranch
blog.redbranch.net › 2019 › 12 › 23 › postgresql-vacuum-and-analyze-all-tables-in-a-database
Postgresql VACUUM and ANALYZE all tables in a database » Red Branch
December 23, 2019 - It gets a list of the tables in the database and for each one performs a full vacuum and analyze. There is probably a more efficient way to do this from the psql prompt but this is what worked for me: su - postgres for TABLE in $(psql -d netbox -c "\dt" | grep table | awk -F "|" '{print $2}' | tr -d " "); do psql -d netbox -c "VACUUM FULL ANALYZE VERBOSE $TABLE" ; done
DataCamp
datacamp.com › doc › postgresql › vacuum
PostgreSQL VACUUM
The `VACUUM` command is employed to reclaim storage and improve I/O performance by removing obsolete data. It is typically used after tables have undergone significant updates or deletions. ... This command performs a standard vacuum operation across all tables in the current database to free ...
Atlassian Support
support.atlassian.com › atlassian-knowledge-base › kb › optimize-and-improve-postgresql-performance-with-vacuum-analyze-and-reindex
Optimize and Improve PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX | Atlassian knowledge base | Atlassian Support
April 17, 2025 - The parenthesized syntax was added in PostgreSQL 9.0; after which the unparenthesized syntax is deprecated. In the examples below, [tablename] is optional. Without a table specified, VACUUM will be run on ALL available tables in the current schema that the user has access to.
Castsoftware
doc.castsoftware.com › export › TG › Tools+-+How+to+VACUUM+FULL+tables+of+a+schema
Tools - How to VACUUM FULL tables of a schema
February 11, 2022 - SELECT 'vacuum full ' || table_name || ';' FROM information_schema.tables WHERE table_schema = 'delta_local' ORDER BY table_schema, table_name; ... psql --host <hostname> -p<port> -U <username> -v schema=<schema_name> -f "<sql file generated in step 1>" -d postgres -L <log file>
PostgreSQL
postgresql.org › docs › 9.5 › app-vacuumdb.html
PostgreSQL: Documentation: 9.5: vacuumdb
February 11, 2021 - vacuumdb is a utility for cleaning a PostgreSQL database. vacuumdb will also generate internal statistics used by the PostgreSQL query optimizer. vacuumdb is a wrapper around the SQL command VACUUM. There is no effective difference between vacuuming and analyzing databases via this utility and via other methods for accessing the server. vacuumdb accepts the following command-line arguments: ... Vacuum all databases.
CYBERTEC PostgreSQL
cybertec-postgresql.com › home › grant vacuum, analyze in postgresql 16
GRANT VACUUM, ANALYZE in PostgreSQL 16 New Feature
March 5, 2024 - Now we can see that "petro" can vacuum "foo" (and every other table in all databases!) even though we didn't grant vacuum on "foo" to "petro" role. The reason "petro" can vacuum "foo" is because "petro" is a member of the "pg_vacuum_all_tables" role. VACUUM and ANALYZE are PostgreSQL commands used to optimize the database.
Medium
medium.com › @dmitry.romanoff › postgresql-how-to-run-vacuum-analyze-explicitly-5879ec39da47
PostgreSQL: How to run VACUUM ANALYZE explicitly? | by Dmitry Romanoff | Medium
November 21, 2023 - In PostgreSQL, there are a few approaches to run VACUUM and ANALYZE. In this post, I’ll share knowledge of how to run VACUUM and ANALYZE explicitly. To run the vacuum on the specific table and all its related indexes:
Percona
percona.com › home › blog › essential guide to the postgresql vacuum command for performance
Essential Guide to the PostgreSQL VACUUM Command for Performance - Percona
May 5, 2026 - Using the PostgreSQL VACUUM command involves removing deleted or outdated row versions (“dead tuples”) from tables and indexes, and optionally updating statistics used by the query planner.
AWS
docs.aws.amazon.com › aws prescriptive guidance › maintenance for postgresql databases in amazon rds and amazon aurora to avoid performance issues › vacuuming and analyzing tables manually
Vacuuming and analyzing tables manually - AWS Prescriptive Guidance
DocumentationAWS Prescriptive GuidanceMaintenance for PostgreSQL databases in Amazon RDS and Amazon Aurora to avoid performance issues ... If your database is vacuumed by the autovacuum process, it's best practice to avoid running manual vacuums on the entire database too frequently. A manual vacuum might result in unnecessary I/O loads or CPU spikes, and might also fail to remove any dead tuples. Run manual vacuums on a table...