0Pricing
PostgreSQL Performance & Query Optimization · Ders

Bölüm Oluşturma ve Saklama Süreçlerini Otomatikleştirme

Yeni bölümleri devreye alıp eskilerini düşük maliyetle ayırmak için pg_partman veya özel DDL ile bakım işleri oluşturun.

Bölüm Oluşturma ve Saklama Süreçlerini Otomatikleştirme, CoddyKit'te ücretsiz bir PostgreSQL Performance & Query Optimization dersidir. Bu, 4 dersinin 3. dersidir. Aşağıdan dersin tamamını ücretsiz okuyabilir, sonra tarayıcıda yerleşik kod editörü ve 7/24 yapay zeka koçu ile uygulamalı olarak pratik yapabilirsin. Bu, PostgreSQL Performance & Query Optimization öğrenme yolunun bir parçasıdır ve ilerlemeniz web ve CoddyKit uygulaması arasında senkronize olur. PostgreSQL Performance & Query Optimization kursu toplamda 4 dersten oluşur.

Bu dersin bazı bölümleri henüz çevrilmemiş olup İngilizce olarak gösterilmektedir.

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.

Sıkça Sorulan Sorular

“Bölüm Oluşturma ve Saklama Süreçlerini Otomatikleştirme” dersi ücretsiz mi?

Evet — “Bölüm Oluşturma ve Saklama Süreçlerini Otomatikleştirme” dersin tüm metni burada web'de ücretsiz olarak okunabilir. Etkileşimli olarak pratik yapmak (yerleşik kod editörü ve 7/24 yapay zeka koçu) ve PostgreSQL Performance & Query Optimization kursunun geri kalanını açmak için CoddyKit PRO'ya yükselt. PostgreSQL Performance & Query Optimization kursu toplamda 4 dersten oluşur.

“Bölüm Oluşturma ve Saklama Süreçlerini Otomatikleştirme” dersinde ne öğreneceğim?

Yeni bölümleri devreye alıp eskilerini düşük maliyetle ayırmak için pg_partman veya özel DDL ile bakım işleri oluşturun. PostgreSQL Performance & Query Optimization ile uygulamalı kodu tarayıcıda doğrudan çalıştırarak pratik yaparsın ve 7/24 yapay zeka koçu dersi çalışırken sorularını yanıtlar.

PostgreSQL Performance & Query Optimization öğrenmeye başlamak için deneyim gerekli mi?

Önceden deneyim gerekmez. CoddyKit'te PostgreSQL Performance & Query Optimization, başlangıçtan ileri seviyeye kadar yapılandırıldığı için buradan başlayabilir veya başından başlayıp kendi hızında ilerleme yapabilirsin. Bu, 4 dersinin 3. dersidir.

“Bölüm Oluşturma ve Saklama Süreçlerini Otomatikleştirme” dersi ne kadar sürer?

Çoğu CoddyKit dersi yaklaşık 5–10 dakika sürer. Her biri kısa ve etkileşimli olduğu için sabit ilerleme yaparsın ve web ile uygulama arasında tam olarak bıraktığın yerden devam edebilirsin.

Bu PostgreSQL Performance & Query Optimization dersinde kod yazıp çalıştırabilir miyim?

Evet. Her PostgreSQL Performance & Query Optimization dersi yerleşik bir kod editörü içerir, bu sayede tarayıcıda gerçek kod yazıp çalıştırabilir ve anlık yapay zeka geri bildirimi alırsın — yerel kurulum gerekli değildir.

Bu kursun tüm dersleri

  1. Bölümleme Anahtarı ve Stratejisi Seçme
  2. Planlama ve Çalıştırma Sırasında Bölüm Budama
  3. Bölüm Oluşturma ve Saklama Süreçlerini Otomatikleştirme
  4. Büyük Bir Tabloyu Çevrimiçi Olarak Bölümlere Taşıma
← PostgreSQL Performance & Query Optimization Sayfasına Dön