Tutorial Real-Time CDC PostgreSQL ke ClickHouse Pakai Redpanda di VPS

Tantangan Sync Data OLTP ke OLAP dan Solusi Redpanda Connect

Pernah mengalami situasi ini? Lagi santai menikmati kopi, mendadak ada notifikasi monitoring server. Database PostgreSQL utama aplikasi meledak dengan penggunaan CPU mencapai 100%. Setelah dicek, ternyata penyebabnya sepele: tim bisnis sedang menjalankan query laporan bulanan di database produksi.

Masalah ini seperti toko fisik yang ramai antrean. Kasir bertugas melayani pembayaran pelanggan secara cepat. Tiba-tiba, manajer toko datang membawa tumpukan dokumen dan meminta kasir menghitung total penjualan tahunan di tempat saat itu juga. Antrean kasir langsung macet total, pelanggan lain terganggu.

Itulah gambaran saat kita memaksakan database OLTP (Online Transaction Processing) seperti PostgreSQL untuk menangani beban kerja analitik berat atau OLAP (Online Analytical Processing). PostgreSQL didesain untuk transaksi cepat per baris data, sedangkan query analitik membutuhkan pembacaan jutaan baris data sekaligus.

Solusinya adalah memisahkan database transaksi dan database analitik. Data dipindahkan dari PostgreSQL ke database khusus OLAP seperti ClickHouse. Namun, tantangan terbesarnya ada pada metode pemindahan data tersebut agar tidak membebani PostgreSQL dan tetap mendapatkan data secara real-time.

Mengapa Memilih CDC dan Redpanda Connect?

Pendekatan tradisional menggunakan script CRON berkala setiap jam memiliki dua kelemahan utama. Pertama, data di database analitik tidak real-time. Kedua, saat script CRON berjalan, PostgreSQL akan kejatuhan beban query pembacaan data yang besar secara mendadak.

Change Data Capture (CDC) hadir memecahkan masalah ini. CDC bekerja dengan cara membaca log internal transaksi database. Di PostgreSQL, log ini disebut Write-Ahead Logging (WAL). Setiap ada perubahan data, PostgreSQL mencatatnya di WAL. Worker CDC membaca aliran data dari WAL secara langsung tanpa membebani engine query PostgreSQL.

Arsitektur CDC konvensional biasanya melibatkan Apache Kafka, Zookeeper, dan Debezium. Stack berbasis JVM (Java Virtual Machine) ini membutuhkan konsumsi RAM yang besar (minimal 4 GB hingga 8 GB). Jika dijalankan di VPS spesifikasi terbatas, RAM server akan langsung habis sebelum data pertama terkirim.

Redpanda Connect (sebelumnya bernama Benthos) hadir sebagai solusi hemat resource. Ditulis menggunakan bahasa Go, Redpanda Connect berupa satu file binary tunggal tanpa ketergantungan pada JVM. Memory footprint-nya sangat kecil (sering di bawah 50 MB), namun sanggup memproses ribuan event per detik dengan latency milidetik.

Setup PostgreSQL Logical Replication dan Worker Redpanda Connect

Berikut alur teknis penyiapan pipeline CDC hemat resource dari PostgreSQL ke ClickHouse menggunakan Redpanda Connect di VPS.

1. Konfigurasi PostgreSQL

Aktifkan fitur Logical Replication di PostgreSQL agar log WAL bisa dibaca oleh worker eksternal.

Buka file postgresql.conf dan pastikan parameter berikut diaktifkan:

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10

Parameter wal_level = logical menginstruksikan PostgreSQL untuk mencatat detail perubahan data per baris. Parameter max_wal_senders dan max_replication_slots mengatur batasan koneksi serta slot replikasi.

Restart service PostgreSQL untuk menerapkan perubahan:

sudo systemctl restart postgresql

Selanjutnya, buka terminal PostgreSQL (psql) untuk membuat user replikasi, memberikan hak akses, serta membuat Publication dan Replication Slot:

CREATE ROLE cdc_user WITH REPLICATION LOGIN PASSWORD 'password_rahasia_anda';
GRANT ALL PRIVILEGES ON DATABASE database_aplikasi TO cdc_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc_user;

-- Buat Publication untuk tabel transaksi
CREATE PUBLICATION cdc_publication FOR TABLE transactions;

-- Buat Logical Replication Slot manual
SELECT pg_create_logical_replication_slot('cdc_slot', 'pgoutput');

2. Konfigurasi Pipeline Redpanda Connect

Redpanda Connect menggunakan berkas konfigurasi YAML berbasis deklaratif. Buat berkas bernama pipeline.yaml di server VPS:

input:
  postgres_cdc:
    dsn: "postgres://cdc_user:password_rahasia_anda@127.0.0.1:5432/database_aplikasi?sslmode=disable"
    slot_name: "cdc_slot"
    publisher_name: "cdc_publication"
    tables:
      - "public.transactions"

pipeline:
  processors:
    - mapping: |
        root.id = this.id
        root.user_id = this.user_id
        root.amount = this.amount
        root.status = this.status
        root.updated_at = this.updated_at
        root._version = this.lsn

output:
  clickhouse:
    dsn: "clickhouse://127.0.0.1:9000/default"
    table: "transactions_target"
    columns:
      - id
      - user_id
      - amount
      - status
      - updated_at
      - _version
    batching:
      count: 1000
      period: 1s

Pada bagian pipeline.processors, bahasa transformasi Bloblang digunakan untuk memetakan kolom database dan mengonversi nilai LSN (Log Sequence Number) PostgreSQL menjadi kolom _version. Nilai versi ini berguna untuk memproses operasi update di ClickHouse.

Jalankan worker Redpanda Connect dengan perintah berikut:

redpanda-connect run ./pipeline.yaml

Optimasi Ingestion ClickHouse Engine dan Handling Schema Changes

ClickHouse dirancang untuk memproses penulisan data dalam bentuk batch besar. Penulisan data satu per satu akan memicu masalah pada storage engine ClickHouse.

1. Bahaya Insert Per Baris di ClickHouse

Setiap eksekusi perintah INSERT di ClickHouse menciptakan partisi file baru di dalam disk storage. Jika data dimasukkan per satu baris secara berulang, ClickHouse akan mengalami error Too many parts in all data parts in table.

Konfigurasi batching pada Redpanda Connect menyelesaikan masalah ini:

batching:
  count: 1000
  period: 1s

Konfigurasi tersebut menahan data perubahan hingga terkumpul 1.000 baris atau mencapai durasi 1 detik sebelum dikirim sekaligus ke ClickHouse. Penulisan menjadi efisien tanpa mengorbankan sifat real-time.

2. Menangani Update Data Menggunakan ReplacingMergeTree

Tabel ClickHouse standar tidak dirancang untuk operasi UPDATE langsung per baris. Untuk menangani perubahan data dari PostgreSQL, gunakan engine ReplacingMergeTree.

Eksekusi perintah SQL berikut di ClickHouse untuk membuat tabel tujuan:

CREATE TABLE transactions_target
(
    id UInt64,
    user_id UInt64,
    amount Float64,
    status String,
    updated_at DateTime,
    _version UInt64
)
ENGINE = ReplacingMergeTree(_version)
ORDER BY (id);

Engine ReplacingMergeTree menggunakan kolom _version untuk menentukan baris data paling baru. Saat operasi update terjadi di PostgreSQL, Redpanda Connect memasukkan baris baru ke ClickHouse dengan nilai _version yang lebih tinggi. Proses penggabungan (merge) dan penghapusan data lama dilakukan secara otomatis di background oleh ClickHouse.

Untuk memastikan query analitik selalu membaca data versi terbaru sebelum proses merge background berjalan, gunakan modifier FINAL:

SELECT * FROM transactions_target FINAL WHERE status = 'COMPLETED';

3. Penanganan Perubahan Skema (Schema Evolution)

Saat terjadi penambahan kolom baru di PostgreSQL, pipeline CDC dapat terhambat jika pemetaan skema belum disesuaikan. Gunakan fungsi .or() pada pemetaan Bloblang Redpanda Connect untuk memberikan nilai default pada kolom baru:

root.new_column = this.new_column.or("default_value")

Pendekatan ini memastikan alur pemrosesan data tetap berjalan meskipun terdapat perbedaan struktur kolom sementara waktu.

Best Practice Monitoring Latensi dan Performa

Pemantauan alur data secara berkala diperlukan untuk memastikan latensi pipeline tetap terjaga di lingkungan produksi.

1. Monitoring Replication Lag

Periksa selisih log replikasi PostgreSQL dengan menjalankan query berikut di PostgreSQL:

SELECT 
    slot_name,
    active,
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes
FROM pg_stat_replication_slots;

Jika nilai lag_bytes meningkat secara terus-menerus, periksa penggunaan CPU pada worker Redpanda Connect atau I/O bottleneck pada disk ClickHouse.

2. Tuning Batas Batching

Untuk volume transaksi tinggi (ribuan event per detik), tingkatkan batas count pada konfigurasi batching Redpanda Connect ke angka 5.000 atau 10.000 baris untuk mengurangi beban I/O ClickHouse.

Kesimpulan dan Langkah Selanjutnya

Pemisahan beban kerja OLTP dan OLAP tidak harus selalu membutuhkan infrastruktur kompleks bernilai tinggi. Kombinasi Logical Replication PostgreSQL, Redpanda Connect, dan ClickHouse ReplacingMergeTree menyediakan arsitektur CDC real-time yang stabil dan hemat RAM untuk server VPS.

Langkah praktis yang dapat diterapkan:

• Pilih 1 tabel transaksi utama yang paling sering di-query oleh tim analitik.
• Jalankan 1 instance Redpanda Connect di VPS menggunakan konfigurasi di atas.
• Amati penurunan penggunaan CPU pada PostgreSQL dan ukur kecepatan agregasi query di ClickHouse.