0Pricing
PostgreSQL Performance & Query Optimization · Aula

Automatização da criação e retenção de partições

Crie tarefas de manutenção com pg_partman ou DDL personalizado para adicionar novas partições e desanexar as antigas a baixo custo.

Automatização da criação e retenção de partições é uma aula grátis de PostgreSQL Performance & Query Optimization no CoddyKit. Esta é a aula 3 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de PostgreSQL Performance & Query Optimization, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de PostgreSQL Performance & Query Optimization inclui 4 aulas no total.

Partes desta aula ainda não foram traduzidas e aparecem em inglês.

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.

Perguntas Frequentes

A aula “Automatização da criação e retenção de partições” é grátis?

Sim — o texto completo de “Automatização da criação e retenção de partições” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de PostgreSQL Performance & Query Optimization, atualize para CoddyKit PRO. O curso de PostgreSQL Performance & Query Optimization inclui 4 aulas no total.

O que vou aprender em “Automatização da criação e retenção de partições”?

Crie tarefas de manutenção com pg_partman ou DDL personalizado para adicionar novas partições e desanexar as antigas a baixo custo. Você pratica PostgreSQL Performance & Query Optimization com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar PostgreSQL Performance & Query Optimization?

Nenhuma experiência prévia é necessária. PostgreSQL Performance & Query Optimization no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 3 de 4.

Quanto tempo leva a aula “Automatização da criação e retenção de partições”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de PostgreSQL Performance & Query Optimization?

Sim. Cada aula de PostgreSQL Performance & Query Optimization inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

  1. Escolha de chave e estratégia de particionamento
  2. Eliminação de partições no planejamento e na execução
  3. Automatização da criação e retenção de partições
  4. Migração online de uma tabela enorme para partições
← Voltar para PostgreSQL Performance & Query Optimization