Membuat dan Menghapus Index

Chapter sebelumnya membuktikan bahwa satu perintah CREATE INDEX mampu membalik rencana eksekusi dari sequential scan menjadi index scan. Sekarang kita bedah perintah kerjanya secara utuh: bentuk-bentuk CREATE INDEX, konvensi penamaan, cara memeriksa index yang ada, cara menghapusnya, dan satu varian khusus CONCURRENTLY yang wajib dikuasai sebelum menyentuh tabel produksi. Siklus tambah–periksa–buang ini adalah rutinitas nyata pengelolaan database: query baru lahir, index dibuat; pola query bergeser, index lama dijatuhkan. Seluruh contoh tetap memakai tabel pelanggan 1000 baris dari chapter sebelumnya, dibangun ulang di sini agar chapter ini berdiri sendiri:

-- Tabel contoh chapter ini (domain yang sama dengan chapter Konsep Index).
CREATE TABLE pelanggan (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    email    text NOT NULL,
    kota     text NOT NULL,
    kategori text NOT NULL,
    saldo    numeric(12,2) NOT NULL,
    aktif    boolean NOT NULL
);

INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
SELECT
    g,
    'Pelanggan ' || lpad(g::text, 4, '0'),
    'pelanggan' || lpad(g::text, 4, '0') || '@example.com',
    (ARRAY['Jakarta','Bandung','Surabaya','Medan','Denpasar'])[1 + (g - 1) / 200],
    (ARRAY['reguler','silver','gold'])[1 + g % 3],
    ((g - 1) * 1000 + 500)::numeric(12,2),
    (g % 10 <> 0)
FROM generate_series(1, 1000) AS g;

ANALYZE pelanggan;

Output yang Dihasilkan:

CREATE TABLE
INSERT 0 1000
ANALYZE

Sintaks Dasar CREATE INDEX #

Bentuk paling sederhana perintah ini adalah CREATE INDEX <nama_index> ON <tabel> (<kolom>). Nama index sebaiknya eksplisit dan mengikuti konvensi idx_<tabel>_<kolom> agar pemiliknya terbaca sekali lihat — jauh lebih jujur daripada nama acak semacam index_17. Dua perintah berikut membuat index pada kolom kota, lalu mencoba membuatnya lagi dalam bentuk IF NOT EXISTS:

-- Membuat index dengan nama eksplisit mengikuti konvensi idx_<tabel>_<kolom>.
CREATE INDEX idx_pelanggan_kota ON pelanggan (kota);

-- Varian aman untuk script yang dijalankan berulang: index sudah ada,
-- PostgreSQL melewati pembuatan dan hanya mengirim NOTICE.
CREATE INDEX IF NOT EXISTS idx_pelanggan_kota ON pelanggan (kota);

Output yang Dihasilkan:

CREATE INDEX
CREATE INDEX

Output memperlihatkan dua baris CREATE INDEX, tetapi hanya perintah pertama yang benar-benar membangun struktur; perintah kedua mendapati index yang dicari sudah ada, melewati pembuatannya, dan tetap mencetak tag CREATE INDEX sambil mengirim NOTICE: relation "idx_pelanggan_kota" already exists, skipping ke alamat stderr — pesan itu tidak tampak di blok output karena psql menulisnya di kanal terpisah. Perilaku ini membuat IF NOT EXISTS cocok untuk script migrasi yang dijalankan berulang: script tidak lagi gagal dijalankan kedua kalinya.

Melihat Index yang Ada #

Meta-command psql \d <tabel> memperlihatkan kolom-kolom tabel beserta seluruh index yang menempel padanya:

-- Meta-command psql \d memperlihatkan struktur tabel
-- beserta seluruh index yang menempel padanya.
\d pelanggan

Output yang Dihasilkan:

                 Table "public.pelanggan"
  Column  |     Type      | Collation | Nullable | Default 
----------+---------------+-----------+----------+---------
 id       | integer       |           | not null | 
 nama     | text          |           | not null | 
 email    | text          |           | not null | 
 kota     | text          |           | not null | 
 kategori | text          |           | not null | 
 saldo    | numeric(12,2) |           | not null | 
 aktif    | boolean       |           | not null | 
Indexes:
    "pelanggan_pkey" PRIMARY KEY, btree (id)
    "idx_pelanggan_kota" btree (kota)

Bagian Indexes: di output mendaftar pelanggan_pkey (milik primary key, otomatis) dan idx_pelanggan_kota (buatan kita, satu menit lalu). Setiap entri menyebutkan tipe strukturnya — di sini btree — dan kolom yang diindeks. Jika Anda bekerja lewat tool selain psql (GUI atau driver aplikasi), view pg_indexes yang diperkenalkan chapter sebelumnya adalah padanan yang murni SQL: SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'pelanggan';.

CREATE INDEX Mengunci Tulis — dan Jalan Keluarnya #

Fakta yang jarang disadari pemula: CREATE INDEX biasa memegang kunci SHARE pada tabel target selama pembangunan berlangsung. Kunci itu tidak mengganggu pembaca — query SELECT tetap leluasa — tetapi seluruh INSERT, UPDATE, dan DELETE pada tabel itu mengantre sampai index selesai dibangun. Pada tabel 1000 baris, penantian itu berlangsung milidetik sehingga tidak terasa. Pada tabel produksi ratusan juta baris, penantian bisa berlangsung puluhan menit — dan itulah periode aplikasi Anda berhenti menulis.

Varian CONCURRENTLY menyelesaikan masalah ini dengan membangun index dalam beberapa fase sambil tetap mengizinkan tulisan berjalan:

-- Varian produksi: index dibangun tanpa memblokir tulis pada tabel.
-- CONCURRENTLY tidak boleh berada di dalam blok transaksi,
-- jadi file ini sengaja tidak membungkus perintah dengan BEGIN.
CREATE INDEX CONCURRENTLY idx_pelanggan_saldo ON pelanggan (saldo);

Output yang Dihasilkan:

CREATE INDEX

Perintahnya berhasil dan hasilnya index yang sama fungsinya. Ada tiga harga yang dibayar untuk kebebasan ini. Pertama, pembangunan jadi lebih lambat dan butuh lebih banyak resource, karena PostgreSQL harus dua kali melewati tabel dan menunggu transaksi lain selesai. Kedua, CONCURRENTLY tidak boleh berjalan di dalam blok transaksi — jika dipaksa dibungkus BEGIN, PostgreSQL menolaknya dengan pesan ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block; file contoh di atas sengaja tidak memakai BEGIN. Ketiga, jika pembangunan gagal di tengah jalan (misalnya karena server mati), sisa kerjanya berupa index berstatus INVALID yang harus di-DROP lalu dibuat ulang.

Menghapus Index #

Index yang tidak lagi dipakai sebaiknya dijatuhkan: setiap index menggandakan pekerjaan tulis dan memakan storage. Perintahnya DROP INDEX <nama_index> — data tabel tidak tersentuh sama sekali, yang hilang hanya struktur pelengkapnya:

-- Menghapus index: data tabel tidak tersentuh, hanya strukturnya yang lenyap.
DROP INDEX idx_pelanggan_kota;

-- Varian aman untuk index yang mungkin sudah tidak ada.
DROP INDEX IF EXISTS idx_pelanggan_kota;

Output yang Dihasilkan:

DROP INDEX
DROP INDEX

Pola yang sama dengan CREATE INDEX IF NOT EXISTS berlaku di sini: DROP INDEX kedua menemukan index yang dicari sudah tidak ada, namun karena ditulis IF EXISTS, perintah itu tidak membatalkan script dengan error — PostgreSQL hanya mengirim NOTICE: index "idx_pelanggan_kota" does not exist, skipping ke stderr dan melanjutkan. Menghapus index di tabel kecil selesai dalam sekejap; di tabel besar, DROP INDEX biasa tetap cepat, dan varian DROP INDEX CONCURRENTLY tersedia bila Anda tidak boleh mengunci tabel saat menjatuhkan index.

Index Milik Constraint Tidak Bisa Dijatuhkan Langsung #

Satu jebakan menunggu siapa pun yang mencoba membereskan index bawaan: index penopang primary key bukan index biasa. Mencoba menjatuhkannya lewat DROP INDEX ditolak mentah-mentah:

-- Index milik primary key tidak bisa dijatuhkan lewat DROP INDEX:
-- index itu "dipegang" oleh constraint pelanggan_pkey.
DROP INDEX pelanggan_pkey;

Output yang Dihasilkan:

psql:gagal-hapus.sql:3: ERROR:  cannot drop index pelanggan_pkey because constraint pelanggan_pkey on table pelanggan requires it
HINT:  You can drop constraint pelanggan_pkey on table pelanggan instead.

Pesan ERROR: cannot drop index pelanggan_pkey because constraint pelanggan_pkey on table pelanggan requires it menjelaskan hubungan kepemilikan: index itu hidup sebagai penopang constraint, bukan sebagai objek mandiri. Petunjuk di baris HINT memberi jalan yang benar — jatuhkan constraint-nya, misalnya ALTER TABLE pelanggan DROP CONSTRAINT pelanggan_pkey;, dan index penopangnya ikut lenyap. Aturan yang sama berlaku untuk index penopang constraint UNIQUE.

Ringkasan perintah chapter ini:

Semua perintah di atas membangun index bertipe btree tanpa kita menuliskannya — memang begitulah default PostgreSQL. Output \d tadi menyebutnya eksplisit: btree (kota). Struktur apa sebenarnya yang bekerja di balik nama itu, dan mengapa index sanggup melayani rentang nilai sekaligus urutan? Chapter B-tree Index membedahnya sampai ke halaman daunnya.

Referensi #

• CREATE INDEX Reference
• DROP INDEX Reference
• Pengantar Index

-- Varian produksi: index dibangun tanpa memblokir tulis pada tabel.
-- CONCURRENTLY tidak boleh berada di dalam blok transaksi,
-- jadi file ini sengaja tidak membungkus perintah dengan BEGIN.
CREATE INDEX CONCURRENTLY idx_pelanggan_saldo ON pelanggan (saldo);

-- Membuat index dengan nama eksplisit mengikuti konvensi idx_<tabel>_<kolom>.
CREATE INDEX idx_pelanggan_kota ON pelanggan (kota);

-- Varian aman untuk script yang dijalankan berulang: index sudah ada,
-- PostgreSQL melewati pembuatan dan hanya mengirim NOTICE.
CREATE INDEX IF NOT EXISTS idx_pelanggan_kota ON pelanggan (kota);

-- Tabel contoh chapter ini (domain yang sama dengan chapter Konsep Index).
CREATE TABLE pelanggan (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    email    text NOT NULL,
    kota     text NOT NULL,
    kategori text NOT NULL,
    saldo    numeric(12,2) NOT NULL,
    aktif    boolean NOT NULL
);

INSERT INTO pelanggan (id, nama, email, kota, kategori, saldo, aktif)
SELECT
    g,
    'Pelanggan ' || lpad(g::text, 4, '0'),
    'pelanggan' || lpad(g::text, 4, '0') || '@example.com',
    (ARRAY['Jakarta','Bandung','Surabaya','Medan','Denpasar'])[1 + (g - 1) / 200],
    (ARRAY['reguler','silver','gold'])[1 + g % 3],
    ((g - 1) * 1000 + 500)::numeric(12,2),
    (g % 10 <> 0)
FROM generate_series(1, 1000) AS g;

ANALYZE pelanggan;

-- Index milik primary key tidak bisa dijatuhkan lewat DROP INDEX:
-- index itu "dipegang" oleh constraint pelanggan_pkey.
DROP INDEX pelanggan_pkey;

-- Menghapus index: data tabel tidak tersentuh, hanya strukturnya yang lenyap.
DROP INDEX idx_pelanggan_kota;

-- Varian aman untuk index yang mungkin sudah tidak ada.
DROP INDEX IF EXISTS idx_pelanggan_kota;

-- Meta-command psql \d memperlihatkan struktur tabel
-- beserta seluruh index yang menempel padanya.
\d pelanggan