Pengantar Indeks PostgreSQL

 (dlt.github.io)
30 poin oleh GN⁺ 2026-01-26 | 1 komentar | Bagikan ke WhatsApp
  • Indeks PostgreSQL adalah struktur inti untuk meningkatkan kecepatan akses data, dengan mengurangi jumlah data yang harus dibaca dari disk sehingga meningkatkan performa kueri
  • Indeks tersedia dalam berbagai bentuk seperti Btree, Hash, BRIN, GIN, GiST, SP-GiST, dan masing-masing dioptimalkan untuk karakteristik data serta pola kueri yang berbeda
  • Indeks juga membawa berbagai biaya seperti ruang disk, performa tulis, kompleksitas query planner, dan penggunaan memori
  • Melalui fitur lanjutan seperti partial index, multi-column index, covering index, expression index, efisiensi dapat dimaksimalkan dalam situasi tertentu
  • Pemilihan dan pengelolaan indeks yang tepat ditekankan sebagai faktor kunci optimasi performa PostgreSQL

Konsep dasar indeks

  • Indeks adalah struktur yang mempercepat kueri dengan mengurangi jumlah data yang dibaca database dari disk
    • Primary key, unique key, dan exclusion constraint juga diimplementasikan melalui indeks
    • Indeks efektif ketika hasil kueri kurang dari sekitar 15~20% dari seluruh tabel; di atas itu, sequential scan bisa lebih efisien
  • PostgreSQL secara bawaan menyediakan 6 jenis indeks, dan lebih banyak jenis bisa digunakan melalui ekstensi
    • Setiap indeks menghubungkan nilai kunci dengan lokasi data terkait (TID)

Struktur data yang disimpan di disk

  • Tabel PostgreSQL disimpan sebagai file heap, yang tersusun dalam unit halaman 8KB
  • Setiap baris (tuple) disimpan tanpa urutan tertentu, dan alamat internalnya diidentifikasi dengan ctid (current tuple id)
    • Contoh: (0,1) berarti tuple pertama pada halaman 0
  • Indeks menghubungkan posisi heap tersebut (ctid) dalam struktur pohon untuk mendukung pencarian cepat

Cara indeks mempercepat akses data

  • Tanpa indeks, PostgreSQL melakukan sequential scan dengan membaca semua halaman
    • Pada contoh kueri, saat mencari name='Ronaldo', dibutuhkan pembacaan 6272 halaman dan waktu 265ms
  • Setelah indeks ditambahkan, proses berubah menjadi Index Scan, hanya membaca 4 halaman dan selesai dalam 0.077ms
    • Indeks memetakan nilai ke ctid sehingga hanya baris yang diperlukan yang ditemukan dengan cepat
  • Ukuran file indeks bisa sebanding dengan ukuran tabel (contoh: tabel 30MB → indeks 30MB)

Faktor biaya indeks

  • Selain meningkatkan performa, indeks juga membawa berbagai beban tambahan

Ruang disk

  • Indeks memakai ruang penyimpanan terpisah dan bisa lebih besar daripada tabel
    • Menimbulkan biaya tambahan saat backup, replikasi, dan pemulihan bencana
    • Efisiensi ruang dapat ditingkatkan dengan partial index, multi-column index, BRIN, dan lainnya

Operasi tulis

  • Saat UPDATE, INSERT, DELETE, jika kolom yang terindeks berubah maka muncul overhead pembaruan indeks

Query planner

  • Semakin banyak indeks, semakin banyak opsi yang harus dipertimbangkan planner, sehingga waktu penyusunan rencana kueri bertambah

Penggunaan memori

  • Halaman indeks dimuat ke shared buffer untuk di-cache, sehingga semakin banyak indeks maka beban memori meningkat
  • Karena batas ukuran node btree, semakin besar kolom maka kedalaman pohon meningkat
  • Pada sorting, multi-column scan, vacuum, reindex, dan proses lain, work memory juga digunakan tambahan

Jenis-jenis utama indeks

Btree

  • Struktur indeks default PostgreSQL, merupakan indeks serbaguna yang digunakan di sebagian besar DBMS
    • Mendukung pencarian cepat dengan kompleksitas waktu O(log n)
    • Menggunakan struktur balanced tree di mana semua leaf node memiliki kedalaman yang sama
    • Menguntungkan untuk operasi ORDER BY, JOIN, serta digunakan untuk constraint primary key dan unique key
  • Node internal menyimpan pointer ke node anak, sedangkan leaf node menyimpan key dan pointer ke heap
  • Melalui pointer node kiri dan kanan, penelusuran dua arah dimungkinkan
Penggunaan banyak indeks
  • PostgreSQL dapat menggabungkan beberapa indeks melalui operasi bitmap AND/OR untuk menangani kondisi gabungan
    • Contoh: pada kondisi age=30 AND login_count=100, bitmap dari dua indeks digabungkan
Multi-column index
  • Beberapa kolom dapat digabungkan dalam satu indeks untuk menghemat ruang dan meningkatkan kecepatan
    • Namun, urutan kolom itu penting dan indeks hanya dapat digunakan untuk kondisi yang cocok mulai dari sisi kiri
Partial index
  • Mengindeks hanya baris tertentu menggunakan ekspresi kondisi
    • Mengurangi ukuran indeks, meningkatkan kecocokan dengan RAM, dan mempercepat pencarian
    • Contoh: create index on rules(status) where status='enabled';
    • Berguna saat distribusi nilai tidak seimbang (status <> 'TODO' dan sebagainya)
Covering index
  • Jika semua kolom yang dibutuhkan kueri ada di dalam indeks, hasil bisa dikembalikan tanpa mengakses heap (index-only scan)
    • create index abc_cov_idx on bar(a, b) including c;
    • Lebih efisien dalam penggunaan ruang dibanding multi-column index
Expression index
  • Mengindeks hasil fungsi atau ekspresi, bukan nilai kolom secara langsung
    • Contoh: CREATE INDEX idx_lower_name ON customers (lower(name));
    • Berguna saat mencari berdasarkan nilai yang telah ditransformasi seperti LOWER(name)
    • Hanya fungsi immutable yang dapat digunakan

Hash

  • Indeks berbasis struktur hash map yang efisien secara ruang untuk string panjang atau UUID
    • Menyimpan hash code 32-bit untuk mengurangi ukuran
    • Hanya mendukung operasi perbandingan kesetaraan (=), dan tidak mendukung sorting maupun multi-column index
    • Jika distribusi hash merata, performa baca bisa lebih cepat daripada Btree
  • Menurut dokumentasi resmi, hash index dapat mengurangi I/O pada tabel besar melalui akses langsung ke bucket page

BRIN (Block Range Index)

  • Indeks yang hanya menyimpan nilai minimum dan maksimum untuk setiap rentang blok
    • Sangat ringkas dan ramah cache
    • Cocok untuk data berskala besar, append-only, dan time-series
  • Jika baris sering diperbarui, efisiensinya menurun akibat penyimpanan duplikat karena MVCC
  • Dengan pengaturan pages_per_range, dapat diatur trade-off antara akurasi dan ukuran

GIN (Generalized Inverted Index)

  • Indeks yang cocok untuk pencarian data kompleks
    • Mendukung pencarian elemen tertentu pada teks, array, JSONB, dan lainnya
    • Menggunakan strategi khusus (opclass) sesuai tipe data
    • Untuk JSON disarankan memakai kolom JSONB, sedangkan teks dianjurkan bersama tsvector atau ekstensi pg_trgm

GiST & SP-GiST

  • Generalized Search Tree (GiST) dan space-partitioned tree (SP-GiST) adalah framework implementasi indeks untuk tipe data tertentu
    • GiST mendukung balanced tree, sedangkan SP-GiST mendukung struktur tidak seimbang
    • Digunakan untuk geospatial, inet, range, text vector, dan lainnya
    • GIN unggul untuk pencarian cepat, sedangkan GiST lebih murah dalam biaya pembuatan dan pemeliharaan
    • Untuk full-text search, pilih salah satu sesuai kebutuhan

Kesimpulan

  • Indeks adalah inti optimasi performa PostgreSQL, dan penting menjaga keseimbangan antara peningkatan kecepatan baca dengan biaya tulis serta penyimpanan
  • Dengan memilih jenis indeks yang sesuai karakteristik data dan pola kueri, dimungkinkan pengoperasian database yang cepat dan efisien
  • Desain indeks yang tepat merupakan elemen penting untuk menjamin skalabilitas dan stabilitas sistem berskala besar

Bacaan terkait

1 komentar

 
GN⁺ 2026-01-26
Komentar Hacker News

  • Dokumentasi resmi PostgreSQL benar-benar ditulis dengan sangat baik dan juga enak dibaca, jadi saya membagikannya
    Dokumen pengantar PostgreSQL Indexes

  • Bagian indeks multikolom hampir sama persis dengan cara yang saya pelajari
    Tetapi saya penasaran apakah itu masih berlaku bahkan di versi PostgreSQL terbaru
    Dulu saya pernah melihat bitmap index scan digunakan pada kueri yang mirip dengan contoh ketiga, dan sejak saat itu saya mulai memikirkan ulang ‘aturan baku’ yang lama
    Sebagai referensi untuk indeks, menurut saya situs dan buku Use The Index, Luke adalah materi klasik yang layak dibaca oleh seluruh tim

    • Di PostgreSQL 18, index skip scan ditambahkan sehingga sekarang pencarian efisien menjadi mungkin bahkan hanya dengan kolom turunan dari indeks multikolom
      Di versi sebelumnya hal itu juga mungkin, tetapi saat itu diperlukan pemindaian seluruh indeks sehingga tidak efisien
      Video terkait: tautan YouTube
    • bitmap index scan mempersempit halaman yang kemungkinan berisi data, tetapi verifikasi kondisi sebenarnya tetap harus dilakukan lagi, jadi performanya lebih rendah daripada pemindaian indeks biasa

  • Saya berharap PostgreSQL mendukung incremental view maintenance secara bawaan
    Ini adalah konsep yang, seperti indeks, diperbarui otomatis saat data dasar berubah, tetapi tidak terbatas pada view tertentu dan bisa diterapkan pada view arbitrer

    • Ini masalah yang cukup sulit karena melibatkan pemrosesan transaksi
      Ada banyak proyek terkait seperti Noria, Materialize, Apache Flink, GCP Continuous Queries, Spark Streaming Tables, Delta Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB, dan StreamSQL
      Di PostgreSQL, belakangan ini ekstensi bernama pg_ivm mulai menangani masalah ini
    • Untuk data deret waktu, fitur continuous aggregates milik TimescaleDB sudah menjalankan peran seperti ini

  • Diskusi B-tree vs Hash indeks menarik
    Banyak orang mengira kolom ID lebih cocok memakai hash, tetapi pada praktiknya B-tree bawaan lebih efisien
    Terutama untuk penyisipan nilai yang nyaris berurutan, struktur tree lebih menguntungkan
    Namun, dalam tulisan blog yang disebut kali ini, justru dikatakan bahwa hash menang dalam benchmark

  • Timing tulisan ini bagus
    Aturan leading column pada indeks multikolom selalu membingungkan, tetapi berkat bitmap index scan, hal itu tidak lagi sefatal dulu
    Fitur skip scan di PostgreSQL 18 sangat mengubah pemahaman lama, jadi jika Anda belajar berdasarkan versi lama, Anda perlu memperbarui mental model

  • Menurut saya ini tulisan yang sangat bagus sebagai materi untuk PostgreSQL
    Untuk indeks B-tree, saya juga sudah lama sering merujuk ke Use The Index, Luke

  • Menurut saya ini bacaan wajib
    Ini melampaui level buku pengantar biasa, cukup mendalam tetapi tetap mudah dibaca selama tidak membahas struktur internal

  • Saya suka gaya penulisan yang sederhana dan rendah hati seperti ini
    Saya menyukai cara penyampaian pengetahuan yang langsung ke inti