Teknik Optimasi PostgreSQL yang Tidak Konvensional

• Memperkenalkan 3 pendekatan kreatif untuk meningkatkan kinerja kueri PostgreSQL dengan keluar dari pola lama, bukan sekadar menambah indeks atau menulis ulang kueri
• Menghilangkan full table scan berbasis CHECK constraint, optimasi kardinalitas rendah dengan indeks berbasis fungsi, dan menerapkan unique constraint lewat Hash index
• 1. Memanfaatkan pengaturan constraint_exclusion untuk mencegah scan yang tidak perlu pada kueri dengan kondisi yang salah
• 2. Menggunakan indeks berbasis fungsi dan virtual generated column untuk mengurangi ukuran indeks dan menjaga konsistensi kueri
• 3. Menggabungkan Hash index dan exclusion constraint untuk menerapkan unique constraint secara efisien pada kolom teks berukuran besar, sekaligus sangat menghemat ruang penyimpanan

---

Menghilangkan full table scan berbasis CHECK constraint

• Meski ada CHECK constraint yang hanya mengizinkan nilai 'free' dan 'pro' pada kolom plan, jika kueri yang salah dijalankan dengan 'Pro', PostgreSQL tetap melakukan scan ke seluruh tabel
  • Pada rencana eksekusi, seluruh 100 ribu baris dibaca, tetapi hasil aktualnya 0 baris
• Jika parameter constraint_exclusion diatur ke 'on', PostgreSQL mempertimbangkan constraint dan melewati scan sepenuhnya
  • Waktu eksekusi turun dari 7.4ms menjadi 0.008ms
• Nilai default-nya adalah 'partition', dan pada kueri sederhana justru planning overhead bisa lebih besar
  • Namun, dalam lingkungan BI dan reporting, pengguna sering memasukkan kondisi yang salah sehingga pengaturan 'on' berguna

Optimasi kardinalitas rendah dengan indeks berbasis fungsi

• Pada tabel sale dengan 10 juta data penjualan, kueri agregasi pendapatan harian membutuhkan 627ms karena melakukan scan penuh
• Jika menambahkan indeks B-Tree pada kolom sold_at, waktunya turun menjadi 187ms, tetapi ukuran indeks membesar hingga 214MB
• Jika membuat indeks berbasis fungsi pada ekspresi date_trunc('day', sold_at), ukurannya turun menjadi 66MB dan waktu eksekusi makin cepat menjadi 145ms
  • Berkat kardinalitas yang rendah, deduplication indeks dapat dilakukan
• Namun, ekspresi kueri harus persis sama dengan definisi indeks, sehingga konsistensi ekspresi perlu dijaga
  • Untuk itu bisa dibuat VIEW yang memuat ekspresi yang sama, atau
  • Menambahkan virtual generated column yang didukung mulai PostgreSQL 18 untuk mengotomatiskan konsistensi
• Dengan virtual generated column, indeks akan digunakan secara otomatis, sehingga indeks kecil, kueri cepat, dan konsistensi ekspresi dapat diperoleh sekaligus
• Namun, pada PostgreSQL 18, pembuatan indeks pada virtual column masih belum didukung, dan direncanakan hadir pada versi 19 mendatang

Menerapkan unique constraint dengan Hash index

• Pada tabel urls yang menyimpan URL panjang, jika membuat indeks unik berbasis B-Tree untuk mencegah URL duplikat, ukuran indeks mencapai 154MB
• Hash index jauh lebih kecil karena tidak menyimpan nilai asli, hanya nilai hash-nya
  • PostgreSQL pada dasarnya tidak mendukung indeks Hash unik, tetapi
  • Dengan memanfaatkan exclusion constraint, kita bisa menerapkan unique constraint secara tidak langsung dalam bentuk EXCLUDE USING HASH (url WITH =)
• Dengan cara ini, penyisipan duplikat tetap menghasilkan error, dan kinerja kueri juga lebih cepat daripada B-Tree (0.022ms vs 0.046ms)
• Ukuran indeks menjadi 32MB, yakni lebih dari 5 kali lebih kecil dibanding B-Tree
• Kekurangan:
  • Tidak bisa direferensikan oleh foreign key (REFERENCES constraint tidak memungkinkan)
  • Kompatibilitas terbatas dengan sintaks INSERT ... ON CONFLICT
  • Bisa diganti dengan ON CONFLICT ON CONSTRAINT atau sintaks MERGE
• Hash index cocok untuk menjamin keunikan pada kolom teks berukuran besar, dan menjadi alternatif hemat ruang ketika foreign key tidak diperlukan

[GN⁺]

Komentar Hacker News

• Indeksnya 214MB, sekitar setengah ukuran seluruh tabel
  Dari sudut pandang analis ini bagus, tetapi dari sisi performa tulis muncul masalah write amplification
  Desain indeks berubah tergantung rasio baca/tulis, dan itulah alasan adanya data warehouse atau read replica
  Jika harus melayani terlalu banyak pengguna, sebaiknya tidak menaruh indeks BI/OLAP di DB OLTP

  • Akan bagus jika PostgreSQL mendukung clustered index (Index Organized Table milik Oracle)
    Jika pola akses tabel konsisten, tabel itu sendiri bisa menjadi indeks dan memberi efisiensi tanpa write amplification

• Untuk contoh pertama, menurut saya lebih baik mendefinisikan Plan sebagai tipe enum
  Lebih ringan daripada teks, dan saat input filter salah akan menghasilkan error alih-alih hasil kosong sehingga lebih aman

• Tulisan yang luar biasa. Saya sudah memakai PostgreSQL dan MySQL selama puluhan tahun, tetapi setelah membaca ini saya merasa masih hanya tahu sebagian kecil dari kemungkinannya

  • Saya juga sudah memakai Postgres lebih dari 10 tahun, tetapi setiap kali membaca dokumentasinya saya masih merasa baru menggores permukaannya. Sistem yang benar-benar kuat
  • PostgreSQL itu seperti Emacs. Dari luar tampak sederhana, tetapi sebenarnya punya fleksibilitas setingkat sistem operasi

• Bagian paling menarik adalah sintaks MERGE yang disebutkan di akhir tulisan
  Biasanya saya menangani upsert dengan INSERT ... ON CONFLICT DO UPDATE, tetapi MERGE tampaknya lebih kuat dan bisa dipakai dalam lebih banyak situasi

  • MERGE sudah lama ada dalam standar SQL, tetapi Postgres menunda penerapannya karena masalah non-atomis pada model MVCC
    Ini juga dijelaskan di artikel blog pganalyze
    Secara pribadi saya lebih memilih INSERT ... ON CONFLICT, dan hanya memakai MERGE saat benar-benar perlu sambil sangat berhati-hati dengan penanganan error
  • Dari sisi konkurensi, INSERT ... ON CONFLICT lebih mudah diprediksi
    Lihat artikel perbandingan di modern-sql.com
  • Untuk insert batch skala besar, yang paling cepat adalah menggunakan COPY INTO dalam format binary. Overhead di sisi server hampir tidak ada

• Indeks BRIN yang tidak dibahas dalam tulisan juga menarik
  Jika datanya meningkat secara monoton, ini ideal sebagai indeks yang sangat kecil dan cepat

  • Tidak harus benar-benar data yang sepenuhnya monoton. Jika sebagian besar monoton, biasanya tetap bekerja dengan baik
    Misalnya untuk data timestamp yang diterima server, meski urutannya sedikit berantakan tetap cocok
    Dalam kasus UUIDv7, mungkin perlu menyesuaikan pages_per_range

• Saya selalu menyayangkan bahwa indeks hash tidak bisa diberi constraint unik
  Rasanya ini bisa diselesaikan hanya dengan sedikit glue code untuk mengubahnya menjadi exclusion constraint, jadi saya penasaran kenapa sampai sekarang belum ada

• Validasi keunikan berbasis hash tidak didukung di indeks karena penanganan collision tidak ada
  Solusi yang diusulkan juga menghadapi masalah yang sama

  • Itu sama sekali tidak benar. Indeks hanya menyimpan hash, tetapi tabel menyimpan nilai lengkapnya
    Postgres menganggapnya duplikat hanya jika hash dan nilai aslinya sama-sama cocok
  • Ini juga bisa dilihat di contoh dbfiddle

• Isi tulisannya terasa segar. Kolom virtual dan indeks hash menarik, tetapi rasanya belum sepenuhnya terintegrasi ke ekosistem

  • Kolom virtual hampir selesai. Sebagian besar sudah diimplementasikan di PostgreSQL 18
    Indeks hash sudah lama punya banyak keterbatasan, tetapi terus membaik, dan constraint unik otomatis adalah tugas yang masih tersisa

• Saya berpikir, bukankah kita bisa langsung membuat indeks dengan memakai stored generated column

  • Tulisan itu menjelaskan alasan menghindari metode ini
    Sejak PostgreSQL 14 fitur ini didukung, tetapi hasilnya disimpan secara fisik dan memakan storage tambahan
  • Saya juga penasaran apakah mungkin membuat partial index berbasis ekspresi
  • Pada akhirnya ini tetap menambah ruang penyimpanan, jadi dalam contoh di tulisan tersebut pendekatan ini memang ingin dihindari

• Sejak pindah ke cloud, saya jadi lebih jarang menangani pgsql secara langsung di lingkungan server tetap
  Saya penasaran apakah highlighting sintaks SQL pada tulisan itu adalah fitur bawaan atau alat terpisah

  • Saya memakai pgcli. Ada banyak fitur kenyamanan seperti penanda status transaksi, autocomplete, highlighting, dan lain-lain
    Hanya saja saat menyalin kueri panjang, setelah line break otomatis ditambahkan spasi sehingga agak mengganggu
  • Jika memakai IDE seperti IntelliJ, Anda juga bisa mendapatkan syntax highlighting dan autocomplete sekaligus