The Database Index You Forgot
Most engineers understand that indexes speed up queries. Fewer understand why — and that gap produces systems that work fine until they do not.
Every production performance incident I have been part of had a database at the center of it. Not because databases are fragile — they are not — but because they absorb the cost of every architectural decision made above them. When you are at 10,000 users, this cost is invisible. At 500,000, it becomes a pager alert at 3am.
The root cause in most of these incidents is not a missing index. It is a missing understanding of indexes. Engineers who know that “indexes are good” tend to add them reactively — after the incident, on the obvious columns — rather than proactively, on the queries that will matter.
This is an attempt to build the mental model that makes the proactive instinct possible.
What a Query Does Without an Index
When PostgreSQL receives a query without a useful index, it performs a sequential scan: it reads every row in the table and checks whether each one satisfies the WHERE clause. On a table with 1,000 rows, this takes microseconds. On a table with 10,000,000 rows, it takes seconds.
-- This query on a 10M row table without an index on `created_at`
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';
-- PostgreSQL reads all 10,000,000 rows to find the ~50,000 recent onesThe query is not broken. The results are correct. But the database did approximately 200 times more work than necessary.
An index — specifically a B-tree index, which is the default in PostgreSQL — allows the database to skip directly to the rows it needs. Instead of reading all 10 million rows, it reads a few hundred index entries and retrieves only the matching rows.
The Columns You Actually Need to Index
The guidance “index your foreign keys” is correct but incomplete. The columns worth indexing are the ones that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses in your most frequent queries.
This sounds obvious. The less obvious part is that the order of columns in a composite index matters enormously.
Say you have a table of events with columns user_id, event_type, and created_at. If your most common query is “all login events for user X in the past week”, the right index is (user_id, event_type, created_at) — in that order. The database can use this index for any prefix of the column list, but not for arbitrary combinations.
-- This query uses the composite index efficiently
SELECT * FROM events
WHERE user_id = 42
AND event_type = 'login'
AND created_at > NOW() - INTERVAL '7 days';
-- This query cannot use the same index
-- (event_type is not a prefix of the index)
SELECT * FROM events
WHERE event_type = 'login'
AND created_at > NOW() - INTERVAL '7 days';The second query needs its own index: (event_type, created_at). Two queries with different access patterns need different indexes. One index cannot serve both efficiently.
EXPLAIN Is the Tool You Are Not Using Enough
Every engineer working with a relational database should be in the habit of running EXPLAIN ANALYZE on any query that handles significant data.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 1234
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;The output tells you exactly what the database did: whether it used an index, how many rows it scanned, how long each step took. A sequential scan on a large table in a plan you expected to use an index is the signal to investigate.
The Indexes That Hurt
Indexes are not free. Every index you add is a data structure that must be maintained on every INSERT, UPDATE, and DELETE. On write-heavy tables, too many indexes slow down writes significantly.
The rule is not “more indexes are better.” It is “the right indexes are better.” A table with thirty indexes on it is almost certainly carrying some that are never used. PostgreSQL tracks index usage in pg_stat_user_indexes — a query against it will tell you which indexes have zero scans and can be safely dropped.
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;Dropping an unused index is a small, safe improvement. Keeping it is a permanent tax on every write.
Partial Indexes: A Tool Worth Knowing
One underused feature is the partial index — an index that only includes rows matching a condition.
If you have a jobs queue table where 99% of rows have status 'completed', and all your queries only touch rows with status 'pending', an index on (status, created_at) is mostly wasted on completed rows you will never query for.
A partial index covers only what you need:
CREATE INDEX idx_jobs_pending ON jobs (created_at)
WHERE status = 'pending';This index is dramatically smaller, faster to maintain, and works perfectly for the query it was designed for. The trade-off is that it only helps queries that include the same WHERE condition — but if that describes your actual query pattern, it is a significant win.
Understanding indexes is not optional knowledge for engineers who work with data at scale. It is the difference between a system that degrades gracefully and one that falls apart in a way that looks mysterious until you run EXPLAIN ANALYZE and see ten million rows getting scanned for the result you needed in a hundred.
Setiap insiden performa produksi yang pernah saya alami memiliki database di pusatnya. Bukan karena database itu rapuh — tidak. Tapi karena mereka menyerap biaya dari setiap keputusan arsitektur yang dibuat di atasnya. Saat kamu memiliki 10.000 pengguna, biaya ini tidak terlihat. Saat 500.000, itu menjadi peringatan pager jam 3 pagi.
Akar penyebab dalam sebagian besar insiden ini bukan index yang hilang. Ini adalah pemahaman tentang index yang hilang. Engineer yang tahu bahwa “index itu bagus” cenderung menambahkannya secara reaktif — setelah insiden, pada kolom yang jelas — daripada secara proaktif, pada query yang akan penting.
Ini adalah upaya untuk membangun model mental yang membuat insting proaktif itu mungkin.
Apa yang Dilakukan Query Tanpa Index
Ketika PostgreSQL menerima query tanpa index yang berguna, ia melakukan sequential scan: membaca setiap baris dalam tabel dan memeriksa apakah masing-masing memenuhi klausa WHERE. Pada tabel dengan 1.000 baris, ini membutuhkan mikrodetik. Pada tabel dengan 10.000.000 baris, ini membutuhkan detik.
-- Query ini pada tabel dengan 10 juta baris tanpa index pada `created_at`
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';
-- PostgreSQL membaca semua 10.000.000 baris untuk menemukan ~50.000 baris terbaruQuery-nya tidak rusak. Hasilnya benar. Tapi database melakukan sekitar 200 kali lebih banyak pekerjaan dari yang diperlukan.
Index — khususnya B-tree index, yang merupakan default di PostgreSQL — memungkinkan database untuk langsung ke baris yang dibutuhkan. Alih-alih membaca 10 juta baris, ia membaca beberapa ratus entri index dan mengambil hanya baris yang cocok.
Kolom yang Sebenarnya Perlu Di-index
Panduan “index foreign key kamu” itu benar tapi tidak lengkap. Kolom yang layak di-index adalah yang muncul dalam klausa WHERE, kondisi JOIN, dan klausa ORDER BY dalam query kamu yang paling sering.
Ini terdengar jelas. Bagian yang kurang jelas adalah bahwa urutan kolom dalam composite index sangat penting.
Katakan kamu memiliki tabel events dengan kolom user_id, event_type, dan created_at. Jika query kamu yang paling umum adalah “semua event login untuk pengguna X dalam seminggu terakhir”, index yang tepat adalah (user_id, event_type, created_at) — dalam urutan itu. Database dapat menggunakan index ini untuk awalan kolom manapun, tapi tidak untuk kombinasi sembarang.
-- Query ini menggunakan composite index secara efisien
SELECT * FROM events
WHERE user_id = 42
AND event_type = 'login'
AND created_at > NOW() - INTERVAL '7 days';
-- Query ini tidak bisa menggunakan index yang sama
-- (event_type bukan awalan dari index)
SELECT * FROM events
WHERE event_type = 'login'
AND created_at > NOW() - INTERVAL '7 days';Query kedua membutuhkan indexnya sendiri: (event_type, created_at). Dua query dengan pola akses berbeda membutuhkan index yang berbeda. Satu index tidak bisa melayani keduanya secara efisien.
EXPLAIN Adalah Alat yang Tidak Cukup Kamu Gunakan
Setiap engineer yang bekerja dengan database relasional harus memiliki kebiasaan menjalankan EXPLAIN ANALYZE pada setiap query yang menangani data signifikan.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 1234
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;Outputnya memberi tahu kamu persis apa yang dilakukan database: apakah ia menggunakan index, berapa baris yang dipindai, berapa lama setiap langkah. Sequential scan pada tabel besar dalam rencana yang kamu harapkan menggunakan index adalah sinyal untuk diselidiki.
Index yang Menyakitkan
Index tidak gratis. Setiap index yang kamu tambahkan adalah struktur data yang harus dikelola pada setiap INSERT, UPDATE, dan DELETE. Pada tabel yang banyak menulis, terlalu banyak index memperlambat penulisan secara signifikan.
Aturannya bukan “lebih banyak index lebih baik.” Melainkan “index yang tepat lebih baik.” Tabel dengan tiga puluh index hampir pasti membawa beberapa yang tidak pernah digunakan. PostgreSQL melacak penggunaan index dalam pg_stat_user_indexes — query terhadapnya akan memberi tahu kamu index mana yang memiliki nol scan dan bisa dihapus dengan aman.
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;Menghapus index yang tidak digunakan adalah peningkatan kecil yang aman. Mempertahankannya adalah pajak permanen pada setiap penulisan.
Partial Index: Alat yang Layak Diketahui
Satu fitur yang kurang digunakan adalah partial index — index yang hanya menyertakan baris yang cocok dengan kondisi.
Jika kamu memiliki tabel antrian jobs di mana 99% baris memiliki status 'completed', dan semua query kamu hanya menyentuh baris dengan status 'pending', index pada (status, created_at) sebagian besar terbuang untuk baris completed yang tidak akan pernah kamu query.
Partial index hanya mencakup apa yang kamu butuhkan:
CREATE INDEX idx_jobs_pending ON jobs (created_at)
WHERE status = 'pending';Index ini jauh lebih kecil, lebih cepat dikelola, dan bekerja sempurna untuk query yang dirancangnya. Trade-off-nya adalah ia hanya membantu query yang menyertakan kondisi WHERE yang sama — tapi jika itu menggambarkan pola query aktualmu, ini adalah kemenangan yang signifikan.
Memahami index bukan pengetahuan opsional bagi engineer yang bekerja dengan data pada skala besar. Ini adalah perbedaan antara sistem yang menurun dengan anggun dan yang berantakan dengan cara yang tampak misterius sampai kamu menjalankan EXPLAIN ANALYZE dan melihat sepuluh juta baris dipindai untuk hasil yang kamu butuhkan dalam seratus.