FULL VACUUM PostgreSQL
Хотя операция VACUUM необходима для эффективного функционирования PostgreSQL, все же ее одной недостаточно. В частности, она не может уменьшить размер таблицы, даже если удалить большое количество “мертвых” кортежей.
На рисунке 76 показана следующая ситуация: предположим, что наша таблица состоит из трех страниц, каждая из которых содержит шесть кортежей. Для удаления кортежей выполняется команда DELETE, для удаления “мертвых кортежей” - команда VACUUM:
testdb=# DELETE FROM tbl WHERE id % 6 != 0; testdb=# VACUUM tbl;
Несмотря на то, что “мертвые” кортежи удалены, размер таблицы остался прежним, что ведет к пустой трате дискового пространства и негативно сказывается на производительности БД в целом.
В этом случае PostgreSQL запускает FULL VACUUM:
- [1] Создайте новый файл таблицы – рис. 77(1);
При выполнении FULL VACUUM PostgreSQL сначала блокирует доступ к таблице с помощью AccessExclusiveLock. Блокировка AccessExclusiveLock не позволяет другим пользователям получить доступ к таблице.
- [2] Скопируйте живые кортежи в новую таблицу - рис. 77(2)
PostgreSQL копирует в новую таблицу только живые кортежи из старого файла таблицы.
- [3] Удалите старый файл и обновите статистику, FSM и VM - рис. 77(3)
После копирования всех живых кортежей PostgreSQL удаляет старый файл, перестраивает все связанные с таблицей индексы, обновляет FSM и VM этой таблицы, а также обновляет связанные с ней статистику и системные каталоги.
Псевдокод FULL VACUUM:
(1) FOR each table
(2) Acquire AccessExclusiveLock lock for the table
(3) Create a new table file
(4) FOR each live tuple in the old table
(5) Copy the live tuple to the new table file
(6) Freeze the tuple IF necessary
END FOR
(7) Remove the old table file
(8) Rebuild all indexes
(9) Update FSM and VM
(10) Update statistics
Release AccessExclusiveLock lock
END FOR
(11) Remove unnecessary clog files and pages if possible
При использовании команды FULL VACUUM следует учесть следующие два момента:
- Никто не может получить доступ (на чтение/запись) к таблице, когда происходит Full VACUUM;
- При обработке большой таблицы необходимо проверять оставшуюся емкость диска.
Примечание: В каких случаях следует использовать FULL VACUUM?
Режим FULL не рекомендуется для обычного применения, но в некоторых случаях он бывает полезен. Например, когда были удалены или изменены почти все строки таблицы, может возникнуть желание физически сжать её, чтобы освободить место на диске и ускорить сканирование таблицы. Чаще всего VACUUM FULL сжимает таблицу более эффективно, чем обычный VACUUM.
Следующий запрос отражает свободное пространство для интересующей Вас таблицы:
testdb=# CREATE EXTENSION pg_freespacemap;
CREATE EXTENSION
testdb=# SELECT count(*) as "number of pages",
pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
FROM pg_freespace('accounts');
number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
1640 | 99 bytes | 1.21
(1 row)
Результат запроса свидетельствует о том, что свободного места немного.
Если удалить почти все кортежи и выполнить команду VACUUM, то почти все страницы окажутся пустыми , при этом количество страниц останется прежним.
testdb=# DELETE FROM accounts WHERE aid %10 != 0 OR aid < 100;
DELETE 90009
testdb=# VACUUM accounts;
VACUUM
testdb=# SELECT count(*) as "number of pages",
pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
FROM pg_freespace('accounts');
number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
1640 | 7124 bytes | 86.97
(1 row)
Следующий запрос проверяет соотношение свободного пространства для каждой страницы указанной таблицы:
testdb=# SELECT *, round(100 * avail/8192 ,2) as "freespace ratio"
FROM pg_freespace('accounts');
blkno | avail | freespace ratio
-------+-------+-----------------
0 | 7904 | 96.00
1 | 7520 | 91.00
2 | 7136 | 87.00
3 | 7136 | 87.00
4 | 7136 | 87.00
5 | 7136 | 87.00
....
Если в данном случае запустить FULL VACUUM, таблица будет сжата:
testdb=# VACUUM FULL accounts;
VACUUM
testdb=# SELECT count(*) as "number of blocks",
pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
FROM pg_freespace('accounts');
number of pages | Av. freespace size | Av. freespace ratio
-----------------+--------------------+---------------------
164 | 0 bytes | 0.00
(1 row)
