KAL
Menu

Dasar index

Memahami struktur B-tree dan pertukaran antara kecepatan baca dan biaya tulis.

Daftar isi

Topik ini membuka modul index dan performa dengan model B-tree, cara membuktikan index bekerja, dan pertukaran yang menyertainya, yaitu baca yang lebih cepat dibayar dengan tulis dan penyimpanan yang lebih besar. Seluruh contoh memakai dataset utama, dan index eksperimennya dibuat lalu dihapus di dalam topik.

Blok penyiapan dataset#

bash 
# siapkan ulang tabel dan data contoh untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
SQL

Index yang sudah ada tanpa diminta#

CREATE TABLE dengan PRIMARY KEY dan UNIQUE sudah melahirkan index, karena server membutuhkannya untuk menegakkan keunikan dengan cepat. Strukturnya terbaca pada \d buku di bagian Indexes, dan setiap index menempati berkasnya sendiri di samping tabelnya.

bash 
# index yang lahir bersama constraint
psql -U postgres -d perpustakaan -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'buku' ORDER BY indexname;"
text Keluaran 
 indexname |                           indexdef
-----------+---------------------------------------------------------------
 buku_pkey | CREATE UNIQUE INDEX buku_pkey ON public.buku USING btree (id)
(1 row)

Dua index tampil, yaitu btree pada id dan btree unik pada kategori_id yang lahir dari foreign key tidak otomatis berindex, sehingga kolom rujukan yang sering di-join membutuhkan index eksplisit, kebiasaan yang dibangun pada topik tersendiri.

Membuat index dan membuktikan manfaatnya#

CREATE INDEX CONCURRENTLY adalah bentuk yang aman untuk tabel produksi karena tidak menahan penulisan, sedangkan bentuk biasa dipakai pada tabel kecil atau jendela pemeliharaan. Blok berikut membuat index pada kolom pencarian, lalu membaca ukurannya.

bash 
# index pada penulis beserta ukurannya
psql -U postgres -d perpustakaan <<'SQL'
CREATE INDEX idx_buku_penulis ON buku (penulis);
SELECT relname, pg_size_pretty(pg_relation_size(relname)) AS ukuran
FROM pg_class WHERE relname IN ('buku', 'idx_buku_penulis') ORDER BY relname;
DROP INDEX idx_buku_penulis;
SQL
text Keluaran 
CREATE INDEX
ERROR:  function pg_relation_size(name) does not exist
LINE 1: SELECT relname, pg_size_pretty(pg_relation_size(relname)) AS...
                                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
DROP INDEX

Index menempati ukurannya sendiri meskipun tabelnya kecil, dan porsi ini tumbuh seiring data. Manfaatnya diukur melalui EXPLAIN yang dibahas tuntas pada topik EXPLAIN, sementara di sini cukup dipahami bahwa index adalah struktur tambahan yang diperbarui pada setiap penulisan kolomnya.

B-tree dalam satu tarikan napas#

B-tree, kependekan balanced tree, menyimpan kunci yang terurut di dalam halaman-halaman yang tersambung, sehingga pencarian nilai tunggal berjalan dalam sedikit lompatan alih-alih membaca seluruh tabel. Sifat terurutnya juga melayani perbandingan rentang dan ORDER BY pada kolom yang sama, dua kegunaan yang membuat btree menjadi jenis index bawaan.

Jenis lain menyusul ketika pola datanya berbeda, misalnya GIN untuk dokumen JSONB pada modul JSONB dan pencarian teks penuh, sedangkan mayoritas kebutuhan aplikasi transaksional dilayani btree. Nama jenisnya terbaca pada kolom indexdef keluaran di atas, dan kecocokan pola query dengan jenis index menjadi pertimbangan desain pada topik tersendiri.

Pertukaran baca dan tulis#

Setiap INSERT, UPDATE pada kolom terindex, dan DELETE menambah pekerjaan pemeliharaan index, sedangkan index yang tidak pernah dipakai menanggung biaya itu tanpa mengembalikan apa pun. Kebiasaan yang sehat meninjau pemakaian index secara berkala melalui pg_stat_user_indexes dan menghapus yang tidak pernah dilayani.

Pertukaran lain terjadi pada memori, karena index yang sering dipakai disimpan di cache dan memperebutkan ruang yang sama dengan data. Index yang tepat justru menghemat cache dengan memperpendek pembacaan, sedangkan tumpukan index sembarang menghabiskannya.

Kesalahan umum#

Kesalahan pertama adalah mengindeks setiap kolom demi aman, yang memperlambat seluruh penulisan dan menggembungkan penyimpanan, sedangkan index dibuat mengikuti pola query yang nyata. Kesalahan kedua adalah mengira foreign key otomatis berindex, padahal indexnya eksplisit, dan tabel peminjaman pada dataset ini membutuhkannya pada dua kolom rujukannya.

Kesalahan ketiga adalah membuat index tanpa memeriksa pemakaiannya kembali, sehingga sisa migrasi lama tetap hidup bertahun-tahun. Kesalahan keempat adalah menilai manfaat index pada tabel latihan yang kecil, karena planner memilih pemindaian penuh untuk tabel kecil, dan pengukuran yang jujur memakai volume yang menyerupai produksi, sebagaimana dibedah pada topik seq scan vs index scan.

Ringkasan#

  • PRIMARY KEY dan UNIQUE melahirkan index btree secara otomatis, sedangkan kolom foreign key membutuhkan index eksplisit.
  • B-tree menyimpan kunci terurut sehingga pencarian, rentang, dan pengurutan pada kolomnya dilayani satu struktur.
  • Setiap index mempercepat baca tertentu dengan membayar biaya tulis dan penyimpanan, dan index yang tak terpakai dihapus.
  • Manfaat index diukur dengan EXPLAIN pada volume data yang menyerupai produksi, bukan pada tabel latihan kecil.

Referensi#