Немного про 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$ <<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) клиента, что намного меньше времени заполнения такой таблицы.