Mengelola partisi PostgreSQL dengan ekstensi pg_partman

Partisi tabel PostgreSQL menyediakan kerangka kerja untuk penanganan input data dan laporan performa tinggi. Gunakan partisi untuk basis data yang memerlukan input data dalam jumlah besar dengan cepat. Partisi juga menyediakan kueri tabel besar yang lebih cepat. Partisi membantu memelihara data tanpa memengaruhi instans basis data karena sumber daya I/O yang diperlukan lebih sedikit.

Dengan partisi, Anda dapat membagi data menjadi beberapa bagian berukuran kustom untuk diproses. Misalnya, Anda dapat membagi data deret waktu ke dalam berbagai rentang seperti per jam, harian, mingguan, bulanan, triwulanan, tahunan, kustom, atau kombinasinya. Untuk contoh data deret waktu, jika Anda membagi tabel berdasarkan jam, setiap partisi akan berisi data per satu jam. Jika Anda membagi tabel deret waktu berdasarkan hari, partisi akan berisi data per hari, dan seterusnya. Kunci partisi mengontrol ukuran partisi.

Saat Anda menggunakan perintah SQL INSERT atau UPDATE pada tabel yang dipartisi, mesin basis data merutekan data ke partisi yang sesuai. Partisi tabel PostgreSQL yang menyimpan data adalah tabel turunan dari tabel utama.

Selama pembacaan kueri basis data, pengoptimal PostgreSQL memeriksa klausul WHERE pada kueri dan, jika memungkinkan, mengarahkan pemindaian basis data hanya untuk partisi yang relevan.

Mulai versi 10, PostgreSQL menggunakan partisi deklaratif untuk mengimplementasikan partisi tabel. Ini juga dikenal sebagai partisi PostgreSQL asli. Sebelum PostgreSQL versi 10, Anda menggunakan pemicu untuk mengimplementasikan partisi.

Partisi tabel PostgreSQL menyediakan fitur berikut:

• Pembuatan partisi baru setiap saat.

• Rentang partisi bervariasi.

• Partisi yang dapat dilepas dan dapat dipasang kembali menggunakan pernyataan bahasa definisi data (DDL).

  Sebagai contoh, partisi yang dapat dilepas berguna untuk menghapus data historis dari partisi utama, tetapi menyimpan data historis untuk analisis.

• Partisi baru mewarisi properti tabel basis data induk, termasuk yang berikut ini:

  • Indeks

  • Kunci primer, yang harus berisi kolom kunci partisi

  • Kunci asing

  • Batasan pemeriksaan

  • Referensi

• Membuat indeks untuk seluruh tabel atau partisi tertentu.

Anda tidak dapat mengubah skema partisi individual. Namun, Anda dapat mengubah tabel induk (seperti menambahkan kolom baru), yang disebarkan ke partisi.

Ikhtisar ekstensi pg_partman PostgreSQL

Anda dapat menggunakan ekstensi pg_partman PostgreSQL untuk mengotomatiskan pembuatan dan pemeliharaan partisi tabel. Untuk informasi umum selengkapnya, lihat Manajer Partisi PG dalam dokumentasi pg_partman.

catatan

Ekstensi pg_partman didukung pada RDS for PostgreSQL versi 12.5 dan yang lebih tinggi.

Alih-alih membuat setiap partisi secara manual, Anda dapat mengonfigurasi pg_partman dengan pengaturan berikut:

• Tabel yang akan dipartisi

• Jenis partisi

• Kunci partisi

• Granularitas partisi

• Opsi pra-pembuatan dan manajemen partisi

Setelah membuat tabel yang dipartisi PostgreSQL, daftarkan dengan pg_partman dengan memanggil fungsi create_parent. Tindakan ini akan membuat partisi yang diperlukan berdasarkan parameter yang Anda teruskan ke fungsi.

Ekstensi pg_partman juga menyediakan fungsi run_maintenance_proc, yang dapat Anda panggil sesuai jadwal untuk secara otomatis mengelola partisi. Untuk memastikan bahwa partisi yang tepat dibuat sesuai kebutuhan, jadwalkan fungsi ini untuk berjalan secara berkala (seperti per jam). Anda juga dapat memastikan bahwa partisi secara otomatis dibatalkan.

Mengaktifkan ekstensi pg_partman

Jika Anda memiliki beberapa basis data di dalam instans DB PostgreSQL yang partisinya ingin Anda kelola, aktifkan ekstensi pg_partman secara terpisah untuk setiap basis data. Untuk mengaktifkan ekstensi pg_partman untuk basis data tertentu, buat skema pemeliharaan partisi, kemudian buat ekstensi pg_partman seperti berikut.

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

catatan

Untuk membuat ekstensi pg_partman, pastikan Anda memiliki hak istimewa rds_superuser.

Jika Anda menerima kesalahan seperti berikut, berikan hak istimewa rds_superuser untuk akun tersebut atau gunakan akun pengguna super Anda.

ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.

Untuk memberikan hak istimewa rds_superuser, hubungkan dengan akun pengguna super Anda dan jalankan perintah berikut.

GRANT rds_superuser TO user-or-role;

Untuk contoh yang menunjukkan penggunaan ekstensi pg_partman, kita gunakan contoh tabel dan partisi basis data berikut. Basis data ini menggunakan tabel yang dipartisi berdasarkan stempel waktu. Skema data_mart berisi tabel bernama events dengan kolom bernama created_at. Pengaturan berikut disertakan dalam tabel events:

• Kunci primer event_id dan created_at, yang harus memiliki kolom yang digunakan untuk memandu partisi.

• Batasan pemeriksaan ck_valid_operation guna menerapkan nilai untuk kolom tabel operation.

• Dua kunci asing, yang salah satunya (fk_orga_membership)) menunjuk ke tabel eksternal organization dan kunci lainnya (fk_parent_event_id) adalah kunci asing referensi mandiri.

• Dua indeks, yang salah satunya (idx_org_id) untuk kunci asing dan indeks lainnya (idx_event_type) untuk jenis peristiwa.

Pernyataan DDL berikut membuat objek ini, yang secara otomatis disertakan pada setiap partisi.

CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
        org_name TEXT,
        CONSTRAINT pk_organization PRIMARY KEY (org_id)  
    );

CREATE TABLE data_mart.events(
        event_id        BIGSERIAL, 
        operation       CHAR(1), 
        value           FLOAT(24), 
        parent_event_id BIGINT, 
        event_type      VARCHAR(25), 
        org_id          BIGSERIAL, 
        created_at      timestamp, 
        CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), 
        CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), 
        CONSTRAINT fk_orga_membership 
            FOREIGN KEY(org_id) 
            REFERENCES data_mart.organization (org_id),
        CONSTRAINT fk_parent_event_id 
            FOREIGN KEY(parent_event_id, created_at) 
            REFERENCES data_mart.events (event_id,created_at)
    ) PARTITION BY RANGE (created_at);

CREATE INDEX idx_org_id     ON  data_mart.events(org_id);
CREATE INDEX idx_event_type ON  data_mart.events(event_type);

Mengonfigurasi partisi menggunakan fungsi create_parent

Setelah mengaktifkan ekstensi pg_partman, gunakan fungsi create_parent untuk mengonfigurasi partisi di dalam skema pemeliharaan partisi. Contoh berikut menggunakan contoh tabel events yang dibuat di Mengaktifkan ekstensi pg_partman. Panggil fungsi create_parent seperti berikut.

SELECT partman.create_parent( p_parent_table => 'data_mart.events',
 p_control => 'created_at',
 p_type => 'native',
 p_interval=> 'daily',
 p_premake => 30);

Parameternya adalah sebagai berikut:

• p_parent_table – Tabel induk yang dipartisi. Tabel ini harus sudah ada dan sepenuhnya memenuhi syarat, termasuk skemanya.

• p_control – Kolom yang menjadi dasar pembuatan partisi. Jenis data harus bilangan bulat atau berbasis waktu.

• p_type – Jenisnya adalah 'native' atau 'partman'. Anda biasanya menggunakan jenis native untuk peningkatan performa dan fleksibilitas. Jenis partman bergantung pada warisan.

• p_interval – Interval waktu atau rentang bilangan bulat untuk setiap partisi. Contoh nilainya termasuk daily, per jam, dan sebagainya.

• p_premake – Jumlah partisi yang akan dibuat terlebih dahulu untuk mendukung sisipan baru.

Untuk keterangan lengkap tentang fungsi create_parent, lihat Fungsi Pembuatan dalam dokumentasi pg_partman.

Mengonfigurasi pemeliharaan partisi menggunakan fungsi run_maintenance_proc

Anda dapat menjalankan operasi pemeliharaan partisi untuk secara otomatis membuat partisi baru, melepaskan partisi, atau menghapus partisi lama. Pemeliharaan partisi bergantung pada fungsi run_maintenance_proc pada ekstensi pg_partman dan pg_cron, yang memulai penjadwal internal. Penjadwal pg_cron secara otomatis mengeksekusi pernyataan, fungsi, dan prosedur SQL yang ditetapkan dalam basis data Anda.

Contoh berikut menggunakan contoh tabel events yang dibuat di Mengaktifkan ekstensi pg_partman untuk mengatur operasi pemeliharaan partisi agar berjalan secara otomatis. Sebagai prasyarat, tambahkan pg_cron ke parameter shared_preload_libraries dalam grup parameter instans DB.

CREATE EXTENSION pg_cron;

UPDATE partman.part_config 
SET infinite_time_partitions = true,
    retention = '3 months', 
    retention_keep_table=true 
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

Berikut penjelasan langkah demi langkah untuk contoh sebelumnya:

1. Modifikasi grup parameter yang terkait dengan instans DB Anda dan tambahkan pg_cron ke nilai parameter shared_preload_libraries. Untuk menerapkan perubahan, instans DB harus dimulai ulang. Untuk informasi selengkapnya, lihat Memodifikasi parameter dalam grup parameter DB.

2. Jalankan perintah CREATE EXTENSION pg_cron; menggunakan akun yang memiliki izin rds_superuser. Tindakan ini akan mengaktifkan ekstensi pg_cron. Untuk informasi selengkapnya, lihat Menjadwalkan pemeliharaan dengan ekstensi pg_cron PostgreSQL.

3. Jalankan perintah UPDATE partman.part_config guna menyesuaikan pengaturan pg_partman untuk tabel data_mart.events.

4. Jalankan perintah SET . . . untuk mengonfigurasi tabel data_mart.events dengan klausul berikut:

   1. infinite_time_partitions = true, – Mengonfigurasi tabel untuk dapat secara otomatis membuat partisi baru tanpa batas.

   2. retention = '3 months', – Mengonfigurasi tabel agar memiliki retensi maksimum tiga bulan.

   3. retention_keep_table=true – Mengonfigurasi tabel agar ketika periode retensi sudah habis, tabel tidak akan dihapus secara otomatis. Sebaliknya, partisi yang lebih lama dari periode retensi hanya dilepaskan dari tabel induk.

5. Jalankan perintah SELECT cron.schedule . . . untuk membuat panggilan fungsi pg_cron. Panggilan ini menetapkan frekuensi penjadwal menjalankan prosedur pemeliharaan pg_partman, partman.run_maintenance_proc. Untuk contoh ini, prosedur berjalan setiap jam.

Untuk keterangan lengkap tentang fungsi run_maintenance_proc, lihat Fungsi Pemeliharaan dalam dokumentasi pg_partman.