И еще раз про VACUUM

Про распухание таблиц, мертвые строки и команду VACUUM я уже несколько раз рассказывал, включая перевод:

• SeqScan из не вакуумированной таблицы
• Count() с NULL и «мертвые» строки
• Перевод: Percona: Иллюстрация распухания таблиц в PostgreSQL
• Иллюстрация распухания в PostgreSQL. Дополнение

Но можно еще разок об этом поговорить.

В этих статьях я говорю о том, что команда VACUUM очищает таблицу от мертвых строк, но не возвращает место операционной системе. Но бывают исключения. В документации об этом сказано так:

Однако освобожденное место не возвращается операционной системе (в большинстве случаев); оно просто остается доступным для размещения данных этой же таблицы.

Поискав целенаправленно — можно найти подробности:

Однако это дисковое пространство не возвращается операционной системе, кроме особого случая, когда полностью освобождаются одна или несколько страниц в конце таблицы и можно легко получить исключительную блокировку таблицы.

Самый простой способ показать это — очистить пустую таблицу 🙂

Проведем тот же эксперимент, что делали раньше (чтобы сначала убедиться что VACUUM не уменьшит размер не пустой таблицы). Для этого создаем таблицу:

CREATE TABLE t_vacuum (id INTEGER);
CREATE TABLE

Вставляем в нее миллион строк:

INSERT INTO t_vacuum SELECT * FROM generate_series(1,1000000);
INSERT 0 1000000

И смотрим размер таблицы:

\dt+ t_vacuum;
                                   List of relations
 Schema |   Name   | Type  | Owner | Persistence | Access method | Size  | Description 
--------+----------+-------+-------+-------------+---------------+-------+-------------
 public | t_vacuum | table | pavel | permanent   | heap          | 35 MB | 
(1 row)

Далее обновляем все строки таблицы:

UPDATE t_vacuum SET id = id+2;
UPDATE 1000000

И опять смотрим размер:

\dt+ t_vacuum;
                                   List of relations
 Schema |   Name   | Type  | Owner | Persistence | Access method | Size  | Description 
--------+----------+-------+-------+-------------+---------------+-------+-------------
 public | t_vacuum | table | pavel | permanent   | heap          | 69 MB | 
(1 row)

Размер увеличился почти в два раза. Выполняем очистку этой таблицы:

VACUUM VERBOSE t_vacuum;
psql:myI.txt:6: INFO:  vacuuming "public.t_vacuum"
psql:myI.txt:6: INFO:  "t_vacuum": removed 1000000 row versions in 4425 pages
psql:myI.txt:6: INFO:  "t_vacuum": found 1000000 removable, 1000000 nonremovable row versions in 8850 out of 8850 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 907
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.13 s, system: 0.00 s, elapsed: 0.13 s.
VACUUM

Найден миллион строк которые можно удалить и миллион — которые нельзя. Смотрим размер:

\dt+ t_vacuum;
                                   List of relations
 Schema |   Name   | Type  | Owner | Persistence | Access method | Size  | Description 
--------+----------+-------+-------+-------------+---------------+-------+-------------
 public | t_vacuum | table | pavel | permanent   | heap          | 69 MB | 
(1 row)

Размер не уменьшился. А теперь удалим все строки из таблицы:

DELETE FROM t_vacuum;;
DELETE 1000000

И опять посмотрим размер:

\dt+ t_vacuum;
                                   List of relations
 Schema |   Name   | Type  | Owner | Persistence | Access method | Size  | Description 
--------+----------+-------+-------+-------------+---------------+-------+-------------
 public | t_vacuum | table | pavel | permanent   | heap          | 69 MB | 
(1 row)

Размер не изменился, хотя реальных строк в таблице нет. Проверим количество:

SELECT COUNT(*) from t_vacuum;
 count 
-------
     0
(1 row)

А теперь снова выполним очистку таблицы:

VACUUM VERBOSE t_vacuum;
psql:myI.txt:10: INFO:  vacuuming "public.t_vacuum"
psql:myI.txt:10: INFO:  "t_vacuum": removed 1000000 row versions in 4426 pages
psql:myI.txt:10: INFO:  "t_vacuum": found 1000000 removable, 0 nonremovable row versions in 4426 out of 8850 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 908
There were 176 unused item identifiers.
Skipped 0 pages due to buffer pins, 4424 frozen pages.
0 pages are entirely empty.
CPU: user: 0.06 s, system: 0.00 s, elapsed: 0.07 s.
psql:myI.txt:10: INFO:  "t_vacuum": truncated 8850 to 0 pages
DETAIL:  CPU: user: 0.01 s, system: 0.03 s, elapsed: 0.18 s
VACUUM

И опять посмотрим размер таблицы:

\dt+ t_vacuum;
                                   List of relations
 Schema |   Name   | Type  | Owner | Persistence | Access method | Size  | Description 
--------+----------+-------+-------+-------------+---------------+-------+-------------
 public | t_vacuum | table | pavel | permanent   | heap          | 16 kB | 
(1 row)

А вот теперь размер изменился — да как! Всё освобожденное место в таблице было возвращено операционной системе.

А теперь на подумать — что будет с местом в таблице, если произойдет такой же случай, только реальная миллионно-первая строка останется в последней странице таблице, а миллион строк перед ней будут удалены?