Arsitektur Retensi Data Postgres: Partisi vs Batch Delete

Dilema Retensi Data Skala Besar di PostgreSQL

Mengelola lifecycle data time-series, log audit, atau data analitik dengan volume jutaan baris per hari membutuhkan strategi pembersihan (data purging) yang terencana. Pendekatan standar yang paling sering diterapkan adalah mengeksekusi query DELETE terjadwal dalam ukuran batch tertentu. Namun, pada skala gigabyte hingga terabyte, pendekatan ini menimbulkan degradasi I/O, latensi query, dan fragmentasi disk yang parah.

Artikel ini membandingkan mekanisme internal arsitektur retensi data Postgres: partisi vs batch delete, menganalisis dampak MVCC engine, dan memberikan panduan implementasi partisi deklaratif native.

Anatomi Masalah: Mengapa Batch DELETE Gagal pada Skala Besar

PostgreSQL menggunakan model Multi-Version Concurrency Control (MVCC). Operasi DELETE tidak langsung menghapus data dari disk fisik. Postgres hanya menandai header tuple (field xmax) dengan ID transaksi pembuat operasi tersebut.

1. Table Bloat dan Vacuum Starvation

Tuple yang dihapus menjadi dead tuples. Dead tuples hanya dapat dibersihkan dan ruangnya dapat dipakai kembali oleh proses VACUUM. Jika rate DELETE lebih cepat dari throughput autovacuum, rasio dead tuples membengkak. Hal ini menyebabkan index bloat dan table bloat; query sequential scan atau index scan tetap harus membaca blok memori (buffer pages) yang berisi dead tuples.

2. Write-Ahead Logging (WAL) Amplification

Setiap operasi DELETE menulis record ke Write-Ahead Log (WAL). Menghapus 10 juta baris menghasilkan payload WAL yang masif. Beban I/O disk melonjak, proses checkpointing menjadi sering terjadi, dan beban replikasi streaming ke read replica meningkat drastis.

3. Lock Contention dan Deadlock

Menjalankan batch DELETE menggunakan transaksi berkepanjangan mengambil row-exclusive lock pada tabel target. Ini memicu pertentangan lock (contention) dengan transaksi concurrent UPDATE atau INSERT, serta berisiko memblokir proses query harian.

Pendekatan Alternatif: Native Declarative Partitioning

Sejak PostgreSQL 10, deklaratif table partitioning memungkinkan pemisahan tabel logis menjadi sub-tabel fisik berdasarkan kriteria kolom, umumnya rentang waktu (range partitioning). Untuk retensi data, membuang data lama tidak memerlukan modifikasi baris demi baris, melainkan operasi metadata catalog via DROP TABLE atau ALTER TABLE ... DETACH PARTITION.

Implementasi DDL Range Partitioning

Contoh berikut mendefinisikan tabel induk log metrik yang dipartisi berdasarkan rentang tanggal per bulan:

CREATE TABLE system_metrics (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    metric_name VARCHAR(64) NOT NULL,
    metric_value NUMERIC(12, 4) NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (recorded_at, id)
) PARTITION BY RANGE (recorded_at);

-- Partisi manual untuk bulan berjalan
CREATE TABLE system_metrics_y2026m03 PARTITION OF system_metrics
    FOR VALUES FROM ('2026-03-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

CREATE TABLE system_metrics_y2026m04 PARTITION OF system_metrics
    FOR VALUES FROM ('2026-04-01 00:00:00+00') TO ('2026-05-01 00:00:00+00');

Catatan Desain: PostgreSQL mensyaratkan setiap kolom pada Primary Key atau Unique Constraint menyertakan partition key (dalam kasus ini: recorded_at). Constraint foreign key global antar partisi tidak didukung secara langsung jika menunjuk ke partition key non-unik.

Automasi Retensi Data

1. Menggunakan Ekstensi pg_partman

Ekstensi pg_partman adalah solusi standar industri untuk otomatisasi pembuatan dan penghapusan partisi:

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- Konfigurasi partisi otomatis bulanan
SELECT partman.create_parent(
    p_parent_table => 'public.system_metrics',
    p_control => 'recorded_at',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 3
);

-- Atur kebijakan retensi: hapus partisi lebih dari 6 bulan
UPDATE partman.part_config 
SET retention = '6 months',
    retention_keep_table = false
WHERE parent_table = 'public.system_metrics';

Jalankan CALL partman.run_maintenance_proc(); melalui background worker PostgreSQL atau Linux cron job terjadwal.

2. Zero-Downtime Purging dengan DETACH CONCURRENTLY

Jika menggunakan PostgreSQL 14 ke atas, hindari locking lama pada tabel induk saat menghapus partisi berukuran besar dengan memanfaatkan DETACH CONCURRENTLY:

-- Lepas partisi tanpa memblokir pembacaan concurrent pada partisi lain
ALTER TABLE system_metrics DETACH PARTITION system_metrics_y2025m01 CONCURRENTLY;

-- Hapus tabel fisik secara terpisah setelah terlepas dari tabel induk
DROP TABLE system_metrics_y2025m01;

Evaluasi Trade-off dan Kompleksitas Query

• Partition Pruning: Pastikan parameter enable_partition_pruning = on selalu aktif. Query yang menyertakan filter pada recorded_at hanya akan memindai sub-tabel yang relevan, menghemat scanning I/O secara masif.
• Query Tanpa Partition Key: Jika query analitik tidak menyertakan range timestamp, Postgres harus memindai seluruh sub-partisi (scatter-gather scanning), yang dapat memperlambat performa perencanaan query (planner overhead).
• Jumlah Partisi Maksimal: Hindari membuat partisi terlalu granular (misal: per jam untuk data beberapa tahun). Jumlah total sub-partisi dianjurkan berada di bawah ribuan agar konsumsi memori untuk catalog caching tidak membengkak.

Panduan Keputusan

Gunakan Batch DELETE jika volume data yang dihapus harian relatif kecil (di bawah ratusan ribu baris), struktur skema memerlukan foreign key kompleks yang tidak kompatibel dengan partisi, dan tabel jarang mengalami sequential scan berat.

Gunakan Table Partitioning jika volume data tumbuh teratur berdasarkan rentang waktu, data yang harus dibersihkan berskala jutaan baris per batch, dan disk space harus segera dikembalikan ke sistem operasi tanpa memicu degradasi autovacuum.