Разделение таблиц PostgreSQL обеспечивает основу для высокопроизводительной обработки ввода данных и создания отчетов. Используйте секционирование для баз данных, требующих очень быстрого ввода больших объемов данных. Секционирование также обеспечивает более быстрые запросы к большим таблицам. Секционирование помогает поддерживать данные, не влияя на экземпляр базы данных, поскольку требует меньше ресурсов ввода-вывода.

Вы можете использовать pg_partman расширение PostgreSQL для автоматизации создания и обслуживания разделов таблиц.

Вместо того, чтобы вручную создавать каждый раздел, вы настраиваете pg_partman со следующими параметрами:

• Table to be partitioned - Таблица для разделения
• Partition type - Тип перегородки
• Partition key - Ключ раздела
• Partition granularity - Детализация раздела
• Partition precreation and management options - Параметры предварительного создания и управления разделами

После создания многораздельной таблицы PostgreSQL вы регистрируете ее pg_partman, вызывая create_parent функцию. При этом создаются необходимые разделы на основе параметров, которые вы передаете функции.

pg_partman расширение также обеспечивает run_maintenance_proc функцию, которую вы можете позвонить по расписанию автоматически управлять разделами. Чтобы гарантировать создание нужных разделов по мере необходимости, запланируйте периодический запуск этой функции (например, ежечасно). Вы также можете обеспечить автоматическое удаление разделов.

Если у вас есть несколько баз данных в одном экземпляре БД PostgreSQL, для которых вы хотите управлять разделами, включите pg_partmanрасширение отдельно для каждой базы данных. Чтобы включить pg_partmanрасширение для конкретной базы данных, создайте схему обслуживания раздела, а затем создайте pg_partmanрасширение, как показано ниже.

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

Чтобы создать pg_partman расширение, убедитесь, что у вас есть rds_superuser права.

Если вы получаете сообщение об ошибке, подобное приведенному ниже, предоставьте rds_superuser привилегии учетной записи или используйте свою учетную запись суперпользователя.

ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.

Чтобы предоставить rds_superuser привилегии, подключитесь к своей учетной записи суперпользователя и выполните следующую команду.

GRANT rds_superuser TO user-or-role;

В примерах, показывающих использование расширения pg_partman, мы используем следующий образец таблицы и раздела базы данных. Эта база данных использует многораздельную таблицу на основе отметки времени. Схема data_mart содержит таблицу events с именем столбца created_at. В events таблицу включены следующие настройки:

• ключи event_id и created_at, в которых должен быть столбец, используемый для управления разделом.
• Проверочное ограничение ck_valid_operation для принудительного применения значений для operation столбца таблицы.
• Два внешних ключа, где один (fk_orga_membership)указывает на внешнюю таблицу, organizationа другой (fk_parent_event_id) - это внешний ключ, на который имеется ссылка.
• Два индекса, где один (idx_org_id) предназначен для внешнего ключа, а другой (idx_event_type) - для типа события.

Следующие ниже операторы DDL создают эти объекты, которые автоматически включаются в каждый раздел.

CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
        org_name TEXT,
        CONSTRAINT pk_organization PRIMARY KEY (org_id)  
    );
 
CREATE TABLE data_mart.events(
        event_id        BIGSERIAL, 
        operation       CHAR(1), 
        VALUE           FLOAT(24), 
        parent_event_id BIGINT, 
        event_type      VARCHAR(25), 
        org_id          BIGSERIAL, 
        created_at      TIMESTAMP, 
        CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), 
        CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), 
        CONSTRAINT fk_orga_membership 
            FOREIGN KEY(org_id) 
            REFERENCES data_mart.organization (org_id),
        CONSTRAINT fk_parent_event_id 
            FOREIGN KEY(parent_event_id, created_at) 
            REFERENCES data_mart.events (event_id,created_at)
    ) PARTITION BY RANGE (created_at);
 
CREATE INDEX idx_org_id     ON  data_mart.events(org_id);
CREATE INDEX idx_event_type ON  data_mart.events(event_type);

После включения pg_partman расширения используйте create_parent функцию для настройки разделов внутри схемы обслуживания разделов. В следующем примере используется events таблица, созданная в разделе Включение расширения pg_partman Настройка обслуживания разделов с помощью функции run_main maintenance_proc. Вызовите create_parent функцию следующим образом.

SELECT partman.create_parent( p_parent_table => 'data_mart.events',
 p_control => 'created_at',
 p_type => 'native',
 p_interval=> 'daily',
 p_premake => 30);

Параметры следующие:

• p_parent_table- Родительская секционированная таблица. Эта таблица должна уже существовать и быть полностью определена, включая схему.
• p_control- Столбец, на котором будет основано разбиение. Тип данных должен быть целым числом или основанным на времени.
• p_type- Тип - либо, native либо partman. Обычно вы используете этот native тип для повышения производительности и гибкости. partman тип полагается на наследование.
• p_interval- Временной интервал или целочисленный диапазон для каждого раздела. Примеры значений включают daily, ежечасно и т. Д.
• p_premake - Количество разделов, которые необходимо создать заранее для поддержки новых вставок.

Пример:

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

CREATE schema test;
CREATE TABLE test.part_test (col1 serial, col2 text, col3 timestamptz NOT NULL DEFAULT now());

Затем просто запустите функцию create_parent () с соответствующими параметрами.

SELECT partman.create_parent('test.part_test', 'col3', 'time', 'daily');

или

SELECT partman.create_parent('test.part_test', 'col1', 'id', '100000');

Это превратит вашу таблицу в родительскую и создаст 4 будущих раздела, а также 4 прошлых раздела. Чтобы создать новые разделы для разбиения по времени, запланируйте периодический запуск функции run_main maintenance () или используйте настройки фонового рабочего в postgresql.conf (рекомендуется последнее). Последовательное разбиение на разделы не всегда требует run_main maintenance () (см. Файл документации ниже).

Этого должно быть достаточно, чтобы вы начали. Пожалуйста, посмотрите файл pg_partman.md в папке doc для получения дополнительной информации о поддерживаемых типах разбиения и значениях параметров в функции create_parent ().

Просто создайте секционированную таблицу и присоедините существующую таблицу как секцию:

CREATE TABLE test (a INT);
 
INSERT INTO test SELECT generate_series(1,10);
 
ALTER TABLE test_parent attach partition test DEFAULT;
 
SELECT * FROM test_parent;
 a
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10

Вы можете запускать операции обслуживания разделов для автоматического создания новых разделов, отсоединения разделов или удаления старых разделов. Обслуживание разделов зависит от run_maintenance_proc функции pg_partman расширения и pg_cron расширения, которое запускает внутренний планировщик. pg_cron планировщик автоматически выполняет SQL - операторы, функции и процедуры, определенные в базах данных.

В следующем примере используется events таблица, созданная в разделе Включение расширения pg_partman Настройка обслуживания раздела с помощью функции run_main maintenance_proc для автоматического запуска операций обслуживания раздела. В качестве предварительного условия добавьте pg_cron к shared_preload_libraries параметру в группе параметров экземпляра БД.

CREATE EXTENSION pg_cron;
 
UPDATE partman.part_config 
SET infinite_time_partitions = TRUE,
    retention = '3 months', 
    retention_keep_table=TRUE 
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

Ниже вы можете найти пошаговое объяснение предыдущего примера:

1. Измените группу параметров, связанную с вашим экземпляром БД, и добавьте pg_cron к shared_preload_libraries значению параметра. Это изменение требует перезапуска экземпляра БД, чтобы оно вступило в силу. Для получения дополнительной информации см. Изменение параметров в группе параметров БД.
2. Запустите команду, CREATE EXTENSION pg_cron; используя учетную запись с rds_superuser разрешениями. Это включает pg_cron расширение. Для получения дополнительной информации см. Планирование обслуживания с расширением PostgreSQL pg_cron.
3. Запустите команду, UPDATE partman.part_config, чтобы настроить pg_partman параметры data_mart.events таблицы.
4. Запустите команду SET . . . для настройки data_mart.events таблицы с этими пунктами: infinite_time_partitions = true, - Настраивает таблицу, чтобы иметь возможность автоматически создавать новые разделы без каких-либо ограничений. retention = 3 months - Настраивает таблицу для хранения до трех месяцев. retention_keep_table=true - Настраивает таблицу таким образом, чтобы по истечении срока хранения таблица не удалялась автоматически. Вместо этого разделы, возраст которых превышает срок хранения, отсоединяются только от родительской таблицы.
5. Запустите команду «SELECT cron.schedule . . .», чтобы сделать pg_cron вызов функции. Этот вызов определяет, как часто планировщик запускает pg_partman процедуру обслуживания partman.run_maintenance_proc. В этом примере процедура выполняется каждый час.

-- Период 2021-09-01 00:00:00 по 2022-01-01 00:00:00 , 4 месяца будет удалено
SELECT public.drop_partitions(CURRENT_DATE + INTERVAL '2 MONTH', 'public', 'stocks_jsons', 4, 'month');
 
-- Период 2021-09-01 00:00:00 по 2022-01-01 00:00:00 , 4 недели будет удалено
SELECT public.drop_partitions(CURRENT_DATE + INTERVAL '2 week', 'public', 'search_requests', 4, 'week');

код функции

CREATE OR REPLACE FUNCTION public.drop_partitions(retention_period TIMESTAMP WITHOUT TIME zone, schema_name text, base_table_name text, partition_count INTEGER, partition_plan text)
 RETURNS INTEGER
 LANGUAGE plpgsql
AS $function$
          DECLARE dateFormat text;
          DECLARE planInterval INTERVAL;
          DECLARE searchInterval INTERVAL;
          DECLARE endTime TIMESTAMP;
          DECLARE startTime TIMESTAMP;
          DECLARE deletedTables INTEGER;
          DECLARE fullTablename text;
          DECLARE deleteStatement text;
 
          BEGIN
          --set partition table name format based on how automated partitioning was configured
          dateFormat := CASE 
          WHEN partition_plan='month' THEN 'YYYY_MM'
          WHEN partition_plan='week'  THEN 'IYYY"w"IW'
          WHEN partition_plan='day'   THEN 'YYYY_MM_DD'
          WHEN partition_plan='year'  THEN 'YYYY'
          ELSE 'error'
          END;
 
          --throw error if an invalid partition_plan is specified
          IF dateFormat = 'error' THEN
          RAISE EXCEPTION 'Non valid plan --> %', partition_plan;
          END IF;
 
          --set variable values
          planInterval := ('1 ' || partition_plan)::INTERVAL; --increment by one day/week/month/year
          searchInterval := (partition_count || ' ' || partition_plan)::INTERVAL; --the number of tables/partitions to look for
              endTime := (date_trunc(partition_plan, retention_period)); --the oldest time period to retain data for
              startTime := (date_trunc(partition_plan, (endTime - searchInterval))); --the oldest time period to delete data for
              deletedTables := 0;
 
          RAISE NOTICE 'startTime % < %', startTime, endTime;
          --look for tables/partitions to drop
          while (startTime < endTime) LOOP
          fullTablename := base_table_name || '_p' || to_char(startTime, dateFormat);
          startTime := startTime + planInterval;
 
          --test if the partition to delete does exist
          IF EXISTS(SELECT * FROM information_schema.tables WHERE table_schema = schema_name AND TABLE_NAME = fullTablename) THEN
          deleteStatement := 'DROP TABLE '||schema_name||'.'||fullTablename||';';
          RAISE NOTICE 'DROP TABLE  %', fullTablename;
 
          --run the delete/drop statement
          EXECUTE deleteStatement;
 
          --increment the counter
          deletedTables := deletedTables+1;
          END IF;
          END LOOP;
 
          --return the number of deleted tables/partitions to the caller
          RETURN deletedTables;
 
          END;
          $function$
;

Обычно набор секций, образованный изначально при создании таблиц, не предполагается сохранять неизменным. Чаще наоборот, планируется удалять старые секции данных и периодически добавлять новые. Одно из наиболее важных преимуществ секционирования состоит именно в том, что оно позволяет практически моментально выполнять трудоёмкие операции, изменяя структуру секций, а не физически перемещая большие объёмы данных.

Самый лёгкий способ удалить старые данные — просто удалить секцию, ставшую ненужной:

DROP TABLE measurement_y2006m02;

Так можно удалить миллионы записей гораздо быстрее, чем удалять их по одной. Заметьте, однако, что приведённая выше команда требует установления блокировки ACCESS EXCLUSIVE.

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

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

При этом можно будет продолжать работать с данными, пока таблица не будет удалена. Например, в этом состоянии очень кстати будет сделать резервную копию данных, используя COPY, pg_dump или подобные средства. Возможно, эти данные также можно будет агрегировать, перевести в компактный формат, выполнить другую обработку или построить отчёты.

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

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

А иногда удобнее создать новую таблицу вне структуры секций и сделать её полноценной секцией позже. При таком подходе данные можно будет загрузить, проверить и преобразовать до того, как они появятся в секционированной таблице:

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;
 
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
 
\copy measurement_y2008m02 FROM 'measurement_y2008m02'
-- possibly some other data preparation work
 
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

Прежде чем выполнять команду ATTACH PARTITION, рекомендуется создать ограничение CHECK в присоединяемой таблице, соответствующее ожидаемому ограничению секции. Благодаря этому система сможет не сканировать таблицу для проверки неявного ограничения секции. Без этого ограничения CHECK таблицу нужно будет просканировать и убедиться в выполнении ограничения секции, удерживая блокировку ACCESS EXCLUSIVE в родительской таблице. После выполнения команды ATTACH PARTITION это ставшее ненужным ограничение CHECK при желании можно удалить.