PostgreSQL VACUUM

PostgreSQL VACUUM garbage-collect and optionally analyze a database.

• https://www.postgresql.org/docs/current/sql-vacuum.html

VACUUM reclaims storage occupied by dead tuples.

Examples

• VACUUM (VERBOSE, ANALYZE) my_table;
• VACUUM FULL my_table;

SELECT
  schemaname, relname,
  last_vacuum, last_autovacuum,
  vacuum_count, autovacuum_count
FROM pg_stat_user_tables;

select count(*) from pg_stat_activity where query like 'autovacuum:%';

PosgreSQL recommend that active production databases be vacuumed frequently (at least nightly), in order to remove dead rows.

VACUUM causes a substantial increase in I/O traffic.

News

• PostgreSQL 17.0 (2024)
• PostgreSQL 13 (Oct 2020) parallelized vacuuming of indexes

Logs

2023-07-10 10:01:39 UTC [520415]: LOG: skipping vacuum of "your_test" --- lock not available

Related terms

• GIN indexes
• Autovacuum
• Tuple
• Redshift VACCUM
• "plain" VACCUM
• Index Locking

See also

• VACUUM: PostgreSQL VACUUM, Autovacuum, pg_database, pg_stat_activity, Redshift VACUUM
• PostgreSQL: pg_dump, pg_restore, pgAdmin, pg_config, psql, pg_ctl, pg_isready, initdb, Role, Schema, DBeaver, Navicat, DataGrip, OmniDB, Adminer, docker-compose.xml PostgreSQL, PostgreSQL version, PostgreSQL logs, postgresql.conf, pg_hba.conf, $HOME/.pg_service.conf, Create database (PostgreSQL), Create user, createdb, GRANT, pg_stat, PostgreSQL VACUUM, EXPLAIN, pg stat activity, Autovacuum, ALTER DATABASE, PostgreSQL statistics collector, Shared buffers, EXPLAIN (PostgreSQL), EXPLAIN ANALYZE, Bitmap scan, EXPLAIN VERBOSE, EXPLAIN VERBOSE (PostgresSQL), WAL, ALTER USER, CREATE ROLE, CREATE USER, \du, show users, \l, The Statistics Collector, pganalyze, cron.schedule, Master, pg_tables, PostgreSQL replication, CREATE, SET, TOAST, PgBouncer, Restore DB, Index, meta-commands, Table, foreign table, Schema, Sequence, Views, materialized view, Table Partitioning, monitoring, PostgreSQL System Administration Functions, PostgreSQL extension, privileges, logging, PGTune, PostgreSQL parameter tunning, PostgreSQL modules, pgbench, PostgreSQL users, catalogs, Patroni, Binary Data Types

https://www.postgresql.org/docs/current/sql-vacuum.html