maintenance with PostgreSQL
VACUUM, REINDEX, progress
October 29, 2021
This is incomplete information - Please refer to the PostgreSQL documentation
VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done.
VACUUM (VERBOSE, ANALYZE) onek;
VACUUM FULL -- Selects “full” vacuum, which can reclaim more space, but takes much longer and exclusively locks the table.
REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy of the index.
REINDEX TABLE CONCURRENTLY my_broken_table;
REINDEX CONCURRENTLY -- When this option is used, PostgreSQL will rebuild the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index rebuild locks out writes (but not reads) on the table until it's done
Show progress
| DDL Command | System Catalog | Supported PostgreSQL Version |
|---|---|---|
| VACUUM | pg_stat_progress_vacuum |
9.6 |
| VACUUM | FULL pg_stat_progress_cluster |
12 |
| CLUSTER | pg_stat_progress_cluster |
12 |
| CREATE INDEX | pg_stat_progress_create_index |
12 |
| REINDEX | pg_stat_progress_create_index |
12 |