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