0Pricing
PostgreSQL Performance & Query Optimization · Leçon

Automatiser la création et la conservation des partitions

Créez des tâches de maintenance avec pg_partman ou du DDL personnalisé pour ajouter progressivement les nouvelles partitions et détacher les anciennes à moindre coût.

Automatiser la création et la conservation des partitions est une leçon PostgreSQL Performance & Query Optimization gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage PostgreSQL Performance & Query Optimization, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours PostgreSQL Performance & Query Optimization comprend 4 leçons au total.

Certaines parties de cette leçon n'ont pas encore été traduites et s'affichent en anglais.

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.

Questions Fréquemment Posées

La leçon « Automatiser la création et la conservation des partitions » est-elle gratuite ?

Oui — le texte complet de « Automatiser la création et la conservation des partitions » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours PostgreSQL Performance & Query Optimization, passe à CoddyKit PRO. Le cours PostgreSQL Performance & Query Optimization comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Automatiser la création et la conservation des partitions » ?

Créez des tâches de maintenance avec pg_partman ou du DDL personnalisé pour ajouter progressivement les nouvelles partitions et détacher les anciennes à moindre coût. Tu pratiques PostgreSQL Performance & Query Optimization avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer PostgreSQL Performance & Query Optimization ?

Aucune expérience préalable n'est requise. PostgreSQL Performance & Query Optimization sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.

Combien de temps prend la leçon « Automatiser la création et la conservation des partitions » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon PostgreSQL Performance & Query Optimization ?

Oui. Chaque leçon PostgreSQL Performance & Query Optimization inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Choisir une clé et une stratégie de partitionnement
  2. Élagage des partitions lors de la planification et de l’exécution
  3. Automatiser la création et la conservation des partitions
  4. Migrer une table volumineuse vers des partitions en ligne
← Retour à PostgreSQL Performance & Query Optimization