0Pricing
PostgreSQL Performance & Query Optimization · Урок

Автоматизация создания и хранения секций

Создавайте задания обслуживания с pg_partman или пользовательским DDL, недорого добавляя новые секции и отсоединяя старые.

«Автоматизация создания и хранения секций» — бесплатный урок PostgreSQL Performance & Query Optimization на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения PostgreSQL Performance & Query Optimization, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Части этого урока еще не переведены и отображаются на английском.

Why Partition Maintenance Must Be Automated

Range partitioning by time (daily, weekly, monthly) only pays off if a future partition always exists before data arrives. If a row's partition key falls outside every defined partition, the INSERT fails with no partition of relation found for row.

  • Roll-in: create the next partition(s) ahead of time.
  • Roll-out (retention): detach or drop old partitions once they pass your retention window.

Doing this by hand is error-prone, so you either automate it with a maintenance function on a schedule, or use the pg_partman extension. This lesson covers both.

The Parent Table

Everything starts from a declaratively partitioned parent. Here we partition events by month on created_at. The parent holds no rows itself; it only routes inserts to child partitions.

Note that the partition key column must be part of the primary key, which is why the PK is (id, created_at).

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    created_at  timestamptz NOT NULL DEFAULT now(),
    user_id     bigint NOT NULL,
    payload     jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

Creating One Monthly Partition by Hand

A range partition covers a half-open interval: the lower bound is inclusive, the upper bound is exclusive. For June 2026 you use FROM ('2026-06-01') TO ('2026-07-01').

Defining bounds as [start, next_start) guarantees that adjacent partitions never overlap and never leave a gap on month boundaries.

CREATE TABLE events_2026_06
    PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE TABLE events_2026_07
    PARTITION OF events
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

A Custom Roll-In Function

To automate roll-in, write a function that creates the partition for a given month only if it does not already exist. Using to_char for the name and format(... %I ...) for safe identifier quoting keeps the DDL dynamic but injection-safe.

Calling it for date_trunc('month', now()) + interval '1 month' ensures next month is always ready.

CREATE OR REPLACE FUNCTION create_events_partition(p_month date)
RETURNS void AS $$
DECLARE
    start_date date := date_trunc('month', p_month);
    end_date   date := start_date + interval '1 month';
    part_name  text := 'events_' || to_char(start_date, 'YYYY_MM');
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = part_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF events FOR VALUES FROM (%L) TO (%L)',
            part_name, start_date, end_date
        );
    END IF;
END;
$$ LANGUAGE plpgsql;

Pre-Creating a Buffer of Partitions

Never cut it close to the boundary. A good maintenance run creates the current month plus a few months ahead, so a clock skew, a delayed job, or a backfill of future-dated rows cannot hit a missing partition.

Loop over the next N months and call your roll-in function for each.

DO $$
DECLARE
    m int;
BEGIN
    FOR m IN 0..3 LOOP
        PERFORM create_events_partition(
            (date_trunc('month', now()) + (m || ' month')::interval)::date
        );
    END LOOP;
END;
$$;

Detach Is Cheap, Drop Is Final

For retention you have two roll-out strategies:

  • DETACH PARTITION turns the child into a standalone, independent table. The data survives; you can archive it, dump it, or move it to cheaper storage before dropping.
  • DROP TABLE on the child removes it permanently.

Both are metadata operations and do not rewrite the surviving partitions, so retention stays cheap regardless of table size. Prefer DETACH first when the data has any archival value.

ALTER TABLE events DETACH PARTITION events_2025_01;
-- archive / dump events_2025_01 here, then:
DROP TABLE events_2025_01;

DETACH CONCURRENTLY Avoids Long Locks

A plain DETACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent, blocking all reads and writes for its duration. On a hot table that is a visible stall.

Since PostgreSQL 14, DETACH PARTITION ... CONCURRENTLY performs the detach in two phases with only a brief SHARE UPDATE EXCLUSIVE lock, so concurrent queries keep running. It cannot run inside a transaction block.

ALTER TABLE events
    DETACH PARTITION events_2025_01 CONCURRENTLY;

A Retention Function

Automate roll-out by scanning the catalog for child partitions whose upper bound is older than your retention window, then detaching and dropping them. pg_partitions isn't built in, so read partition bounds from pg_inherits joined to pg_class, or simply derive expected names from the date.

The name-derivation approach below is simple and predictable for monthly partitions.

CREATE OR REPLACE FUNCTION drop_old_events_partitions(p_keep_months int)
RETURNS void AS $$
DECLARE
    cutoff date := date_trunc('month', now()) - (p_keep_months || ' month')::interval;
    r record;
BEGIN
    FOR r IN
        SELECT c.relname
        FROM pg_inherits i
        JOIN pg_class c    ON c.oid = i.inhrelid
        JOIN pg_class p    ON p.oid = i.inhparent
        WHERE p.relname = 'events'
          AND c.relname ~ '^events_\d{4}_\d{2}$'
          AND to_date(right(c.relname, 7), 'YYYY_MM') < cutoff
    LOOP
        EXECUTE format('ALTER TABLE events DETACH PARTITION %I', r.relname);
        EXECUTE format('DROP TABLE %I', r.relname);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Scheduling the Maintenance Job

PostgreSQL has no built-in scheduler, so wire your roll-in and retention calls to one of:

  • pg_cron — runs SQL on a cron schedule from inside the database.
  • An external OS cron / systemd timer calling psql.

With pg_cron you register a job once and it survives restarts. Run maintenance daily so partitions are always provisioned well ahead of need.

SELECT cron.schedule(
    'events-maintenance',
    '0 3 * * *',
    $job$
        DO $$
        BEGIN
            PERFORM create_events_partition(
                (date_trunc('month', now()) + interval '1 month')::date);
            PERFORM drop_old_events_partitions(12);
        END;
        $$;
    $job$
);

Doing It the pg_partman Way

pg_partman packages all of this. After CREATE EXTENSION pg_partman, you register the parent once with create_parent: specify the partition column, type (range), and interval (e.g. '1 month'). It immediately builds a buffer of premade partitions.

It also stores config in part_config, including how many partitions to keep ahead (premake) and the retention window.

CREATE EXTENSION IF NOT EXISTS pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control      := 'created_at',
    p_type         := 'range',
    p_interval     := '1 month',
    p_premake      := 4
);

pg_partman Retention and run_maintenance

Set retention in part_config: retention defines the age threshold and retention_keep_table decides whether old partitions are detached (kept as standalone tables) or dropped outright.

The single entry point run_maintenance_proc() then rolls new partitions in and applies retention. Schedule it with pg_cron and you are done — no custom DDL to maintain.

UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false   -- false = DROP old partitions
WHERE parent_table = 'public.events';

-- run on a schedule (e.g. via pg_cron)
CALL partman.run_maintenance_proc();

Quick Check: Cheap, Online Retention

You run a high-traffic, time-partitioned table and need to remove partitions older than 12 months every night without blocking live reads and writes, while keeping the dropped data available for archival.

Recap

You now have two reliable patterns for partition lifecycle automation:

  • Roll-in early: a function that creates the next N months of partitions idempotently, run daily, so an INSERT never hits a missing partition.
  • Roll-out cheaply: DETACH PARTITION ... CONCURRENTLY (then archive and DROP) avoids long ACCESS EXCLUSIVE locks and never rewrites surviving data.
  • Schedule it: wire both into pg_cron or OS cron.
  • Or use pg_partman: create_parent + part_config retention + run_maintenance_proc() replace the custom DDL entirely.

The decision that matters: detach-then-drop to keep retention cheap and online, instead of DELETE-based purges.

Часто задаваемые вопросы

Урок «Автоматизация создания и хранения секций» бесплатный?

Да — полный текст урока «Автоматизация создания и хранения секций» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс PostgreSQL Performance & Query Optimization, подпишись на CoddyKit PRO. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Чему я научусь в уроке «Автоматизация создания и хранения секций»?

Создавайте задания обслуживания с pg_partman или пользовательским DDL, недорого добавляя новые секции и отсоединяя старые. Ты практикуешь PostgreSQL Performance & Query Optimization с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать PostgreSQL Performance & Query Optimization?

Предыдущий опыт не требуется. PostgreSQL Performance & Query Optimization на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Автоматизация создания и хранения секций»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке PostgreSQL Performance & Query Optimization?

Да. Каждый урок PostgreSQL Performance & Query Optimization включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Выбор ключа и стратегии секционирования
  2. Отсечение секций на этапе планирования и выполнения
  3. Автоматизация создания и хранения секций
  4. Перенос огромной таблицы в секции без остановки работы
← Назад к PostgreSQL Performance & Query Optimization