Немного про SLA для DWH на PostgreSQL
Описание проблемы
В некоторых случаях в 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$
<>
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) клиента, что намного меньше времени заполнения такой таблицы.
