Мёртвые кортежи и раздувание таблиц

В прошлом уроке мы увидели, что UPDATE не переписывает строку, а кладёт рядом новую версию. Старая остаётся на странице. Сегодня посмотрим, что происходит с таблицей, когда таких старых версий становится много, и почему одна забытая транзакция может раздуть базу в разы.

Тетрадь с зачёркиваниями

Представь тетрадь учёта, где ничего не стирают ластиком. Исправил запись, значит зачеркнул старую строку и написал новую ниже. Через месяц тетрадь толстая, но половина строк в ней зачёркнута. Чтобы найти актуальную запись, приходится листать и зачёркнутые страницы.

Таблица PostgreSQL ведёт себя так же. Версия строки, которую уже не видит ни одна транзакция, называется мёртвым кортежем. Она занимает место на странице, пока её не уберёт VACUUM. Таблица, в которой мёртвых версий заметно больше, чем нужно, раздута: она занимает больше страниц, чем требуют живые строки.

Раздуваем таблицу

Заведём журнал просмотров книг на 10 000 строк и один раз увеличим счётчик у всех:

CREATE TABLE views_log (id integer PRIMARY KEY, book_id integer, hits integer);
INSERT INTO views_log SELECT g, g % 9 + 1, 0 FROM generate_series(1, 10000) AS g;

SELECT pg_size_pretty(pg_relation_size('views_log')) AS table_size,
       pg_size_pretty(pg_relation_size('views_log_pkey')) AS index_size;

 table_size | index_size
------------+------------
 440 kB     | 240 kB
(1 row)

UPDATE views_log SET hits = hits + 1;

 table_size | index_size
------------+------------
 872 kB     | 456 kB
(1 row)

Строк по-прежнему 10 000, а таблица выросла почти вдвое. Каждый UPDATE положил новую версию, и старые 10 000 версий никуда не делись. Индекс тоже вырос: новая версия лежит по новому адресу ctid, и индексу нужна запись, которая на этот адрес указывает.

Ещё один такой UPDATE отдельной транзакцией:

 table_size | index_size
------------+------------
 1304 kB    | 472 kB
(1 row)

Таблица уже втрое больше нужного. Если журнал просмотров обновляется весь день, а уборки нет, он растёт без конца. Индекс на этот раз вырос слабее: PostgreSQL умеет прибираться на странице прямо во время чтения и записи, если на ней лежат мёртвые версии, которых точно никто не видит. Но это частичная уборка, полную делает VACUUM.

Чем вредит раздувание

• Медленнее чтение. Последовательное чтение проходит все страницы, а их стало втрое больше. Мёртвые версии читаются и отбрасываются.
• Хуже кэш. Страницы с мусором занимают место в общей памяти сервера, вытесняя полезные.
• Больше диска и бэкапов. Раздутая таблица целиком попадает в физическую копию.
• Индексы тоже пухнут. Индекс с записями на мёртвые версии больше и глубже, а Index Scan ходит по лишним ссылкам.