# Lesson 05: MVCC / Vacuuming > Native Markdown source page: MVCC / Vacuuming --- LLMS index: [llms.txt](/en/llms.txt) --- > [!NOTE] > This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. > **HTML source:** [dba2_05_mvcc_vacuum.html](https://edu.postgrespro.com/16/dba2-16-en/dba2_05_mvcc_vacuum.html) > **Archive:** [DBA2-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA2-handouts-16-en.zip) > **PDF pages:** 25 ## Page 1: MVCC / Vacuuming {#source-page-001} ### Copyright © Postgres Professional, 2016–2026 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal) ### Use of Course Materials Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials. ### Feedback Please send your feedback, comments and suggestions to: edu@postgrespro.ru ### Disclaimer Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications. ## Page 2: Topics {#source-page-002} - Plain Vacuuming and its Operation - Throttling Analysis - Full Vacuuming and Analogues ## Page 3: Plain Vacuuming {#source-page-003} **Performed by VACUUM command** - does not conflict with regular database activity - Processes a table and all its indexes - cleans dead tuples in heap pages - (skipping pages marked in the visibility map) - cleans index entries referencing vacuumed tuples - releases item identifiers - updates the free space map - updates the visibility map - main table and its TOAST table can be vacuumed separately ### Speaker notes Table pruning is done quickly, but it does not solve all issues. It works only within one table page and does not affect indexes. The VACUUM command processes the entire table, including all indexes. It removes both dead row versions and their identifiers in the page headers. During vacuuming, tables and indexes can be used in the usual manner, both for read and write operations. However, concurrent execution of some commands (e.g., CREATE INDEX, ALTER TABLE) is not possible. Vacuuming skips table pages without dead tuples. To avoid scanning extra pages, PostgreSQL uses a visibility map, which marks pages that contain only live versions. The map also gets updated. If we want vacuuming to process all pages, we should enable the DISABLE\_PAGE\_SKIPPING option. The operation also updates the free space map, which tracks available space in pages. It is possible to skip vacuuming the main table or its TOAST table by disabling the PROCESS\_MAIN or PROCESS\_TOAST parameters (both are enabled by default). ## Page 4: Before Vacuuming {#source-page-004} ![Source slide page 4: Before Vacuuming](images/page-004.png){#source-figure-004 caption="Source slide page 4: Before Vacuuming"} ### Speaker notes The figure shows the scenario before vacuuming. The table page has three versions of the same row. Two of them are dead, not visible in any snapshot and can be removed. The index has three references to each of the row versions. ## Page 5: 1. Heap Scan {#source-page-005} ![Source slide page 5: 1. Heap Scan](images/page-005.png){#source-figure-005 caption="Source slide page 5: 1. Heap Scan"} ### Speaker notes First, VACUUM scans the table (skipping pages that are marked in the visibility map). In read pages, the process identifies dead tuples and stores their identifiers (TIDs) in a special array which resides in the local memory of the backend process. The memory chunk is allocated at once rather than on demand, and its size is limited by the maintenance\_work\_mem parameter. The default value for this parameter is 64 MB. ## Page 6: 2. Index Vacuuming {#source-page-006} ![Source slide page 6: 2. Index Vacuuming](images/page-006.png){#source-figure-006 caption="Source slide page 6: 2. Index Vacuuming"} ### Speaker notes When the memory allocated for the array runs out (or if the end of the table is reached), index vacuuming begins. To do this, each of the indexes created on the table is fully scanned to find all the entries that refer to the tuples registered in the tid array. These entries are removed from index pages. Starting with PostgreSQL 14, vacuuming skips index scanning if it is ineffective (when the number of pages containing dead rows does not exceed 2% of the total number of table pages). However, if the INDEX\_CLEANUP parameter is set to on, VACUUM will delete obsolete entries from the indexes. ## Page 7: 3. Heap Vacuuming {#source-page-007} ![Source slide page 7: 3. Heap Vacuuming](images/page-007.png){#source-figure-007 caption="Source slide page 7: 3. Heap Vacuuming"} ### Speaker notes After that, the necessary table pages are read again to remove dead tuples and to free the item identifiers that are no longer referenced from indexes. If the table was not fully scanned in the first pass, VACUUM cleans the tid array and continues its work from that point. Thus, if vacuuming removes so many row versions that their TIDs exceed maintenance\_work\_mem, all indexes will be fully scanned several times. For large tables, it can be time-consuming and put a significant load on the system. In such cases, you can either distribute the load over time, by running VACUUM more frequently (so that each run processes a smaller number of row versions), or allocate more memory to prevent reprocessing of indexes. Vacuuming ends with a truncation phase. If vacuuming produced a lot of pages at the end of the file, it can “bite off” this tail. This action requires an exclusive lock to be set. If such locking causes issues, you can disable truncation using vacuum\_truncate storage parameter or explicitly: VACUUM (TRUNCATE off). For more details, see the Locks module. ## Page 8: Monitoring {#source-page-008} **VACUUM VERBOSE** - pg\_stat\_progress\_vacuum view - total table size - number of scanned pages and number of vacuumed pages - number of index vacuum cycles already completed - number of TIDs that fit into memory - and current number of TIDs in memory - current vacuum phase ### Speaker notes If vacuuming takes a long time, you may need to check its current status. To do this, you can run VACUUM with the VERBOSE option. The console displays progress information. In addition, the pg\_stat\_progress\_vacuum view provides comprehensive information about currently running vacuum operations. You can track the progress of the table vacuuming by comparing the number of pages already vacuumed (heap\_blks\_vacuumed) with the total number of pages (heap\_blks\_total). The index vacuuming progress is not displayed in detail, it is more important to pay attention to the number of vacuum cycles (index\_vacuum\_count). If this value is greater than one, it means that the maintenance\_work\_mem was not enough to complete vacuuming in one pass. ## Page 9: Plain Vacuuming {#source-page-009} Let’s create a table and an index. ```sql => CREATE DATABASE mvcc_vacuum; ``` ```sql CREATE DATABASE ``` ```sql => \c mvcc_vacuum ``` ```text You are now connected to database "mvcc_vacuum" as user "student". ``` ```sql => CREATE TABLE t(id integer); ``` ```sql CREATE TABLE ``` ```sql => CREATE INDEX t_id ON t(id); ``` ```sql CREATE INDEX ``` As usual, use the pageinspect extension: ```sql => CREATE EXTENSION pageinspect; ``` ```sql CREATE EXTENSION ``` And also the view for the table page: ```sql => CREATE VIEW t_v AS SELECT '(0,'||lp||')' AS ctid, CASE lp_flags WHEN 0 THEN 'unused' WHEN 1 THEN 'normal' WHEN 2 THEN 'redirect to '||lp_off WHEN 3 THEN 'dead' END AS state, t_xmin || CASE WHEN (t_infomask & 256) > 0 THEN ' (c)' WHEN (t_infomask & 512) > 0 THEN ' (a)' ELSE '' END AS xmin, t_xmax || CASE WHEN (t_infomask & 1024) > 0 THEN ' (c)' WHEN (t_infomask & 2048) > 0 THEN ' (a)' ELSE '' END AS xmax, CASE WHEN (t_infomask2 & 16384) > 0 THEN 't' END AS hhu, CASE WHEN (t_infomask2 & 32768) > 0 THEN 't' END AS hot, t_ctid FROM heap_page_items(get_raw_page('t',0)) ORDER BY lp; ``` ```sql CREATE VIEW ``` And the view for the index: ```sql => CREATE VIEW t_id_v AS SELECT itemoffset, ctid FROM bt_page_items('t_id',1); ``` ```sql CREATE VIEW ``` Insert a row into the table and update it: ```sql => INSERT INTO t VALUES (1); ``` ```sql INSERT 0 1 ``` ```sql => UPDATE t SET id = 2; ``` ```sql UPDATE 1 ``` Now, to remind ourselves about the concept of the horizon, let’s open another session with an active data snapshot. ```sql => \c mvcc_vacuum ``` ```text You are now connected to database "mvcc_vacuum" as user "student". ``` ```text => BEGIN ISOLATION LEVEL REPEATABLE READ; ``` ## Page 10: Page 10 {#source-page-010} ```text BEGIN ``` ```sql => SELECT * FROM t; ``` ```text id ---- 2 (1 row) ``` The database horizon is determined by this snapshot: ```sql => SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid(); ``` ```text backend_xmin -------------- 751 (1 row) ``` Update the row again. ```sql => UPDATE t SET id = 3; ``` ```sql UPDATE 1 ``` There are now three row versions in the table: ```sql => SELECT * FROM t_v; ``` ```text ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+---------+---------+-----+-----+-------- (0,1) | normal | 749 (c) | 750 (c) | | | (0,2) (0,2) | normal | 750 (c) | 751 | | | (0,3) (0,3) | normal | 751 | 0 (a) | | | (0,3) (3 rows) ``` Run vacuuming. ```sql => VACUUM t; ``` ```sql VACUUM ``` How does the table page change? ```sql => SELECT * FROM t_v; ``` ```text ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+---------+---------+-----+-----+-------- (0,1) | unused | | | | | (0,2) | normal | 750 (c) | 751 (c) | | | (0,3) (0,3) | normal | 751 (c) | 0 (a) | | | (0,3) (3 rows) ``` Vacuuming removed one row version, but the second remained unchanged, since the parallel transaction is still incomplete and its snapshot is active. The index contains two references to the remaining versions: ```sql => SELECT * FROM t_id_v; ``` ```text itemoffset | ctid ------------+------- 1 | (0,2) 2 | (0,3) (2 rows) ``` We can request vacuuming to provide details about its operations: ```sql => VACUUM VERBOSE t; ``` ## Page 11: Page 11 {#source-page-011} ```sql INFO: vacuuming "mvcc_vacuum.public.t" INFO: finished vacuuming "mvcc_vacuum.public.t": index scans: 0 pages: 0 removed, 1 remain, 1 scanned (100.00% of total) tuples: 0 removed, 2 remain, 1 are dead but not yet removable removable cutoff: 751, which was 1 XIDs old when operation ended frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 10 hits, 0 misses, 0 dirtied WAL usage: 0 records, 0 full page images, 0 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ``` Please note: tuples: 0 removed, 2 remain, 1 are dead but not yet removable index scan not needed removable cutoff shows the current horizon Now let’s complete the parallel transaction and run vacuuming again. ```text => COMMIT; ``` ```text COMMIT ``` ```sql => VACUUM VERBOSE t; ``` ```sql INFO: vacuuming "mvcc_vacuum.public.t" INFO: finished vacuuming "mvcc_vacuum.public.t": index scans: 1 pages: 0 removed, 1 remain, 1 scanned (100.00% of total) tuples: 1 removed, 1 remain, 0 are dead but not yet removable removable cutoff: 752, which was 0 XIDs old when operation ended new relfrozenxid: 751, which is 1 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 1 pages from table (100.00% of total) had 1 dead item identifiers removed index "t_id": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable avg read rate: 0.000 MB/s, avg write rate: 91.912 MB/s buffer usage: 16 hits, 0 misses, 1 dirtied WAL usage: 6 records, 1 full page images, 8791 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ``` The horizon moved forward and allowed the vacuuming to remove the dead row — tuples: 1 removed. Now only the live row version remains in the page: ```sql => SELECT * FROM t_v; ``` ```text ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+---------+-------+-----+-----+-------- (0,1) | unused | | | | | (0,2) | unused | | | | | (0,3) | normal | 751 (c) | 0 (a) | | | (0,3) (3 rows) ``` Only one entry remains in the index as well: ```sql => SELECT * FROM t_id_v; ``` ```text itemoffset | ctid ------------+------- 1 | (0,3) (1 row) ``` ## Page 12: Throttling {#source-page-012} **The process alternates between work and sleep modes** - approximately vacuum\_cost\_limit units of work, - then sleeps for vacuum\_cost\_delay ms - Settings - vacuum\_cost\_limit = 200 - vacuum\_cost\_delay = 0 ms - operation costs: - vacuum\_cost\_page\_hit = 1 - if a page is found in the buffer cache - vacuum\_cost\_page\_miss = 2 - if a page is read from disk - vacuum\_cost\_page\_dirty = 20 - if a clean page turned dirty ### Speaker notes Since vacuuming processes tables and indexes intensively, it may be necessary to distribute activities over time to smooth out load spikes. This can be achieved by performing the vacuuming in chunks, alternating between work and sleep modes. The vacuum\_cost\_limit chunk size is specified in conventional units. The other three options on the slide determine the cost of processing one page in these units, depending on the performed actions. In the best-case scenario, the cost is vacuum\_cost\_page\_hit (if the page was found in the cache and either has not changed, or is already dirty). In the worst-case scenario, the cost is vacuum\_cost\_page\_miss + vacuum\_cost\_page\_dirty (if the page was read from disk and was modified by vacuuming). The default setting actually disables throttling because the delay time is set to zero. It is assumed that if administrators have to resort to manual vacuuming, they expect its completion as soon as possible. ME-CONFIG-RESOURCE-VACUUM-COST ## Page 13: Parallel Vacuuming {#source-page-013} **Scanning and vacuuming of a table is performed sequentially** - Index cleanup can be performed in parallel - each index is processed by a single worker process - index size must exceed min\_parallel\_index\_scan\_size - the number of processes is limited by max\_parallel\_maintenance\_workers - and, if specified, by VACUUM (PARALLEL n) value ### Speaker notes The phases of scanning and vacuuming a table are always performed sequentially by one process. However, the index cleanup phase can be performed in parallel. This occurs when the table has several (more than one) sufficiently large indexes: the size of an index must exceed the min\_parallel\_index\_scan\_size parameter (512 KB by default). A separate worker process is started for each appropriate index. Each index is processed by a single worker process, that is, several processes cannot clean the same index concurrently. The total number of worker processes is limited by the max\_parallel\_maintenance\_workers parameter and can be further limited by explicitly specifying the level of parallelism in the VACUUM (PARALLEL n) command. ## Page 14: Analysis {#source-page-014} **Performed by VACUUM ANALYZE or ANALYZE** - does not conflict with regular database activity - Collects statistics for the planner ### Speaker notes Another task that is usually combined with vacuuming is analysis, that is, collecting statistics for the query planner. The number of rows in a table, the distribution of data within columns and other metrics are analyzed. Statistics collection is discussed in detail in the QPT course. You can run the analysis manually using the ANALYZE (analysis only) or VACUUM ANALYZE (both vacuuming and analysis). As with vacuuming, processing runs in the background and does not interfere with regular database activity. ## Page 15: Analysis {#source-page-015} Create a table with a large number of identical rows and index it: ```sql => CREATE TABLE tt(s) AS SELECT 'FOO' FROM generate_series(1,1_000_000) AS g; ``` ```sql SELECT 1000000 ``` ```sql => CREATE INDEX ON tt(s); ``` ```sql CREATE INDEX ``` The planner does not know anything about the data and chooses index access, although the entire table has to be read: ```sql => EXPLAIN (costs off) SELECT * FROM tt WHERE s = 'FOO'; ``` ```text QUERY PLAN --------------------------------------- Bitmap Heap Scan on tt Recheck Cond: (s = 'FOO'::text) -> Bitmap Index Scan on tt_s_idx Index Cond: (s = 'FOO'::text) (4 rows) ``` During analysis, statistics are collected from a random sample of rows: ```text => ANALYZE VERBOSE tt; ``` ```text INFO: analyzing "public.tt" INFO: "tt": scanned 4480 of 4480 pages, containing 1000000 live rows and 0 dead rows; 30000 rows in sample, 1000000 estimated total rows ANALYZE ``` The statistics are saved in the system catalog. After that, the planner knows that all rows have the same value, and stops using the index: ```sql => EXPLAIN (costs off) SELECT * FROM tt WHERE s = 'FOO'; ``` ```text QUERY PLAN ----------------------------- Seq Scan on tt Filter: (s = 'FOO'::text) (2 rows) ``` ## Page 16: Full Vacuuming {#source-page-016} **Performed by the VACUUM FULL command** - incompatible with any table operations, including reading - Completely rebuilds the table and all its indexes - requires additional disk space for new files - released space is returned to the operating system - takes longer than plain vacuuming ### Speaker notes Plain vacuuming sometimes may still be not enough. If a table or an index has grown significantly in size, vacuuming will not reduce the number of pages (and therefore will not reduce the file size). Instead, it creates holes (free space) within existing pages that can be used for new inserts or updates. The only exception is completely cleaned pages at the end of the file, such pages are cut off and the free space is returned to the operating system. If the file sizes have exceeded reasonable limits, you can perform full vacuuming. In this case, the table and all its indexes are rebuilt completely from scratch, and the data is packed as compactly as possible (taking into account the fillfactor). During the rebuild, PostgreSQL first rebuilds the table, and then sequentially rebuilds each of the indexes. New files are created for them, and old ones are deleted. Note that additional disk space will be required during operation. Full vacuuming is not intended for regular use, since it completely locks the table (including reads) for the entire runtime. This is often not viable on a heavily used system. In such cases, the pg\_repack extension ( which is not included in the standard distribution, can serve as an alternative. ## Page 17: Before Full Vacuuming {#source-page-017} ![Source slide page 17: Before Full Vacuuming](images/page-017.png){#source-figure-017 caption="Source slide page 17: Before Full Vacuuming"} ### Speaker notes The figure shows the state before full vacuuming. The table page has three versions of the same row. Two of them are obsolete, not visible in any snapshot and can be removed. The index has three references to each of the three row versions. ## Page 18: After Full Vacuuming {#source-page-018} ![Source slide page 18: After Full Vacuuming](images/page-018.png){#source-figure-018 caption="Source slide page 18: After Full Vacuuming"} ### Speaker notes After full vacuuming, the contents of the table and its indexes are completely rebuilt and reside in physically different files. However, the OID of the table remains unchanged. ## Page 19: Full Vacuuming {#source-page-019} Files occupied by the table and index: ```sql => SELECT pg_relation_filepath('t'), pg_relation_filepath('t_id'); ``` ```text pg_relation_filepath | pg_relation_filepath ----------------------+---------------------- base/16390/16391 | base/16390/16394 (1 row) ``` Let’s run full vacuuming. ```sql => VACUUM FULL VERBOSE t; ``` ```sql INFO: vacuuming "public.t" INFO: "public.t": found 0 removable, 1 nonremovable row versions in 1 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM ``` The table and index are now completely rebuilt: ```sql => SELECT * FROM t_v; ``` ```text ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+---------+-------+-----+-----+-------- (0,1) | normal | 751 (c) | 0 (a) | | | (0,1) (1 row) ``` ```sql => SELECT * FROM t_id_v; ``` ```text itemoffset | ctid ------------+------- 1 | (0,1) (1 row) ``` The file names have changed: ```sql => SELECT pg_relation_filepath('t'), pg_relation_filepath('t_id'); ``` ```text pg_relation_filepath | pg_relation_filepath ----------------------+---------------------- base/16390/16455 | base/16390/16458 (1 row) ``` ## Page 20: Full Vacuuming Analogues {#source-page-020} **CLUSTER** - completely rebuilds the table and all its indexes - physically reorders row versions - according to one of the indexes - REINDEX - completely rebuilds indexes - TRUNCATE - empties the table - Features - all these commands completely lock the table - all these commands create new data files ### Speaker notes There are several commands that operate using a similar mechanism. All the commands completely lock the table for operations, remove old data files and create new ones. The CLUSTER command is functionally similar to VACUUM FULL, but it also physically arranges the row versions according to a specified index. This allows the planner to use index access more efficiently in some cases. However, this physical order is not maintained, subsequent data modifications will break it. REINDEX rebuilds indexes without modifying the table data. In fact, VACUUM FULL uses this command to rebuild indexes. While reading the table is not formally prohibited during the operation, the planner will attempt to get a lock on the index being rebuilt in order to consider possible query plans. As a result, it is practically impossible to read the table until the operation completes. The command with CONCURRENTLY option lasts longer, but it neither locks the index nor interferes with reading and updating data. The TRUNCATE command achieves the same result as DELETE by removing all rows from a table. However, as previously discussed, DELETE marks row versions as deleted, which requires subsequent vacuuming. TRUNCATE, by contrast, just creates a new, empty data file. This works faster, but it is important to note that TRUNCATE locks the table until the end of the transaction. ## Page 21: Takeaways {#source-page-021} **Plain vacuuming frees up space in pages** - runs as a background process - may require several index scans - Analysis collects statistics for the planner - runs as a background process can be combined with vacuuming Full vacuuming rebuilds the table and indexes - locks the table for the entire runtime ## Page 22: Practice {#source-page-022} 1. Create a large table with an index. Temporarily reduce the maintenance\_work\_mem to force several index passes. Check by starting VACUUM VERBOSE. - 2. Remove 90% of random rows from large table and check how the space the table occupies on the disk has changed after plain vacuuming. 3. Repeat step 2, but use full vacuuming this time. ### Speaker notes 1. To prevent autovacuum (discussed in the next lesson), specify the autovacuum\_enabled storage parameter, when creating a table: CREATE TABLE ... WITH (autovacuum\_enabled = off); ## Page 23: 1. Index Scanning during Vacuuming {#source-page-023} Let’s create a table and an index. The autovacuum\_enabled parameter is off to prevent automatic vacuuming. ```sql => CREATE DATABASE mvcc_vacuum; ``` ```sql CREATE DATABASE ``` ```sql => \c mvcc_vacuum ``` ```text You are now connected to database "mvcc_vacuum" as user "student". ``` ```sql => CREATE TABLE t(id integer) WITH (autovacuum_enabled = off); ``` ```sql CREATE TABLE ``` ```sql => INSERT INTO t SELECT gen.id FROM generate_series(1,1_000_000) gen(id); ``` ```sql INSERT 0 1000000 ``` ```sql => CREATE INDEX t_id ON t(id); ``` ```sql CREATE INDEX ``` Reduce the memory allocated for the identifier array: ```sql => ALTER SYSTEM SET maintenance_work_mem = '1MB'; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` Update all rows: ```sql => UPDATE t SET id = id + 1; ``` ```sql UPDATE 1000000 ``` Let’s run vacuuming. After a while check the pg\_stat\_progress\_vacuum view in another session. ```sql => VACUUM VERBOSE t; ``` ```sql => \c mvcc_vacuum ``` ```text You are now connected to database "mvcc_vacuum" as user "student". ``` ```sql => SELECT * FROM pg_stat_progress_vacuum \gx ``` ```text -[ RECORD 1 ]------+------------------ pid | 50567 datid | 16390 datname | mvcc_vacuum relid | 16391 phase | vacuuming indexes heap_blks_total | 8850 heap_blks_scanned | 3088 heap_blks_vacuumed | 2315 index_vacuum_count | 3 max_dead_tuples | 174761 num_dead_tuples | 174472 ``` ## Page 24: Page 24 {#source-page-024} ```sql INFO: vacuuming "mvcc_vacuum.public.t" INFO: finished vacuuming "mvcc_vacuum.public.t": index scans: 6 pages: 0 removed, 8850 remain, 8850 scanned (100.00% of total) tuples: 1000000 removed, 1000000 remain, 0 are dead but not yet removable removable cutoff: 748, which was 0 XIDs old when operation ended new relfrozenxid: 747, which is 3 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 4425 pages from table (50.00% of total) had 1000000 dead item identifiers removed index "t_id": pages: 5486 in total, 0 newly deleted, 0 currently deleted, 0 reusable avg read rate: 0.000 MB/s, avg write rate: 0.086 MB/s buffer usage: 55073 hits, 0 misses, 6 dirtied WAL usage: 23177 records, 6 full page images, 10055108 bytes system usage: CPU: user: 0.34 s, system: 0.04 s, elapsed: 0.54 s VACUUM ``` Restore the value of the changed parameter. ```sql => ALTER SYSTEM RESET maintenance_work_mem; ``` ```sql ALTER SYSTEM ``` ```sql => SELECT pg_reload_conf(); ``` ```text pg_reload_conf ---------------- t (1 row) ``` ### 2. Vacuuming a Large Number of Rows Current data file size: ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ```text pg_size_pretty ---------------- 69 MB (1 row) ``` Randomly delete 90% of rows. Random deletion guarantees that some rows remain on each page, vacuuming will not reduce the file size in this case. ```sql => DELETE FROM t WHERE random() < 0.9; ``` ```sql DELETE 899717 ``` Size after vacuuming: ```sql => VACUUM t; ``` ```sql VACUUM ``` ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ```text pg_size_pretty ---------------- 69 MB (1 row) ``` The size has not changed. ### 3. Full Vacuuming of a Large Number of Rows Let’s refill the table. ```text => TRUNCATE t; ``` ```text TRUNCATE TABLE ``` ```sql => INSERT INTO t SELECT gen.id FROM generate_series(1,1_000_000) gen(id); ``` ```sql INSERT 0 1000000 ``` Current data file size: ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ## Page 25: Page 25 {#source-page-025} ```text pg_size_pretty ---------------- 35 MB (1 row) ``` > [!NOTE] > **Note** > Note that last time the size was about twice as large. The second half was occupied by row versions created by the UPDATE command. The size after deletion and full vacuuming: ```sql => DELETE FROM t WHERE random() < 0.9; ``` ```sql DELETE 900400 ``` ```sql => VACUUM FULL t; ``` ```sql VACUUM ``` ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ```text pg_size_pretty ---------------- 3528 kB (1 row) ``` The size reduced by 90%.