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

Сегодня займемся двумя довольно тесно связанными вопросами: внутристраничной очисткой и HOT-обновлениями. Оба механизма можно отнести к разряду оптимизаций; они важны, но в пользовательской документации практически не освещены.

Внутристраничная очистка при обычных обновлениях

При обращении к странице — как при обновлении, так и при чтении — может происходить быстрая внутристраничная очистка, если PostgreSQL поймет, что место на странице заканчивается. Это происходит в двух случаях.

1. Ранее выполненное на этой странице обновление (UPDATE) не обнаружило достаточно места, чтобы разместить новую версию строки на той же странице. Такая ситуация запоминается в заголовке страницы, и в следующий раз страница очищается.
2. Страница заполнена больше, чем на fillfactor. При этом очистка происходит сразу, не откладывая на следующий раз.

Fillfactor — параметр хранения, который можно определить для таблицы (и для индекса). PostgreSQL вставляет новую строку (INSERT) на страницу, только если эта страница заполнена менее, чем на fillfactor процентов. Остальное место резервируется для новых версий строк, которые получаются в результате обновлений (UPDATE). Значение по умолчанию для таблиц равно 100, то есть место не резервируется (а значение для индексов — 90).

Внутристраничная очистка убирает версии строк, не видимые ни в одном снимке (находящиеся за «горизонтом событий» базы данных, об этом мы говорили в прошлый раз), но работает строго в пределах одной табличной страницы. Указатели на вычищенные версии строк не освобождаются, так как на них могут вести ссылки из индексов, а индекс — это уже другая страница. Внутристраничная очистка никогда не выходит за одну табличную страницу, зато выполняется очень быстро.

Из этих же соображений не обновляется карта свободного пространства; также это приберегает освобожденное место для обновлений, а не для вставок. Не обновляется и карта видимости.

Тот факт, что страница может очищаться при чтении, означает, что запрос на чтение (SELECT) может вызвать изменение страниц. Это еще один такой случай, в дополнение к рассмотренному ранее отложенному изменению битов-подсказок.

Посмотрим, как это работает, на примере. Создадим таблицу и индексы по обоим столбцам.

=> CREATE TABLE hot(id integer, s char(2000)) WITH (fillfactor = 75);
=> CREATE INDEX hot_id ON hot(id);
=> CREATE INDEX hot_s ON hot(s);

Если в столбце s хранить только латинские буквы, то каждая версия строки будет занимать 2004 байта плюс 24 байта заголовка. Параметр хранения fillfactor установим в 75% — места будет хватать на три строки.

Для удобства пересоздадим уже знакомую функцию, дополнив вывод двумя полями:

=> CREATE FUNCTION heap_page(relname text, pageno integer)
RETURNS TABLE(ctid tid, state text, xmin text, xmax text, hhu text, hot text, t_ctid tid)
AS $$
SELECT (pageno,lp)::text::tid 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(relname,pageno))
ORDER BY lp;
$$ LANGUAGE SQL;

И еще создадим функцию, чтобы заглядывать внутрь индексной страницы:

=> CREATE FUNCTION index_page(relname text, pageno integer)
RETURNS TABLE(itemoffset smallint, ctid tid)
AS $$
SELECT itemoffset,
       ctid
FROM bt_page_items(relname,pageno);
$$ LANGUAGE SQL;

Проверим, как работает внутристраничная очистка. Для этого вставим одну строку и несколько раз изменим ее:

=> INSERT INTO hot VALUES (1, 'A');
=> UPDATE hot SET s = 'B';
=> UPDATE hot SET s = 'C';
=> UPDATE hot SET s = 'D';

В странице сейчас четыре версии строки:

=> SELECT * FROM heap_page('hot',0);

 ctid  | state  |   xmin   |   xmax   | hhu | hot | t_ctid 
-------+--------+----------+----------+-----+-----+--------
 (0,1) | normal | 3979 (c) | 3980 (c) |     |     | (0,2)
 (0,2) | normal | 3980 (c) | 3981 (c) |     |     | (0,3)
 (0,3) | normal | 3981 (c) | 3982     |     |     | (0,4)
 (0,4) | normal | 3982     | 0 (a)    |     |     | (0,4)
(4 rows)

Как и ожидалось, мы только что превысили порог fillfactor. На это указывает разница между значениями pagesize и upper: она превышает порог в 75% от размера страницы, что составляет 6144 байтов.

=> SELECT lower, upper, pagesize FROM page_header(get_raw_page('hot',0));

 lower | upper | pagesize 
-------+-------+----------
    40 |    64 |     8192
(1 row)

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

=> UPDATE hot SET s = 'E';
=> SELECT * FROM heap_page('hot',0);

 ctid  | state  |   xmin   | xmax  | hhu | hot | t_ctid 
-------+--------+----------+-------+-----+-----+--------
 (0,1) | dead   |          |       |     |     | 
 (0,2) | dead   |          |       |     |     | 
 (0,3) | dead   |          |       |     |     | 
 (0,4) | normal | 3982 (c) | 3983  |     |     | (0,5)
 (0,5) | normal | 3983     | 0 (a) |     |     | (0,5)
(5 rows)

Все неактуальные версии строк (0,1), (0,2) и (0,3) очищены; после этого на освободившееся место добавлена новая версия строки (0,5).

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

Указатели на удаленные версии строк освободить нельзя, поскольку на них существует ссылки из индексной страницы. Посмотрим в первую страницу индекса hot_s (потому что нулевая занята метаинформацией):

=> SELECT * FROM index_page('hot_s',1);

 itemoffset | ctid  
------------+-------
          1 | (0,1)
          2 | (0,2)
          3 | (0,3)
          4 | (0,4)
          5 | (0,5)
(5 rows)

Ту же картину мы увидим и в другом индексе:

=> SELECT * FROM index_page('hot_id',1);

 itemoffset | ctid  
------------+-------
          1 | (0,5)
          2 | (0,4)
          3 | (0,3)
          4 | (0,2)
          5 | (0,1)
(5 rows)

Можно обратить внимание, что указатели на табличные строки идут здесь “задом наперед”, но это не имеет значения, поскольку во всех версиях строк находится одно и то же значение id = 1. А вот в предыдущем индексе указатели упорядочены по значениям s, и это существенно.

При индексном доступе PostgreSQL может получить (0,1), (0,2) или (0,3) в качестве идентификатора версии строки. Тогда он попробует получить соответствующую строку из табличной станицы, но благодаря статусу dead указателя обнаружит, что такая версия уже не существует и проигнорирует ее. (На самом деле, в первый раз обнаружив отсутствие версии табличной строки, PostgreSQL изменит и статус указателя в индексной странице, чтобы повторно не обращаться к табличной странице.)

Принципиально то, что внутристраничная очистка работает только в пределах одной табличной страницы и не очищает индексные страницы.

HOT-обновления

Чем плохо держать в индексе ссылки на все версии строки?

Во-первых, при любом изменении строки приходится обновлять все индексы, созданные для таблицы: раз появилась новая версия, необходимо иметь на нее ссылки. И делать это надо в любом случае, даже если изменяются поля, не входящие в индекс. Очевидно, что это не слишком эффективно.

Во-вторых, в индексах накапливаются ссылки на исторические версии строки, которые потом приходится очищать вместе с самими версиями (как это делается, мы рассмотрим чуть позже).

Более того, есть особенность реализации B-дерева в PostgreSQL. Если на индексной странице недостаточно места для вставки новой строки, страница делится на две и все данные перераспределяются между ними. Это называется расщеплением (split) страницы. Однако при удалении строк две индексные страницы уже не «склеиваются» в одну. Из-за этого размер индекса может не уменьшиться даже при удалении существенной части данных.

Естественно, чем больше индексов создано на таблице, тем с большими сложностями приходится сталкиваться.

Однако если меняется значение столбца, который не входит ни в один индекс, то нет никакого смысла создавать дополнительную запись в B-дереве, содержащую то же самое значение ключа. Именно так работает оптимизация, называемая HOT-обновлением — Heap-Only Tuple Update.

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

• строки, которые изменены и входят в цепочку, маркируются битом Heap Hot Updated;
• строки, на которые нет ссылок из индекса, маркируются битом Heap Only Tuple (то есть — «только табличная версия строки»);
• поддерживается обычная связь версий строк через поле ctid.

Если при сканировании индекса PostgreSQL попадает в табличную страницу и обнаруживает версию, помеченную как Heap Hot Updated, он понимает, что не надо останавливаться и проходит дальше по всей цепочке обновлений. Разумеется, для всех полученных таким образом версий строк проверяется видимость, прежде чем они будут возвращены клиенту.

Чтобы посмотреть на работу HOT-обновления, удалим один индекс и очистим таблицу.

=> DROP INDEX hot_s;
=> TRUNCATE TABLE hot;

Повторим вставку и обновление строки.

=> INSERT INTO hot VALUES (1, 'A');
=> UPDATE hot SET s = 'B';

Вот что мы видим в табличной странице:

=> SELECT * FROM heap_page('hot',0);

 ctid  | state  |   xmin   | xmax  | hhu | hot | t_ctid 
-------+--------+----------+-------+-----+-----+--------
 (0,1) | normal | 3986 (c) | 3987  | t   |     | (0,2)
 (0,2) | normal | 3987     | 0 (a) |     | t   | (0,2)
(2 rows)

В странице — цепочка изменений:

• флаг Heap Hot Updated показывает, что надо идти по цепочке ctid,
• флаг Heap Only Tuple показывает, что на данную версию строки нет ссылок из индексов.

При дальнейших изменениях цепочка будет расти (в пределах страницы):

=> UPDATE hot SET s = 'C';
=> UPDATE hot SET s = 'D';
=> SELECT * FROM heap_page('hot',0);

 ctid  | state  |   xmin   |   xmax   | hhu | hot | t_ctid 
-------+--------+----------+----------+-----+-----+--------
 (0,1) | normal | 3986 (c) | 3987 (c) | t   |     | (0,2)
 (0,2) | normal | 3987 (c) | 3988 (c) | t   | t   | (0,3)
 (0,3) | normal | 3988 (c) | 3989     | t   | t   | (0,4)
 (0,4) | normal | 3989     | 0 (a)    |     | t   | (0,4)
(4 rows)

При этом в индексе одна-единственная ссылка на “голову” цепочки:

=> SELECT * FROM index_page('hot_id',1);

 itemoffset | ctid  
------------+-------
          1 | (0,1)
(1 row)

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

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

Внутристраничная очистка при HOT-обновлениях

Частный, но важный случай внутристраничной очистки представляет собой очистка при HOT-обновлениях.

Как и в прошлый раз, мы уже превысили порог fillfactor, так что следующее обновление должно привести к внутристраничной очистке. Но в этот раз в странице — цепочка обновлений. «Голова» этой HOT-цепочки всегда должна оставаться на своем месте, поскольку на нее ссылается индекс, а остальные указатели могут быть освобождены: известно, что на них нет ссылок извне.

Чтобы не трогать «голову», применяется двойная адресация: указатель, на который ссылается индекс — в данном случае (0,1), — получает статус «redirect», перенаправляющий на нужную версию строки.

=> UPDATE hot SET s = 'E';
=> SELECT * FROM heap_page('hot',0);

 ctid  |     state     |   xmin   | xmax  | hhu | hot | t_ctid 
-------+---------------+----------+-------+-----+-----+--------
 (0,1) | redirect to 4 |          |       |     |     | 
 (0,2) | normal        | 3990     | 0 (a) |     | t   | (0,2)
 (0,3) | unused        |          |       |     |     | 
 (0,4) | normal        | 3989 (c) | 3990  | t   | t   | (0,2)
(4 rows)

Обратите внимание, что:

• версии (0,1), (0,2) и (0,3) были очищены,
• «головной» указатель (0,1) остался, но получил статут redirect,
• новая версия строки записана на место (0,2), поскольку на эту версию гарантированно не было ссылок из индексов и указатель был освобожден (unused).

Выполним обновление еще несколько раз:

=> UPDATE hot SET s = 'F';
=> UPDATE hot SET s = 'G';
=> SELECT * FROM heap_page('hot',0);

 ctid  |     state     |   xmin   |   xmax   | hhu | hot | t_ctid 
-------+---------------+----------+----------+-----+-----+--------
 (0,1) | redirect to 4 |          |          |     |     | 
 (0,2) | normal        | 3990 (c) | 3991 (c) | t   | t   | (0,3)
 (0,3) | normal        | 3991 (c) | 3992     | t   | t   | (0,5)
 (0,4) | normal        | 3989 (c) | 3990 (c) | t   | t   | (0,2)
 (0,5) | normal        | 3992     | 0 (a)    |     | t   | (0,5)
(5 rows)

Следующее обновление снова вызывает внутристраничную очистку:

=> UPDATE hot SET s = 'H';
=> SELECT * FROM heap_page('hot',0);

 ctid  |     state     |   xmin   | xmax  | hhu | hot | t_ctid 
-------+---------------+----------+-------+-----+-----+--------
 (0,1) | redirect to 5 |          |       |     |     | 
 (0,2) | normal        | 3993     | 0 (a) |     | t   | (0,2)
 (0,3) | unused        |          |       |     |     | 
 (0,4) | unused        |          |       |     |     | 
 (0,5) | normal        | 3992 (c) | 3993  | t   | t   | (0,2)
(5 rows)

Снова часть версий очищена, а указатель на «голову» соответствующим образом сдвинут.

Вывод: при частых обновлениях столбцов, не входящих в индексы, может иметь смысл уменьшить параметр fillfactor, чтобы зарезервировать некоторое место на странице для обновлений. Конечно, надо учитывать, что чем ниже fillfactor, тем больше остается незанятого места на странице и, соответственно, физический размер таблицы увеличивается.

Разрыв HOT-цепочки

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

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

|  => BEGIN ISOLATION LEVEL REPEATABLE READ;
|  => SELECT count(*) FROM hot;

|   count 
|  -------
|       1
|  (1 row)

Снимок не даст очистить версии строк на странице. Теперь выполняем обновление в первом сеансе:

=> UPDATE hot SET s = 'I';
=> UPDATE hot SET s = 'J';
=> UPDATE hot SET s = 'K';
=> SELECT * FROM heap_page('hot',0);

 ctid  |     state     |   xmin   |   xmax   | hhu | hot | t_ctid 
-------+---------------+----------+----------+-----+-----+--------
 (0,1) | redirect to 2 |          |          |     |     | 
 (0,2) | normal        | 3993 (c) | 3994 (c) | t   | t   | (0,3)
 (0,3) | normal        | 3994 (c) | 3995 (c) | t   | t   | (0,4)
 (0,4) | normal        | 3995 (c) | 3996     | t   | t   | (0,5)
 (0,5) | normal        | 3996     | 0 (a)    |     | t   | (0,5)
(5 rows)

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

=> UPDATE hot SET s = 'L';

|  => COMMIT; -- снимок больше не нужен

=> SELECT * FROM heap_page('hot',0);

 ctid  |     state     |   xmin   |   xmax   | hhu | hot | t_ctid 
-------+---------------+----------+----------+-----+-----+--------
 (0,1) | redirect to 2 |          |          |     |     | 
 (0,2) | normal        | 3993 (c) | 3994 (c) | t   | t   | (0,3)
 (0,3) | normal        | 3994 (c) | 3995 (c) | t   | t   | (0,4)
 (0,4) | normal        | 3995 (c) | 3996 (c) | t   | t   | (0,5)
 (0,5) | normal        | 3996 (c) | 3997     |     | t   | (1,1)
(5 rows)

В версии (0,5) видим ссылку на (1,1), ведущую на страницу 1.

=> SELECT * FROM heap_page('hot',1);

 ctid  | state  | xmin | xmax  | hhu | hot | t_ctid 
-------+--------+------+-------+-----+-----+--------
 (1,1) | normal | 3997 | 0 (a) |     |     | (1,1)
(1 row)

Теперь в индексе — две строки, каждая из которых указывает на начало своей HOT-цепочки:

=> SELECT * FROM index_page('hot_id',1);

 itemoffset | ctid  
------------+-------
          1 | (1,1)
          2 | (0,1)
(2 rows)

К сожалению, сведения о внутристраничной очистке и HOT-обновлениях практически отсутствуют в документации, и правду надо искать в исходном коде. Рекомендую начать с README.HOT.

Продолжение следует.