Query pada tabel terpartisi tetap membutuhkan index — pruning mengurangi jumlah partisi yang dibaca, tetapi partisi yang tersisa masih dicari baris demi baris kalau tidak ada index. Pertanyaannya: apakah kita harus membuat index satu per satu pada setiap partisi, lalu mengulanginya setiap bulan saat partisi baru lahir? Jawaban PostgreSQL elegan: satu CREATE INDEX pada tabel induk menjalar otomatis ke semua partisi — saat ini dan yang akan datang. Chapter ini membahas partitioned index beserta dua konsekuensi pentingnya: index unik pada tabel terpartisi wajib memuat partition key, dan pelepasan partisi membawa indexnya pergi sebagai index mandiri. Semuanya kita buktikan pada tabel log_penjualan dua bulanan.
Menyiapkan Tabel Dua Partisi #
Tabel demo memakai domain yang sudah akrab: log penjualan Toko Maju Jaya untuk Januari-Februari 2026, dipecah berdasarkan tanggal:
-- Tabel penjualan terpartisi dua bulan sebagai basis contoh index.
CREATE TABLE log_penjualan (
id bigint NOT NULL,
tanggal date NOT NULL,
kota text NOT NULL,
total numeric(12,2) NOT NULL
) PARTITION BY RANGE (tanggal);
CREATE TABLE log_penjualan_2026_01 PARTITION OF log_penjualan
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE log_penjualan_2026_02 PARTITION OF log_penjualan
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
INSERT INTO log_penjualan VALUES
(1, DATE '2026-01-15', 'Jakarta', 150000.00),
(2, DATE '2026-01-20', 'Bandung', 75000.00),
(3, DATE '2026-02-05', 'Jakarta', 220000.00),
(4, DATE '2026-02-18', 'Surabaya', 98000.00);
SELECT id, tanggal, kota, total FROM log_penjualan ORDER BY id;Output yang Dihasilkan:
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 4
id | tanggal | kota | total
----+------------+----------+-----------
1 | 2026-01-15 | Jakarta | 150000.00
2 | 2026-01-20 | Bandung | 75000.00
3 | 2026-02-05 | Jakarta | 220000.00
4 | 2026-02-18 | Surabaya | 98000.00
(4 rows)Empat baris penjualan tersebar dua-dua di dua partisi bulanan, dan query dari induk mengembalikan semuanya — tabel ini belum punya satu index pun, sehingga setiap pencarian bernilai tertentu masih berupa pemindaian berurut per partisi.
Satu CREATE INDEX, Banyak Index Partisi #
Membuat index pada tabel induk tidak membangun satu index raksasa; PostgreSQL membuat kerangka index terpartisi pada induk, lalu satu index nyata pada setiap partisi:
-- Satu perintah CREATE INDEX pada induk...
CREATE INDEX idx_log_tanggal ON log_penjualan (tanggal);
-- ...menghasilkan index pada setiap partisi + kerangka pada induk.
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename LIKE 'log_penjualan%'
ORDER BY tablename, indexname;Output yang Dihasilkan:
CREATE INDEX
tablename | indexname | indexdef
-----------------------+-----------------------------------+------------------------------------------------------------------------------------------------------
log_penjualan | idx_log_tanggal | CREATE INDEX idx_log_tanggal ON ONLY public.log_penjualan USING btree (tanggal)
log_penjualan_2026_01 | log_penjualan_2026_01_tanggal_idx | CREATE INDEX log_penjualan_2026_01_tanggal_idx ON public.log_penjualan_2026_01 USING btree (tanggal)
log_penjualan_2026_02 | log_penjualan_2026_02_tanggal_idx | CREATE INDEX log_penjualan_2026_02_tanggal_idx ON public.log_penjualan_2026_02 USING btree (tanggal)
(3 rows)Daftar pg_indexes memperlihatkan tiga objek dari satu perintah. idx_log_tanggal pada induk ditandai ON ONLY — kerangka tanpa data, persis seperti induknya. Dua index lainnya bernama otomatis mengikuti pola <partisi>_<kolom>_idx: satu pada log_penjualan_2026_01, satu pada log_penjualan_2026_02. Setiap index partisi berdiri sendiri secara fisik, sehingga pemeliharaannya (rebuild, vacuum) berjalan per partisi kecil — dan partisi baru yang dibuat setelah ini otomatis mendapat index kembar dengan definisi sama.
Index Unik Wajib Memuat Partition Key #
Tabel log_penjualan dipecah berdasarkan tanggal, tetapi kolom identitas yang alami untuk keunikan justru id. Coba kita buat index unik pada id saja:
-- Index unik pada kolom id saja: ditolak karena tidak memuat partition key.
CREATE UNIQUE INDEX idx_log_unik_id ON log_penjualan (id);Output yang Dihasilkan:
psql:unik-tanpa-kunci.sql:2: ERROR: unique constraint on partitioned table must include all partitioning columns
DETAIL: UNIQUE constraint on table "log_penjualan" lacks column "tanggal" which is part of the partition key.PostgreSQL menolaknya dengan tegas: constraint unik pada tabel terpartisi harus memuat semua kolom partition key, dan index kita kekurangan kolom tanggal. Alasannya mekanis, bukan aturan administratif — penegakan unik dilakukan per index partisi, dan dua baris id sama bisa saja hidup di dua partisi berbeda sehingga tidak ada satu index pun yang melihat keduanya sekaligus. Keunikan id lintas partisi tidak bisa dijamin tanpa memasukkan kunci pembagi ke dalam index. Solusinya langsung dicoba:
-- Index unik yang memuat partition key: diterima.
CREATE UNIQUE INDEX idx_log_unik_id_tanggal ON log_penjualan (id, tanggal);
-- id 1 boleh muncul lagi selama tanggalnya berbeda:
-- kombinasi (id, tanggal) yang dijaga tetap unik.
INSERT INTO log_penjualan VALUES
(1, DATE '2026-02-10', 'Medan', 63000.00);
-- Ganti dengan PRIMARY KEY: kolom partition key ikut otomatis.
DROP INDEX idx_log_unik_id_tanggal;
ALTER TABLE log_penjualan
ADD CONSTRAINT pk_log PRIMARY KEY (id, tanggal);
SELECT tablename, indexname
FROM pg_indexes
WHERE tablename LIKE 'log_penjualan%'
ORDER BY tablename, indexname;Output yang Dihasilkan:
CREATE INDEX
INSERT 0 1
DROP INDEX
ALTER TABLE
tablename | indexname
-----------------------+-----------------------------------
log_penjualan | idx_log_tanggal
log_penjualan | pk_log
log_penjualan_2026_01 | log_penjualan_2026_01_pkey
log_penjualan_2026_01 | log_penjualan_2026_01_tanggal_idx
log_penjualan_2026_02 | log_penjualan_2026_02_pkey
log_penjualan_2026_02 | log_penjualan_2026_02_tanggal_idx
(6 rows)Index unik (id, tanggal) diterima. Bukti semantiknya menyusul: INSERT baru dengan id = 1 dan tanggal = '2026-02-10' berhasil — nilai id yang sama kini hidup di dua partisi, dan itu sah karena pasangan (id, tanggal)-nya berbeda. Bagian akhir file mengganti index unik itu dengan PRIMARY KEY (id, tanggal); klausa PRIMARY KEY pada tabel terpartisi otomatis memaksa kolom partition key ikut di dalamnya, dan daftar index terakhir menunjukkan pk_log pada induk beserta log_penjualan_2026_01_pkey dan log_penjualan_2026_02_pkey pada kedua partisi.
Index Khusus Satu Partisi #
Index pada induk menjalar merata ke semua partisi, tetapi kebutuhan nyata sering tidak merata: partisi arsip lama jarang dikueri, partisi bulan berjalan panas. PostgreSQL tetap mengizinkan index dibuat langsung pada satu partisi dengan definisi apa pun — termasuk partial index:
-- Index khusus satu partisi: partial index transaksi besar hanya di Januari.
CREATE INDEX idx_jan_besar ON log_penjualan_2026_01 (kota)
WHERE total > 100000;
-- Seq scan dimatikan sejenak agar rencana memakai index pada tabel kecil ini.
SET enable_seqscan = off;
-- Query lewat induk tetap bisa memakai index milik partisi.
EXPLAIN SELECT id
FROM log_penjualan
WHERE tanggal = DATE '2026-01-15'
AND total > 100000
AND kota = 'Jakarta';
SET enable_seqscan = on;Output yang Dihasilkan:
CREATE INDEX
SET
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Index Scan using idx_jan_besar on log_penjualan_2026_01 log_penjualan (cost=0.12..8.14 rows=1 width=8)
Index Cond: (kota = 'Jakarta'::text)
Filter: (tanggal = '2026-01-15'::date)
(3 rows)
SETIndex idx_jan_besar hanya ada di partisi Januari dan hanya mengindeks baris total > 100000. Rencana eksekusi membuktikan query lewat induk tetap memanfaatkannya: setelah pruning memilih partisi Januari, planner memakai Index Scan using idx_jan_besar dengan kondisi kota = 'Jakarta'. Tabel demo ini sangat kecil sehingga enable_seqscan sengaja dimatikan sejenak untuk memaksa index terpilih; pada partisi produksi berjuta baris, planner memilih index ini tanpa paksaan. Konsekuensi desain ini juga berarti index per partisi harus dikelola manual — partisi Februari tidak akan pernah mendapat idx_jan_besar padanannya kecuali kita buat sendiri.
Implikasi DETACH dan ATTACH pada Index #
Saat partisi dilepas dari induk (DETACH PARTITION — dibahas tuntas di chapter Partition Maintenance), index milik partisi ikut lepas dan menjadi index biasa milik tabel mandiri. Saat partisi dipasang kembali, PostgreSQL mencocokkan index tabel itu dengan index induk yang definisinya setara, lalu menempelkan secara otomatis:
-- Lepas partisi Februari: index miliknya ikut menjadi index tabel biasa.
ALTER TABLE log_penjualan DETACH PARTITION log_penjualan_2026_02;
SELECT tablename, indexname
FROM pg_indexes
WHERE tablename LIKE 'log_penjualan%'
ORDER BY tablename, indexname;
-- Pasang kembali: index partisi otomatis menempel pada index induknya.
ALTER TABLE log_penjualan ATTACH PARTITION log_penjualan_2026_02
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
SELECT inhrelid::regclass AS index_anak,
inhparent::regclass AS index_induk
FROM pg_inherits
WHERE inhparent::regclass::text IN ('idx_log_tanggal', 'pk_log')
ORDER BY 2, 1;Output yang Dihasilkan:
ALTER TABLE
tablename | indexname
-----------------------+-----------------------------------
log_penjualan | idx_log_tanggal
log_penjualan | pk_log
log_penjualan_2026_01 | idx_jan_besar
log_penjualan_2026_01 | log_penjualan_2026_01_pkey
log_penjualan_2026_01 | log_penjualan_2026_01_tanggal_idx
log_penjualan_2026_02 | log_penjualan_2026_02_pkey
log_penjualan_2026_02 | log_penjualan_2026_02_tanggal_idx
(7 rows)
ALTER TABLE
index_anak | index_induk
-----------------------------------+-----------------
log_penjualan_2026_01_tanggal_idx | idx_log_tanggal
log_penjualan_2026_02_tanggal_idx | idx_log_tanggal
log_penjualan_2026_01_pkey | pk_log
log_penjualan_2026_02_pkey | pk_log
(4 rows)Tiga tahap terlihat jelas. Setelah DETACH, daftar pg_indexes masih menampilkan log_penjualan_2026_02_pkey dan log_penjualan_2026_02_tanggal_idx — keduanya kini index milik tabel biasa log_penjualan_2026_02, bukan anggota index terpartisi. Setelah ATTACH, query terakhir membaca katalog pg_inherits dari sisi index: kedua index partisi tanggal menempel pada induk idx_log_tanggal, dan kedua pkey partisi menempel pada pk_log. Penempelan otomatis ini mensyaratkan definisi index tabel mandiri identik dengan induknya (kolom, urutan, opclass, predikat); bila tidak identik, ATTACH tetap berhasil tetapi index partisi dibiarkan berdiri sendiri dan index kembar baru dibuat ulang oleh sistem.
Index terpartisi dan pruning saling melengkapi untuk sisi baca. Namun tabel terpartisi adalah makhluk yang hidup: partisi bulan depan harus dibuat sebelum tanggal berganti, dan partisi lama harus diputar keluar seingga memakan tempat — seluruh siklus hidup itu dibahas di chapter Partition Maintenance.
Referensi #
Berkas Sumber
Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.
buat-tabel.sql
Unduh berkas-- Tabel penjualan terpartisi dua bulan sebagai basis contoh index.
CREATE TABLE log_penjualan (
id bigint NOT NULL,
tanggal date NOT NULL,
kota text NOT NULL,
total numeric(12,2) NOT NULL
) PARTITION BY RANGE (tanggal);
CREATE TABLE log_penjualan_2026_01 PARTITION OF log_penjualan
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE log_penjualan_2026_02 PARTITION OF log_penjualan
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
INSERT INTO log_penjualan VALUES
(1, DATE '2026-01-15', 'Jakarta', 150000.00),
(2, DATE '2026-01-20', 'Bandung', 75000.00),
(3, DATE '2026-02-05', 'Jakarta', 220000.00),
(4, DATE '2026-02-18', 'Surabaya', 98000.00);
SELECT id, tanggal, kota, total FROM log_penjualan ORDER BY id;
detach-attach-index.sql
Unduh berkas-- Lepas partisi Februari: index miliknya ikut menjadi index tabel biasa.
ALTER TABLE log_penjualan DETACH PARTITION log_penjualan_2026_02;
SELECT tablename, indexname
FROM pg_indexes
WHERE tablename LIKE 'log_penjualan%'
ORDER BY tablename, indexname;
-- Pasang kembali: index partisi otomatis menempel pada index induknya.
ALTER TABLE log_penjualan ATTACH PARTITION log_penjualan_2026_02
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
SELECT inhrelid::regclass AS index_anak,
inhparent::regclass AS index_induk
FROM pg_inherits
WHERE inhparent::regclass::text IN ('idx_log_tanggal', 'pk_log')
ORDER BY 2, 1;
index-induk.sql
Unduh berkas-- Satu perintah CREATE INDEX pada induk...
CREATE INDEX idx_log_tanggal ON log_penjualan (tanggal);
-- ...menghasilkan index pada setiap partisi + kerangka pada induk.
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename LIKE 'log_penjualan%'
ORDER BY tablename, indexname;
index-per-partisi.sql
Unduh berkas-- Index khusus satu partisi: partial index transaksi besar hanya di Januari.
CREATE INDEX idx_jan_besar ON log_penjualan_2026_01 (kota)
WHERE total > 100000;
-- Seq scan dimatikan sejenak agar rencana memakai index pada tabel kecil ini.
SET enable_seqscan = off;
-- Query lewat induk tetap bisa memakai index milik partisi.
EXPLAIN SELECT id
FROM log_penjualan
WHERE tanggal = DATE '2026-01-15'
AND total > 100000
AND kota = 'Jakarta';
SET enable_seqscan = on;
unik-dan-pk.sql
Unduh berkas-- Index unik yang memuat partition key: diterima.
CREATE UNIQUE INDEX idx_log_unik_id_tanggal ON log_penjualan (id, tanggal);
-- id 1 boleh muncul lagi selama tanggalnya berbeda:
-- kombinasi (id, tanggal) yang dijaga tetap unik.
INSERT INTO log_penjualan VALUES
(1, DATE '2026-02-10', 'Medan', 63000.00);
-- Ganti dengan PRIMARY KEY: kolom partition key ikut otomatis.
DROP INDEX idx_log_unik_id_tanggal;
ALTER TABLE log_penjualan
ADD CONSTRAINT pk_log PRIMARY KEY (id, tanggal);
SELECT tablename, indexname
FROM pg_indexes
WHERE tablename LIKE 'log_penjualan%'
ORDER BY tablename, indexname;
unik-tanpa-kunci.sql
Unduh berkas-- Index unik pada kolom id saja: ditolak karena tidak memuat partition key.
CREATE UNIQUE INDEX idx_log_unik_id ON log_penjualan (id);