# Rebuilding Tables and Indexes LLMS index: [llms.txt](/en/llms.txt) --- ## 8.1 Full Vacuuming {#internal-section-8-1} ### Why is Routine Vacuuming not Enough? {#internal-section-star-73} Routine vacuuming can free more space than page pruning, but sometimes it may still be not enough. If table or index files have grown in size, `VACUUM` can clean up some space within pages, but it can rarely reduce the number of pages. The reclaimed space can only be returned to the operating system if several empty pages appear at the very end of the file, which does not happen too often. An excessive size can lead to unpleasant consequences: - Full table (or index) scan will take longer. - A bigger buffer cache may be required (pages are cached as a whole, so data density decreases). - B-trees can get an extra level, which slows down index access. - Files take up extra space on disk and in backups. If the fraction of useful data in a file has dropped below some reasonable level, an administrator can perform *full vacuuming* by running the `VACUUM FULL` command.[^internal-135-1] In this case, the table and all its indexes are rebuilt from scratch, and the data is packed as densely as possible (taking the *fillfactor* parameter into account). (*p.* [92](/en/05-page-pruning-and-hot-updates/#internal-page-92)) When full vacuuming is performed, PostgreSQL first fully rebuilds the table and then each of its indexes. While an object is being rebuilt, both old and new files have to be stored on disk,[^internal-135-2] so this process may require a lot of free space. You should also keep in mind that this operation fully blocks access to the table, both for reads and writes. ### Estimating Data Density {#internal-section-star-74} For the purpose of illustration, let’s insert some rows into the table: ```psql => TRUNCATE vac; => INSERT INTO vac(id,s) SELECT id, id::text FROM generate_series(1,500000) id; ``` Storage density can be estimated using the `pgstattuple` extension: ```psql => CREATE EXTENSION pgstattuple; => SELECT * FROM pgstattuple('vac') \gx -[ RECORD 1 ]------+--------- table_len | 70623232 tuple_count | 500000 tuple_len | 64500000 tuple_percent | 91.33 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 381844 free_percent | 0.54 ``` The function reads the whole table and displays statistics on space distribution in its files. The `tuple_percent` field shows the percentage of space taken up by useful data (heap tuples). This value is inevitably less than 100 % because of various metadata within pages, but in this example it is still quite high. For indexes, the displayed information differs a bit, but the `avg_leaf_density` field has the same meaning: it shows the percentage of useful data (in B-tree leaf pages). ```psql => SELECT * FROM pgstatindex('vac_s') \gx -[ RECORD 1 ]------+---------- version | 4 tree_level | 3 index_size | 114302976 root_block_no | 2825 internal_pages | 376 leaf_pages | 13576 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 53.88 leaf_fragmentation | 10.59 ``` The previously used `pgstattuple` functions read the table or index in full to get the precise statistics. For large objects, it can turn out to be too expensive, so the extension also provides another function called `pgstattuple_approx`, which skips the pages tracked in the visibility map to show approximate figures. A faster but even less accurate method is to roughly estimate the ratio between the data volume and the file size using the system catalog.[^internal-137-1] Here are the current sizes of the table and its index: ```psql => SELECT pg_size_pretty(pg_table_size('vac')) AS table_size, pg_size_pretty(pg_indexes_size('vac')) AS index_size; table_size | index_size ------------+------------ 67 MB | 109 MB (1 row) ``` Now let’s delete 90 % of all the rows: ```psql => DELETE FROM vac WHERE id % 10 != 0; DELETE 450000 ``` Routine vacuuming does not affect the file size because there are no empty pages at the end of the file: ```psql => VACUUM vac; => SELECT pg_size_pretty(pg_table_size('vac')) AS table_size, pg_size_pretty(pg_indexes_size('vac')) AS index_size; table_size | index_size ------------+------------ 67 MB | 109 MB (1 row) ``` However, data density has dropped about 10 times: ```psql => SELECT vac.tuple_percent, vac_s.avg_leaf_density FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s; tuple_percent | avg_leaf_density ---------------+------------------ 9.13 | 6.71 (1 row) ``` The table and the index are currently located in the following files: ```psql => SELECT pg_relation_filepath('vac') AS vac_filepath, pg_relation_filepath('vac_s') AS vac_s_filepath \gx -[ RECORD 1 ]--+----------------- vac_filepath | base/16391/16514 vac_s_filepath | base/16391/16515 ``` Let’s check what we will get after `VACUUM FULL`. While the command is running, its progress can be tracked in the `pg_stat_progress_cluster` view (which is similar to the `pg_stat_progress_vacuum` view provided for `VACUUM`): (v. 12) ```psql => VACUUM FULL vac; ``` ```psql => SELECT * FROM pg_stat_progress_cluster \gx -[ RECORD 1 ]-------+----------------- pid | 19488 datid | 16391 datname | internals relid | 16479 command | VACUUM FULL phase | rebuilding index cluster_index_relid | 0 heap_tuples_scanned | 50000 heap_tuples_written | 50000 heap_blks_total | 8621 heap_blks_scanned | 8621 index_rebuild_count | 0 ``` Expectedly, `VACUUM FULL` phases[^internal-138-1] differ from those of routine vacuuming. Full vacuuming has replaced old files with new ones: ```psql => SELECT pg_relation_filepath('vac') AS vac_filepath, pg_relation_filepath('vac_s') AS vac_s_filepath \gx -[ RECORD 1 ]--+----------------- vac_filepath | base/16391/16526 vac_s_filepath | base/16391/16529 ``` Both index and table sizes are much smaller now: ```psql => SELECT pg_size_pretty(pg_table_size('vac')) AS table_size, pg_size_pretty(pg_indexes_size('vac')) AS index_size; table_size | index_size ------------+------------ 6904 kB | 6504 kB (1 row) ``` As a result, data density has increased. For the index, it is even higher than the original one: it is more efficient to create a B-tree from scratch based on the available data than to insert entries row by row into an already existing index: ```psql => SELECT vac.tuple_percent, vac_s.avg_leaf_density FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s; tuple_percent | avg_leaf_density ---------------+------------------ 91.23 | 91.08 (1 row) ``` ### Freezing {#internal-section-star-75} When the table is being rebuilt, PostgreSQL freezes its tuples because this operation costs almost nothing compared to the rest of the work: ```psql => SELECT * FROM heap_page('vac',0,0) LIMIT 5; ctid | state | xmin | xmin_age | xmax -------+--------+-------+----------+------ (0,1) | normal | 861 f | 5 | 0 a (0,2) | normal | 861 f | 5 | 0 a (0,3) | normal | 861 f | 5 | 0 a (0,4) | normal | 861 f | 5 | 0 a (0,5) | normal | 861 f | 5 | 0 a (5 rows) ``` But pages are registered neither in the visibility map nor in the freeze map, and the page header does not receive the visibility attribute (as it happens when the `COPY` command is executed with the `FREEZE` option): (*p.* [133](/en/07-freezing/#internal-page-133)) ```psql => SELECT * FROM pg_visibility_map('vac',0); all_visible | all_frozen -------------+------------ f | f (1 row) => SELECT flags & 4 > 0 all_visible FROM page_header(get_raw_page('vac',0)); all_visible ------------- f (1 row) ``` The situation improves only after `VACUUM` is called (or autovacuum is triggered): ```psql => VACUUM vac; => SELECT * FROM pg_visibility_map('vac',0); all_visible | all_frozen -------------+------------ t | t (1 row) => SELECT flags & 4 > 0 AS all_visible FROM page_header(get_raw_page('vac',0)); all_visible ------------- t (1 row) ``` It essentially means that even if all tuples in a page are beyond the database horizon, such a page will still have to be rewritten. ## 8.2 Other Rebuilding Methods {#internal-section-8-2} ### Alternatives to Full Vacuuming {#internal-section-star-76} In addition to `VACUUM FULL`, there are several other commands that can fully rebuild tables and indexes. All of them exclusively lock the table, all of them delete old data files and recreate them anew. The `CLUSTER` command is fully analogous to `VACUUM FULL`, but it also reorders tuples in files based on one of the available indexes. In some cases, it can help the planner use index scans more efficiently. But you should bear in mind that clusterization is not supported: all further table updates will be breaking the physical order of tuples. (*p.* [326](/en/19-index-access-methods/#internal-page-326)) (*p.* [331](/en/20-index-scans/#internal-page-331)) Programmatically, `VACUUM FULL` is simply a special instance of the `CLUSTER` command that does not require tuple reordering.[^internal-140-1] The `REINDEX` command rebuilds one or more indexes.[^internal-140-2] In fact, `VACUUM FULL` and `CLUSTER` use this command under the hood when rebuilding indexes. The `TRUNCATE` command[^internal-140-3] deletes all table rows; it is a logical equivalent of `DELETE` run without the `WHERE` clause. But while `DELETE` simply marks heap tuples as deleted (so they still have to be vacuumed), `TRUNCATE` creates a new empty file, which is usually faster. (*p.* [72](/en/03-pages-and-tuples/#internal-page-72)) ### Reducing Downtime During Rebuilding {#internal-section-star-77} `VACUUM FULL` is not meant to be run regularly, as it exclusively locks the table (even for queries) for the whole duration of its operation. This is usually not an option for highly available systems. (*p.* [204](/en/12-relation-level-locks/#internal-page-204)) There are several extensions (such as `pg_repack`[^internal-141-1]) that can rebuild tables and indexes with almost zero downtime. An exclusive lock is still required, but only at the beginning and at the end of this process, and only for a short time. It is achieved by a more complex implementation: all the changes made on the original table while it is being rebuilt are saved by a trigger and then applied to the new table. To complete the operation, the utility replaces one table with the other in the system catalog. An unconventional solution is offered by the `pgcompacttable` utility.[^internal-141-2] It performs multiple fake row updates (that do not change any data) so that current row versions gradually move towards the start of the file. Between these update series, vacuuming removes outdated tuples and truncates the file little by little. This approach takes much more time and resources, but it requires no extra space for rebuilding the table and does not lead to load spikes. Short-time exclusive locks are still acquired while the table is being truncated, but vacuuming handles them rather smoothly. (*p.* [108](/en/06-vacuum-and-autovacuum/#internal-page-108)) ## 8.3 Precautions {#internal-section-8-3} ### Read-Only Queries {#internal-section-star-78} One of the reasons for file bloating is executing long-running transactions that hold the database horizon alongside intensive data updates. (*p.* [88](/en/04-snapshots/#internal-page-88)) As such, long-running (read-only) transactions do not cause any issues. So a common approach is to split the load between different systems: keep fast OLTP queries on the primary server and direct all OLAP transactions to a replica. Although it makes the solution more expensive and complicated, such measures may turn out to be indispensable. In some cases, long transactions are the result of application or driver bugs rather than a necessity. If an issue cannot be resolved in a civilized way, the administrator can resort to the following two parameters: - The *old\_snapshot\_threshold* parameter defines the maximum lifetime of a snapshot. Once this time is up, the server has the right to remove outdated tuples; if a long-running transaction still requires them, it will get an error (“snapshot too old”). (v. 9.6) - The *idle\_in\_transaction\_session\_timeout* parameter limits the lifetime of an idle transaction. The transaction is aborted upon reaching this threshold. (v. 9.6) ### Data Updates {#internal-section-star-79} Another reason for bloating is simultaneous modification of a large number of tuples. If all table rows get updated, the number of tuples can double, and vacuuming will not have enough time to interfere. Page pruning can reduce this problem, but not resolve it entirely. Let’s extend the output with another column to keep track of the processed rows: ```psql => ALTER TABLE vac ADD processed boolean DEFAULT false; => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 6936 kB (1 row) ``` Once all the rows are updated, the table gets almost two times bigger: ```psql => UPDATE vac SET processed = true; UPDATE 50000 => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 14 MB (1 row) ``` To address this situation, you can reduce the number of changes performed by a single transaction, spreading them out over time; then vacuuming can delete outdated tuples and free some space for new ones within the already existing pages. Assuming that each row update can be committed separately, we can use the following query that selects a batch of rows of the specified size as a template: ```sql SELECT ID FROM table WHERE filtering the already processed rows LIMIT batch size FOR UPDATE SKIP LOCKED ``` This code snippet selects and immediately locks a set of rows that does not exceed the specified size. The rows that are already locked by other transactions are skipped: they will get into another batch next time. It is a rather flexible and convenient solution that allows you to easily change the batch size and restart the operation in case of a failure. Let’s unset the `processed` attribute and perform full vacuuming to restore the original size of the table: (*p.* [224](/en/13-row-level-locks/#internal-page-224)) ```psql => UPDATE vac SET processed = false; => VACUUM FULL vac; ``` Once the first batch is updated, the table size grows a bit: ```psql => WITH batch AS ( SELECT id FROM vac WHERE NOT processed LIMIT 1000 FOR UPDATE SKIP LOCKED ) UPDATE vac SET processed = true WHERE id IN (SELECT id FROM batch); UPDATE 1000 => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 7064 kB (1 row) ``` But from now on, the size remains almost the same because new tuples replace the removed ones: ```psql => VACUUM vac; => WITH batch AS ( SELECT id FROM vac WHERE NOT processed LIMIT 1000 FOR UPDATE SKIP LOCKED ) UPDATE vac SET processed = true WHERE id IN (SELECT id FROM batch); UPDATE 1000 => SELECT pg_size_pretty(pg_table_size('vac')); pg_size_pretty ---------------- 7072 kB (1 row) ``` [^internal-135-1]: [postgresql.org/docs/14/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY]() [^internal-135-2]: [backend/commands/cluster.c]() [^internal-137-1]: [wiki.postgresql.org/wiki/Show\_database\_bloat]() [^internal-138-1]: [postgresql.org/docs/14/progress-reporting.html#CLUSTER-PHASES]() [^internal-140-1]: [backend/commands/cluster.c]() [^internal-140-2]: [backend/commands/indexcmds.c]() [^internal-140-3]: [backend/commands/tablecmds.c](), ExecuteTruncate function [^internal-141-1]: [github.com/reorg/pg\_repack]() [^internal-141-2]: [github.com/dataegret/pgcompacttable]()