أتمتة إنشاء الأقسام والاحتفاظ بها

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.

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).

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.