Difference between revisions of "PostgreSQL VACUUM"
Jump to navigation
Jump to search
↑ https://www.postgresql.org/docs/current/sql-vacuum.html
(→News) |
|||
| (21 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | PostgreSQL VACUUM [[garbage-collect]] and optionally analyze a database. | + | PostgreSQL [[VACUUM]] [[garbage-collect]] and optionally analyze a database. |
* https://www.postgresql.org/docs/current/sql-vacuum.html | * https://www.postgresql.org/docs/current/sql-vacuum.html | ||
| Line 5: | Line 5: | ||
| − | Examples | + | == Examples == |
| − | * <code>VACUUM (VERBOSE, ANALYZE) my_table;</code> | + | * <code>VACUUM (VERBOSE, [[ANALYZE]]) my_table;</code> |
| − | + | * <code>[[VACUUM FULL]] my_table;</code> | |
SELECT | SELECT | ||
| Line 15: | Line 15: | ||
FROM [[pg_stat_user_tables]]; | FROM [[pg_stat_user_tables]]; | ||
| − | select count(*) from [[pg_stat_activity]] where query like 'autovacuum:%'; | + | [[select count]](*) from [[pg_stat_activity]] where query like '[[autovacuum]]:%'; |
| Line 21: | Line 21: | ||
VACUUM causes a substantial increase in [[I/O]] traffic. | VACUUM causes a substantial increase in [[I/O]] traffic. | ||
| + | |||
| + | == News == | ||
| + | * [[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 == | == Related terms == | ||
* [[GIN]] indexes | * [[GIN]] indexes | ||
| + | * [[Autovacuum]] | ||
| + | * [[Tuple]] | ||
| + | * [[Redshift VACCUM]] | ||
| + | * "plain" VACCUM | ||
| + | * [[Index Locking]] | ||
== See also == | == See also == | ||
| + | * {{VACUUM}} | ||
* {{PostgreSQL}} | * {{PostgreSQL}} | ||
[[Category:PostgreSQL]] | [[Category:PostgreSQL]] | ||
Latest revision as of 10:32, 11 July 2023
PostgreSQL VACUUM garbage-collect and optionally analyze a database.
VACUUM reclaims storage occupied by dead tuples.
Contents
Examples[edit]
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. [1]
VACUUM causes a substantial increase in I/O traffic.
News[edit]
- PostgreSQL 13 (Oct 2020) parallelized vacuuming of indexes
Logs[edit]
2023-07-10 10:01:39 UTC [520415]: LOG: skipping vacuum of "your_test" --- lock not available
Related terms[edit]
- GIN indexes
- Autovacuum
- Tuple
- Redshift VACCUM
- "plain" VACCUM
- Index Locking
See also[edit]
- 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
Advertising:
