Как избавиться от блокировок при обслуживание партиций внешней таблицы в БД PostgreSQL?

Question

[Александр]

Приветствую!

Ситуация следующая: в базе есть таблица, которая подключена как внешняя, и на удалённом сервере настроено партицирование. Всё работает отлично, но заканчивается место на диске, и встал вопрос об оптимизации партиций. Напрямую VACUUM, REINDEX и DROP выполнить не удаётся потому, что постоянно пишется большое кол-во данных, и, как я понял, основная база, при подключении внешней таблицы, требует эксклюзивную блокировку, которая распространяется и на партиции. Пробовал удалять наследование, но так же безрезультатно.

Вопрос: как обойти блокировку не отключая внешнюю таблицу?

Answer 1

[Melkij]

vacuum full и reindex требуют тяжёлую блокировку и держать блокировку будут долго. Поэтому обычно их избегают.
drop разумеется тоже требует эксклюзивную блокировку, но на короткое время. Важно понимать скользкую ситуацию - если вы пытаетесь взять эксклюзивную блокировку, но не можете её получить из-за других выполняемых транзакций - вы будете ждать окончания этих мешающих транзакций, но уже будете блокировать работу последующих транзакций.

Если вы считаете, что много места пропало впустую - то это можно посчитать (поставив contrib расширение pgstattuple) вот этими нашими запросами: для таблиц и для индексов
Если да, проблема с местом в этом - то вам помогут pgcompacttable или pg_repack

Если данные по большей части архивные - имеет смысл заменить btree индексы на brin. Они медленнее, но очень сильно компактнее по диску.

А отцеплять партиции - посмотреть в pg_stat_activity что вообще происходит, нет ли аномально длинных транзакций или idle in transaction (т.е. затупов на приложении). Затем с statement_timeout и lock_timeout в 1 секунду (или меньше даже) пробовать отцепить пока не получится.

Comments

[Александр]

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

[Melkij]

которые, кстати, проходят напрямую, без транзакций

Если вы не делаете begin & commit - это не значит, что у вас нет транзакций.

fdw с точки зрения удалённого сервера ничем не отличается от других клиентов, рядовой libpq клиент. Две программы для возвращения места ФС я указал, на очень многих базах проверены.

[Александр]

fdw с точки зрения удалённого сервера ничем не отличается от других клиентов, рядовой libpq клиент.

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

[Melkij]

Отличия нет. База к которой fdw подключается вообще ничего не знает о fdw, для неё это рядовой libpq клиент. Любой запрос к любой таблице хватает как минимум ACCESS SHARE на эту таблицу. И даже ACCESS SHARE конфликтует с ACCESS EXCLUSIVE, который требуют drop, vacuum full, reindex и многие виды alter table.

Смотреть, как уже сказал, в pg_stat_activity на предмет долгих транзакций. Ну или, если хотите, можете поискать в pg_locks, pid'ы удерживающих блокировки транзакций там тоже будут.
fdw никаких локов не держит от одного своего существования на другом сервере. Транзакции через него проходящие на этот кластер - разумеется держат.