Menjalankan PostgreSQL di atas FreeBSD dengan OpenZFS menawarkan redundansi data tinggi, checksum bawaan, dan fitur snapshot instan. Namun, konfigurasi default kedua sistem ini saling bertolak belakang. Tanpa penyesuaian, arsitektur Copy-on-Write (CoW) ZFS dan manajemen memori FreeBSD menyebabkan write amplification berat, fragmentasi blok, dan latensi transaksi tinggi.

Artikel ini membahas konfigurasi teknis untuk mengoptimalkan alignment ZFS recordsize, kernel shared memory, alokasi memori PostgreSQL, strategi indexing, serta verifikasi performa menggunakan pgbench.

1. Penataan Dataset ZFS dan Optimasi Recordsize

Secara default, PostgreSQL membaca dan menulis data ke disk dalam satuan page 8KB. Sebaliknya, ZFS menggunakan ukuran blok dinamis hingga recordsize=128k. Jika PostgreSQL mengubah blok 8KB pada dataset dengan recordsize 128KB, ZFS terpaksa membaca seluruh 128KB, memodifikasinya di memori, lalu menulis ulang 128KB ke lokasi baru (Read-Modify-Write).

Untuk mencegah write amplification, pisahkan data heap/indeks, Write-Ahead Log (WAL), dan file log database ke dalam dataset terpisah:

  • pg_data (Heap & Index): Gunakan recordsize=8k (atau 16KB jika PostgreSQL dikompilasi khusus dengan --with-blocksize=16).
  • pg_wal: Transaksi WAL ditulis secara sekuensial dalam blok hingga 64KB, sehingga recordsize=64k memberikan throughput terbaik.
  • logbias: Ubah ke throughput pada tabel analitik atau biarkan latency untuk OLTP guna meminimalkan latensi fsync ZFS Intent Log (ZIL).
# Buat struktur dataset khusus PostgreSQL
zfs create -o mountpoint=/var/db/postgres zroot/postgres
zfs create -o recordsize=8k  -o atime=off -o compression=lz4 zroot/postgres/data
zfs create -o recordsize=64k -o atime=off -o compression=lz4 zroot/postgres/wal

# Pastikan kepemilikan direktori
chown -R postgres:postgres /var/db/postgres

2. Tuning Kernel FreeBSD: Shared Memory & IPC Sysctl

PostgreSQL mengandalkan System V IPC dan POSIX shared memory untuk komunikasi antar-worker. FreeBSD memiliki batas kernel default yang konservatif terhadap shared memory (sysvshm) dan limit transfer soket.

Tambahkan parameter berikut ke /etc/sysctl.conf untuk server dengan RAM 32GB (sesuaikan dengan kapasitas server Anda):

# Alokasi shared memory (dalam bytes atau pages)
# kern.ipc.shmmax: 8GB (8589934592 bytes)
# kern.ipc.shmall: 8GB / 4096 (ukuran page FreeBSD) = 2097152 pages
kern.ipc.shmmax=8589934592
kern.ipc.shmall=2097152
kern.ipc.shmmni=4096
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semume=256
kern.ipc.semmnu=256

# Optimasi jaringan & I/O buffer socket
kern.ipc.somaxconn=1024
net.inet.tcp.sendbuf_max=16777216
net.inet.tcp.recvbuf_max=16777216

Muat konfigurasi tanpa me-reboot mesin:

sysctl -f /etc/sysctl.conf

3. Konfigurasi postgresql.conf

Saat berjalan di atas ZFS, PostgreSQL dan Adaptive Replacement Cache (ARC) ZFS berbagi RAM yang sama. Nilai shared_buffers sebaiknya diatur ke sekitar 15%–25% dari total RAM agar ARC tetap memiliki ruang untuk menampung cache metadata dan data terkompresi.

Edit file postgresql.conf:

# Memory Management
shared_buffers = 6GB
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 20GB

# Checkpoint & WAL
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 8GB
checkpoint_completion_target = 0.9

# Optimasi ZFS CoW
# Mengurangi bloat WAL karena CoW menjamin page tidak corrupt akibat partial write
full_page_writes = off

# Perencana Query (Costing)
random_page_cost = 1.1
effective_io_concurrency = 200
Peringatan Keamanan Data: Parameter full_page_writes = off aman digunakan pada ZFS karena integritas blok dijaga oleh checksum dan CoW. Namun, pastikan dataset ZFS tidak menggunakan non-standard sync setting seperti sync=disabled pada data produksi.

4. Strategi Indexing pada Workload I/O Tinggi: B-Tree vs GiST

Pada dataset dengan jutaan baris di atas ZFS CoW, struktur indeks menentukan frekuensi modifikasi blok disk. Pemilihan strategi indeks yang salah mempercepat timbulnya fragmentasi.

  • B-Tree: Ideal untuk data skalar berurutan (ID, timestamp, relasi kunci foreign). B-Tree memiliki performa pencarian point query O(log N). ZFS 8KB recordsize sangat cocok dengan ukuran leaf node B-Tree default.
  • GiST (Generalized Search Tree): Digunakan untuk data geometris (PostGIS), full-text search, dan data interval/range. GiST menghasilkan frekuensi split node yang lebih tinggi dibandingkan B-Tree saat penambahan data masif. Hal ini memicu amplifikasi tulis di ZFS.

Jika workload membutuhkan pencarian teks parsial atau rentang waktu intensif, gunakan BRIN (Block Range Index) alih-alih B-Tree atau GiST jika data dimasukkan secara sekuensial:

-- B-Tree standar untuk identitas unik / lookup langsung
CREATE INDEX idx_orders_user_id ON orders USING btree (user_id);

-- GiST untuk geolokasi atau rentang data
CREATE INDEX idx_locations_geom ON user_locations USING gist (coordinates);

-- Alternatif hemat I/O untuk log transaksi masif berurutan waktu (BRIN)
-- Ukuran indeks jauh lebih kecil, meminimalkan write amplification di ZFS
CREATE INDEX idx_logs_created_at ON access_logs USING brin (created_at);

5. Validasi Performa Menggunakan pgbench

Uji efektivitas konfigurasi baru menggunakan tool bawaan PostgreSQL, pgbench. Inisialisasi basis data uji dengan skala 100 (~1,5 GB data):

# Buat basis data benchmark
createdb -U postgres benchmark_db

# Inisialisasi tabel dengan scale factor 100
pgbench -i -s 100 -U postgres benchmark_db

# Jalankan uji transaksi OLTP selama 300 detik dengan 16 klien konkurensi
pgbench -c 16 -j 4 -T 300 -U postgres benchmark_db

Pantau status disk ZFS dari terminal FreeBSD lain selama benchmark berlangsung:

zpool iostat -v 2

Perhatikan metrik I/O bandwidth. Jika konfigurasi recordsize=8k dan shared memory berhasil diterapkan, nilai write rate pada pool akan jauh lebih rendah dibandingkan saat menggunakan recordsize default 128k untuk beban transaksi per detik (TPS) yang sebanding.