PostgreSQL Performance & Query Optimization · Pelajaran

Mengotomatiskan Pembuatan dan Retensi Partisi

Bangun pekerjaan pemeliharaan dengan pg_partman atau DDL khusus untuk menambahkan partisi baru dan melepaskan partisi lama dengan biaya rendah.

Pelajaran 3 dari 413 langkah

Mengotomatiskan Pembuatan dan Retensi Partisi adalah pelajaran PostgreSQL Performance & Query Optimization gratis di CoddyKit. Ini adalah pelajaran 3 dari 4. Kamu bisa membaca pelajaran lengkapnya di bawah secara gratis — lalu praktikkan langsung di browser dengan editor kode bawaan dan tutor AI 24/7. Ini adalah bagian dari jalur belajar PostgreSQL Performance & Query Optimization, dan progresmu tersinkronisasi di web dan aplikasi CoddyKit. Kursus PostgreSQL Performance & Query Optimization mencakup 4 pelajaran total.

Mengapa Pemeliharaan Partisi Harus Diotomatisasi

Pemartisian berdasarkan rentang waktu (harian, mingguan, bulanan) hanya bermanfaat jika partisi untuk masa depan selalu tersedia sebelum data tiba. Jika kunci partisi suatu baris berada di luar semua partisi yang ditentukan, INSERT gagal dengan pesan no partition of relation found for row.

Roll-in: buat partisi berikutnya terlebih dahulu.
Roll-out (retensi): lepaskan atau hapus partisi lama setelah melewati jangka retensi Anda.

Melakukannya secara manual rentan terhadap kesalahan, jadi Anda perlu mengotomatiskannya dengan fungsi pemeliharaan berdasarkan jadwal, atau menggunakan ekstensi pg_partman. Pelajaran ini membahas keduanya.

Tabel Induk

Semuanya dimulai dari induk yang dipartisi secara deklaratif. Di sini kita mempartisi events berdasarkan bulan menggunakan created_at. Induk itu sendiri tidak menyimpan baris apa pun; tugasnya hanya mengarahkan penyisipan ke partisi anak.

Perhatikan bahwa kolom kunci partisi harus menjadi bagian dari kunci utama, sehingga PK-nya adalah (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);

Membuat Satu Partisi Bulanan Secara Manual

Partisi rentang mencakup interval setengah terbuka: batas bawah termasuk, sedangkan batas atas tidak termasuk. Untuk Juni 2026, gunakan FROM ('2026-06-01') TO ('2026-07-01').

Menentukan batas sebagai [start, next_start) menjamin partisi yang bersebelahan tidak pernah saling tumpang tindih dan tidak pernah menyisakan celah pada batas bulan.

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');

Fungsi Roll-In Kustom

Untuk mengotomatiskan roll-in, tulislah fungsi yang membuat partisi untuk bulan tertentu hanya jika partisi tersebut belum ada. Menggunakan to_char untuk nama dan format(... %I ...) untuk mengutip pengenal dengan aman membuat DDL tetap dinamis sekaligus aman dari injeksi.

Memanggilnya untuk date_trunc('month', now()) + interval '1 month' memastikan bulan berikutnya selalu siap.

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;

Membuat Buffer Partisi Terlebih Dahulu

Jangan pernah menunggu hingga mendekati batas waktu. Proses pemeliharaan yang baik membuat partisi untuk bulan berjalan serta beberapa bulan ke depan, sehingga perbedaan waktu pada jam, pekerjaan yang tertunda, atau pengisian ulang baris bertanggal masa depan tidak menemukan partisi yang hilang.

Ulangi untuk N bulan berikutnya dan panggil fungsi roll-in untuk masing-masing bulan.

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 Hemat, DROP Bersifat Permanen

Untuk retensi, Anda memiliki dua strategi roll-out:

DETACH PARTITION mengubah anak menjadi tabel mandiri yang independen. Data tetap ada; Anda dapat mengarsipkan, membuang, atau memindahkannya ke penyimpanan yang lebih murah sebelum menghapusnya.
DROP TABLE pada anak menghapusnya secara permanen.

Keduanya merupakan operasi metadata dan tidak menulis ulang partisi yang masih ada, sehingga retensi tetap murah terlepas dari ukuran tabel. Sebaiknya gunakan DETACH terlebih dahulu jika data tersebut masih bernilai untuk arsip.

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

DETACH CONCURRENTLY Menghindari Kunci yang Lama

DETACH PARTITION biasa mengambil kunci ACCESS EXCLUSIVE pada induk, sehingga memblokir semua pembacaan dan penulisan selama proses berlangsung. Pada tabel yang sibuk, hal ini menyebabkan jeda yang terlihat.

Sejak PostgreSQL 14, DETACH PARTITION ... CONCURRENTLY melakukan pelepasan dalam dua tahap dengan hanya kunci SHARE UPDATE EXCLUSIVE yang singkat, sehingga kueri bersamaan tetap berjalan. Operasi ini tidak dapat dijalankan di dalam blok transaksi.

ALTER TABLE events
    DETACH PARTITION events_2025_01 CONCURRENTLY;

Fungsi Retensi

Otomatiskan roll-out dengan memindai katalog untuk mencari partisi anak yang batas atasnya lebih lama daripada jangka retensi Anda, lalu melepaskan dan menghapusnya. pg_partitions bukan fitur bawaan, jadi baca batas partisi dari pg_inherits yang digabungkan dengan pg_class, atau cukup turunkan nama yang diharapkan dari tanggal.

Pendekatan penurunan nama di bawah ini sederhana dan mudah diprediksi untuk partisi bulanan.

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;

Menjadwalkan Pekerjaan Pemeliharaan

PostgreSQL tidak memiliki penjadwal bawaan, jadi hubungkan pemanggilan roll-in dan retensi Anda ke salah satu pilihan berikut:

pg_cron — menjalankan SQL berdasarkan jadwal cron dari dalam basis data.
Cron OS eksternal / pengatur waktu systemd yang memanggil psql.

Dengan pg_cron, Anda mendaftarkan pekerjaan sekali dan pekerjaan itu tetap ada setelah dimulai ulang. Jalankan pemeliharaan setiap hari agar partisi selalu tersedia jauh sebelum dibutuhkan.

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$
);

Melakukannya dengan Cara pg_partman

pg_partman menyediakan semua kebutuhan ini dalam satu paket. Setelah CREATE EXTENSION pg_partman, daftarkan induk sekali dengan create_parent: tentukan kolom partisi, jenisnya (range), dan intervalnya (misalnya '1 month'). Fungsi ini langsung membuat buffer partisi yang telah disiapkan.

Fungsi ini juga menyimpan konfigurasi dalam part_config, termasuk jumlah partisi yang harus disiapkan di muka (premake) dan jangka retensinya.

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

Retensi pg_partman dan run_maintenance

Atur retensi dalam part_config: retention menentukan ambang usia, sedangkan retention_keep_table menentukan apakah partisi lama dilepaskan (dipertahankan sebagai tabel mandiri) atau langsung dihapus.

Titik masuk tunggal run_maintenance_proc() kemudian melakukan roll-in partisi baru dan menerapkan retensi. Jadwalkan dengan pg_cron, dan selesai — tidak perlu memelihara DDL kustom.

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();

Pemeriksaan Singkat: Retensi Murah dan Daring

Anda menjalankan tabel yang dipartisi berdasarkan waktu dengan lalu lintas tinggi dan perlu menghapus partisi yang lebih lama dari 12 bulan setiap malam tanpa memblokir pembacaan dan penulisan aktif, sekaligus tetap menyediakan data yang dihapus untuk pengarsipan.

Jawab di pelajaran interaktif

Ringkasan

Sekarang Anda memiliki dua pola tepercaya untuk mengotomatiskan siklus hidup partisi:

Roll-in lebih awal: fungsi yang secara idempoten membuat partisi untuk N bulan berikutnya, dijalankan setiap hari, sehingga INSERT tidak pernah menemukan partisi yang hilang.
Roll-out dengan biaya rendah: DETACH PARTITION ... CONCURRENTLY (lalu arsipkan dan lakukan DROP) menghindari kunci ACCESS EXCLUSIVE yang lama dan tidak pernah menulis ulang data yang masih ada.
Jadwalkan: hubungkan keduanya ke pg_cron atau cron OS.
Atau gunakan pg_partman: create_parent + retensi part_config + run_maintenance_proc() sepenuhnya menggantikan DDL kustom.

Keputusan yang penting: lepaskan lalu hapus partisi agar retensi tetap murah dan daring, alih-alih melakukan pembersihan berbasis DELETE.

Gratis untuk memulai

Belajar SQL dengan tutor AI — gratis

Tulis dan jalankan kode asli di browser kamu, dapatkan bantuan instan dari tutor AI 24/7, dan lanjutkan di mana kamu tinggalkan di web atau aplikasi.

Kursus: 22
Pelajaran: 88

Mulai belajar gratis Lihat kursus lengkapnya

Pertanyaan yang Sering Diajukan

Apakah pelajaran “Mengotomatiskan Pembuatan dan Retensi Partisi” gratis?

Ya — teks lengkap “Mengotomatiskan Pembuatan dan Retensi Partisi” gratis dibaca di sini di web. Untuk praktiknya secara interaktif (editor kode bawaan dan tutor AI 24/7) dan buka sisa kursus PostgreSQL Performance & Query Optimization, upgrade ke CoddyKit PRO. Kursus PostgreSQL Performance & Query Optimization mencakup 4 pelajaran total.

Apa yang akan aku pelajari di “Mengotomatiskan Pembuatan dan Retensi Partisi”?

Bangun pekerjaan pemeliharaan dengan pg_partman atau DDL khusus untuk menambahkan partisi baru dan melepaskan partisi lama dengan biaya rendah. Kamu berlatih PostgreSQL Performance & Query Optimization dengan kode praktik yang langsung kamu jalankan di browser, dan tutor AI 24/7 menjawab pertanyaanmu saat kamu mengerjakan pelajaran ini.

Apakah aku perlu pengalaman untuk memulai PostgreSQL Performance & Query Optimization?

Tidak diperlukan pengalaman sebelumnya. PostgreSQL Performance & Query Optimization di CoddyKit dirancang untuk pemula hingga pelajar tingkat lanjut, jadi kamu bisa memulai di sini atau dari awal dan belajar sesuai kecepatan kamu sendiri. Ini adalah pelajaran 3 dari 4.

Berapa lama pelajaran “Mengotomatiskan Pembuatan dan Retensi Partisi” memakan waktu?

Sebagian besar pelajaran CoddyKit memakan waktu sekitar 5–10 menit. Setiap pelajaran ringkas dan interaktif, jadi kamu membuat kemajuan stabil dan melanjutkan dari tempat kamu tinggalkan di web dan aplikasi.

Bisakah aku menulis dan menjalankan kode dalam pelajaran PostgreSQL Performance & Query Optimization ini?

Ya. Setiap pelajaran PostgreSQL Performance & Query Optimization menyertakan editor kode bawaan, jadi kamu menulis dan menjalankan kode nyata langsung di browser dan mendapatkan umpan balik AI instan — tidak diperlukan penyiapan lokal.

Semua pelajaran dalam kursus ini

Memilih Kunci dan Strategi Partisi
Penyaringan Partisi saat Perencanaan dan Eksekusi
Mengotomatiskan Pembuatan dan Retensi Partisi
Memigrasikan Tabel Besar ke Partisi Secara Daring

← Kembali ke PostgreSQL Performance & Query Optimization