=====Секционирование (партицирование)===== Разделение таблиц PostgreSQL обеспечивает основу для высокопроизводительной обработки ввода данных и создания отчетов. Используйте секционирование для баз данных, требующих очень быстрого ввода больших объемов данных. Секционирование также обеспечивает более быстрые запросы к большим таблицам. Секционирование помогает поддерживать данные, не влияя на экземпляр базы данных, поскольку требует меньше ресурсов ввода-вывода.\\ \\ ====Обзор расширения PostgreSQL pg_partman ==== Вы можете использовать pg_partman расширение PostgreSQL для автоматизации создания и обслуживания разделов таблиц. pg_partman поддерживается RDS для PostgreSQL версии 12.5 и выше. Вместо того, чтобы вручную создавать каждый раздел, вы настраиваете 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** функцию, которую вы можете позвонить по расписанию автоматически управлять разделами. Чтобы гарантировать создание нужных разделов по мере необходимости, запланируйте периодический запуск этой функции (например, ежечасно). Вы также можете обеспечить автоматическое удаление разделов. ====Включение расширения pg_partman==== Если у вас есть несколько баз данных в одном экземпляре БД 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); ====Настройка разделов с помощью функции create_parent==== После включения **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** - Количество разделов, которые необходимо создать заранее для поддержки новых вставок. ^p_interval^обозначение^ |yearly|1 год| |quarterly|3 месяца| |monthly|1 месяц| |weekly|1 неделя| |daily|1 день| |hourly|1 час| |half-hour|30 минут| |quarter-hour|15 минут| **Пример:** Сначала создайте родительскую таблицу с соответствующим типом столбца для того типа разделения, который вы будете делать. Примените все значения по умолчанию, индексы, ограничения, привилегии и права собственности к родительской таблице, и они будут автоматически унаследованы вновь созданным дочерним таблицам (еще не существующим разделам, см. Документы, чтобы узнать, как это исправить). Вот столбцы, которые можно использовать для 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 (). ====добавить разделение postgres в существующую таблицу==== Просто создайте секционированную таблицу и присоедините существующую таблицу как секцию: 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_main maintenance_proc ==== Вы можете запускать операции обслуживания разделов для автоматического создания новых разделов, отсоединения разделов или удаления старых разделов. Обслуживание разделов зависит от **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()$$); Ниже вы можете найти пошаговое объяснение предыдущего примера: - Измените группу параметров, связанную с вашим экземпляром БД, и добавьте **pg_cron** к **shared_preload_libraries** значению параметра. Это изменение требует перезапуска экземпляра БД, чтобы оно вступило в силу. Для получения дополнительной информации см. [[https://docs-aws-amazon-com.translate.goog/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html?_x_tr_sl=en&_x_tr_tl=ru&_x_tr_hl=en&_x_tr_pto=nui#USER_WorkingWithParamGroups.Modifying|Изменение параметров в группе параметров БД]]. - Запустите команду, **CREATE EXTENSION pg_cron**; используя учетную запись с **rds_superuser** разрешениями. Это включает **pg_cron** расширение. Для получения дополнительной информации см. [[https://docs-aws-amazon-com.translate.goog/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html?_x_tr_sl=en&_x_tr_tl=ru&_x_tr_hl=en&_x_tr_pto=nui|Планирование обслуживания с расширением PostgreSQL pg_cron]]. - Запустите команду, **UPDATE partman.part_config**, чтобы настроить **pg_partman** параметры **data_mart.events** таблицы. - Запустите команду** SET . . . ** для настройки **data_mart.events** таблицы с этими пунктами: ''infinite_time_partitions = true,'' - Настраивает таблицу, чтобы иметь возможность автоматически создавать новые разделы без каких-либо ограничений. **retention = 3 months** - Настраивает таблицу для хранения до трех месяцев. **retention_keep_table=true** - Настраивает таблицу таким образом, чтобы по истечении срока хранения таблица не удалялась автоматически. Вместо этого разделы, возраст которых превышает срок хранения, отсоединяются только от родительской таблицы. - Запустите команду **"SELECT cron.schedule . . ."**, чтобы сделать **pg_cron** вызов функции. Этот вызов определяет, как часто планировщик запускает **pg_partman** процедуру обслуживания **partman.run_maintenance_pro**c. В этом примере процедура выполняется каждый час. ==== drop_partitions ==== -- Период 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'' при желании можно удалить.