Postgres Professional corporate blog
PostgreSQL
SQL
Comments 16
+2
Возможно, имеет смысл реализовать VACUUM DEFRAGMENT, который итеративно (по несколько страниц) перенесет записи из конца файла в начало (например, реализовав UPDATE в результате которого сами данные не меняются, а только их физическое расположение), а затем выполнит тоже, что и VACUUM для этой таблицы. Далее прервется для выполнения возможных запросов типа CREATE INDEX, ALTER TABLE и перейдет на следующую итерацию. Таким образом, реализуется перенос данных из конца файла в начало, аналог VACUUM FULL, но без длительной блокировки таблицы. Нагружаться дисковая система будет чуть больше, но такую дефрагментацию можно будет в любой момент прервать и проделанная часть работы сохранится.
+1
Спасибо за ответ. Попробовал я проверить, как работает этот инструмент, но так и не понял. Там механизм работы описан таким образом:
Если кратко то если в таблице есть свободное место то при update записи новая версия пойдет с это свободное место. При этом безусловное предпочтение отдается свободному месту в начале таблицы при его наличии. В итоге если обновлять таблицу (fake updates вида поле=поле) начиная с последней страницы в какой то момент все записи с последней страницы перейдут в свободное место в начале таблицы.


Я проделал следующее:
DROP TABLE IF EXISTS __test;
CREATE TABLE __test as select id,random() as f1,random() as
    f2,random()::text as f3,now() as mtime,(random()>1)::boolean as flag
    FROM generate_series(1,10000000) as t(id);
select ctid,* from __test order by ctid desc limit 10 ;
-- тут, насколько я понимаю, в ctid первая цифра - номер страницы, вторая - номер строки
delete from __test where id < 10000000;
vacuum __test;
select ctid,* from __test order by ctid desc limit 10 ;
-- осталась одна строка в самом конце, как и ожидалось
update __test set mtime=clock_timestamp() where id=10000000;
select ctid,* from __test order by ctid desc limit 10 ;
-- строка переместилась в начало страницы, но не в первую страницу таблицы.

Возможно, я что-то понял не так, но у меня этот механизм не работает. Впрочем, логика понятна. Если перемещать запись в начало таблицы, то придется обновлять две страницы — откуда строку удалили и куда записали. При записи в ту же страницу, обновляется единственная страница.

select version();
                                                version                                                 
--------------------------------------------------------------------------------------------------------
 PostgreSQL 11.3 (Debian 11.3-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-7) 8.3.0, 64-bit
0

Это работает HOT, пытаясь удержать обновления в пределах той же страницы.
Думаю, что инструмент использует не SQL UPDATE, а что-то более низкоуровневое. Я не смотрел, но пожалуй надо будет разобраться.

0
Я понял, как эта штука работает. При UPDATE, если есть свободное место в той же странице, новая версия строки записывается в нее, но когда место заканчивается, вот тогда запись происходит в начало таблицы. Т.е. нужно делать UPDATE записей в последней странице, пока там место не закончится.
update foo set a = a where ctid >= '(3,0)';
UPDATE 117
update foo set a = a where ctid >= '(3,0)';
UPDATE 117
update foo set a = a where ctid >= '(3,0)';
UPDATE 21
update foo set a = a where ctid >= '(3,0)';
UPDATE 0

После чего можно сделать VACUUM. Этот инструмент вполне решает нужную мне задачу. Было бы отлично, если бы эта функция входила в штатную поставку Postgres в какой-нибудь Additional Supplied Modules, например, (если внутри SQL функции или процедуры разрешена команда VACUUM).
0
Кстати, что скажешь насчет такого механизма ускорения копирования хвоста таблицы в начало. Задается threshold, насколько заполненной должна быть таблица (70%, например, или используются параметры для запуска автовакуума), VACUUM пробегает по таблице и помечает все страницы хвоста с данными, превышающие этот порог (место, необходимое для данных + 30%), как подлежащие постепенному освобождению (отключает запись в эти страницы при обновлении данных) и при каждом UPDATE строки этих страниц перемещаются в начало, независимо от наличия места в текущей странице. Таким образом, все строки страниц в хвосте таблицы после однократного обновления переместятся в начало независимо от наличия места в текущей странице.
0

Ну, придумать-то много чего можно. Самая проблема — убедить сообщество в том, что это действительно так необходимо и что у решения нет неприятных побочных эффектов.
Сейчас все очарованы магией подключаемых движков хранения. Вот у zheap нет проблем с разрастанием (правда, наверняка есть много других, но это другое дело).

+1
Егор, большое спасибо за очередную отличную статью! Вопросы :)

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


Но при этом физически они выглядят одинаково. Пусть на мастере совершилась очистка а на реплике идет отчетная транзакция, которая завершится через пару часов условно.

WAL sender отправляет сегмент, где страницы уже очищены. Реплика не будет его накатывать, пока транзакция не завершится? И все остальные сегменты тоже выстроятся в очередь.

Если так то отчетная реплика может существенно отставать от мастера
+1

Владимир, спасибо.
Да, идея именно такая. Реплика будет отставать. Но для отчетов, которые выполняются часами, пара часов отставания обычно не страшна.
Ведь даже если запустить такой отчет на мастере, за пару часов его работы многое может измениться, но эти изменения в отчет не попадут, потому что — согласованность.

0
Будет ли мастер хранить сегмент WAL в этом случае до тех пор, пока реплика его себе не накатит?

Получается затятная ситуация:
* Очень длинные отчеты нагружают мастер. Только ли тем, что нужно хранить сегменты WAL? Не страдает ли от этого VACUUM, table bloat? Так ли критично то, что сегменты хранятся полдня? Но задержки такие идут скажем на постоянной основе.
* Очень длинные отчеты конкурируют между собой. Если один аналитик запустил отчет на полдня, то другие должны учитывать, что не могут получить актуальные данные в этот период.

Тогда получается, что нужно делать даже 2 реплики — одну для очень долгих отчетов, другую — для отчетов но покороче.
0
Будет ли мастер хранить сегмент WAL в этом случае до тех пор, пока реплика его себе не накатит?

Нет, хранить будет реплика.


Очень длинные отчеты нагружают мастер. Только ли тем, что нужно хранить сегменты WAL? Не страдает ли от этого VACUUM, table bloat? Так ли критично то, что сегменты хранятся полдня? Но задержки такие идут скажем на постоянной основе.

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


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

Это мы уже про реплику говорим, правильно?
Сами по себе отчёты (=запросы) конкурировать не должны, они выполняются одновременно. При этом реплика может откладывать применение журнальных записей (обычно связанных с очисткой), которые несовместимы с запросами. Ну да, будет отставание. Его можно мониторить.


Тогда получается, что нужно делать даже 2 реплики — одну для очень долгих отчетов, другую — для отчетов но покороче.

Это, мне кажется, перебор, слишком сложно. Короткие отчёты и на мастере никому сильно не помешают.


Я вообще про репликацию думаю отдельную серию написать. Там очень много разных вариантов, можно по-разному настраивать в зависимости от задачи.

0
Я немного расплывчато сформулировал кейс. Он такой:
* Пусть решили сделать «отчетную» реплику по просьбе аналитиков (и чтобы они нам не грузили мастер).
* Создали отдельного читающего юзера для всех аналитиков, поставили уровень изоляции Repeatable Read для согласованности, раздали логин-пароль (один на всех)
* Аналитики начали экспериментировать, строить свои отчеты.
* И вот аналитик Вася запускает отчет на таблице orders, длительность которого 3 часа.
* Реплика начала отставать все больше и больше с каждым часом, потому что запрос Васи затронул очень много данных в таблице orders.
* Вася не подозревал что получится такой долгий запрос и терпеливо решил подождать.
* Аналитик Петя решил посмотреть данные за последний час, он предполагает что данные актуальны и не знает о запросе Васи.
* Аналитик Петя строит аггрегаты, получает результат, не подозревая, что он пользуется устаревшими данными.
* И не дай бог это некий отчет, который будет использоваться при финансовых расчетах.

Если я все правильно понял, такая ситуация вполне имеет место быть.

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

Очень неудобно. Как вариант можно сделать «отчетную реплику для коротких запросов» и «отчетную тормозящую реплику» для длинных. И даже может «финансовую реплику» куда ходить будут строго по расписанию.

Как Вам такая идея? Может есть идея получше? Кейс очень актуальный.

Сорри, что немного не в тему статьи
0
Реплика начала отставать все больше и больше с каждым часом, потому что запрос Васи затронул очень много данных в таблице orders.

Тут логическая ошибка. Реплика будет отставать не из-за того, что кто-то затронул какие-то данные. У запросов на реплике есть обычные снимки, вот они и используются, чтобы понять, конфликтует журнальная запись или нет. Так же, как на мастере.


И не дай бог это некий отчет, который будет использоваться при финансовых расчетах.

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


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


Да, ещё можно настроить max_standby_streaming_delay так, чтобы конфликтующие записи применялись, но с задержкой. Тогда реплика будет отставать не больше, чем на это значение, а у более долгих запросов будет шанс доработать, если они не «наступят» на отсутствующие данные.

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


О команде CLUSTER очень часто упоминают и постоянно критикуют ее за то, что ее эффект «выдыхается» (в силу MVCC) и за то, что она блокирует все.

Когда все-таки ее целесообразно использовать?
+1

Скажу честно — не знаю, есть ли реальные примеры успешного применения. Мне не попадались.

Only those users with full accounts are able to leave comments. , please.