Optimasi Bulk Upsert Jutaan Record: COPY vs Unnest di PostgreSQL

Tantangan Bulk Upsert Skala Besar

Melakukan ingest dan sinkronisasi puluhan juta baris data—seperti dataset identitas kependudukan—sering menimbulkan bottleneck I/O, lonjakan konsumsi memori, dan lock contention. Eksekusi INSERT ... ON CONFLICT baris demi baris tidak realistis karena overhead round-trip network dan logging WAL yang masif. Pendekatan bulk mutlak diperlukan tanpa memblokir pembacaan tabel produksi.

Dua metode utama di PostgreSQL untuk kebutuhan ini adalah: COPY ke staging table lalu di-merge, versus streaming batched UNNEST langsung ke target table.

Metode 1: COPY ke Staging Table + INSERT ON CONFLICT

Metode ini memisahkan proses ingest mentah dari evaluasi konflik. Data di-stream langsung ke temporary table atau unlogged table menggunakan protokol COPY, kemudian digabungkan ke tabel utama dengan satu query INSERT ... SELECT ON CONFLICT.

Implementasi SQL

-- 1. Buat temporary table tanpa WAL logging overhead
CREATE TEMP TABLE stage_identitas (
    nik CHAR(16) NOT NULL,
    nama VARCHAR(100) NOT NULL,
    alamat TEXT,
    updated_at TIMESTAMPTZ NOT NULL
) ON COMMIT DROP;

-- 2. Stream data via protokol binary/CSV COPY
COPY stage_identitas (nik, nama, alamat, updated_at) 
FROM '/path/to/identitas_chunk.csv' WITH (FORMAT csv, HEADER true);

-- 3. Analisis statistik temp table untuk mengoptimalkan join
ANALYZE stage_identitas;

-- 4. Eksekusi upsert ke tabel produksi
INSERT INTO identitas (nik, nama, alamat, updated_at)
SELECT nik, nama, alamat, updated_at 
FROM stage_identitas
ORDER BY nik -- Mengurangi deadlocks jika ada concurrent worker
ON CONFLICT (nik) DO UPDATE SET
    nama = EXCLUDED.nama,
    alamat = EXCLUDED.alamat,
    updated_at = EXCLUDED.updated_at
WHERE identitas.updated_at < EXCLUDED.updated_at;

Metode 2: Streaming Batched UNNEST

Metode ini menghindari pembentukan temporary table di database. Aplikasi mengirim data dalam batch array via parameter query terikat (biasanya batch size 5.000 hingga 10.000 record) menggunakan fungsi unnest().

Implementasi SQL

INSERT INTO identitas (nik, nama, alamat, updated_at)
SELECT * FROM UNNEST(
    $1::char(16)[],
    $2::varchar(100)[],
    $3::text[],
    $4::timestamptz[]
) AS t(nik, nama, alamat, updated_at)
ON CONFLICT (nik) DO UPDATE SET
    nama = EXCLUDED.nama,
    alamat = EXCLUDED.alamat,
    updated_at = EXCLUDED.updated_at
WHERE identitas.updated_at < EXCLUDED.updated_at;

Analisis Latensi dan Memory Overhead

Pendekatan UNNEST memiliki limitasi alokasi parser Postgres ketika array parameter terlalu besar, berpotensi memicu overhead alokasi memori pada PostgreSQL backend worker. Pendekatan COPY memanfaatkan transfer block I/O tingkat rendah engine PostgreSQL, meminimalkan overhead parsing CPU.

Tuning Database Engine untuk Ingestion Berat

Saat menyuntikkan puluhan juta record, konfigurasi default PostgreSQL akan memicu throttling akibat penulisan checkpoint dan I/O disk.

1. checkpoint_completion_target

Setel parameter ini ke 0.9. Nilai ini menyebarkan I/O penulisan checkpoint ke 90% durasi interval, mencegah disk I/O spikes yang mengunci I/O subsistem database.

ALTER SYSTEM SET checkpoint_completion_target = 0.9;

2. maintenance_work_mem

Alokasikan memori kerja pemeliharaan yang cukup untuk proses re-indexing dan sorting temp table. Default 64MB tidak cukup untuk dataset jutaan baris.

SET maintenance_work_mem = '2GB';

3. Strategi Indexing Temporary Table

Untuk dataset di atas 10 juta baris pada staging table, tambahkan index pada conflict column di tabel staging sebelum menjalankan merge statement untuk mempercepat Hash Join atau Merge Join:

CREATE INDEX idx_stage_nik ON stage_identitas(nik);

Catatan: Untuk tabel produksi, jangan menghapus primary index saat live production. Namun, jika ada secondary index yang tidak krusial untuk integritas constraint (seperti index pencarian full-text atau trigram), nonaktifkan atau drop sementara sebelum batching besar, lalu bangun kembali menggunakan CREATE INDEX CONCURRENTLY.

Query Plan: EXPLAIN ANALYZE

Berikut potongan profil eksekusi untuk merge step pada 5 juta record staging table:

EXPLAIN (ANALYZE, BUFFERS) 
INSERT INTO identitas SELECT * FROM stage_identitas ON CONFLICT (nik) DO NOTHING;

-- Ekstrak Execution Plan:
Insert on identitas  (cost=0.00..85420.00 rows=5000000 width=128) (actual time=12401.120..12401.120 rows=0 loops=1)
  Conflict Resolution: NOTHING
  Conflict Arbiter Indexes: identitas_pkey
  Buffers: shared hit=482104 read=11203 dirtied=24010, temp read=0 written=0
  ->  Seq Scan on stage_identitas  (cost=0.00..85420.00 rows=5000000 width=128) (actual time=0.015..820.450 rows=5000000 loops=1)
Planning Time: 0.125 ms
Execution Time: 12401.350 ms

Plan menunjukkan engine membaca data staging secara linear (sequential scan berbiaya rendah) dan menerapkan conflict resolution langsung ke buffer shared target table tanpa round-trip overhead antar baris.

Rekomendasi Keputusan Arsitektur

• Gunakan COPY via Temp/Staging Table jika proses berbentuk ETL/ELT batch besar harian, import dump file besar, atau inisialisasi awal database identitas.
• Gunakan Streaming Batched UNNEST jika aplikasi menerima event-driven updates berbasis message broker (seperti Kafka/RabbitMQ) dengan batch micro-sinkronisasi (1.000–5.000 pesan per commit).