# Занятие 05: Многоверсионность / Очистка > Исходная страница в нативном Markdown: Многоверсионность / Очистка --- Индекс LLMS: [llms.txt](/ru/llms.txt) --- > [!NOTE] > Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета. > **HTML source:** [dba2_05_mvcc_vacuum.html](https://edu.postgrespro.ru/16/dba2-16/dba2_05_mvcc_vacuum.html) > **Archive:** [DBA2-handouts-16.zip](https://edu.postgrespro.ru/16/DBA2-handouts-16.zip) > **Страниц PDF:** 24 ## Page 1: Многоверсионность / Очистка {#source-page-001} ### Авторские права © Postgres Professional, 2016–2025 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал) ### Использование материалов курса Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса. ### Обратная связь Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru ### Отказ от ответственности Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения. ## Page 2: Темы {#source-page-002} - Обычная очистка и схема ее работы - Регулирование нагрузки - Анализ - Полная очистка и аналоги ## Page 3: Обычная очистка {#source-page-003} **Выполняется командой VACUUM** - не конфликтует с обычной активностью в системе - Обрабатывает таблицу и все ее индексы - очищает ненужные версии строк в табличных страницах - (пропуская страницы, уже отмеченные в карте видимости) - очищает индексные записи, ссылающиеся на очищенные версии строк - освобождает указатели - обновляет карту свободного пространства - обновляет карту видимости - возможна отдельная очистка основной таблицы и TOAST-части ### Комментарии преподавателя Самоочистка таблицы выполняется быстро, но не решает всех задач. Она работает только в пределах одной табличной страницы и не затрагивает индексы. Очистка, выполняемая командой VACUUM, обрабатывает всю таблицу, включая все созданные на ней индексы. При этом удаляются и ненужные версии строк, и указатели на них в начале страницы. Во время работы очистки таблица может использоваться обычным образом и для чтения, и для изменения данных. Но одновременное выполнение ряда команд (например CREATE INDEX, ALTER TABLE и некоторых других) будет невозможно. Очистка пропускает страницы таблицы, в которых нет ненужных версий строк. Для этого используется карта видимости (visibility map), где отмечаются страницы, заведомо содержащие только актуальные версии. Сама карта при этом тоже обновляется. Если нужно, чтобы очистка обработала все страницы, следует включить параметр DISABLE\_PAGE\_SKIPPING. В процессе работы обновляется также и карта свободного пространства (free space map), в которой отражается наличие свободного места в страницах. Можно отказаться от очистки основной таблицы или ее TOAST-части, выключив параметры PROCESS\_MAIN или PROCESS\_TOAST (по умолчанию оба включены). ## Page 4: До очистки {#source-page-004} ![Source slide page 4: До очистки](images/page-004.png){#source-figure-004 caption="Source slide page 4: До очистки"} ### Комментарии преподавателя На рисунке приведена ситуация до очистки. В табличной странице три версии одной строки. Две из них неактуальны, не видны ни в одном снимке и могут быть удалены. В индексе есть три ссылки: на каждую из версий строки. ## Page 5: 1. Сканирование таблицы {#source-page-005} ![Source slide page 5: 1. Сканирование таблицы](images/page-005.png){#source-figure-005 caption="Source slide page 5: 1. Сканирование таблицы"} ### Комментарии преподавателя Сначала VACUUM выполняет сканирование таблицы (пропуская те страницы, которые помечены в карте видимости). В прочитанных страницах процесс определяет ненужные версии строк и записывает их идентификаторы в специальный массив. Для этого в локальной памяти процесса VACUUM выделяется фрагмент размером maintenance\_work\_mem (или меньший, если его заведомо будет достаточно). Значение этого параметра по умолчанию — 64 Мбайт. Отметим, что эта память выделяется сразу в полном объеме, а не по мере необходимости. ## Page 6: 2. Очистка индексов {#source-page-006} ![Source slide page 6: 2. Очистка индексов](images/page-006.png){#source-figure-006 caption="Source slide page 6: 2. Очистка индексов"} ### Комментарии преподавателя Когда выделенная под массив память заканчивается (или если мы уже дошли до конца таблицы), выполняется очистка индексов. Для этого каждый из индексов, созданных на таблице, полностью сканируется в поисках записей, которые ссылаются на очищаемые версии строк. Найденные записи вычищаются из индексных страниц. Начиная с PostgreSQL 14, очистка пропускает сканирование индексов, если это малоэффективно (когда количество страниц, содержащих мертвые строки, не превышает 2% от общего количества страниц таблицы). Но если параметр INDEX\_CLEANUP выставлен в значение on, то VACUUM будет безусловно удалять ненужные записи из индексов. ## Page 7: 3. Очистка таблицы {#source-page-007} ![Source slide page 7: 3. Очистка таблицы](images/page-007.png){#source-figure-007 caption="Source slide page 7: 3. Очистка таблицы"} ### Комментарии преподавателя После этого необходимые табличные страницы повторно читаются, чтобы очистить в них ненужные версии строк и освободить указатели, на которые больше нет ссылок из индексов. Если на первом проходе таблица не была просканирована полностью, VACUUM очищает массив в памяти и продолжает работу с этой точки. Таким образом, если при очистке удаляется так много версий строк, что их идентификаторы не помещаются в память размером maintenance\_work\_mem, все индексы будут полностью сканироваться несколько раз. На больших таблицах это может занимать существенное время и заметно нагружать систему. В таком случае можно либо распределить нагрузку во времени, вызывая очистку чаще (чтобы за каждый раз очищалось не очень большое количество версий строк), либо выделить больше памяти, чтобы исключить повторную обработку индексов. В конце очистки выполняется этап усечения таблицы. Если в самом конце файла образовалось достаточно много пустых страниц, они «откусываются» и освободившееся место возвращается операционной системе. Это действие требует установки исключительной блокировки. Если такая блокировка вызывает проблемы, этап усечения можно отключить параметром хранения vacuum\_truncate или явно: VACUUM (TRUNCATE off). Подробнее см. модуль «Блокировки». ## Page 8: Мониторинг {#source-page-008} **VACUUM VERBOSE Представление pg\\\_stat\\\_progress\\\_vacuum** - полный размер таблицы - число прочитанных страниц и число очищенных страниц - количество уже завершенных циклов очистки индексов - число идентификаторов версий строк, помещающихся в память - и текущее число идентификаторов в памяти - текущая фаза очистки ### Комментарии преподавателя Если очистка выполняется долго, может потребоваться узнать текущее состояние дел. Для этого можно вызывать VACUUM с указанием VERBOSE — на консоль будет выводиться информация о ходе выполнения. Кроме того, есть представление pg\_stat\_progress\_vacuum, которое содержит всю информацию о выполняющейся в данный момент очистке. Ход очистки таблицы можно отслеживать, сравнивая число очищенных страниц (heap\_blks\_vacuumed) с общим количеством (heap\_blks\_total). Ход очистки индексов детально не отображается, но основное внимание надо обращать на количество циклов очистки (index\_vacuum\_count) — значение больше 1 означает, что памяти maintenance\_work\_mem не хватило для того, чтобы завершить очистку за один проход. GRESS-REPORTING ## Page 9: Обычная очистка {#source-page-009} Создадим таблицу и индекс. ```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 ``` Как обычно, используем расширение pageinspect: ```sql => CREATE EXTENSION pageinspect; ``` ```sql CREATE EXTENSION ``` А также представление для табличной страницы: ```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 ``` И представление для индекса: ```sql => CREATE VIEW t_id_v AS SELECT itemoffset, ctid FROM bt_page_items('t_id',1); ``` ```sql CREATE VIEW ``` Вставим строку в таблицу и обновим ее: ```sql => INSERT INTO t VALUES (1); ``` ```sql INSERT 0 1 ``` ```sql => UPDATE t SET id = 2; ``` ```sql UPDATE 1 ``` Теперь, чтобы еще раз напомнить про понятие горизонта, откроем в другом сеансе транзакцию с активным снимком данных. ```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) ``` Горизонт базы данных определяется этим снимком: ```sql => SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid(); ``` ```text backend_xmin -------------- 751 (1 row) ``` Снова обновим строку. ```sql => UPDATE t SET id = 3; ``` ```sql UPDATE 1 ``` Сейчас в таблице три версии строки: ```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) ``` Выполним теперь очистку. ```sql => VACUUM t; ``` ```sql VACUUM ``` Как изменится табличная страница? ```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) ``` Очистка освободила одну версию строки, а вторая осталась без изменений, так как параллельная транзакция до сих пор не завершена и ее снимок активен. В индексе — два указателя на оставшиеся версии: ```sql => SELECT * FROM t_id_v; ``` ```text itemoffset | ctid ------------+------- 1 | (0,2) 2 | (0,3) (2 rows) ``` Можно попросить очистку рассказать о том, что происходит: ```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 ``` Обратите внимание: tuples: 0 removed, 2 remain, 1 are dead but not yet removable, index scan not needed removable cutoff показывает текущий горизонт Теперь завершим параллельную транзакцию и снова вызовем очистку. ```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: 27.606 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 ``` Горизонт сдвинулся вперед и позволил очистке удалить мертвую строку — tuples: 1 removed. Теперь в странице осталась только последняя актуальная версия строки: ```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) ``` В индексе также осталась только одна запись: ```sql => SELECT * FROM t_id_v; ``` ```text itemoffset | ctid ------------+------- 1 | (0,3) (1 row) ``` ## Page 12: Регулирование нагрузки {#source-page-012} **Процесс чередует работу и ожидание** - примерно vacuum\_cost\_limit условных единиц работы, - затем засыпает на vacuum\_cost\_delay мс - Настройки - vacuum\_cost\_limit = 200 - vacuum\_cost\_delay = 0 ms - стоимость обработки: - vacuum\_cost\_page\_hit = 1 - страница нашлась в кеше - vacuum\_cost\_page\_miss = 2 - страница прочитана с диска - vacuum\_cost\_page\_dirty = 20 - чистая страница стала грязной ### Комментарии преподавателя Поскольку процесс очистки интенсивно работает с таблицами и индексами, может оказаться необходимым распределить действия во времени и тем самым сгладить пики нагрузки. Для этого существует возможность выполнять очистку порциями, чередуя работу и ожидание. Размер порции vacuum\_cost\_limit указывается в условных единицах. Три других параметра, указанных на слайде, определяют стоимость обработки одной страницы в этих единицах в зависимости от выполненных действий. В лучшем случае стоимость будет равна vacuum\_cost\_page\_hit (если страница нашлась в кеше и либо не изменилась, либо уже была грязной); в худшем случае — vacuum\_cost\_page\_miss + vacuum\_cost\_page\_dirty (если страница была прочитана с диска и изменилась в результате очистки). Настройка по умолчанию фактически отключает механизм регулирования нагрузки, так как время ожидания выставлено в ноль. Это сделано из тех соображений, что если администратору пришлось запускать VACUUM вручную, он, скорее всего, хочет выполнить очистку как можно быстрее. E-CONFIG-RESOURCE-VACUUM-COST ## Page 13: Параллельная очистка {#source-page-013} **Сканирование и очистка таблицы — последовательно** - Очистка индексов может выполняться параллельно - каждый индекс — только одним рабочим процессом - размер индекса должен превышать min\_parallel\_index\_scan\_size - количество процессов ограничено max\_parallel\_maintenance\_workers - и, если указано, значением VACUUM (PARALLEL n) ### Комментарии преподавателя Этапы сканирования и очистки таблицы всегда выполняются последовательно одним процессом. Этап очистки индексов может выполняться в параллельном режиме. Это происходит, если на таблице создано несколько (больше одного) достаточно больших индексов: размер индекса должен превышать значение параметра min\_parallel\_index\_scan\_size (512 Кбайт по умолчанию). Тогда для каждого подходящего индекса запускается отдельный рабочий процесс. Каждый индекс обрабатывается только одним рабочим процессом, то есть несколько процессов не могут очищать один и тот же индекс. Количество процессов ограничено сверху значением параметра max\_parallel\_maintenance\_workers и может быть дополнительно ограничено явным указанием степени параллелизма при вызове команды VACUUM (PARALLEL n). ## Page 14: Анализ {#source-page-014} **Выполняется при VACUUM ANALYZE или ANALYZE** - не конфликтует с обычной активностью в системе - Собирает статистику для планировщика ### Комментарии преподавателя Еще одна задача, которую обычно совмещают с очисткой, — анализ, то есть сбор статистической информации для планировщика запросов. Анализируется число строк в таблице, распределение данных по столбцам и т. п. Подробно сбор статистики рассматривается в курсе QPT «Оптимизация запросов» . Вручную анализ выполняется командой ANALYZE (только анализ) или VACUUM ANALYZE (и очистка, и анализ). Как и в случае очистки, обработка происходит в фоновом режиме и не мешает обычному использованию таблиц и индексов. ## Page 15: Анализ {#source-page-015} Создадим таблицу с большим количеством одинаковых строк и проиндексируем ее: ```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 ``` Планировщик ничего не знает про данные и выбирает индексный доступ, хотя читать придется всю таблицу: ```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) ``` При анализе собирается статистика по случайной выборке строк: ```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 ``` Статистика сохраняется в системном каталоге. После этого планировщик знает, что во всех строках находится одно и то же значение, и перестает использовать индекс: ```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: Полная очистка {#source-page-016} **Выполняется командой VACUUM FULL** - не совместима ни с какими операциями над таблицей, включая чтение - Полностью перестраивает таблицу и все ее индексы - при работе потребуется дополнительное место для новых файлов - освобожденное место возвращается операционной системе - выполняется дольше, чем обычная очистка ### Комментарии преподавателя Обычная очистка не решает всех задач по освобождению места. Если таблица или индекс сильно выросли в размерах, то очистка не приведет к сокращению числа страниц (и к уменьшению файлов). Вместо этого внутри существующих страниц появятся «дыры», которые могут быть использованы для вставки новых строк или изменения существующих. Единственное исключение составляют полностью очищенные страницы, находящиеся в конце файла — такие страницы «откусываются» и освободившееся место возвращается операционной системе. Если размер файлов превышает некие разумные пределы, можно выполнить полную очистку. При этом таблица и все ее индексы перестраиваются полностью с нуля, а данные упаковываются максимально компактно (разумеется, с учетом fillfactor). При перестройке PostgreSQL последовательно перестраивает сначала таблицу, а затем и каждый из индексов. Для них создаются новые файлы, а старые удаляются. Следует учитывать, что в процессе работы на диске потребуется дополнительное место. Полная очистка не предполагает регулярного использования, так как полностью блокирует все действия с таблицей (включая ее чтение) на все время своей работы. На активно используемой системе это может быть неприемлемым; в таком случае может помочь расширение pg\_repack (, не входящее в поставку. ## Page 17: До полной очистки {#source-page-017} ![Source slide page 17: До полной очистки](images/page-017.png){#source-figure-017 caption="Source slide page 17: До полной очистки"} ### Комментарии преподавателя На рисунке приведена ситуация до полной очистки. В табличной странице три версии одной строки. Две из них неактуальны, не видны ни в одном снимке и могут быть удалены. В индексе есть три ссылки на каждую из версий строки. ## Page 18: После полной очистки {#source-page-018} ![Source slide page 18: После полной очистки](images/page-018.png){#source-figure-018 caption="Source slide page 18: После полной очистки"} ### Комментарии преподавателя После выполнения полной очистки содержимое таблицы и индекса полностью перестроено и физически находится в других файлах. При этом OID таблицы в системном каталоге сохраняется. ## Page 19: Полная очистка {#source-page-019} Файлы, занимаемые таблицей и индексом: ```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) ``` Вызываем полную очистку. ```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.01 s. VACUUM ``` Таблица и индекс теперь полностью перестроены: ```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) ``` Имена файлов поменялись: ```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: Аналоги полной очистки {#source-page-020} CLUSTER - полностью перестраивает таблицу и все ее индексы - дополнительно физически упорядочивает версии строк - в соответствии с одним из индексов - REINDEX - полностью перестраивает индексы - TRUNCATE - «опустошает» таблицу - Особенности - все команды полностью блокируют работу с таблицей - все команды создают новые файлы для данных ### Комментарии преподавателя Есть несколько команд, которые работают, используя схожий механизм. Все они полностью блокируют работу с таблицей, все они удаляют старые файлы данных и создают новые. Команда CLUSTER во всем аналогична VACUUM FULL, но дополнительно физически упорядочивает версии строк в соответствии с одним из индексов. Это дает планировщику возможность более эффективно использовать индексный доступ в некоторых случаях. Однако надо понимать, что кластеризация не поддерживается: при последующих изменениях таблицы физический порядок версий строк будет нарушаться. Команда REINDEX перестраивает индексы, не трогая при этом таблицу. Фактически, VACUUM FULL использует эту команду для перестроения индексов. При этом читать таблицу формально не запрещается, однако планировщик попытается получить блокировку перестраиваемого индекса, чтобы рассмотреть все планы, и до окончания работы команды прочитать таблицу фактически не удастся. Вариант REINDEX CONCURRENTLY работает дольше, но не блокирует индекс и не мешает чтению и обновлению данных. Команда TRUNCATE логически работает так же, как и DELETE — удаляет все табличные строки. Но DELETE, как уже было рассмотрено, помечает версии строк как удаленные, что требует дальнейшей очистки. TRUNCATE же просто создает новый, чистый файл. Это работает быстрее, но надо учитывать, что TRUNCATE заблокирует работу с таблицей на все время до конца транзакции. ## Page 21: Итоги {#source-page-021} **Обычная очистка освобождает место в страницах** - выполняется в фоновом режиме - может потребоваться несколько раз сканировать индексы - Анализ собирает статистику для планировщика - выполняется в фоновом режиме можно совместить с очисткой Полная очистка перестраивает таблицу и индексы - блокирует работу с таблицей на все время выполнения ## Page 22: Практика {#source-page-022} 1. Создайте большую таблицу с индексом. Временно уменьшите значение maintenance\_work\_mem так, чтобы потребовалось несколько проходов для очистки индекса. Проконтролируйте, запуская VACUUM VERBOSE. - 2. Удалите из большой таблицы 90% случайных строк и проверьте, как изменился объем, который таблица занимает на диске, после выполнения обычной очистки. 3. Повторите п. 2 с полной очисткой. ### Комментарии преподавателя 1. Чтобы исключить срабатывание автоматической очистки (рассматривается в следующей теме), при создании таблицы укажите параметр хранения autovacuum\_enabled: CREATE TABLE ... WITH (autovacuum\_enabled = off); ## Page 23: 1. Сканирование индексов при очистке {#source-page-023} Создадим таблицу с данными и индекс. Параметр autovacuum\_enabled выключен, чтобы не срабатывала автоматическая очистка. ```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 ``` Уменьшаем размер памяти, выделяемой под массив идентификаторов: ```text => SET maintenance_work_mem = '1MB'; ``` ```text SET ``` Обновляем все строки: ```sql => UPDATE t SET id = id + 1; ``` ```sql UPDATE 1000000 ``` Запускаем очистку. Через небольшое время в другом сеансе обратимся к pg\_stat\_progress\_vacuum. ```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 | 49703 datid | 16390 datname | mvcc_vacuum relid | 16391 phase | vacuuming indexes heap_blks_total | 8850 heap_blks_scanned | 1544 heap_blks_vacuumed | 771 index_vacuum_count | 1 max_dead_tuples | 174761 num_dead_tuples | 174472 ``` ```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.038 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.70 s, system: 0.06 s, elapsed: 1.22 s VACUUM ``` ### 2. Очистка большого количества строк ## Page 24: Page 24 {#source-page-024} Текущий размер файла данных: ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ```text pg_size_pretty ---------------- 69 MB (1 row) ``` - Удалим 90% случайных строк. Случайность важна, чтобы в каждой странице остались какие-нибудь неудаленные строки — в противном случае очистка имеет шанс уменьшить размер файла. ```sql => DELETE FROM t WHERE random() < 0.9; ``` ```sql DELETE 900465 ``` Объем после очистки: ```sql => VACUUM t; ``` ```sql VACUUM ``` ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ```text pg_size_pretty ---------------- 69 MB (1 row) ``` Объем не изменился. ### 3. Полная очистка большого количества строк Заново наполним таблицу. ```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 ``` Текущий размер файла данных: ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ```text pg_size_pretty ---------------- 35 MB (1 row) ``` Обратите внимание, что в прошлый раз размер был примерно в два раза больше. Вторая половина была занята версиями строк, которые создала команда UPDATE. Объем после удаления и полной очистки: ```sql => DELETE FROM t WHERE random() < 0.9; ``` ```sql DELETE 899673 ``` ```sql => VACUUM FULL t; ``` ```sql VACUUM ``` ```sql => SELECT pg_size_pretty(pg_table_size('t')); ``` ```text pg_size_pretty ---------------- 3552 kB (1 row) ``` Объем уменьшился на 90%.