Меня зовут Якупов Азат. Я архитектор данных, и вот продолжение саги о табличных типах в PostgreSQL. В этой части мы поговорим о кластеризованных и внешних таблицах. Давайте рассмотрим примеры их создания, области применения, плюсы и минусы их использования.

Кластерные таблицы в PostgreSQL

В PostgreSQL кластеризованная таблица — это таблица, в которой физический порядок строк был изменен, чтобы соответствовать порядку строк в индексе. Это отличается от обычного поведения таблицы, где физический порядок строк может не совпадать с порядком строк, определенным каким-либо конкретным индексом.

Мало кто любит хаос, всем нравится порядок. В рамках реляционных баз данных понятие хаоса тесно переплетается с хранением информации, поскольку на протяжении всего своего жизненного цикла таблица постоянно изменяется.

В процессе работы с СУБД на уровне диска содержимое таблицы постоянно меняется. Например, вы обновили данные, и ваша обновленная строка попадает на другую страницу таблицы (здесь следует упомянуть FILLFACTOR) с мертвым кортежем в текущей позиции. Затем процесс автоочистки удалял мертвый кортеж, а освободившийся слот заполнялся вновь полученной строкой.

Простой тест, который вы можете сделать сами. Превратите следующие команды в обычную вновь созданную таблицу:

INSERT INTO test(id,name) VALUES(1, ‘Peter’);
INSERT INTO test(id,name) VALUES(2, ‘Ivan’);
INSERT INTO test(id,name) VALUES(3, ‘Sergey’);

После выполнения SQL-запроса (обратите внимание, ORDER BY нет):

SELECT *
   FROM test;

Вы увидите ожидаемую картину:

Но, выполнив обновление строки

UPDATE test
  SET name = ‘Ruslan’
WHERE id = 2;

а затем, выполнив тот же SQL, вы получите:

Порядок строк изменился! Энтропия выросла.

Теперь представьте, что вы ищете данные в таблице, например, под номером 4. Как вы можете это сделать в хаотической зеленой топологии, которую я нарисовал слева внизу? Просто просматриваешь запись за записью: случайно тыкаешь в какую-то цифру и сравниваешь ее с нужной цифрой 4. По сути, тебе придется перебрать все записи, потому что цифры 4 может быть больше одной. Другими словами, необходимо последовательное сканирование.

Но когда у вас есть порядок, как в таблице справа, вы будете четко знать, что число 4 лежит между 3 и 5. В этом весь смысл организации порядка и сгруппированных таблиц: они помогают создать упорядоченную структуру из хаоса. Если случайным образом выбрать случайную позицию в упорядоченной синей таблице в поисках числа 4, то возможны три исхода:

  • Число равно желаемому.
  • Число меньше желаемого.
  • Число больше желаемого.

Это дает большое преимущество в скорости выполнения поиска. Если число больше 4, вы продолжите поиск в таблице. Если меньше, то упадешь. Или вы можете получить диапазон и найти число 4 внутри него. Это гораздо быстрее, чем поиск по всем данным, как это было в неорганизованной зеленой топологии, а именно в логарифмические разы быстрее.

Рассмотрим пример создания кластеризованной таблицы:

CREATE TABLE test.cluster_table
(id       INTEGER,
 name VARCHAR) WITH (FILLFACTOR = 90);

CREATE INDEX id_idx ON test.cluster_table (id);

CLUSTER [VERBOSE] test.cluster_table USING id_idx;

Здесь я создал таблицу с именем cluster_table и установил для нее значение FILLFACTOR на 90% — это процент заполнения. Это никак не влияет на нашу кластеризованную таблицу, это просто пример того, как можно задать свойство при создании таблицы такого типа. Далее я создаю индекс BTree для таблицы (CREATE INDEX) в поле id и вызываю команду CLUSTER. Команда CLUSTER выполняет кластеризацию таблицы с использованием ранее созданного индекса.

Здесь важно знать, что пока кластеризация не будет завершена, все текущие транзакции в таблице будут заблокированы. Блокировка трафика происходит из-за того, что Postgres пытается перестроить таблицу в нужном вам порядке на основе индекса. И после создания этого заказа Postgres должен сохранить его в другой файл.

По сути, это операция переноса данных на уровне диска из одного файла в другой, но только в указанном порядке. Данные должны размещаться на основе индекса, в нашем случае по полю id. Я графически показал это на рисунке ниже, обратившись к метаданным до и после кластеризации таблицы.

Изначально таблица была помещена в файл с номером 45969. После команды CLUSTER имя файла изменилось. Данные были перемещены из одного файла в другой. Поэтому происходит блокировка, и, соответственно, входящий трафик не может использовать эту таблицу, пока она не станет доступной.

Вы также можете создать индекс для последующей кластеризации, содержащий много столбцов (многостолбцовый индекс), или указать порядок убывания для определенных столбцов (DESC/ASC).

При желании вы можете использовать команду CLUSTER VERBOSE, которая вернет сведения о том, что сделал PostgreSQL, а именно, сколько страниц было, какие страницы были перемещены и так далее.

Тестовые случаи и порядок данных

Проведем небольшой тест:

CREATE TABLE test.cluster_table
(id       INTEGER,
 name VARCHAR) WITH (FILLFACTOR = 90);

CREATE INDEX id_idx ON test.cluster_table (id);

INSERT INTO test.cluster_table
  SELECT (random( )*100)::INTEGER, 'test'
     FROM generate_series(1,100) AS g(i);

SELECT id
   FROM test.cluster_table;

Создадим таблицу, индекс по полю id и сгенерируем 100 произвольных строк с помощью команды generate_series. Результатом являются неупорядоченные данные:

Чтобы добиться порядка в выводе, нам нужно добавить ключевое слово ORDER BY. Но тут важно помнить, что операция ORDER BY тоже требует ресурсов и за нее надо платить. Каждая наносекунда на счету при высокой нагрузке трафика, а потом идет сортировка.

В данном случае сделаем кластеризацию таблицы командой CLUSTER VERBOSE, используя заранее созданный мною индекс:

CLUSTER VERBOSE test.cluster_table USING id_idx;

SELECT id
 FROM test.cluster_table;

Вуаля, данные сортируются без сортировки:

Но здесь есть ловушка. Обновим все строки — но на самом деле достаточно изменить значение одной строки.

UPDATE test.cluster_table
   SET id = id * (random( )::INTEGER);

SELECT id
 FROM test.cluster_table;

В этом случае в нашу кластеризованную таблицу вернется хаос:

Чтобы восстановить порядок, вам нужно снова запустить команду CLUSTER. Вам даже не нужно снова указывать индекс, потому что он сохраняется в метаданных PostgreSQL. И база данных в следующий раз поймет, на чем вы делаете кластеризацию.

CLUSTER VERBOSE test.cluster_table;

SELECT id
   FROM test.cluster_table;

Вы сможете снова наблюдать за порядком только после команды CLUSTER. Это ахиллесова пята кластеризованных таблиц: любое изменение ключа кластеризации может немедленно внести беспорядок в данные.

Когда подходят кластеризованные таблицы

Кластерные таблицы подходят, если ваши данные — это справочные таблицы (ну или SCD — Slowly Changing Dimension), например, адресная система. Этот тип таблиц удобен, если вы загружаете новые данные довольно редко, например, раз в месяц.

Если таблица меняется очень часто и подвергается INSERT, UPDATE и DELETE операциям, ее придется постоянно кластеризовать, а это не очень удобно и вообще критично. Цель кластеризации — избежать ненужных ORDER BY постоянных запросов к таблице по кластеризованному полю или полям.

Метаданные кластеризованной таблицы

Из метаданных кластеризованной таблицы можно понять, что она кластеризована:

SELECT  c.oid AS “OID”,
        c.relname AS “Relation name”
  FROM pg_class c INNER JOIN pg_index i 
                  ON i.indrelid = c.oid
WHERE c.relkind = ‘r’ AND 
      c.relhasindex AND 
      i.indisclustered;

«Истинное» значение в поле relhasindex указывает на наличие индекса для поддержки кластеризации. Когда мы перестроим кластер в следующей команде CLUSTER, PostgreSQL будет использовать указанный индекс из метаданных.

Внешние таблицы в PostgreSQL

Внешние таблицы в PostgreSQL — это таблицы, которые хранятся вне базы данных. Эти таблицы могут находиться, например, на другом сервере базы данных или в файле. Внешние таблицы полезны с точки зрения быстрого получения данных из другого источника, если у вас есть возможность к нему присоединиться.

Кроме того, если вы повозитесь с этим, вы можете предоставить так называемый жизненный цикл данных, чтобы предоставить метрику политики хранения. Здесь вам может помочь следующий набор инструментов:

  • ПРОСМОТР (виртуальная таблица).
  • Набор обычных таблиц, разделенных логикой хранения данных (дизайн POOD) с актуальными данными.
  • Внешние таблицы, которые сосредоточены на файлах, хранящих данные вне базы данных на более дешевых дисках (здесь вы найдете старые данные, которые превысили показатель Retention Policy).

Есть много сторонних таблиц и типов подключения, например:

  • CSV-файл.
  • Связь со многими другими СУБД.
  • Подключение к некоторым базам данных NoSQL.

Давайте рассмотрим пример внешней таблицы на основе CSV-файла. В этом нам поможет расширение file_fdw на основе fdw — внешней обёртки данных:

CREATE EXTENSION file_fdw;

CREATE SERVER csv_log FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE test.csv (
  id   INTEGER,
  name VARCHAR
) SERVER csv_log 
  OPTIONS (filename '/var/lib/postgresql/file.csv', 
                   delimiter ‘;', format 'csv');

Создаю чужую таблицу и описываю атрибуты, указывая сервер для fdw, который я создал заранее с вариантами работы с файлом.

Если я сделаю SQL-запрос к сторонней таблице, я увижу данные, представленные в файле. Поскольку внешняя таблица зарегистрирована (имеется в виду запись в метаданных PostgreSQL), у меня есть гипотеза: данные хранятся не во внешнем файле, а в файле данных PostgreSQL?

SELECT  oid AS “OID”,
        pg_relation_filepath(oid) AS “File path”,
        pg_relation_size(oid) AS “Relation Size”  
 FROM pg_class
WHERE relname = ‘csv’;

Результат выполнения:

Итак, внешняя таблица как объект прописана в метаданных (имеется OID-идентификатор объекта), но нет соответствующего файла данных, то есть данные представлены только во внешнем источнике.

Запросы к внешним таблицам

Как работают запросы к внешним таблицам? Возьмем в качестве примера файл CSV.

Пока данные загружаются, происходит довольно большая задержка, поэтому мы храним старые данные где-то на старых дисках. Чтобы получить данные, нам нужно открыть внешний файловый дескриптор, затем скопировать данные в память или во временный файл и вернуть нам данные. Если мы повторим тот же запрос чуть позже, никакого ускорения не будет: процесс останется прежним.

Существует великое множество библиотек сторонних таблиц для различных нужд. Например, postgres_fdw. С его помощью мы можем подключиться к PostgreSQL из PostgreSQL. Это очень похоже на ссылку базы данных:

CREATE EXTENSION postgres_fdw;

DROP FOREIGN TABLE test.csv;

CREATE SERVER pg_log FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.56.10', port '5432', dbname ‘course_db');

CREATE USER MAPPING FOR test SERVER pg_log 
OPTIONS (user 'test', password 'test');

CREATE FOREIGN TABLE test.csv (
  id   INTEGER,
  name VARCHAR
) SERVER pg_log 
  OPTIONS (schema_name 'test', table_name ‘user');

Для работы с внешними источниками доступно огромное количество библиотек. Например:

  • Oracle, MySQL, SQLite, MS SQL Server, Sybase.
  • Кассандра, MongoBD, HBase, Redis, Neo4j.
  • Твиттер, Телеграм
  • JSON, XLM, геофайлы, LDAP.

Метаданные внешней таблицы

Как мы выяснили, внешняя таблица как объект фиксируется в метаданных:

SELECT  oid AS "OID",
        relname AS “Relation name",
        CASE
         WHEN relpersistence = 'p' THEN 'Permanent'
         WHEN relpersistence = 't' THEN 'Temporary'
         ELSE 'Unlogged'
       END AS “Type”,
       relkind AS “Subtype”
 FROM pg_class
WHERE relname = ‘csv’;

Это постоянная таблица (что удивительно), но она имеет указатель «f», который является подтипом отношения. И указывает на то, что наша таблица чужая, то есть внешняя.

В следующей серии

Это все на сегодня. В следующем материале разберем:

  • Разделенные таблицы.
  • Унаследованные таблицы.