Описание проблемы
В некоторых случаях в DWH приходится периодическим заданием очищать таблицу и заполнять ее новыми актуальными данными. Например, раз в сутки. Если в таблице десятки или сотни тысяч строк, то это не проблема. А вот если миллиард - то уже точно проблема. Потому что каким бы способом ее не заполнять, но в течении достаточно длительного времени данные из этой таблицы не будут доступны пользователям. А если система должна быть доступна 24/7, то такие процессы начинают заметно ухудшать SLA.
Варианты решения
Один из вариантов решения - это свалить данную проблему на клиента. Создаем две таблицы одинаковой структуры. Периодическим заданием заполняем эти таблицы по очереди. Сегодня одну, завтра другую. В третьей таблице указываем, какая из этих таблиц актуальна на данный момент. Клиент же определяет нужную таблицу предварительным запросом к этой третьей таблице. Минус решения - динамический SQL на каждом клиенте и более сложная логика.
Второй вариант - через переименование таблиц. И именно он выглядит предпочтительней.
Описание решения
Нашу таблицу очищает и заполняет какая-то конкретная хранимая процедура. Назовем ее условно SomeScheme.ThisTablePeriodicFill_sp. Тогда у нас возникает логический ресурс to_regproc('SomeScheme.ThisTablePeriodicFill_sp')::oid::integer, который мы и будем использовать для логических блокировок. Перед доступом к нашей таблице каждый клиент должен выполнить:
PERFORM pg_advisory_lock_shared(
to_regproc('SomeScheme.ThisTablePeriodicFill_sp')::oid::integer, 0);А по окончании работы с нашей таблицей клиент должен освободить ресурс:
PERFORM pg_advisory_unlock_shared(
to_regproc('SomeScheme.ThisTablePeriodicFill_sp')::oid::integer, 0);Теперь процедура SomeScheme.ThisTablePeriodicFill_sp может создать новую таблицу SomeScheme.ThisTable_Shadow совпадающую по структуре с таблицей SomeScheme.ThisTable, но без индексов. После чего, никому не мешая, заполнить эту таблицу и, после ее заполнения, создать для нее необходимые индексы.
Однако просто переименовать эту таблицу мало. Ведь индексы в PostgreSQL являются relation и их имена глобальны. Для решения этой проблемы имена индексов делаем всегда начинающиеся с имени таблицы (например, ThisTable_IndexName_Idx) и создаем такую хранимую процедуру:
CREATE OR REPLACE PROCEDURE SomeSchema.normalize_index_names_sp (
schema_name varchar,
table_name varchar,
from_index_prefix varchar ) AS $proc$
<<proc>>
DECLARE
sql_str varchar;
BEGIN
SELECT string_agg('ALTER INDEX '
||schema_name||'.'||indexname||' RENAME TO '||LOWER(table_name)
||RIGHT(indexname,LENGTH(indexname)-LENGTH(from_index_prefix)),'; ')
INTO sql_str
FROM pg_catalog.pg_indexes
WHERE schemaname=LOWER(schema_name) AND tablename=LOWER(table_name)
AND indexname LIKE LOWER(from_index_prefix)||'%';
EXECUTE sql_str;
END; $proc$ LANGUAGE plpgsql;Используя данную процедуру мы получаем возможность быстро переименовать таблицы и их индексы:
PERFORM pg_advisory_lock(
to_regproc('SomeScheme.ThisTablePeriodicFill_sp')::oid::integer, 0);
ALTER TABLE IF EXISTS SomeScheme.ThisTable RENAME TO ThisTable_Bak;
CALL SomeScheme.normalize_index_names_sp (
'SomeScheme','ThisTable_Bak','ThisTable');
ALTER TABLE IF EXISTS SomeScheme.ThisTable_Shadow RENAME TO ThisTable;
CALL SomeScheme.normalize_index_names_sp (
'SomeScheme','ThisTable','ThisTable_Shadow');
DROP TABLE IF EXISTS SomeScheme.ThisTable_Bak;
PERFORM pg_advisory_unlock(
to_regproc('SomeScheme.ThisTablePeriodicFill_sp')::oid::integer, 0);В данном коде опущен блок EXCEPTION, в котором при возникновении ошибок производится журналирование и попытка восстановить имя ThisTable.
Процесс переименования таблиц и их индексов в PostgreSQL выполняется очень быстро. Таким образом мы улучшаем SLA, сократив недоступность таблицы до времени ожидания в очереди, в худшем случае, на время логической блокировки (advisory_lock_shared) клиента, что намного меньше времени заполнения такой таблицы.
Комментарии (28)
Batalmv
05.10.2023 07:54Один из вариантов решения - это свалить данную проблему на клиента.
Не зная деталей вашей задачи, хочется понять. А почему для этого варианта не подходит view? Он ничего не стоит, создается за мгновение. Какие-то нюансы переименования во время открытых курсоров?
-------------------------------------------------
Ну и также, допустим есть таблица с некими данными. Я так понял, они нечто вроде аналитической выборки, т.е. инкрементальное добавление тут не подходит, надо именно пересчитать и создать новые данные (вроде объема продаж за последние 7 дней). Можете добавить колонку "актуальность". Таблиц может быть много, на идею это не влияет
Клиенты работают только с данными, где актуальность == "true" и у них все хорошо. Вы рассчитываете и льете новые даные с актуальность == "false" и никому не мешаете. Ну стала табличка в два раза больше, переживем. Потом в рамках одной транзакции меняете актуальность у двух наборов. Кроме люто длинной транзакции - проблем нет.
Но если данных много, а их у вас много, то проблема "длинной" транзакции обходится тем, что вы храните актуальность не как двухпозиционный переключатель, а как например дату. И тогда вы пишете новые данные с новой датой, а клиентам предоставляете данные через view, в коде которой спрятано обращение только к данных с актуальной датой. И тогда все что вам надо, это после заливки новых данных поменять актуальную дату. И нет длинной транзакции
Меня просто тригернула фраза "свалить проблему на клиента", так как использование view как интерфейса для прямого доступа к объектам БД является хорошей практикой, и позволяет незаметно от глаз клиента делать "под капотом" любой ахалай-махалай
ptr128 Автор
05.10.2023 07:54+1А почему для этого варианта не подходит view?
Вы уже уходите в частности, явно выходящие за рамки статьи, интересуясь причинами возникновения таких таблиц.
Например, в одном из случаев, это детализация временных серий используемых для обучения моделей прогнозирования, позволяющая оценить достоверность прогноза. Только их подготовка (трансформация, валидации, фильтрации, сглаживания) занимает больше часа на 32-х ядрах и 256 ГБ оперативки. Какая тут на фиг VIEW? Результат - около терабайта. И есть целый ряд ограничений, не позволяющих получить выгоду при инкрементальном обновлении таблицы.в рамках одной транзакции меняете актуальность у двух наборов
Можно, но по производительности обновление такой таблицы будет очень болезненно. Например, в таблице выше сейчас 2,627,101,700 записей. Сколько времени будет выполняться UPDATE на 5 миллиардах записей? Намного быстрее тогда просто сделать TRUNCATE, удалить индексы, залить данные из промежуточной таблицы и построить индексы заново.
это после заливки новых данных поменять актуальную дату
Не намного легче. Сколько времени будет выполняться INSERT и DELETE 2.5 миллиардов записей с обновлением всех индексов? И это не считая десятков гигабайт довеска на лишнее поле в самой таблице и в её индексах.
Вы вообще сами пытались заливать миллиарды записей в индексированную таблицу? В неиндексированную нежурналируемую (UNLOGGED) таблицу я почти без накладных расходов могу лить данные из десятков потоков. В индексированную - уже все намного более грустно.
Batalmv
05.10.2023 07:54View == интерфейс/API. Ви можете делать как вам оптимальнее в реальных таблицах, хоть с переименованием. Но для клиента намного проще спрятать потроха решения за ним. Оно само по себе ничего не стоит, и ничего не добавляет к накладным расходам. Это ж по сути, просто сохраненный запрос. И если нет нюансов с его пересозданием во время активніх запросов и опцией переименования - это явно опция, которая спрячет все детали от read only клиентов
Про как оптимальнее - ну я ж не вижу реальных таблиц. Тут вам карты в руки. Но здесь вопрос того, что вам критичнее, downtime или затраченное процессорное время. Если у вас выделенный сервер, за который вы платите всегда и он от этой нагрузки не уйдет в "философские размышления о вечном" - тогда лишняя процессорная нагрузка не стоит ничего. И ее можно брать по максимум
Но так как вы вынесли SLA в заголовок, разумно рассмотреть trade-off, при котором улучшение SLA будет оплачено менее ценным для вас ресурсом.
P.S. В любом случае єто ваша задача, и бенефиты от нашей дискуссии в практическую плоскость трансформировать можете только вы. Я - максимум узнать о вашем опыте
ptr128 Автор
05.10.2023 07:54View == интерфейс/API. Ви можете делать как вам оптимальнее в реальных таблицах, хоть с переименованием. Но для клиента намного проще спрятать потроха решения за ним.
Если Вы ведете речь о пересоздании представления на другую таблицу, то это намного более трудоемкое решение, чем переименование таблиц, так как потребует или дублирования кода, или перехода на динамический SQL во всех процедурах, формирующих эти таблицы.
Batalmv
05.10.2023 07:54+1Ну, да. Но это уже вопрос как лучше переименовать :) Насколько я помню, view созданные как create ... as select * from <одна таблица>, не имеет ограничений по DML-операциям.
Соответственно, если у вас сейчас есть таблица DATA_1, то вам надо:
код формирования оставить как есть
назвать view как DATA_1, и код формирования тоже работает с view
в начале работы пересоздать view на набор на сегодня
клиентам выдать новый набор
Я понимаю, изменение интерфейса - это не очень приятно. Но я бы раз пошел на это изменение, получив клиентов, которые работают с view (это офигенно ценно само по себе), и после этого у вас развязаны руки
По сути, архитектурно вы можете прийти к ситуации, когда есть модель данных клиента, которые постороены на view. И тогда вы отвязываете модули наполнения от клиентов.
Я бы как архитектор, пошел на это решение чисто из-за его перспектив разделения на слои. Но вам виднее ваши накладные расходы/бюджет
P.S. Я просто обычно наличие слоя view для тех, кто лазит в базу закладываю сразу. Потом, через время, это становится мега удобно.
ptr128 Автор
05.10.2023 07:54Еще раз, если пересоздавать VIEW с таблицы SomeTable1 на SomeTable2 и обратно, то все процедуры, участвующие в формировании этих таблиц должны быть переведены на динамический SQL. Ведь они через раз должны будут заполнять то SomeTable1, то SomeTable2. Поэтому переименовывать таблицы - намного меньшие трудозатраты. А результат тот же.
Я просто обычно наличие слоя view для тех, кто лазит в базу закладываю сразу.
Я наоборот этого избегаю. Потому что не раз уже разгребал у клиентов нагромождения VIEW в запросах друг на друге с такой глубиной, что планировщик запросов уже не мог разобраться, как оптимально выполнить запрос, уходя в глухие циклы.
Чтобы не быть голословным, предлагаю Вам самому сравнить планы запросов. Замените только имя таблицы car, на имя таблицы имеющейся у Вас в схеме public. Получаем список полей таблицы по VIEW information_schema.columns:
SELECT column_name FROM information_schema.columns WHERE table_name = 'car' AND table_schema = 'public';и на прямую по таблицe pg_catalog.pg_attribute
SELECT attname FROM pg_catalog.pg_attribute WHERE attrelid = 'public.car'::regclass AND attnum > 0 AND NOT attisdropped;Результат один и тот же. А вот оверхед в первом запросе - грандиозный.
Batalmv
05.10.2023 07:54Еще раз, если пересоздавать VIEW с таблицы SomeTable1 на SomeTable2 и обратно, то все процедуры, участвующие в формировании этих таблиц должны быть переведены на динамический SQL. Ведь они через раз должны будут заполнять то SomeTable1, то SomeTable2. Поэтому переименовывать таблицы - намного меньшие трудозатраты. А результат тот же.
Не, идея в том, что скрипты всегда пишут во view. В начале натравил их на нужный набор и вперед. Поэтому код тоже остается один и тот же. Возможно в Postgre есть какие-то ограничения для тривиальных view, но я по памяти не помню такого
------------------------------
Про оверхед не понял. Сорян, под рукой сейчас экземпляра нет, куда-то в клауд ломиться ща не могу. Но предположу, что там view тянет инфу с кучи таблиц, и ясное дело, оптимизатор честно выполняет запрос из view, на который сверху накручен еще один критерий от тебя.
Туда ж не кладется бизнес логика, ну разве что довольно таки тривиальная и понятная. И точно без повторного использования, так как слоев должно быть именно два, а не макароны. Я даже стараюсь join в таком слое использовать по минимуму, чтобы клиент работал с логической моделью, а не с уже "типа" подготовленной единственной таблицей.
----------------------------
По код - есть еще опыт, когда стейджинговые процедуры сразу пишутся как параметрические и просто создаются по набор таблиц. Условно в коде вместо имени таблицы ее псевдоним, который при ее создании заменяется на то, который нужен здесь и сейчас.
В любом случае, спасибо за ваш опыт. Прикольно было почитать
ptr128 Автор
05.10.2023 07:54скрипты всегда пишут во view
Во-первых, Вы что опять предлагаете заливать миллиарды записей в индексированную таблицу? Я же писал выше:
Вы вообще сами пытались заливать миллиарды записей в индексированную таблицу? В неиндексированную нежурналируемую (UNLOGGED) таблицу я почти без накладных расходов могу лить данные из десятков потоков. В индексированную - уже все намного более грустно.
Во-вторых, Вы точно о PostgreSQL пишете? В несколько потоков в PostgreSQL можно писать исключительно через CREATE TABLE ... AS или SELECT ... INTO, что по любому исключает запись в VIEW. https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html
Вы уж простите, но в один поток мне формировать вышеописанную таблицу не час, как на 32-х ядрах, а сутки, что уже ни в какие ворота не лезет.
По код - есть еще опыт, когда стейджинговые процедуры сразу пишутся как параметрические и просто создаются по набор таблиц.
Я сам люблю динамический SQL, но при этом отдаю себе отчет, что он требует больше времени для разработки и тяжелей в поддержке.
Что касается подмены имен схем для разных контуров, то я для этого использую при деплое C препроцессор (cpp). Благодаря чему могу иметь в одной базе множество контуров разработки в своих схемах по имени ветки в GIT. Ну и заодно макроопределения порой очень повышают читабельность кода и упрощают его написание.
ptr128 Автор
05.10.2023 07:54что вам критичнее, downtime или затраченное процессорное время
Все критично. Но вариант с переименованием таблиц ничуть не уступает в downtime варианту пересоздания VIEW. Все так же упираемся в продолжительность транзакций клиентов. А вот остальные издержки - явно выше.
miksoft
05.10.2023 07:54Еще возможен вариант с партициями с постоянно возрастающим значением. Пишем в новую партицию одной транзакцией (или перекидываем из временной таблицы, если СУБД так умеет). А клиент при работе с таблице определяет последнюю партицию и читает только ее. Устаревшие партиции грохать.
ptr128 Автор
05.10.2023 07:54Это намного более трудоемко, чем просто переименовать таблицу. А преимуществ не даёт никаких.
miksoft
05.10.2023 07:54Не так уж и трудоемко, несколько строчек кода.
Преимущество - можно не ждать читателей, пока они завершат текущую сессию чтения.
Но я не настаиваю, задачи бывают разные.
ptr128 Автор
05.10.2023 07:54Не так уж и трудоемко, несколько строчек кода.
Нет. Это у меня несколько строчек кода. А в Вашем случае - переход на динамический SQL в процедурах, формирующих таблицу.
можно не ждать читателей, пока они завершат текущую сессию чтения
Плохо читали документацию. Если DETACH PARTITION еще допускает CONCURRENTLY, то ATTACH требует монопольного доступа к секционируемой таблице.
А так как формируются вообще то не одна таблица, а целый ряд связанных друг с другом, то без логической блокировки тут все равно не обойтись.
miksoft
05.10.2023 07:54Кстати, а что скажет клиент, если ему посреди чтения миллиарда записей переименуют читаемую таблицу?
ptr128 Автор
05.10.2023 07:54Сам не установил или снял блокировку - сам виноват. В статье указана необходимость использования логической блокировки клиентом.
miksoft
05.10.2023 07:54Указана, но уже больно кратенько.
Например, что делать, если пришло время нового расчета, а стоит блокировка клиентом? Или как ловить окно расчета, если клиентов много и они практически непрерывно блокируют таблицу.
ptr128 Автор
05.10.2023 07:54Зачем что-то делать? PostgreSQL сам обслуживает очередь advisory_lock. Сколько бы ни было клиентов в момент запроса монопольной блокировки, их конечное количество.
Mapar
05.10.2023 07:54А собственно почему нельзя просто партиционировать таблицу, и удалять а затем подключать новые партиции рассчитаные заранее?
Мы же говорим про DWH, как же там без партиционирования?!
Опять же такой механизм позволяет гибко работать не только подменяя всю таблицу, но и ее часть за конкретный период например.
Для приложения ничего не меняется, все блокировки PostgreSQL сам сделает
ptr128 Автор
05.10.2023 07:54Если честно, уже устал на один и тот же вопрос отвечать.
Потому что тогда все процедуры формирующие такие таблицы потребуется переписывать на динамический SQL, что намного более трудоемко, чем те несколько строк переименования таблиц и индексов, которые приведены в статье.
А преимуществ такой подход не дает никаких, так как манипуляции с секционированными таблицами так же потребуют монопольных блокировок.
Для приложения ничего не меняется, все блокировки PostgreSQL сам сделает
А как Вы собрались выбирать именно из нужной секции? Это можно сделать или через представление, которое нужно пересоздавать, или через дополнительную таблицу, содержащую ключ актуальной секции. Что опять таки сложнее, чем вариант описанный в статье.
Mapar
05.10.2023 07:54Извините, но вы видимо не поняли:
Есть таблица A, она партиционирована, скажем партиции A1 и A2 (можно и одну партицию A1, что бы был ваш случай). Все приложения работают с A и про партиции ничего не знают, оптимизатор сам решает какие партиции брать для запроса.
Теперь готовим отдельно таблицу A3, индексируем ее, далее отключаем/удаляем скажем A1 и подключаем A3. Пользователи как работали с A, так и работают, никаких кастомных скриптов, штатные DETACH PARTITION и ATTACH PARTITION.
Зачем представление и дополнительная таблица мне не понятно.
Партиционирование и подключение/отключение партиций это стандартный паттерн для DWH, зачем изобретать велосипед с переименованием таблиц мне не понятно.
ptr128 Автор
05.10.2023 07:54Теперь готовим отдельно таблицу A3,
Сегодня мы готовим таблицу A3. А завтра тоже A3? Но она ведь уже есть! Значит завтра нам надо готовить уже таблицу A1. И как без динамического SQL Вы собрались это делать одними и теми же процедурами?
Пользователи как работали с A, так и работают
А как теперь выбирать данные из A3, а не A1? Вы знаете способ, как для A3 и A1 указать одинаковый FOR VALUES? Или Вы собрались секционировать по фиктивному ключу, приводя оптимизатор запросов в ступор из-за его отсутствия в запросах клиентов?
стандартный паттерн для DWH
Да, "золотой молоток" называется. Но он хорош, когда данные пополняются, а не полностью замещаются.
Mapar
05.10.2023 07:54Еще детальнее:
готовим a_tmp
старую партицию a1 отключаем/удаляем
новую подключаем с ТЕМ ЖЕ FOR VALUES
переименовываем a_tmp в a1, на пользователей не влияет, они работают с A
-
Ключ партиционирования можно взять любую колонку в таблице, например id, а лучше дату операции (тогда сможем выделить несколько партиций и ускорится), ступора у оптимизатора не будет, даже если нет ключа партиционирования в запросе, не придумывайте
Attach partition именно способ для быстрой замены данных, а не пополнения.
Теперь про ваш способ: вместо стандартных SQL команд, получилось "яйцо фаберже", с которым ничего сделать нельзя, партиционировать и менять часть данных - нельзя, вторичные ключи на него создать - нельзя.
Придут люди вам на замену и будут разбираться с экзотическим кодом.
ptr128 Автор
05.10.2023 07:54старую партицию a1 отключаем/удаляем
Перед этим уже потребуется advisory lock, чтобы клиенты не считали пустоту, вместо данных.
новую подключаем с ТЕМ ЖЕ FOR VALUES
Вот только если DETAСH уже позволяет CONCURRENTLY, то ATTACH - никак.
переименовываем a_tmp в a1, на пользователей не влияет, они работают с
И чем это отличается от переименовывания A_shadow в A, как в статье? Причем все те же проблемы, требующие переименования индексов сохраняются.
с которым ничего сделать нельзя, партиционировать и менять часть данных - нельзя, вторичные ключи на него создать - нельзя.
Почему же? Все тоже самое. Откуда Вы эти ограничения взяли?
Ключ партиционирования можно взять любую колонку в таблице, например id, а лучше дату операции
А этого вообще не понял. Раз речь идет о таблице, которая полностью переписывается ежедневно, какая там дата операции? В моем случае она одна на всю группу таблиц участвующих в формировании и трансформации временных серий и результатах прогнозирования.
uuger
Вот тут я бы порекомендовал дать определение, что вы, как автор, подразумеваете под SLA. В терминах ITIL под статью подходят одна-две метрики из перечня SLI (service level indicator): Availability (доступность) и какая-нибудь связанная с быстродействием системы
ptr128 Автор
Согласен. Имелась в виду именно доступность системы. Это уже профдеформация, когда клиент под SLA тоже подразумевает, в первую очередь, доступность системы.
uuger
С клиентами, иногда, тоже очень тяжело в плане непонимания, для чего ему нужны "лишние" девятки в availability. Часто (но не всегда), в корпоративном мире запланированный простой DWH с чётко ограниченными временными рамками воспринимается не так негативно, как неконтролируемая деградация быстродействия и увеличение время отклика на запросы пользователя / систем потребителей данных DHW. Организационные меры могут быть не менее эффективные, чем внутренняя оптимизация самого хранилища
ptr128 Автор
Ну в моем случае все понятно, так как системой пользуются круглосуточно и ее данные важны для оптимальной адресации вагонов порожняка. И подобные требования у меня далеко не первые на проектах.
Вопросы оптимизации, безусловно, очень важны. Но явно выходят за рамки этой статьи.