Настройку партиционирования (создание партиционированной таблицы и миграция существующих данных) таблицы событий и сопровождение (создание новых партиций) можно реализовать либо с помощью расширения pg_partman, либо с помощью хранимой процедуры PL/pgSQL.
Запуск создания последующих партиций рекомендуется автоматизировать с помощью планировщика, или фонового процесса, например:
- cron — планировщик ОС;
- pg_cron — расширение СУБД;
- pg_partman_bgw — фоновый процесс pg_partman'а.
Настоящая инструкция актуальна для PostgreSQL версии 18. Для использования с другими версиями СУБД, обратитесь к документации, т.к. функции плагинов, наборы параметров, и способы задания значений параметров могут отличаться.
Подготовка
Удаление ограничений
ALTER TABLE pc_confirm_attempt DROP CONSTRAINT pc_confirm_attempt_eventid_fk;
ALTER TABLE pc_device DROP CONSTRAINT pc_device_eventid_fk;
ALTER TABLE pc_extauth DROP CONSTRAINT pc_extauth_eventid_fk;
ALTER TABLE pc_keyinfo DROP CONSTRAINT pc_keyinfo_eventid_fk;
ALTER TABLE pc_publickey DROP CONSTRAINT pc_publickey_eventid_fk;
Переименование существующей таблицы
ALTER TABLE pc_event RENAME TO pc_event_old;
Создание партиционированной таблицы
Новая ("родительская") таблица должна быть идентична оригинальной, но с указанием столбца, по которому производится партиционирование, и с индексом по eventid и дате. При этом индексы по eventid и по userid должны быть созданы дополнительно. Пример (актуальный для PC версии 6.10):
CREATE TABLE pc_event (
eventid bigint NOT NULL,
created_at timestamp without time zone NOT NULL,
is_deleted smallint,
updated_at timestamp without time zone,
auth_code character varying(64),
device_eventid character varying(36),
device_info text,
device_time timestamp without time zone,
event_data text,
request_hmac character varying(64),
request_hash character varying(64),
request_result integer,
request_type character varying(36),
requester_ip character varying(64),
sessionid character varying(36),
transactionid character varying(36),
userid character varying(64),
operationid character varying(36),
request_url character varying(512),
pc_instance_name character varying(32),
pc_version character varying(256),
connection_info character varying(512),
is_cleaned integer,
systemid character varying(64),
key_version integer,
device_fingerprint character varying(255),
PRIMARY KEY (eventid, created_at)
) PARTITION BY RANGE (created_at);
Создание индексов для родительской таблицы
CREATE INDEX pc_event_by_id_idx ON pc_event USING btree (eventid);
CREATE INDEX pc_event_by_userid_idx ON pc_event USING btree (userid);
Уточнить и установить владельца родительской таблицы
При создании новой таблицы из под пользователя postgres у учётной записи, которая используется для подключения к СУБД, не будет прав на запись в неё. Нужно либо вручную установить имя владельца (пример для пользователя pc_user):
ALTER TABLE pc_event OWNER TO pc_user;
либо получить имя пользователя и установить полученное значение в качестве имени владельца:
DO $$
DECLARE
new_owner text;
BEGIN
SELECT tableowner INTO new_owner FROM pg_tables WHERE tablename = 'pc_event_old';
EXECUTE 'ALTER TABLE pc_event OWNER TO ' || quote_ident(new_owner);
END $$;
Партиционирование с помощью расширения pg_partman
Установка расширения производится через пакетный менеджер ОС, название пакета зависит от версии СУБД, например, для PostgreSQL 18, название пакета будет postgresql-18-partman.
Установка расширения pg_partman в БД
Для функционирования pg_partman его необходимо установить в целевую базу данных, в схему partman.
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
Добавление родительской таблицы в pg_partman
При добавлении таблицы в pg_partman также будут созданы партиции за предыдущие периоды, начиная с месяца, для которых есть данные в исходной таблице, и default-партиция.
С помощью следующего запроса получите версию pg_partman.
SELECT extversion FROM pg_extension WHERE extname = 'pg_partman';
Если получена версия ниже 4, то, в качестве значения для интервала (p_interval) в следующем запросе, следует использовать monthly:
SELECT partman.create_parent(
p_parent_table := 'public.pc_event',
p_control := 'created_at',
p_interval := '1 month',
p_type := 'range',
p_premake := 3,
p_start_partition :=
to_char(
date_trunc('month',
(SELECT min(created_at) FROM pc_event_old)
),
'YYYY-MM-DD HH24:MI:SS'
)
);
Перенос данных
Перенос данных помесячно:
DO $$
DECLARE
d date := (SELECT date_trunc('month', min(created_at)) FROM pc_event_old);
date_to date := (SELECT date_trunc('month', max(created_at)) FROM pc_event_old);
BEGIN
WHILE d <= date_to LOOP
EXECUTE format(
'INSERT INTO pc_event
SELECT * FROM pc_event_old
WHERE created_at >= %L
AND created_at < %L;',
d,
d + INTERVAL '1 month'
);
d := d + INTERVAL '1 month';
END LOOP;
END$$;
Проверка переноса
Для формальной проверки того, что данные были перенесены, можно запросить и сравнить общее количество строк в исходной и новой таблицах:
SELECT count(*) FROM pc_event_old;
SELECT count(*) FROM pc_event;
Удаление исходной таблицы
После переноса и проверки для высвобождения дискового пространства следует удалить исходную таблицу
DROP TABLE pc_event_old;
Создание последующих партиций с помощью pg_partman
Новые партиции для записи данных последующих периодов необходимо регулярно создавать. Запуск функции создания партиций рекомендуется автоматизировать с помощью планировщика и выполнять ежедневно.
Функция создания партиций и переноса данных из default-партиции (если вдруг в ней оказались данные):
SELECT partman.run_maintenance();
Партиционирование с помощью PL/pgSQL
Создание партиций для существующих данных
DO $$
DECLARE
d date;
date_to date;
BEGIN
select DATE_TRUNC('month', created_at)::date INTO d from pc_event_old order by created_at asc limit 1;
select DATE_TRUNC('month', created_at)::date INTO date_to from pc_event_old order by created_at desc limit 1;
WHILE d <= date_to limit 1 LOOP
EXECUTE format(
'CREATE TABLE pc_event_%s PARTITION OF pc_event
FOR VALUES FROM (%L) TO (%L);',
to_char(d, 'YYYY_MM'),
d,
d + INTERVAL '1 month'
);
d := d + INTERVAL '1 month';
END LOOP;
END$$;
Создание DEFAULT-партиции
Рекомендуется создать DEFAULT-партицию, которая будет хранилищем для данных, если, вдруг, соответствующая партиция не была создана вовремя.
CREATE TABLE pc_event_default PARTITION OF pc_event DEFAULT;
Перенос данных
Перенос данных помесячно:
DO $$
DECLARE
d date := (SELECT date_trunc('month', min(created_at)) FROM pc_event_old);
date_to date := (SELECT date_trunc('month', max(created_at)) FROM pc_event_old);
BEGIN
WHILE d <= date_to LOOP
EXECUTE format(
'INSERT INTO pc_event
SELECT * FROM pc_event_old
WHERE created_at >= %L
AND created_at < %L;',
d,
d + INTERVAL '1 month'
);
d := d + INTERVAL '1 month';
END LOOP;
END$$;
Проверка переноса
Для формальной проверки того, что данные были перенесены, можно запросить и сравнить общее количество строк в исходной и новой таблицах:
SELECT count(*) FROM pc_event_old;
SELECT count(*) FROM pc_event;
Удаление исходной таблицы
После переноса и проверки для высвобождения дискового пространства следует удалить исходную таблицу
DROP TABLE pc_event_old;
Создание последующих таблиц
Новые таблицы для записи данных последующих периодов необходимо регулярно создавать. Проще всего это сделать с помощью хранимой функции и её вызова с помощью планировщика, например, cron.
CREATE OR REPLACE FUNCTION pc_event_create_month_partitions(p_until timestamptz)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
d timestamp;
BEGIN
FOR d IN
SELECT generate_series(
date_trunc('month', now()),
date_trunc('month', p_until),
interval '1 month'
)
LOOP
BEGIN
EXECUTE format(
'CREATE TABLE %I PARTITION OF public.pc_event
FOR VALUES FROM (%L) TO (%L);',
format('pc_event_%s', to_char(d, 'YYYY_MM')),
d,
d + interval '1 month'
);
EXCEPTION
WHEN duplicate_table THEN
NULL;
END;
END LOOP;
END;
$$;
Вызов функции
Запуск функции создания партиций рекомендуется автоматизировать и выполнять ежедневно с помощью запроса
SELECT pc_event_create_month_partitions(now() + interval '3 months');
Обслуживание
Обратите внимание, что этот раздел относится только к партиционированию с помощью PL/pgSQL, и не относится к партиционированию с помощью pg_partman.
Перенос данных из default-партиции
Если на какой-то момент времени необходимая партиция не была создана, и в default-партиции оказались данные, то их нужно перенести в соответствующие партиции. Узнать есть ли в default данные, за какой период, и сколько, можно с помощью запроса:
SELECT min(created_at), max(created_at), count(*) FROM pc_event_default;
Для переноса необходимо выполнить несколько действий:
Отключить default-партицию
Если этого не сделать, партиция, перекрывающая диапазон данных, находящихся в default не сможет быть создана.
ALTER TABLE pc_event DETACH PARTITION pc_event_default;
Создать недостающие партиции
Создать партиции для текущего и следующих трёх месяцев можно с помощью функции pc_event_create_month_partitions:
SELECT pc_event_create_month_partitions(now() + interval '3 months');
Для любых указанных интервалов вручную, например:
CREATE TABLE pc_event_2026_02 PARTITION OF pc_event
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
Перенести данные
Перенос данных из отсоединённой default-партиции в основную таблицу:
DO $$
DECLARE
d date := (SELECT date_trunc('month', min(created_at)) FROM pc_event_default);
date_to date := (SELECT date_trunc('month', max(created_at)) FROM pc_event_default);
BEGIN
WHILE d <= date_to LOOP
EXECUTE format(
'INSERT INTO pc_event
SELECT * FROM pc_event_default
WHERE created_at >= %L
AND created_at < %L;',
d,
d + INTERVAL '1 month'
);
d := d + INTERVAL '1 month';
END LOOP;
END$$;
Удалить данные из default-партиции
После переноса данных нужно очистить таблицу pc_event_default:
delete from pc_event_default;
Подключить default как партицию к таблице
ALTER TABLE pc_event ATTACH PARTITION pc_event_default DEFAULT;
Создание последующих партиций
Рекомендуется автоматизировать запуск задачи создания последующих партиций, во избежание скапливания записей в default-партиции при отсутствии соответствующих периоду.
С помощью cron
Для запуска заданий создания последующих таблиц создайте в планировщике ОС задачу для вызова клиента СУБД с запросом вызова функции расширения pg_partman, например:
0 2 * * * psql -d pcs_db -c "SELECT partman.run_maintenance();"
или вызова хранимой функции PL/pgSQL, например:
0 2 * * * psql -d pcs_db -c "SELECT pc_event_create_month_partitions(now() + interval '3 months');"
С помощью pg_cron
pg_cron — расширение PostgreSQL, обеспечивающее выполнение запросов к БД и запуск хранимых функций и задач по расписанию. Запускается и работает как фоновая задача в СУБД.
Установка и запуск pg_cron
Установка расширения производится через пакетный менеджер ОС, название пакета зависит от версии СУБД, например, для PostgreSQL 18, название пакета будет postgresql-18-cron.
Для запуска должен быть добавлен параметр pg_cron в переменную shared_preload_libraries в файле postgresql.conf, и выполнен перезапуск СУБД. При запуске СУБД в среде контейнеризации это делается с помощью параметра запуска postgres:
-c 'shared_preload_libraries=pg_cron'
По умолчанию pg_cron работает с БД postgres, при этом объектами выполнения запросов/функций могут быть другие, указанные явно, БД.
Проверить, запущено ли расширение можно с помощью запроса:
SHOW shared_preload_libraries;
Установка расширения в БД
Далее требуется установить расширение в БД. Расширение можно установить только в ту базу, которая указана в качестве значения параметра cron.database_name в файле postgresql.conf. Значением по умолчанию параметра cron.database_name является postgres.
CREATE EXTENSION pg_cron;
Проверить установленные расширения можно с помощью
\dx
или
SELECT * FROM pg_extension;
Создание задания
Для создания задачи для указанной БД, необходимо передать её название в качестве четвёртого параметра. Пример создания задания на создание партиций журнала событий в БД pcs_db с помощью pg_partman ежедневно в 2 часа ночи:
SELECT cron.schedule_in_database(
'pcs-partman-maintenance',
'0 2 * * *',
$$SELECT partman.run_maintenance();$$,
'pcs_db'
);
Пример для хранимой функции:
SELECT cron.schedule_in_database(
'pcs-create-partitions',
'0 2 * * *',
$$SELECT pc_event_create_month_partitions(now() + interval '3 months');$$,
'pcs_db'
);
Получить список задач можно с помощью
select * from cron.job;
Мониторинг
Запуск и завершение задач буду фиксироваться в журнале работы СУБД.
Получить список результатов выполнения задач планировщиком pg_cron можно с помощью:
select * from cron.job_run_details;
С помощью pg_partman background worker
В случае, если используется pg_partman, обслуживание партиционированных таблиц можно выполнять с помощью фонового процесса pg_partman (pg_partman background worker). Однако этот вариант по умолчанию логирует свои действия только в случае если он что-то сделал. Если в рамках обслуживания каких-либо манипуляций не было, в журнале работы postgres (при выключенном режиме отладки) никаких записей не будет. Для мониторинга работы pg_partman_bgw можно использовать pg_jobmon.
Запуск pg_partman background worker
Background worker входит в состав пакета pg_partman, поэтому дополнительные пакеты устанавливать не нужно.
Для запуска должен быть добавлен параметр pg_partman_bgw в переменную shared_preload_libraries в файле postgresql.conf, и выполнен перезапуск СУБД. При запуске СУБД в среде контейнеризации это делается с помощью параметра запуска postgres:
-c 'shared_preload_libraries=pg_partman_bgw'
Проверить, запущено ли расширение можно с помощью запроса:
SHOW shared_preload_libraries;
Указание параметров работы pg_partman_bgw
Параметры работы pg_partman_bgw задаются в файле postgresql.conf, например:
pg_partman_bgw.interval = 3600
pg_partman_bgw.dbname = 'pcs_db'
либо передаются в качестве параметров запуска postgres:
command: -c 'pg_partman_bgw.dbname=pcs_db' -c 'pg_partman_bgw.interval=3600'
где в параметре pg_partman_bgw.dbname перечисляются базы данных, для которых необходимо выполнять обслуживание, а pg_partman_bgw.interval задаёт интервал в секундах, как часто проводить проверку необходимости обслуживания. Для pg_partman_bgw.interval значение 3600 является значением по умолчанию, и если не требуется задать другое значение, то параметр устанавливать не нужно.