Bagian 4: Optimasi Query API

Database yang benar belum tentu database yang cepat. Blog dengan delapan artikel menolak percaya bahwa GET /artikel bisa menjadi masalah — sampai ia punya seribu artikel, endpoint feed dipanggil ribuan kali per menit, dan satu query yang lambat 50 milidetik memakan sebagian besar anggaran waktu respons. Pada bagian penutup ini kita memperbesar data menjadi 1.000 artikel, lalu mengoptimasi dengan disiplin yang sama seperti produksi: index dipasang karena pola akses endpoint mengharuskannya, setiap keputusan dibuktikan EXPLAIN, masalah N+1 ditangkap merah tangannya lewat pg_stat_statements, dan rencana eksekusi dibaca sebelum dipercaya.

Data Skala Endpoint #

Optimasi tanpa beban kerja nyata adalah tebak-tebakan. Berkas pembuka membangun ulang skema versi 2 lalu mengisi 1.000 artikel sekaligus lewat generate_series — setiap artikel bernomor seri unik, satu dari lima adalah draft, tiga penulis bergiliran, dan dua tag menempel pada tiap artikel:

sql
siapkan-tabel.sql
-- siapkan-tabel.sql — fondasi Bagian 4: skema versi 2 (Bagian 3) dengan
-- data skala API sungguhan: 1000 artikel hasil generate_series, dua tag
-- per artikel. Semua nilai deterministik: UUID dibangun dari nomor seri,
-- waktu dari literal tetap. Bagian ini mandiri dari database kosong.

CREATE TABLE pengguna (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email         text NOT NULL,
    password_hash text NOT NULL,
    dibuat_pada   timestamptz NOT NULL,
    dihapus_pada  timestamptz
);

CREATE TABLE artikel (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    penulis_id  uuid NOT NULL REFERENCES pengguna (id),
    judul       text NOT NULL,
    isi         text NOT NULL,
    status      text NOT NULL DEFAULT 'draft',
    dibuat_pada timestamptz NOT NULL,
    diubah_pada timestamptz NOT NULL,
    dihapus_pada timestamptz,
    CONSTRAINT judul_tidak_kosong CHECK (judul <> ''),
    CONSTRAINT isi_tidak_kosong  CHECK (isi <> ''),
    CONSTRAINT status_sah CHECK (status IN ('draft', 'published'))
);

CREATE TABLE tag (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    nama        text NOT NULL,
    dibuat_pada timestamptz NOT NULL,
    CONSTRAINT nama_tag_unik UNIQUE (nama),
    CONSTRAINT tag_selalu_lowercase CHECK (nama = lower(nama))
);

CREATE TABLE artikel_tag (
    artikel_id uuid NOT NULL REFERENCES artikel (id) ON DELETE CASCADE,
    tag_id     uuid NOT NULL REFERENCES tag (id) ON DELETE CASCADE,
    PRIMARY KEY (artikel_id, tag_id)
);

CREATE UNIQUE INDEX pengguna_email_aktif
    ON pengguna (email)
    WHERE dihapus_pada IS NULL;

INSERT INTO pengguna (id, email, password_hash, dibuat_pada)
VALUES
    ('11111111-1111-4111-8111-111111111111',
     '[email protected]', 'sandi_hash_budi_placeholder',
     TIMESTAMPTZ '2026-04-01 08:00:00+07'),
    ('22222222-2222-4222-8222-222222222222',
     '[email protected]', 'sandi_hash_siti_placeholder',
     TIMESTAMPTZ '2026-04-02 09:30:00+07'),
    ('33333333-3333-4333-8333-333333333333',
     '[email protected]', 'sandi_hash_dewi_placeholder',
     TIMESTAMPTZ '2026-04-03 10:15:00+07');

INSERT INTO tag (id, nama, dibuat_pada)
VALUES
    ('10000000-0000-4000-8000-000000000001', 'postgresql',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000002', 'sql',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000003', 'database',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000004', 'backend',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000005', 'kinerja',
     TIMESTAMPTZ '2026-04-01 08:05:00+07');

-- 1000 artikel: 1 dari 5 draft, sisanya published; tiga penulis bergiliran.
INSERT INTO artikel (id, penulis_id, judul, isi, status,
                     dibuat_pada, diubah_pada)
SELECT ('00000000-0000-4000-8000-' || lpad(g::text, 12, '0'))::uuid,
       CASE g % 3
            WHEN 0 THEN '11111111-1111-4111-8111-111111111111'
            WHEN 1 THEN '22222222-2222-4222-8222-222222222222'
            ELSE '33333333-3333-4333-8333-333333333333'
       END::uuid,
       'Catatan minggu ke-' || g,
       'Isi catatan minggu ke-' || g || ' untuk menguji query endpoint.',
       CASE WHEN g % 5 = 0 THEN 'draft' ELSE 'published' END,
       TIMESTAMPTZ '2026-01-05 06:00:00+07' + (g * interval '90 minutes'),
       TIMESTAMPTZ '2026-01-05 06:00:00+07' + (g * interval '90 minutes')
FROM generate_series(1, 1000) AS g;

-- Dua tag per artikel, bergiliran; tidak pernah ganda pada satu artikel.
INSERT INTO artikel_tag (artikel_id, tag_id)
SELECT ('00000000-0000-4000-8000-' || lpad(g::text, 12, '0'))::uuid,
       ('10000000-0000-4000-8000-'
        || lpad(((g % 5) + 1)::text, 12, '0'))::uuid
FROM generate_series(1, 1000) AS g;

INSERT INTO artikel_tag (artikel_id, tag_id)
SELECT ('00000000-0000-4000-8000-' || lpad(g::text, 12, '0'))::uuid,
       ('10000000-0000-4000-8000-'
        || lpad((((g + 2) % 5) + 1)::text, 12, '0'))::uuid
FROM generate_series(1, 1000) AS g;

ANALYZE pengguna;
ANALYZE artikel;
ANALYZE tag;
ANALYZE artikel_tag;

-- Kondisi awal bagian.
SELECT count(*) AS total_artikel,
       count(*) FILTER (WHERE status = 'draft') AS draft,
       count(*) FILTER (WHERE status = 'published') AS published
FROM artikel;

SELECT count(*) AS total_hubungan_tag
FROM artikel_tag;

Output yang Dihasilkan:

text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
INSERT 0 3
INSERT 0 5
INSERT 0 1000
INSERT 0 1000
INSERT 0 1000
ANALYZE
ANALYZE
ANALYZE
ANALYZE
 total_artikel | draft | published 
---------------+-------+-----------
          1000 |   200 |       800
(1 row)

 total_hubungan_tag 
--------------------
               2000
(1 row)

Semua nilai tetap deterministik: UUID dibangun dari nomor seri (lpad(g::text, 12, '0')), dan dibuat_pada dihitung dari literal tetap ditambah kelipatan 90 menit — sehingga 1.000 artikel menempati rentang waktu hampir dua bulan dan urutannya stabil. ANALYZE di akhir berkas memperbarui statistik tabel supaya perencana query mengenal datanya — langkah kecil yang penting sebelum membaca EXPLAIN.

Audit Index untuk Beban Kerja #

Tiga endpoint paling sibuk menuntut tiga pola akses berbeda. Feed membaca “artikel terbit terbaru, terurut, sehalaman” — index untuknya dibedah lewat EXPLAIN di subbab berikutnya. Dua pola lainnya ditutup di sini:

sql
indeks-endpoint.sql
-- indeks-endpoint.sql — audit index untuk beban kerja endpoint.
-- Index umpan (feed) dibedah terpisah lewat EXPLAIN pada berkas berikutnya;
-- di sini dua pola akses lain ditutup: cari artikel menurut penulis, dan
-- filter artikel menurut tag.

-- GET /pengguna/:id/artikel — halaman profil penulis menyaring penulis_id;
-- foreign key TIDAK membuat index otomatis, jadi dipasang manual.
CREATE INDEX idx_artikel_penulis ON artikel (penulis_id);

-- GET /artikel?tag=... — pola aksesnya "dari tag ke daftar artikel":
-- index komposit (tag_id, artikel_id) mengikuti arah tersebut.
CREATE INDEX idx_artikel_tag_tag ON artikel_tag (tag_id, artikel_id);

-- Audit: seluruh index tabel proyek setelah pemasangan manual.
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename IN ('pengguna', 'artikel', 'tag', 'artikel_tag')
ORDER BY tablename, indexname;

Output yang Dihasilkan:

text
CREATE INDEX
CREATE INDEX
  tablename  |      indexname       |                                                   indexdef                                                   
-------------+----------------------+--------------------------------------------------------------------------------------------------------------
 artikel     | artikel_pkey         | CREATE UNIQUE INDEX artikel_pkey ON public.artikel USING btree (id)
 artikel     | idx_artikel_penulis  | CREATE INDEX idx_artikel_penulis ON public.artikel USING btree (penulis_id)
 artikel_tag | artikel_tag_pkey     | CREATE UNIQUE INDEX artikel_tag_pkey ON public.artikel_tag USING btree (artikel_id, tag_id)
 artikel_tag | idx_artikel_tag_tag  | CREATE INDEX idx_artikel_tag_tag ON public.artikel_tag USING btree (tag_id, artikel_id)
 pengguna    | pengguna_email_aktif | CREATE UNIQUE INDEX pengguna_email_aktif ON public.pengguna USING btree (email) WHERE (dihapus_pada IS NULL)
 pengguna    | pengguna_pkey        | CREATE UNIQUE INDEX pengguna_pkey ON public.pengguna USING btree (id)
 tag         | nama_tag_unik        | CREATE UNIQUE INDEX nama_tag_unik ON public.tag USING btree (nama)
 tag         | tag_pkey             | CREATE UNIQUE INDEX tag_pkey ON public.tag USING btree (id)
(8 rows)

idx_artikel_penulis: halaman profil penulis (GET /pengguna/:id/artikel) selalu menyaring penulis_id — dan foreign key, sekali lagi, tidak membuat index otomatis.
idx_artikel_tag_tag (tag_id, artikel_id): filter ?tag=... berjalan dari tag ke daftar artikel; urutan kolom komposit mengikuti arah akses itu, persis materi chapter Composite Index .
Daftar akhir memperlihatkan delapan index — dua di antaranya kerja manual barusan, sisanya lahir dari primary key, constraint unik, dan index parsial email dari Bagian 3.

EXPLAIN: Sebelum dan Sesudah #

Sekarang buktinya. Untuk endpoint terpanas — feed — kita sengaja menjatuhkan index-nya sejenak, membaca rencana eksekusi tanpa index, lalu memasangnya kembali dan membaca ulang:

sql
explain-endpoint.sql
-- explain-endpoint.sql — bukti EXPLAIN sebelum dan sesudah index untuk
-- endpoint terpanas: GET /artikel (feed). Berkas ini tidak dieksekusi
-- otomatis karena bentuk rencana eksekusi dapat berbeda antar mesin;
-- output di bawah adalah hasil eksekusi nyata dan angka cost serta
-- estimasi rows dapat berbeda di lingkungan Anda.

DROP INDEX IF EXISTS idx_artikel_umpan;

-- SEBELUM: tanpa index, feed memindai seluruh tabel lalu mengurutkan.
EXPLAIN
SELECT id, judul, dibuat_pada
FROM artikel
WHERE status = 'published' AND dihapus_pada IS NULL
ORDER BY dibuat_pada DESC, id DESC
LIMIT 5;

-- SESUDAH: index parsial yang meniru persis bentuk query endpoint —
-- hanya baris published yang belum dihapus, sudah terurut.
CREATE INDEX idx_artikel_umpan
    ON artikel (dibuat_pada DESC, id DESC)
    WHERE status = 'published' AND dihapus_pada IS NULL;

EXPLAIN
SELECT id, judul, dibuat_pada
FROM artikel
WHERE status = 'published' AND dihapus_pada IS NULL
ORDER BY dibuat_pada DESC, id DESC
LIMIT 5;

-- Halaman keyset jauh di dalam data (artikel ke-500): perbandingan baris
-- ROW(...) dilayani langsung sebagai kondisi index, biayanya tetap kecil.
EXPLAIN
SELECT id, judul, dibuat_pada
FROM artikel
WHERE (dibuat_pada, id) < (TIMESTAMPTZ '2026-02-05 12:00:00+07',
                            '00000000-0000-4000-8000-000000000500'::uuid)
  AND status = 'published'
  AND dihapus_pada IS NULL
ORDER BY dibuat_pada DESC, id DESC
LIMIT 5;

Output yang Dihasilkan:

text
DROP INDEX
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Limit  (cost=46.79..46.80 rows=5 width=45)
   ->  Sort  (cost=46.79..48.79 rows=800 width=45)
         Sort Key: dibuat_pada DESC, id DESC
         ->  Seq Scan on artikel  (cost=0.00..33.50 rows=800 width=45)
               Filter: ((dihapus_pada IS NULL) AND (status = 'published'::text))
(5 rows)

CREATE INDEX
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Limit  (cost=0.28..0.80 rows=5 width=45)
   ->  Index Scan using idx_artikel_umpan on artikel  (cost=0.28..84.28 rows=800 width=45)
(2 rows)

                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.28..1.06 rows=5 width=45)
   ->  Index Scan using idx_artikel_umpan on artikel  (cost=0.28..62.76 rows=399 width=45)
         Index Cond: (ROW(dibuat_pada, id) < ROW('2026-02-05 12:00:00+07'::timestamp with time zone, '00000000-0000-4000-8000-000000000500'::uuid))
(3 rows)

Cara membaca ketiga rencana tersebut:

Sebelum: Seq Scan on artikel plus node Sort — database membaca seluruh 1.000 baris, menyaring 800 yang terbit, mengurutkan semuanya, lalu membuang 795 untuk menyerahkan 5. Pekerjaan yang membengkak bersama data.
Sesudah: Index Scan using idx_artikel_umpan — index parsial (dibuat_pada DESC, id DESC) WHERE status = 'published' AND dihapus_pada IS NULL meniru persis bentuk query endpoint: isinya hanya baris terbit, urutannya sudah siap, dan LIMIT 5 berhenti setelah lima entri. Index ini bahkan tidak menampung 200 draft dan baris terhapus — dibahas di chapter Partial Index .
Halaman keyset jauh di dalam data: perbandingan baris ROW(dibuat_pada, id) < ROW(...) tampil sebagai Index Cond — dilayani langsung oleh index, bukan filter pasca-scan. Inilah janji pagination keyset dari Bagian 2 jadi kenyataan: halaman ke-1 dan halaman ke-500 sama murahnya.

Pola umumnya sederhana dan keras: bentuk index mengikuti bentuk query. WHERE query menjadi klausa WHERE index parsial; ORDER BY ... LIMIT menjadi urutan kolomnya. Membaca rencana eksekusi adalah keterampilan tersendiri — latih dengan chapter EXPLAIN dan Membaca Query Execution Plan .

Masalah N+1 #

Endpoint daftar yang menyertakan tag punya dua cara mengerjakan: satu query join, atau satu query daftar ditambah satu query tag per artikel. Cara kedua — pola N+1 — adalah penyakit klasis ORM: kode terlihat bersih, query-nya berbunyi diam-diam. Dengan halaman 3 artikel bedanya baru terasa kecil; dengan halaman 20, aplikasi mengirim 21 query per request:

sql
n-plus-satu.sql
-- n-plus-satu.sql — masalah N+1 pada endpoint daftar yang memuat tag.
-- Pola salah: satu query daftar, lalu satu query tag PER ARTIKEL (orm
-- yang malas melakukan ini diam-diam). Pola benar: satu query join.
-- pg_stat_statements dipakai sebagai hitungan bukti.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Statistik statement dikosongkan dulu agar hitungan di akhir berkas
-- hanya mencerminkan berkas ini; keluaran reset disembunyikan.
SELECT pg_stat_statements_reset() \g /dev/null

-- Langkah 1: GET /artikel — halaman 1, tiga artikel terbaru.
SELECT a.id, a.judul, a.dibuat_pada
FROM artikel a
WHERE a.status = 'published'
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC
LIMIT 3;

-- Langkah 2, pola N+1: aplikasi menagih tag satu per satu (3 query).
SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), '[]'::jsonb) AS tags FROM artikel a LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id LEFT JOIN tag t ON t.id = at2.tag_id WHERE a.id = '00000000-0000-4000-8000-000000000999' AND a.dihapus_pada IS NULL GROUP BY a.id, a.judul;

SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), '[]'::jsonb) AS tags FROM artikel a LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id LEFT JOIN tag t ON t.id = at2.tag_id WHERE a.id = '00000000-0000-4000-8000-000000000998' AND a.dihapus_pada IS NULL GROUP BY a.id, a.judul;

SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), '[]'::jsonb) AS tags FROM artikel a LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id LEFT JOIN tag t ON t.id = at2.tag_id WHERE a.id = '00000000-0000-4000-8000-000000000997' AND a.dihapus_pada IS NULL GROUP BY a.id, a.judul;

-- Langkah 3, satu query: seluruh halaman beserta tag-nya sekaligus.
SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), '[]'::jsonb) AS tags
FROM artikel a
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
LEFT JOIN tag t ON t.id = at2.tag_id
WHERE a.status = 'published'
  AND a.dihapus_pada IS NULL
GROUP BY a.id, a.judul, a.dibuat_pada
ORDER BY a.dibuat_pada DESC, a.id DESC
LIMIT 3;

-- Bukti hitungan: query per-item terpanggil 3 kali, alternatif join 1 kali.
-- Nilai konstanta pada teks statement diganti placeholder $n oleh
-- pg_stat_statements; yang diagregasi adalah pola query-nya.
SELECT calls, query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database
              WHERE datname = current_database())
  AND query <> 'SELECT pg_stat_statements_reset()'
ORDER BY calls DESC, query;

Output yang Dihasilkan:

text
CREATE EXTENSION
                  id                  |         judul         |      dibuat_pada       
--------------------------------------+-----------------------+------------------------
 00000000-0000-4000-8000-000000000999 | Catatan minggu ke-999 | 2026-03-08 16:30:00+07
 00000000-0000-4000-8000-000000000998 | Catatan minggu ke-998 | 2026-03-08 15:00:00+07
 00000000-0000-4000-8000-000000000997 | Catatan minggu ke-997 | 2026-03-08 13:30:00+07
(3 rows)

         judul         |        tags        
-----------------------+--------------------
 Catatan minggu ke-999 | ["kinerja", "sql"]
(1 row)

         judul         |           tags            
-----------------------+---------------------------
 Catatan minggu ke-998 | ["backend", "postgresql"]
(1 row)

         judul         |          tags           
-----------------------+-------------------------
 Catatan minggu ke-997 | ["database", "kinerja"]
(1 row)

         judul         |           tags            
-----------------------+---------------------------
 Catatan minggu ke-999 | ["kinerja", "sql"]
 Catatan minggu ke-998 | ["backend", "postgresql"]
 Catatan minggu ke-997 | ["database", "kinerja"]
(3 rows)

 calls |                                                                                                                                           query                                                                                                                                           
-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     3 | SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), $1::jsonb) AS tags FROM artikel a LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id LEFT JOIN tag t ON t.id = at2.tag_id WHERE a.id = $2 AND a.dihapus_pada IS NULL GROUP BY a.id, a.judul
     1 | SELECT a.id, a.judul, a.dibuat_pada                                                                                                                                                                                                                                                      +
       | FROM artikel a                                                                                                                                                                                                                                                                           +
       | WHERE a.status = $1                                                                                                                                                                                                                                                                      +
       |   AND a.dihapus_pada IS NULL                                                                                                                                                                                                                                                             +
       | ORDER BY a.dibuat_pada DESC, a.id DESC                                                                                                                                                                                                                                                   +
       | LIMIT $2
     1 | SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), $1::jsonb) AS tags                                                                                                                                                                         +
       | FROM artikel a                                                                                                                                                                                                                                                                           +
       | LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id                                                                                                                                                                                                                                       +
       | LEFT JOIN tag t ON t.id = at2.tag_id                                                                                                                                                                                                                                                     +
       | WHERE a.status = $2                                                                                                                                                                                                                                                                      +
       |   AND a.dihapus_pada IS NULL                                                                                                                                                                                                                                                             +
       | GROUP BY a.id, a.judul, a.dibuat_pada                                                                                                                                                                                                                                                    +
       | ORDER BY a.dibuat_pada DESC, a.id DESC                                                                                                                                                                                                                                                   +
       | LIMIT $3
(3 rows)

Empat hasil per-item dan satu hasil join memperlihatkan data yang sama. Buktinya yang berbeda — dan diit oleh pg_stat_statements: query per-item terpanggil 3 kali (satu per artikel), alternatif join 1 kali. Extension ini mengagregasi statement per pola query — nilai konstanta diganti placeholder $n — sehingga kolom calls membaca berapa kali pola itu dieksekusi. Rumusnya lugas: halaman berisi N artikel memicu 1 + N query pada pola pertama, dan tetap 1 query pada pola join. Extension perlu dipasang sekali (CREATE EXTENSION) dan statistiknya di-nol-kan dulu dengan pg_stat_statements_reset() agar hitungan bersih; keluaran fungsinya yang berisi waktu eksekusi disembunyikan dengan \g /dev/null.

Radar Endpoint: pg_stat_statements #

Di produksi, beban kerja tidak ditulis tangan — ia terjadi, dan Anda membacanya setelahnya. Berkas terakhir meniru satu menit lalu lintas API (feed 3x, detail 2x, filter tag 2x, POST 1x), lalu membaca radar:

sql
statemen-panas.sql
-- statemen-panas.sql — pg_stat_statements sebagai radar endpoint:
-- jalankan beban kerja satu menit, lalu bacalah query mana yang paling
-- sering dipanggil. Keluaran tiap query beban kerja disembunyikan dengan
-- \g /dev/null karena polanya sudah dibahas; yang dibaca adalah hitungan.
-- Jalankan setelah n-plus-satu.sql (extension pg_stat_statements sudah ada).

SELECT pg_stat_statements_reset() \g /dev/null

-- GET /artikel (feed) — dipanggil tiga kali.
SELECT id, judul FROM artikel WHERE status = 'published' AND dihapus_pada IS NULL ORDER BY dibuat_pada DESC, id DESC LIMIT 5 \g /dev/null
SELECT id, judul FROM artikel WHERE status = 'published' AND dihapus_pada IS NULL ORDER BY dibuat_pada DESC, id DESC LIMIT 5 \g /dev/null
SELECT id, judul FROM artikel WHERE status = 'published' AND dihapus_pada IS NULL ORDER BY dibuat_pada DESC, id DESC LIMIT 5 \g /dev/null

-- GET /artikel/:id — dipanggil dua kali.
SELECT id, judul FROM artikel WHERE id = '00000000-0000-4000-8000-000000000997' AND dihapus_pada IS NULL \g /dev/null
SELECT id, judul FROM artikel WHERE id = '00000000-0000-4000-8000-000000000997' AND dihapus_pada IS NULL \g /dev/null

-- GET /artikel?tag=kinerja — dipanggil dua kali.
SELECT a.id, a.judul FROM artikel a WHERE a.dihapus_pada IS NULL AND EXISTS (SELECT 1 FROM artikel_tag at2 JOIN tag t ON t.id = at2.tag_id WHERE at2.artikel_id = a.id AND t.nama = 'kinerja') ORDER BY a.dibuat_pada DESC, a.id DESC LIMIT 5 \g /dev/null
SELECT a.id, a.judul FROM artikel a WHERE a.dihapus_pada IS NULL AND EXISTS (SELECT 1 FROM artikel_tag at2 JOIN tag t ON t.id = at2.tag_id WHERE at2.artikel_id = a.id AND t.nama = 'kinerja') ORDER BY a.dibuat_pada DESC, a.id DESC LIMIT 5 \g /dev/null

-- POST /artikel — dipanggil sekali. id dihasilkan gen_random_uuid()
-- dan tidak pernah ditampilkan, sehingga berkas ini aman diulang.
INSERT INTO artikel (penulis_id, judul, isi, status,
                     dibuat_pada, diubah_pada)
VALUES ('33333333-3333-4333-8333-333333333333',
        'Beban kerja uji',
        'Artikel ini bagian dari simulasi beban endpoint.',
        'draft',
        TIMESTAMPTZ '2026-03-09 08:00:00+07',
        TIMESTAMPTZ '2026-03-09 08:00:00+07');

-- Radar satu menit: endpoint diurutkan menurut jumlah pemanggilan.
SELECT calls, query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database
              WHERE datname = current_database())
  AND query <> 'SELECT pg_stat_statements_reset()'
ORDER BY calls DESC, query;

Output yang Dihasilkan:

text
INSERT 0 1
 calls |                                                                                                                  query                                                                                                                   
-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     3 | SELECT id, judul FROM artikel WHERE status = $1 AND dihapus_pada IS NULL ORDER BY dibuat_pada DESC, id DESC LIMIT $2
     2 | SELECT a.id, a.judul FROM artikel a WHERE a.dihapus_pada IS NULL AND EXISTS (SELECT $1 FROM artikel_tag at2 JOIN tag t ON t.id = at2.tag_id WHERE at2.artikel_id = a.id AND t.nama = $2) ORDER BY a.dibuat_pada DESC, a.id DESC LIMIT $3
     2 | SELECT id, judul FROM artikel WHERE id = $1 AND dihapus_pada IS NULL
     1 | INSERT INTO artikel (penulis_id, judul, isi, status,                                                                                                                                                                                    +
       |                      dibuat_pada, diubah_pada)                                                                                                                                                                                          +
       | VALUES ($1,                                                                                                                                                                                                                             +
       |         $2,                                                                                                                                                                                                                             +
       |         $3,                                                                                                                                                                                                                             +
       |         $4,                                                                                                                                                                                                                             +
       |         TIMESTAMPTZ $5,                                                                                                                                                                                                                 +
       |         TIMESTAMPTZ $6)
(4 rows)

Satu tabel kecil itu adalah peta panas endpoint: feed paling sering dipanggil, disusul detail dan filter tag. Dari sini keputusan optimasi punya urutan: feed sudah tertolong idx_artikel_umpan, dan bila calls filter tag terus membesar relatif terhadap lainnya, idx_artikel_tag_tag adalah investasi berikutnya. Kolom yang stabil dibaca — query dan calls — sementara kolom waktu seperti total_exec_time berguna namun nilainya bervariasi; praktik membacanya dibahas di chapter Mendeteksi Slow Queries .

Rekap Operasional: Connection Pooling #

Satu topik terakhir menutup proyek: setiap request API yang sampai ke database membuka… sebaiknya bukan koneksi baru. Proses fork per koneksi itu mahal, dan ribuan request per menit akan menghantam batas max_connections jauh sebelum CPU-nya berdesis. Pola produksinya adalah connection pooling: sekelompok koneksi yang dibuka sekali lalu dipinjamkan per request — lewat PgBouncer di sisi server, atau pool internal driver seperti psycopg pool dan pgx. Detail lengkapnya, termasuk kapan PgBouncer dibutuhkan, dibahas di chapter Database Connection Pooling ; dari sisi skema dan query, Anda sudah membawa bekal lengkap: index yang mengikuti bentuk query, transaksi yang pendek, dan radar untuk membaca beban.

Dengan itu, proyek keenam selesai — dan bersamanya seluruh Section Proyek. Enam database dari enam dunia berbeda (perpustakaan, gudang, kasir, dompet, lahan, dan API) memakai satu kumpulan prinsip yang sama: skema yang mengunci kejujuran data, transaksi yang menjaga kesatuan operasi, query yang menjawab pertanyaan nyata, dan bukti — selalu bukti — sebelum percaya bahwa semuanya bekerja. Kemanapun Anda melangkah setelah ini, dokumentasi resmi PostgreSQL adalah teman terdekat Anda: cara menemukan jawaban di dalamnya dibahas di pembuka Section Sumber Belajar .

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- explain-endpoint.sql — bukti EXPLAIN sebelum dan sesudah index untuk
-- endpoint terpanas: GET /artikel (feed). Berkas ini tidak dieksekusi
-- otomatis karena bentuk rencana eksekusi dapat berbeda antar mesin;
-- output di bawah adalah hasil eksekusi nyata dan angka cost serta
-- estimasi rows dapat berbeda di lingkungan Anda.

DROP INDEX IF EXISTS idx_artikel_umpan;

-- SEBELUM: tanpa index, feed memindai seluruh tabel lalu mengurutkan.
EXPLAIN
SELECT id, judul, dibuat_pada
FROM artikel
WHERE status = 'published' AND dihapus_pada IS NULL
ORDER BY dibuat_pada DESC, id DESC
LIMIT 5;

-- SESUDAH: index parsial yang meniru persis bentuk query endpoint —
-- hanya baris published yang belum dihapus, sudah terurut.
CREATE INDEX idx_artikel_umpan
    ON artikel (dibuat_pada DESC, id DESC)
    WHERE status = 'published' AND dihapus_pada IS NULL;

EXPLAIN
SELECT id, judul, dibuat_pada
FROM artikel
WHERE status = 'published' AND dihapus_pada IS NULL
ORDER BY dibuat_pada DESC, id DESC
LIMIT 5;

-- Halaman keyset jauh di dalam data (artikel ke-500): perbandingan baris
-- ROW(...) dilayani langsung sebagai kondisi index, biayanya tetap kecil.
EXPLAIN
SELECT id, judul, dibuat_pada
FROM artikel
WHERE (dibuat_pada, id) < (TIMESTAMPTZ '2026-02-05 12:00:00+07',
                            '00000000-0000-4000-8000-000000000500'::uuid)
  AND status = 'published'
  AND dihapus_pada IS NULL
ORDER BY dibuat_pada DESC, id DESC
LIMIT 5;

sql
-- indeks-endpoint.sql — audit index untuk beban kerja endpoint.
-- Index umpan (feed) dibedah terpisah lewat EXPLAIN pada berkas berikutnya;
-- di sini dua pola akses lain ditutup: cari artikel menurut penulis, dan
-- filter artikel menurut tag.

-- GET /pengguna/:id/artikel — halaman profil penulis menyaring penulis_id;
-- foreign key TIDAK membuat index otomatis, jadi dipasang manual.
CREATE INDEX idx_artikel_penulis ON artikel (penulis_id);

-- GET /artikel?tag=... — pola aksesnya "dari tag ke daftar artikel":
-- index komposit (tag_id, artikel_id) mengikuti arah tersebut.
CREATE INDEX idx_artikel_tag_tag ON artikel_tag (tag_id, artikel_id);

-- Audit: seluruh index tabel proyek setelah pemasangan manual.
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename IN ('pengguna', 'artikel', 'tag', 'artikel_tag')
ORDER BY tablename, indexname;

sql
-- n-plus-satu.sql — masalah N+1 pada endpoint daftar yang memuat tag.
-- Pola salah: satu query daftar, lalu satu query tag PER ARTIKEL (orm
-- yang malas melakukan ini diam-diam). Pola benar: satu query join.
-- pg_stat_statements dipakai sebagai hitungan bukti.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Statistik statement dikosongkan dulu agar hitungan di akhir berkas
-- hanya mencerminkan berkas ini; keluaran reset disembunyikan.
SELECT pg_stat_statements_reset() \g /dev/null

-- Langkah 1: GET /artikel — halaman 1, tiga artikel terbaru.
SELECT a.id, a.judul, a.dibuat_pada
FROM artikel a
WHERE a.status = 'published'
  AND a.dihapus_pada IS NULL
ORDER BY a.dibuat_pada DESC, a.id DESC
LIMIT 3;

-- Langkah 2, pola N+1: aplikasi menagih tag satu per satu (3 query).
SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), '[]'::jsonb) AS tags FROM artikel a LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id LEFT JOIN tag t ON t.id = at2.tag_id WHERE a.id = '00000000-0000-4000-8000-000000000999' AND a.dihapus_pada IS NULL GROUP BY a.id, a.judul;

SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), '[]'::jsonb) AS tags FROM artikel a LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id LEFT JOIN tag t ON t.id = at2.tag_id WHERE a.id = '00000000-0000-4000-8000-000000000998' AND a.dihapus_pada IS NULL GROUP BY a.id, a.judul;

SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), '[]'::jsonb) AS tags FROM artikel a LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id LEFT JOIN tag t ON t.id = at2.tag_id WHERE a.id = '00000000-0000-4000-8000-000000000997' AND a.dihapus_pada IS NULL GROUP BY a.id, a.judul;

-- Langkah 3, satu query: seluruh halaman beserta tag-nya sekaligus.
SELECT a.judul, COALESCE(jsonb_agg(t.nama ORDER BY t.nama) FILTER (WHERE t.nama IS NOT NULL), '[]'::jsonb) AS tags
FROM artikel a
LEFT JOIN artikel_tag at2 ON at2.artikel_id = a.id
LEFT JOIN tag t ON t.id = at2.tag_id
WHERE a.status = 'published'
  AND a.dihapus_pada IS NULL
GROUP BY a.id, a.judul, a.dibuat_pada
ORDER BY a.dibuat_pada DESC, a.id DESC
LIMIT 3;

-- Bukti hitungan: query per-item terpanggil 3 kali, alternatif join 1 kali.
-- Nilai konstanta pada teks statement diganti placeholder $n oleh
-- pg_stat_statements; yang diagregasi adalah pola query-nya.
SELECT calls, query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database
              WHERE datname = current_database())
  AND query <> 'SELECT pg_stat_statements_reset()'
ORDER BY calls DESC, query;

sql
-- siapkan-tabel.sql — fondasi Bagian 4: skema versi 2 (Bagian 3) dengan
-- data skala API sungguhan: 1000 artikel hasil generate_series, dua tag
-- per artikel. Semua nilai deterministik: UUID dibangun dari nomor seri,
-- waktu dari literal tetap. Bagian ini mandiri dari database kosong.

CREATE TABLE pengguna (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email         text NOT NULL,
    password_hash text NOT NULL,
    dibuat_pada   timestamptz NOT NULL,
    dihapus_pada  timestamptz
);

CREATE TABLE artikel (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    penulis_id  uuid NOT NULL REFERENCES pengguna (id),
    judul       text NOT NULL,
    isi         text NOT NULL,
    status      text NOT NULL DEFAULT 'draft',
    dibuat_pada timestamptz NOT NULL,
    diubah_pada timestamptz NOT NULL,
    dihapus_pada timestamptz,
    CONSTRAINT judul_tidak_kosong CHECK (judul <> ''),
    CONSTRAINT isi_tidak_kosong  CHECK (isi <> ''),
    CONSTRAINT status_sah CHECK (status IN ('draft', 'published'))
);

CREATE TABLE tag (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    nama        text NOT NULL,
    dibuat_pada timestamptz NOT NULL,
    CONSTRAINT nama_tag_unik UNIQUE (nama),
    CONSTRAINT tag_selalu_lowercase CHECK (nama = lower(nama))
);

CREATE TABLE artikel_tag (
    artikel_id uuid NOT NULL REFERENCES artikel (id) ON DELETE CASCADE,
    tag_id     uuid NOT NULL REFERENCES tag (id) ON DELETE CASCADE,
    PRIMARY KEY (artikel_id, tag_id)
);

CREATE UNIQUE INDEX pengguna_email_aktif
    ON pengguna (email)
    WHERE dihapus_pada IS NULL;

INSERT INTO pengguna (id, email, password_hash, dibuat_pada)
VALUES
    ('11111111-1111-4111-8111-111111111111',
     '[email protected]', 'sandi_hash_budi_placeholder',
     TIMESTAMPTZ '2026-04-01 08:00:00+07'),
    ('22222222-2222-4222-8222-222222222222',
     '[email protected]', 'sandi_hash_siti_placeholder',
     TIMESTAMPTZ '2026-04-02 09:30:00+07'),
    ('33333333-3333-4333-8333-333333333333',
     '[email protected]', 'sandi_hash_dewi_placeholder',
     TIMESTAMPTZ '2026-04-03 10:15:00+07');

INSERT INTO tag (id, nama, dibuat_pada)
VALUES
    ('10000000-0000-4000-8000-000000000001', 'postgresql',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000002', 'sql',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000003', 'database',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000004', 'backend',
     TIMESTAMPTZ '2026-04-01 08:05:00+07'),
    ('10000000-0000-4000-8000-000000000005', 'kinerja',
     TIMESTAMPTZ '2026-04-01 08:05:00+07');

-- 1000 artikel: 1 dari 5 draft, sisanya published; tiga penulis bergiliran.
INSERT INTO artikel (id, penulis_id, judul, isi, status,
                     dibuat_pada, diubah_pada)
SELECT ('00000000-0000-4000-8000-' || lpad(g::text, 12, '0'))::uuid,
       CASE g % 3
            WHEN 0 THEN '11111111-1111-4111-8111-111111111111'
            WHEN 1 THEN '22222222-2222-4222-8222-222222222222'
            ELSE '33333333-3333-4333-8333-333333333333'
       END::uuid,
       'Catatan minggu ke-' || g,
       'Isi catatan minggu ke-' || g || ' untuk menguji query endpoint.',
       CASE WHEN g % 5 = 0 THEN 'draft' ELSE 'published' END,
       TIMESTAMPTZ '2026-01-05 06:00:00+07' + (g * interval '90 minutes'),
       TIMESTAMPTZ '2026-01-05 06:00:00+07' + (g * interval '90 minutes')
FROM generate_series(1, 1000) AS g;

-- Dua tag per artikel, bergiliran; tidak pernah ganda pada satu artikel.
INSERT INTO artikel_tag (artikel_id, tag_id)
SELECT ('00000000-0000-4000-8000-' || lpad(g::text, 12, '0'))::uuid,
       ('10000000-0000-4000-8000-'
        || lpad(((g % 5) + 1)::text, 12, '0'))::uuid
FROM generate_series(1, 1000) AS g;

INSERT INTO artikel_tag (artikel_id, tag_id)
SELECT ('00000000-0000-4000-8000-' || lpad(g::text, 12, '0'))::uuid,
       ('10000000-0000-4000-8000-'
        || lpad((((g + 2) % 5) + 1)::text, 12, '0'))::uuid
FROM generate_series(1, 1000) AS g;

ANALYZE pengguna;
ANALYZE artikel;
ANALYZE tag;
ANALYZE artikel_tag;

-- Kondisi awal bagian.
SELECT count(*) AS total_artikel,
       count(*) FILTER (WHERE status = 'draft') AS draft,
       count(*) FILTER (WHERE status = 'published') AS published
FROM artikel;

SELECT count(*) AS total_hubungan_tag
FROM artikel_tag;

sql
-- statemen-panas.sql — pg_stat_statements sebagai radar endpoint:
-- jalankan beban kerja satu menit, lalu bacalah query mana yang paling
-- sering dipanggil. Keluaran tiap query beban kerja disembunyikan dengan
-- \g /dev/null karena polanya sudah dibahas; yang dibaca adalah hitungan.
-- Jalankan setelah n-plus-satu.sql (extension pg_stat_statements sudah ada).

SELECT pg_stat_statements_reset() \g /dev/null

-- GET /artikel (feed) — dipanggil tiga kali.
SELECT id, judul FROM artikel WHERE status = 'published' AND dihapus_pada IS NULL ORDER BY dibuat_pada DESC, id DESC LIMIT 5 \g /dev/null
SELECT id, judul FROM artikel WHERE status = 'published' AND dihapus_pada IS NULL ORDER BY dibuat_pada DESC, id DESC LIMIT 5 \g /dev/null
SELECT id, judul FROM artikel WHERE status = 'published' AND dihapus_pada IS NULL ORDER BY dibuat_pada DESC, id DESC LIMIT 5 \g /dev/null

-- GET /artikel/:id — dipanggil dua kali.
SELECT id, judul FROM artikel WHERE id = '00000000-0000-4000-8000-000000000997' AND dihapus_pada IS NULL \g /dev/null
SELECT id, judul FROM artikel WHERE id = '00000000-0000-4000-8000-000000000997' AND dihapus_pada IS NULL \g /dev/null

-- GET /artikel?tag=kinerja — dipanggil dua kali.
SELECT a.id, a.judul FROM artikel a WHERE a.dihapus_pada IS NULL AND EXISTS (SELECT 1 FROM artikel_tag at2 JOIN tag t ON t.id = at2.tag_id WHERE at2.artikel_id = a.id AND t.nama = 'kinerja') ORDER BY a.dibuat_pada DESC, a.id DESC LIMIT 5 \g /dev/null
SELECT a.id, a.judul FROM artikel a WHERE a.dihapus_pada IS NULL AND EXISTS (SELECT 1 FROM artikel_tag at2 JOIN tag t ON t.id = at2.tag_id WHERE at2.artikel_id = a.id AND t.nama = 'kinerja') ORDER BY a.dibuat_pada DESC, a.id DESC LIMIT 5 \g /dev/null

-- POST /artikel — dipanggil sekali. id dihasilkan gen_random_uuid()
-- dan tidak pernah ditampilkan, sehingga berkas ini aman diulang.
INSERT INTO artikel (penulis_id, judul, isi, status,
                     dibuat_pada, diubah_pada)
VALUES ('33333333-3333-4333-8333-333333333333',
        'Beban kerja uji',
        'Artikel ini bagian dari simulasi beban endpoint.',
        'draft',
        TIMESTAMPTZ '2026-03-09 08:00:00+07',
        TIMESTAMPTZ '2026-03-09 08:00:00+07');

-- Radar satu menit: endpoint diurutkan menurut jumlah pemanggilan.
SELECT calls, query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database
              WHERE datname = current_database())
  AND query <> 'SELECT pg_stat_statements_reset()'
ORDER BY calls DESC, query;