Total Views: 130

PostgreSQL dikenal sebagai database paling canggih di dunia open-source. Namun, tanpa konfigurasi yang tepat, fitur canggihnya bisa terasa lambat. Berikut adalah langkah Optimasi PostgreSQL teknisnya.

1. Strategi Indexing yang Cerdas

PostgreSQL memiliki tipe indeks yang lebih kaya dibanding MySQL.

  • B-Tree Index (Standar): Digunakan untuk perbandingan =, >, <, dll.SQLCREATE INDEX idx_user_email ON users(email);
  • Partial Index (Sangat Powerful): Hanya mengindeks baris yang memenuhi syarat. Ini menghemat ruang disk secara signifikan.SQL-- Hanya mengindeks user yang aktif CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';
  • GIN Index (Untuk Data JSONB): Jika Anda menyimpan data JSON, gunakan GIN agar pencarian di dalam JSON secepat kilat.SQLCREATE INDEX idx_json_data ON orders USING GIN (data_jsonb);

2. Gunakan “EXPLAIN ANALYZE”

Sebelum menyalahkan database, cek bagaimana query Anda dijalankan. Gunakan perintah ini untuk melihat bottleneck:

EXPLAIN ANALYZE SELECT * FROM sales WHERE total_price > 1000;

3. Vacuuming & Bloat Management

PostgreSQL menggunakan sistem MVCC (Multi-Version Concurrency Control). Saat data dihapus, baris lama tidak langsung hilang (menjadi dead tuples).

  • Pastikan Autovacuum aktif.
  • Gunakan query ini untuk melihat tabel mana yang paling banyak “sampah” (bloat):SQLSELECT relname, n_dead_tup FROM pg_stat_user_tables;

4. Optimasi PostgreSQL untuk Tabel Skala Besar (1M+ Rows)

a. Table Partitioning (Pembagian Tabel)

Jangan biarkan satu tabel menampung jutaan baris jika data tersebut bisa dipisah (misal berdasarkan waktu). Dengan Partitioning, PostgreSQL hanya akan memindai “potongan” tabel yang relevan.

Contoh Query (Declarative Partitioning):

-- Membuat tabel utama dengan partisi berdasarkan range tanggal
CREATE TABLE sales (
    id SERIAL,
    item_name TEXT,
    sale_date DATE NOT NULL
) PARTITION BY RANGE (sale_date);

-- Membuat partisi untuk tahun 2025
CREATE TABLE sales_2025 PARTITION OF sales
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

b. BRIN Index (Block Range Index)

Untuk tabel yang sangat besar (jutaan baris) dan datanya berurutan (seperti timestamp atau ID serial), indeks B-Tree biasa akan memakan ruang disk yang sangat besar. BRIN adalah solusinya.

  • Keunggulan: Ukuran indeks jauh lebih kecil (bisa 100x lebih kecil dari B-Tree).
CREATE INDEX idx_sales_brin ON sales USING BRIN (sale_date);

c. Parallel Query Tuning

Secara default, PostgreSQL mungkin hanya menggunakan 1 core CPU untuk satu query. Untuk 1 juta baris, kita ingin Optimasi PostgreSQL agar menggunakan lebih banyak “tenaga”. Ubah setting di postgresql.conf:

max_parallel_workers_per_gather = 4  -- Sesuaikan dengan jumlah core CPU Anda
min_parallel_table_scan_size = 8MB

d. Index Maintenance (REINDEX)

Tabel yang sering di-update pada skala jutaan baris akan mengalami “Bloat” (ruang kosong yang tidak efisien). Lakukan reindex secara berkala tanpa mengunci tabel (Concurrent):

REINDEX INDEX CONCURRENTLY idx_user_email;

Download & Instalasi PostgreSQL Terbaru

Untuk mendapatkan performa terbaik (seperti fitur Parallel Query yang lebih matang), pastikan Anda menggunakan versi terbaru.