Postgres Professional corporate blog
PostgreSQL
SQL
Comments 19
+1
Спасибо, Егор, за труд. Я сам автор и знаю, насколько сложно написать хорошую статью. Несколько раз заходил на хабр проверить — не появилась ли Ваша очередная (видимо, уже еженедельная) статья и дождался.

Немного вопросов:

№1
XACT — это аббревиатура. Как ее можно расшифровать, пусть даже и условно?

№2
Поэтому выясненный однажды статус транзакции записывается в биты xmin_committed и xmin_aborted версии строки. Если один из этих битов установлен, то состояние транзакции xmin считается известным и следующей транзакции уже не придется обращаться к XACT.


Что будет происходить при race conditions?
Несколько транзакций параллельно (в рамках разных соединений) пытаются посмотреть эти биты, не найдя их — идут в XACT. И далее одновременно пишут биты в заголовок. Одна транзакция установит биты, вторая — установит те же самые значения. И все.
Видимо, никаких проблем тут не будет кроме лишнего похода в XACT, что не является думаю критичным.
Поправьте, пожалуйста, если что-то не так понял.

№3
XACT — не таблица системного каталога; это файлы в каталоге PGDATA/pg_xact.
А работа с этими файлами ведется постранично, как и со всеми другими.

Используется ли буферный кеш для XACТ, как и для таблиц? Или работаем как с обычными файлами, средствами ОС (файловый кеш в RAM на уровне ОС). Если это так, то то интересно, почему так решили? Насколько я успел привыкнуть — в PostgreSQL стараются всю информацию представлять в едином, табличном виде (способе хранения и работы с данными)

№4
Например, для B-дерева строки, относящиеся к листовым страницам, содержат значение ключа индексирования и ссылку (ctid) на соответствующую строку таблицы. В общем случае индекс может быть устроен совсем другим образом.


А далее:

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


Поясните, пожалуйста, для полноты картины. Получается, что одному значению ctid соответствует несколько записей? Хотя в указанных примерах это вроде бы не так. По какому свойству строки индекс находит все возможные версии? Видимо, это приватный ключ — ID.
Или же — при создании новой версии строки происходит какое-то изменение индекса чтобы он знал о новой версии?

№5
И немного не в тему статьи — индекс перестраивается в рамках транзакции но сразу после COMMIT? То есть в рамках снимка индекс, условно говоря, может быть «устаревшим»?

№6
Когда создается новая версия строки при UPDATE — создается полная копия строки в базе данных? Соответственно, если есть строка с большим значением внутри TEXT, то UPDATE создаст его копию даже если изменилось другое поле — например, у поста счетчик number_of_upvotes увеличился на единицу, но сам текст поста никто не изменял.
Поэтому частые UPDATE могут существенно влиять на размер таблицы?

Спасибо
+1
Владимир, спасибо!
Пожалуй, отвечу на каждый из вопросов отдельно.

1.
XACT — не аббревиатура, а сокращение, образованное по непонятным мне правилам. Дело в том, что в исходных кодах PostgreSQL транзакция часто обозначается буквой «икс». Отсюда всякие xmin, xmax, xid и пр. И «xact» обозначает ровно то же (в «act» угадывается часть слова trans-act-ion). Отсюда multixact — мультитранзакция (про этого зверя я планирую написать, когда дойду до блокировок). В общем, расшифровка такая: нечто, имеющее отношение к транзакциям.
+1
2.
У меня такое же понимание. Исключительная блокировка на строку при проверке видимости не накладывается, так что по идее возможна ситуация, когда обе транзакции обновят те же биты.
«Лишние» походы в XACT случаются (и не только по этой причине), и в этом действительно ничего страшного нет.
+1
3.
Да, кеш там конечно же есть, но отдельный. XACT — довольно специфическая штука, ее под таблицу сложно замаскировать.
+1
4.
Пока мы для простоты считаем, что каждой табличной версии строки в индексе (B-дереве) соответствует своя строка, которая ссылается на эту версию. (И если появляется новая версия строки, она тоже добавляется в индекс.)
Когда мы обращаемся к индексу (дай мне строку, где id = 1), мы получаем ссылки на все версии строки с id = 1. И дальше проверяем по таблице, какую из этих версий нам действительно можно увидеть.

Но это упрощенная картина. Есть карта видимости, есть HOT-цепочки… Это мы все рассмотрим, но позже, чтобы не погрязнуть в деталях.
0
Получается тогда, что VACUUM очищает не только более «невидимые никому» версии строк, но вычищает и ненужные записи в индексе. Но это я видимо забегаю вперед, насколько помню будет статья о VACUUM. Очень хотелось бы почитать об этой особенности там :)
0
Теперь немного понятнее стало, почему массовый апдейт даже небольшого числового поля (INTEGER) привел однажды к тому, что индекс «сломался» — эффективность его работы существенно (на порядок) просела и пришлось делать VACUUM FULL (не то написал) перестроение индекса с блокировкой. Кейс к сожалению (или к счастью) не мой. Но что делать и как обновлять — не понятно пока
0

Индекс действительно может распухнуть, но чтобы "на порядок" — это что-то странное. Возможно, в том случае были ещё какие-то обстоятельства.
В общем, про это тоже будет.

0

Вообще, всегда при массовых изменениях в таблице (будь то UPDATE, INSERT или DELETE) рекомендую делать:


  1. VACUUM ANALYZE для актуализации статистики, иначе у планировщика будут устаревшие данные и он может выбирать странные и не эффективные (для новых данных) планы запросов.
  2. Пересоздание всех индексов без блокировки: CREATE INDEX CONCURRENTLY "new" + DROP INDEX "old" + ALTER INDEX "new" RENAME TO "old".

Причем обе процедуры можно смело делать "на бою" без простоя.


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

0

Всегда и никогда — нехорошие слова. Лучше, когда действия осознанные.

0
Пересоздание всех индексов без блокировки: CREATE INDEX CONCURRENTLY «new» + DROP INDEX «old» + ALTER INDEX «new» RENAME TO «old».


* После подобной процедуры необходимо также запускать проверку целостности индекса, т.к. индекс в CONCURRENTLY режиме может получиться «битый»
* Не все индексы, думаю, нужно пересоздавать, а только те, которые затронуты были массовыми изменениями

Причем обе процедуры можно смело делать «на бою» без простоя.


Эти процедуры могут расходовать ресурсы и таким образом влиять на производительность БД на продакшене. Думаю, выполнять их нужно с осторожностью.

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


Если есть возможность — опишите свой кейс более подробно, будет интересно.
+1
5.
Индекс перестраивается сразу же по ходу транзакции, не дожидаясь фиксации. Какой смысл откладывать?
0
Если предполагать, что транзакции откатываются намного реже, чем происходит коммит, то откладывать действительно нет смысла, не задумался об этом. Наличие большого количества ROLLBACK — это индикатор проблем в архитектуре.
0

Не-не. В PostgreSQL обрыв транзакции не связан с откатом изменений. Все, что транзакция наделала, так и остаётся лежать (никому не видимое) до тех пор, пока vacuum не вычистит. В том числе и в индексах.

+1
6.
Это мы возвращаемся к TOAST-у?
Если длинное значение лежит в toast-таблице, а изменилось только поле в основной таблице, то только в основной таблице и появится новая версия строки. И она будет ссылаться на ту же самую строку в toast-таблице. Иными словами, длинное значение в этом случае не дублируется.
0
На ум приходит ошибочный кейс «чистки БД»:
* Делаем массовый апдейт description поля типа TEXT, например вычищают XSS injections (или решили ругательные слова заменить точками постфактум, как бывает в стартапах)
* Сразу получаем минимум 2х к размеру TOAST-таблицы.
* И это место видимо без FULL VACUUM оказывается очень тяжело высвободить для ОС (и для других таблиц). Даже если обновлять пачками.

Пример несколько надуманный, так ошибиться сложно, если знать требования по безопасности. Просто для примера.

Очень хотелось бы прочитать про эту проблему в будущем, потому что я часто слышал кейсы, когда они что-то обновляли массово а потом ломался индекс или место не могли высвободить. И пришлось просто все блокировать и запускать VACUUM FULL. может еще какие то решения есть
Only those users with full accounts are able to leave comments. , please.