Difference between revisions of "Vacuum"
Jump to navigation
Jump to search
(Created page with "# Postgres Vacuum ``` $ docker run --rm -d --name postgres postgres:10.1 6732b0b9c6245fe9f19dd58e9737e5102089814e4aa96b66217af28a1596f786 $ docker exec -it postgres bash ro...") |
|||
| (7 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | https://confluence.atlassian.com/kb/optimize-and-improve-postgresql-performance-with-vacuum-analyze-and-reindex-885239781.html | ||
| + | |||
| + | SELECT name, setting FROM pg_settings WHERE name ILIKE '%autovacuum%'; | ||
| + | |||
| + | SELECT name, setting FROM pg_settings where setting = '-1'; | ||
| + | |||
| + | -1 uses settings from config | ||
| + | |||
| + | https://andreigridnev.medium.com/db-maintenance-commands-in-postgresql-analyze-reindex-vacuum-4a14d75bee6c | ||
| + | |||
| + | ``` | ||
| + | running VACUUM ANALYZE and then REINDEX commands on the biggest tables in the application database really helps to improve its performance. | ||
| + | ``` | ||
| + | |||
| + | psql mydb | ||
| + | ``` | ||
| + | vacuum analyze verbose | ||
| + | ``` | ||
| + | |||
| + | ``` | ||
| + | vacuum full analyze verbose | ||
| + | ``` | ||
| + | |||
| + | ``` | ||
| + | SHOW AUTOVACUUM; | ||
| + | ``` | ||
| + | |||
| + | Show bloat | ||
| + | |||
| + | https://wiki.postgresql.org/wiki/Show_database_bloat | ||
| + | |||
| + | |||
# Postgres Vacuum | # Postgres Vacuum | ||
Latest revision as of 18:48, 5 August 2022
SELECT name, setting FROM pg_settings WHERE name ILIKE '%autovacuum%';
SELECT name, setting FROM pg_settings where setting = '-1';
-1 uses settings from config
running VACUUM ANALYZE and then REINDEX commands on the biggest tables in the application database really helps to improve its performance.
psql mydb
vacuum analyze verbose
vacuum full analyze verbose
SHOW AUTOVACUUM;
Show bloat
https://wiki.postgresql.org/wiki/Show_database_bloat
Postgres Vacuum
$ docker run --rm -d --name postgres postgres:10.1 6732b0b9c6245fe9f19dd58e9737e5102089814e4aa96b66217af28a1596f786 $ docker exec -it postgres bash root@6732b0b9c624:/# psql -Upostgres psql (10.1) Type "help" for help. postgres=# CREATE TABLE floor ( postgres(# name text, postgres(# type text postgres(# ); CREATE TABLE postgres=# vacuum floor; VACUUM postgres=# SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; relname | last_vacuum | last_autovacuum ---------+-------------------------------+----------------- floor | 2019-04-24 17:52:26.044697+00 | (1 row)