Difference between revisions of "PostgreSQL VACUUM"

From wikieduonline
Jump to navigation Jump to search
 
(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.


Examples[edit]

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]

Logs[edit]

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

Related terms[edit]

See also[edit]

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