Aplikasi mengeluh lambat, tapi lambat di mana? Tanpa data, tuning PostgreSQL menjadi tebak-tebakan: kita menebak index yang kurang, menebak join yang salah, menebak tabel yang membesar. Extension pg_stat_statements menghilangkan semua tebakan itu dengan mencatat setiap statement yang dieksekusi server — teksnya, jumlah pemanggilannya, baris hasilnya, waktu eksekusinya, sampai I/O block-nya — ke dalam satu view yang bisa di-query seperti tabel biasa. Extension ini adalah profiler standar de facto: hampir semua tool monitoring PostgreSQL, dari dashboard internal sampai layanan analitik query, membaca sumber yang sama. Chapter ini mengaktifkannya, membaca keluarannya, dan membangun laporan query tersering dari nol.

Syarat Aktifasi: shared_preload_libraries #

pg_stat_statements adalah satu-satunya extension di section ini yang menuntut persiapan server. Modulnya harus dimuat saat startup lewat setting shared_preload_libraries, karena statistiknya disimpan di shared memory yang alokasinya harus ditetapkan sebelum klien pertama menyambung — tidak bisa disiapkan kapan pun setelah server hidup. Konsekuensinya: mengubah setting ini memerlukan restart server, sesuatu yang tidak pernah dibutuhkan pgcrypto atau citext. Server kursus sudah mengaturnya sejak persiapan lingkungan, sehingga aktivasinya tinggal satu perintah CREATE EXTENSION:

sql aktifkan.sql 
-- pg_stat_statements wajib dimuat server lewat shared_preload_libraries
-- sebelum extension bisa dipasang; server kursus sudah mengaturnya.
CREATE EXTENSION pg_stat_statements;

SHOW shared_preload_libraries;

-- Batas jumlah query berbeda yang dilacak (default 5000).
SHOW pg_stat_statements.max;

-- Mode pelacakan: 'top' berarti tiap statement tingkat atas dihitung.
SHOW pg_stat_statements.track;

-- Statistik bertahan lewat restart server bila 'on'.
SHOW pg_stat_statements.save;

Output yang Dihasilkan:

text 
CREATE EXTENSION
 shared_preload_libraries 
--------------------------
 pg_stat_statements
(1 row)

 pg_stat_statements.max 
------------------------
 5000
(1 row)

 pg_stat_statements.track 
--------------------------
 top
(1 row)

 pg_stat_statements.save 
-------------------------
 on
(1 row)

Empat setting tersebut membentuk perilaku dasar profiler ini. pg_stat_statements.max = 5000 membatasi jumlah queryid berbeda yang dilacak — server sibuk dengan puluhan ribu bentuk query berbeda akan mulai membuang yang tersering kurang populer saat slot habis. track = top berarti statement tingkat atas yang dihitung (mode all turut melacak statement di dalam function). save = on membuat statistik bertahan melewati restart, sehingga akumulasi data tidak mulai dari nol setelah maintenance. Setting ini ditulis di postgresql.conf sebagai parameter dengan prefiks extension, lalu dibaca ulang saat startup.

Cara Membaca View pg_stat_statements #

View ini satu baris per kombinasi database + user + queryid. Kolomnya banyak, tetapi empat kelompok berikut menutupi 90% kebutuhan sehari-hari:

KelompokKolom kunciArtinya
Identitasuserid, dbid, queryid, querySiapa, di database mana, query teks apa
Frekuensicalls, rowsBerapa kali dipanggil; total baris yang dihasilkan
Waktutotal_exec_time, mean_exec_time, stddev_exec_timeTotal, rata-rata, dan sebaran durasi eksekusi (milidetik)
I/Oshared_blks_hit, shared_blks_read, temp_blks_writtenBlock cache yang kena, block yang dibaca dari disk, block sementara yang ditulis

Kolom query menyimpan teks ternormalisasi: konstanta angka dan string diganti parameter $1, $2, sehingga WHERE kota = 'Jakarta' dan WHERE kota = 'Bandung' tercatat sebagai satu baris yang sama. Normalisasi inilah yang membuat profiler tetap berguna — tanpa itu, 5000 slot habis hanya oleh variasi LIMIT dan nilai filter yang tak terhitung.

Normalisasi dalam Praktik #

Mari kita buktikan normalisasi itu secara nyata. File berikut mereset statistik, membangun data contoh, menjalankan tiga query — dua di antaranya hanya berbeda konstanta — lalu membaca kembali laporan lengkapnya. Fungsi pg_stat_statements_reset() mengosongkan seluruh statistik; kita membungkusnya dengan IS NOT NULL karena nilai baliknya berupa timestamp yang berubah setiap eksekusi:

sql normalisasi.sql 
-- Mulai dari statistik bersih agar contoh ini dapat direproduksi.
SELECT pg_stat_statements_reset() IS NOT NULL AS sudah_direset;

CREATE TABLE penjualan (
    id    integer PRIMARY KEY,
    kota  text NOT NULL,
    total numeric NOT NULL
);

INSERT INTO penjualan VALUES
    (1, 'Jakarta', 150000),
    (2, 'Jakarta', 200000),
    (3, 'Bandung', 90000),
    (4, 'Bandung', 60000),
    (5, 'Surabaya', 120000);

-- Dua query berbeda konstanta, satu query lain bentuknya berbeda.
SELECT count(*) AS transaksi FROM penjualan WHERE kota = 'Jakarta';
SELECT count(*) AS transaksi FROM penjualan WHERE kota = 'Bandung';
SELECT sum(total) AS omzet FROM penjualan;

-- Laporan: teks query ternormalisasi beserta jumlah pemanggilannya.
SELECT query, calls
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY query;

Output yang Dihasilkan:

text 
 sudah_direset 
---------------
 t
(1 row)

CREATE TABLE
INSERT 0 5
 transaksi 
-----------
         2
(1 row)

 transaksi 
-----------
         2
(1 row)

 omzet  
--------
 620000
(1 row)

                             query                              | calls 
----------------------------------------------------------------+-------
 CREATE TABLE penjualan (                                      +|     1
     id    integer PRIMARY KEY,                                +| 
     kota  text NOT NULL,                                      +| 
     total numeric NOT NULL                                    +| 
 )                                                              | 
 INSERT INTO penjualan VALUES                                  +|     1
     ($1, $2, $3),                                             +| 
     ($4, $5, $6),                                             +| 
     ($7, $8, $9),                                             +| 
     ($10, $11, $12),                                          +| 
     ($13, $14, $15)                                            | 
 SELECT count(*) AS transaksi FROM penjualan WHERE kota = $1    |     2
 SELECT pg_stat_statements_reset() IS NOT NULL AS sudah_direset |     1
 SELECT sum(total) AS omzet FROM penjualan                      |     1
(5 rows)

Baris-baris laporan itu layak dibedah satu per satu:

  1. Dua query menjadi satu: SELECT count(*) AS transaksi FROM penjualan WHERE kota = $1 tercatat dengan calls = 2 — query Jakarta dan Bandung dilebur karena konstantanya diganti $1. Inilah normalisasi bekerja.
  2. $1 sampai $15 pada INSERT: kelima baris data di satu statement menghasilkan lima belas parameter ternormalisasi.
  3. Statement DDL ikut terlacak: CREATE TABLE muncul dengan calls = 1 — setting track_utility (bawaan on) membuat utility command ikut dihitung.
  4. Filter dbid: view ini cluster-wide; memfilter dbid ke database aktif menjaga laporan bersih dari aktivitas database lain di server yang sama.

Mencari Query Tersering #

Laporan produksi yang paling sering dipakai mengurutkan berdasarkan calls — query yang dieksekusi ribuan kali per menit layak dioptimalkan lebih dulu daripada query berat yang jarang terjadi. File kedua ini merakit beban kerja dengan frekuensi berbeda lalu membaca peringkatnya, sekarang lengkap dengan kolom rows (total baris yang dihasilkan sepanjang pemanggilan):

sql query-terbanyak.sql 
-- Reset kembali agar laporan kedua ini berdiri sendiri.
SELECT pg_stat_statements_reset() IS NOT NULL AS sudah_direset;

-- Beban kerja tetap: tiga bentuk query dengan frekuensi berbeda.
SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT sum(total) AS omzet_bandung FROM penjualan WHERE kota = 'Bandung';
SELECT sum(total) AS omzet_bandung FROM penjualan WHERE kota = 'Bandung';
SELECT count(*) AS total_baris FROM penjualan;

-- Query tersering di database ini, lengkap dengan baris hasilnya.
SELECT query, calls, rows
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND (query LIKE 'SELECT total%'
   OR query LIKE 'SELECT sum%'
   OR query LIKE 'SELECT count%')
ORDER BY calls DESC, query;

Output yang Dihasilkan:

text 
 sudah_direset 
---------------
 t
(1 row)

 total  
--------
 150000
 200000
(2 rows)

 total  
--------
 150000
 200000
(2 rows)

 total  
--------
 150000
 200000
(2 rows)

 omzet_bandung 
---------------
        150000
(1 row)

 omzet_bandung 
---------------
        150000
(1 row)

 total_baris 
-------------
           5
(1 row)

                               query                               | calls | rows 
-------------------------------------------------------------------+-------+------
 SELECT total FROM penjualan WHERE kota = $1                       |     3 |    6
 SELECT sum(total) AS omzet_bandung FROM penjualan WHERE kota = $1 |     2 |    2
 SELECT count(*) AS total_baris FROM penjualan                     |     1 |    1
(3 rows)

Peringkatnya lurus: query SELECT total ... WHERE kota = $1 dipanggil tiga kali dan menghasilkan total enam baris (dua baris per pemanggilan), agregat Bandung dua kali dengan dua baris, dan count(*) sekali. Perhatikan dua hal. Pertama, tiga pemanggilan dengan konstanta berbeda tetap terhitung dalam satu baris calls = 3 — normalisasi mempersatukan frekuensi. Kedua, ORDER BY calls DESC, query membuat urutan laporan deterministik meski dua query punya calls sama; tanpa tie-break semacam ini, urutan baris bisa berpindah-pindah antar eksekusi.

Membaca Waktu Eksekusi #

Kolom mean_exec_time dan total_exec_time adalah alasan utama orang memasang profiler ini — dan sekaligus kolom yang tidak bisa dijadikan output contoh yang tetap, karena durasi eksekusi berbeda di setiap mesin dan setiap eksekusi. Blok berikut karena itu ditandai manual="true" dan dijalankan secara manual; angka waktu milidetik di dalamnya adalah hasil satu eksekusi nyata di server kursus:

sql waktu-eksekusi.sql 
-- Kolom waktu pg_stat_statements tidak deterministik:
-- nilainya berbeda di setiap mesin dan setiap eksekusi.
SELECT pg_stat_statements_reset() IS NOT NULL AS sudah_direset;

SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT sum(total) AS omzet FROM penjualan;

-- Profil waktu nyata: rata-rata dan total dalam milidetik.
SELECT query, calls,
       round(mean_exec_time::numeric, 3) AS rata_rata_ms,
       round(total_exec_time::numeric, 3) AS total_ms
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND (query LIKE 'SELECT total%'
   OR query LIKE 'SELECT sum%')
ORDER BY calls DESC, query;

Output yang Dihasilkan:

text 
 sudah_direset 
---------------
 t
(1 row)

 total  
--------
 150000
 200000
(2 rows)

 total  
--------
 150000
 200000
(2 rows)

 omzet  
--------
 620000
(1 row)

                    query                    | calls | rata_rata_ms | total_ms 
---------------------------------------------+-------+--------------+----------
 SELECT total FROM penjualan WHERE kota = $1 |     2 |        0.009 |    0.018
 SELECT sum(total) AS omzet FROM penjualan   |     1 |        0.008 |    0.008
(2 rows)

Pembacaan hasilnya mengikuti pola yang sama seperti laporan calls: query Jakarta muncul dengan calls = 2 karena dua pemanggilan kita dilebur normalisasi, dan total_ms adalah penjumlahan kedua eksekusinya sementara rata_rata_ms hasilbaginya. Angka sub-0.01 milidetik itu wajar untuk tabel lima baris — di data produksi berjuta baris, rentang inilah yang membedakan query 3 ms dari query 3000 ms, dan lonjakan stddev_exec_time yang mengungkap query dengan kinerja tak stabil (misalnya kadang kena cache, kadang tidak).

Pola Pemakaian di Produksi #

Dari ketiga laporan di atas terbentuk pola kerja harian yang dipakai hampir semua tim operasional PostgreSQL. Pertama, biarkan statistik terakumulasi tanpa reset — save = on menjaga datanya melewati restart, dan pg_stat_statements_reset() hanya dijalankan setelah masalah terisolasi atau saat mulai window pengukuran baru yang bersih. Kedua, kirim laporan urutan calls DESC dan total_exec_time DESC ke dashboard secara berkala: peringkat pertama adalah kandidat optimasi berikutnya. Ketiga, waspadai batas 5000 queryid — aplikasi dengan banyak query dinamis bisa memenuhinya; naikkan pg_stat_statements.max sebelum restart terjadwal berikutnya bila jumlah baris view sering mentok batas. Terakhir, ingat bahwa view ini cluster-wide: laporan internal selalu memfilter dbid (dan biasanya userid) agar tidak tercampur aktivitas database lain.

Profiler mengangkat selubung kinerja; extension berikutnya mengangkat selubung keamanan. pgcrypto membawa hash, enkripsi, dan hashing password langsung ke dalam SQL — tetap di database yang sama, dengan pola pemasangan yang kini sudah kita kuasai.

Referensi #

Berkas Sumber

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

aktifkan.sql

Unduh berkas
sql 
-- pg_stat_statements wajib dimuat server lewat shared_preload_libraries
-- sebelum extension bisa dipasang; server kursus sudah mengaturnya.
CREATE EXTENSION pg_stat_statements;

SHOW shared_preload_libraries;

-- Batas jumlah query berbeda yang dilacak (default 5000).
SHOW pg_stat_statements.max;

-- Mode pelacakan: 'top' berarti tiap statement tingkat atas dihitung.
SHOW pg_stat_statements.track;

-- Statistik bertahan lewat restart server bila 'on'.
SHOW pg_stat_statements.save;

normalisasi.sql

Unduh berkas
sql 
-- Mulai dari statistik bersih agar contoh ini dapat direproduksi.
SELECT pg_stat_statements_reset() IS NOT NULL AS sudah_direset;

CREATE TABLE penjualan (
    id    integer PRIMARY KEY,
    kota  text NOT NULL,
    total numeric NOT NULL
);

INSERT INTO penjualan VALUES
    (1, 'Jakarta', 150000),
    (2, 'Jakarta', 200000),
    (3, 'Bandung', 90000),
    (4, 'Bandung', 60000),
    (5, 'Surabaya', 120000);

-- Dua query berbeda konstanta, satu query lain bentuknya berbeda.
SELECT count(*) AS transaksi FROM penjualan WHERE kota = 'Jakarta';
SELECT count(*) AS transaksi FROM penjualan WHERE kota = 'Bandung';
SELECT sum(total) AS omzet FROM penjualan;

-- Laporan: teks query ternormalisasi beserta jumlah pemanggilannya.
SELECT query, calls
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY query;

query-terbanyak.sql

Unduh berkas
sql 
-- Reset kembali agar laporan kedua ini berdiri sendiri.
SELECT pg_stat_statements_reset() IS NOT NULL AS sudah_direset;

-- Beban kerja tetap: tiga bentuk query dengan frekuensi berbeda.
SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT sum(total) AS omzet_bandung FROM penjualan WHERE kota = 'Bandung';
SELECT sum(total) AS omzet_bandung FROM penjualan WHERE kota = 'Bandung';
SELECT count(*) AS total_baris FROM penjualan;

-- Query tersering di database ini, lengkap dengan baris hasilnya.
SELECT query, calls, rows
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND (query LIKE 'SELECT total%'
   OR query LIKE 'SELECT sum%'
   OR query LIKE 'SELECT count%')
ORDER BY calls DESC, query;

waktu-eksekusi.sql

Unduh berkas
sql 
-- Kolom waktu pg_stat_statements tidak deterministik:
-- nilainya berbeda di setiap mesin dan setiap eksekusi.
SELECT pg_stat_statements_reset() IS NOT NULL AS sudah_direset;

SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT total FROM penjualan WHERE kota = 'Jakarta';
SELECT sum(total) AS omzet FROM penjualan;

-- Profil waktu nyata: rata-rata dan total dalam milidetik.
SELECT query, calls,
       round(mean_exec_time::numeric, 3) AS rata_rata_ms,
       round(total_exec_time::numeric, 3) AS total_ms
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND (query LIKE 'SELECT total%'
   OR query LIKE 'SELECT sum%')
ORDER BY calls DESC, query;