Kenapa penting

Hampir setiap aplikasi backend menyimpan datanya di database relasional: pelanggan, produk, order, pembayaran. Spring Data dan JPA nanti memang akan menulis banyak query untukmu, tetapi saat endpoint lambat, laporan salah hitung, atau data hilang karena UPDATE tanpa WHERE, yang kamu baca adalah SQL. Engineer backend diharapkan bisa membuka psql, menulis query untuk menginvestigasi data production, dan membaca EXPLAIN untuk tahu kenapa query lambat. Bab ini memakai PostgreSQL 17, database open source yang paling sering dipakai bersama Spring Boot.

Konsep inti

Menjalankan PostgreSQL dengan Docker

Tidak perlu meng-install PostgreSQL ke sistem. Satu perintah Docker sudah cukup untuk menjalankan server lengkap:

docker run -d --name jalur-pg \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=rahasia \
  -e POSTGRES_DB=toko \
  -p 5432:5432 \
  postgres:17

# masuk ke psql di dalam container
docker exec -it jalur-pg psql -U app -d toko

• -e POSTGRES_* membuat user, password, dan database awal saat container pertama kali dibuat.
• -p 5432:5432 membuka port agar aplikasi Java di laptop bisa terhubung ke localhost:5432 (dipakai di bab JDBC).
• psql adalah klien baris perintah PostgreSQL. Prompt toko=# berarti kamu siap mengetik SQL.

Data container ini hilang bila container dihapus (docker rm -f jalur-pg). Untuk belajar itu justru praktis: kamu bisa mulai ulang dari nol kapan saja.

Beberapa perintah psql (diawali \, bukan SQL) yang sering dipakai:

Perintah	Fungsi
\dt	daftar tabel
\d product	struktur satu tabel: kolom, tipe, index
\di	daftar index
\x	tampilan baris vertikal (enak untuk kolom panjang)
\timing	tampilkan durasi setiap query
\q	keluar

Perintah SQL diakhiri ;. Tanpa titik koma, psql menunggu lanjutan baris berikutnya.

DDL: membuat tabel

DDL (Data Definition Language) mendefinisikan struktur. Tiga tabel berikut menjadi bahan seluruh bab ini:

CREATE TABLE customer (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text        NOT NULL,
    email      text        NOT NULL,
    city       text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE product (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku    text          NOT NULL,
    name   text          NOT NULL,
    price  numeric(12,2) NOT NULL,
    active boolean       NOT NULL DEFAULT true
);

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint        NOT NULL,
    status      text          NOT NULL,
    total       numeric(12,2) NOT NULL,
    created_at  timestamptz   NOT NULL DEFAULT now()
);

Tabel order dinamai orders karena ORDER adalah kata kunci SQL. GENERATED ALWAYS AS IDENTITY membuat PostgreSQL mengisi id otomatis (1, 2, 3, …); NOT NULL mewajibkan kolom diisi; DEFAULT memberi nilai bila kolom tidak disebut saat INSERT. Primary key, relasi antartabel, dan constraint lain dibahas tuntas di bab berikutnya.

Tipe	Padanan Java	Dipakai untuk
bigint	long	ID, jumlah besar
text	String	nama, email, status (tanpa batas panjang tetap)
numeric(12,2)	BigDecimal	uang — 12 digit total, 2 di belakang koma, eksak
timestamptz	Instant/OffsetDateTime	waktu kejadian, disimpan sebagai titik waktu UTC
boolean	boolean	flag aktif/nonaktif

Kenapa uang tidak memakai double precision? Sama seperti double di Java, floating point biner tidak bisa menyimpan 0.1 dengan tepat:

SELECT 0.1::double precision + 0.2::double precision AS pakai_double,
       0.1::numeric + 0.2::numeric AS pakai_numeric;

    pakai_double     | pakai_numeric
---------------------+---------------
 0.30000000000000004 |           0.3

timestamptz menyimpan titik waktu yang pasti, lalu menampilkannya sesuai zona waktu sesi — persis seperti Instant yang ditampilkan dengan ZoneId di bab java.time. Nilai 2026-10-01 09:15:00+07 tampil sebagai 2026-10-01 02:15:00+00 bila sesi memakai SET TIME ZONE 'UTC'. Pakai timestamptz, bukan timestamp tanpa zona, untuk waktu kejadian.

DML: INSERT, UPDATE, DELETE

DML (Data Manipulation Language) mengubah isi tabel. Satu INSERT boleh memuat banyak baris, dan RETURNING mengembalikan kolom hasil — berguna untuk mengetahui id yang baru dibuat:

INSERT INTO customer (name, email, city) VALUES
    ('Budi Santoso', 'budi@contoh.id', 'Bandung'),
    ('Sari Wulandari', 'sari@contoh.id', 'Jakarta'),
    ('Andi Pratama', 'andi@contoh.id', 'Bandung'),
    ('Dewi Lestari', 'dewi@contoh.id', NULL);

INSERT INTO product (sku, name, price) VALUES
    ('KOPI-250', 'Kopi Gayo 250 g', 85000),
    ('TEH-100', 'Teh Melati 100 g', 40000),
    ('GULA-500', 'Gula Aren 500 g', 30000)
RETURNING id, sku;

INSERT INTO orders (customer_id, status, total, created_at) VALUES
    (1, 'PAID',      200000, '2026-10-01 09:15:00+07'),
    (1, 'PAID',       85000, '2026-10-03 13:40:00+07'),
    (2, 'NEW',        40000, '2026-10-04 08:05:00+07'),
    (2, 'PAID',      370000, '2026-10-05 19:20:00+07'),
    (3, 'CANCELLED',  30000, '2026-10-06 11:00:00+07'),
    (1, 'SHIPPED',   125000, '2026-10-07 16:45:00+07');

String di SQL memakai kutip tunggal '...'; kutip ganda "..." untuk nama kolom/tabel. Mengisi id secara manual ditolak karena kolomnya GENERATED ALWAYS:

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

UPDATE dan DELETE hampir selalu butuh WHERE. Tanpa WHERE, semua baris terkena:

UPDATE product SET price = 90000 WHERE sku = 'KOPI-250';
UPDATE product SET active = false WHERE sku = 'GULA-500' RETURNING sku, price, active;
DELETE FROM orders WHERE status = 'CANCELLED';

psql membalas jumlah baris yang terkena, misalnya UPDATE 1 dan DELETE 1. Biasakan membaca angka ini: bila kamu berniat mengubah satu produk tetapi muncul UPDATE 3000, ada yang salah.

Kebiasaan aman di production

Tulis dulu SELECT ... WHERE <syarat> dan periksa hasilnya, baru ganti SELECT dengan UPDATE/DELETE memakai WHERE yang sama. Bab berikutnya menambahkan pengaman kedua: transaksi yang bisa di-ROLLBACK.

SELECT, WHERE, ORDER BY, LIMIT

SELECT sku, name, price FROM product WHERE active ORDER BY price DESC;

SELECT id, customer_id, status, total
FROM orders
WHERE status = 'PAID' AND total >= 100000
ORDER BY total DESC;

SELECT id, status, total FROM orders ORDER BY created_at DESC LIMIT 2;

   sku    |       name       |  price
----------+------------------+----------
 KOPI-250 | Kopi Gayo 250 g  | 90000.00
 TEH-100  | Teh Melati 100 g | 40000.00

 id | customer_id | status |   total
----+-------------+--------+-----------
  4 |           2 | PAID   | 370000.00
  1 |           1 | PAID   | 200000.00

 id | status  |   total
----+---------+-----------
  6 | SHIPPED | 125000.00
  4 | PAID    | 370000.00

WHERE mendukung =, <>, <, >=, AND, OR, IN ('Jakarta', 'Bandung'), LIKE 'b%' (pola teks), dan BETWEEN. Tanpa ORDER BY, urutan baris tidak dijamin — sama seperti iterasi HashMap. LIMIT tanpa ORDER BY berarti "ambil sembarang N baris".

NULL perlu perhatian khusus. NULL berarti "tidak diketahui", sehingga city = NULL tidak pernah true:

SELECT name, city FROM customer WHERE city = NULL;   -- 0 rows
SELECT name, city FROM customer WHERE city IS NULL;  -- Dewi Lestari

JOIN: menggabungkan tabel

Tabel orders hanya menyimpan customer_id. Untuk menampilkan nama pelanggan, gabungkan dengan JOIN. Inner join (JOIN) hanya mengambil pasangan yang cocok di kedua tabel:

SELECT o.id, c.name, o.status, o.total
FROM orders o
JOIN customer c ON c.id = o.customer_id
ORDER BY o.id;

 id |      name      | status  |   total
----+----------------+---------+-----------
  1 | Budi Santoso   | PAID    | 200000.00
  2 | Budi Santoso   | PAID    |  85000.00
  3 | Sari Wulandari | NEW     |  40000.00
  4 | Sari Wulandari | PAID    | 370000.00
  6 | Budi Santoso   | SHIPPED | 125000.00

Left join mengambil semua baris tabel kiri, walau tidak ada pasangan di tabel kanan; kolom tabel kanan diisi NULL. Andi (order-nya sudah dihapus) dan Dewi tetap muncul:

SELECT c.name, o.id AS order_id, o.total
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

      name      | order_id |   total
----------------+----------+-----------
 Budi Santoso   |        1 | 200000.00
 Budi Santoso   |        2 |  85000.00
 Budi Santoso   |        6 | 125000.00
 Sari Wulandari |        3 |  40000.00
 Sari Wulandari |        4 | 370000.00
 Andi Pratama   |          |
 Dewi Lestari   |          |

Pola LEFT JOIN ... WHERE o.id IS NULL menemukan baris yang tidak punya pasangan — misalnya pelanggan yang belum pernah belanja, target kampanye promo. o dan c adalah alias tabel agar query lebih pendek.

Agregasi: GROUP BY dan HAVING

Fungsi agregat — count, sum, avg, min, max — meringkas banyak baris menjadi satu nilai. GROUP BY membaginya per kelompok, seperti pola Map penghitung frekuensi di M03:

SELECT status, count(*) AS jumlah, sum(total) AS omzet
FROM orders
GROUP BY status
ORDER BY status;

 status  | jumlah |   omzet
---------+--------+-----------
 NEW     |      1 |  40000.00
 PAID    |      3 | 655000.00
 SHIPPED |      1 | 125000.00

Urutan logis eksekusinya: FROM/JOIN → WHERE (saring baris) → GROUP BY → HAVING (saring kelompok) → SELECT → ORDER BY → LIMIT. Karena itu syarat atas hasil agregat tidak bisa ditulis di WHERE:

ERROR:  aggregate functions are not allowed in WHERE

Gunakan HAVING. Contoh: pelanggan dengan belanja di atas Rp300.000, tidak menghitung order NEW:

SELECT c.name, count(o.id) AS jumlah_order, sum(o.total) AS total_belanja
FROM customer c
JOIN orders o ON o.customer_id = c.id
WHERE o.status <> 'NEW'
GROUP BY c.id, c.name
HAVING sum(o.total) > 300000
ORDER BY total_belanja DESC;

      name      | jumlah_order | total_belanja
----------------+--------------+---------------
 Budi Santoso   |            3 |     410000.00
 Sari Wulandari |            1 |     370000.00

Setiap kolom di SELECT harus ada di GROUP BY atau dibungkus fungsi agregat. SELECT name, count(*) FROM customer; ditolak dengan column "customer.name" must appear in the GROUP BY clause or be used in an aggregate function.

Index dan EXPLAIN

Tanpa index, mencari baris berarti membaca seluruh tabel — O(n), persis seperti loop di List. Tambahkan 200.000 order dummy dengan generate_series untuk melihat efeknya:

INSERT INTO orders (customer_id, status, total, created_at)
SELECT 1000 + (i % 50000), 'PAID', 50000 + (i % 100) * 1000, now() - i * interval '1 minute'
FROM generate_series(1, 200000) AS s(i);
ANALYZE orders;

EXPLAIN ANALYZE SELECT id, status, total FROM orders WHERE customer_id = 4242;

 Seq Scan on orders  (cost=0.00..4167.06 rows=4 width=19) (actual time=0.216..12.316 rows=4 loops=1)
   Filter: (customer_id = 4242)
   Rows Removed by Filter: 200001
 Planning Time: 0.031 ms
 Execution Time: 12.330 ms

EXPLAIN menampilkan rencana eksekusi pilihan planner; EXPLAIN ANALYZE benar-benar menjalankan query dan menambahkan waktu nyata. Seq Scan dengan Rows Removed by Filter: 200001 artinya seluruh tabel dibaca demi empat baris. Buat index B-tree:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT id, status, total FROM orders WHERE customer_id = 4242;

 Index Scan using idx_orders_customer_id on orders  (cost=0.42..19.74 rows=4 width=19) (actual time=0.042..0.048 rows=4 loops=1)
   Index Cond: (customer_id = 4242)
 Planning Time: 0.176 ms
 Execution Time: 0.062 ms

Dari 12 ms menjadi 0,06 ms (angka di mesinmu akan berbeda). Index B-tree mirip TreeMap: pencarian sekitar O(log n). Primary key otomatis punya index (orders_pkey di \di). Index bukan gratis: setiap INSERT/UPDATE juga harus memperbarui index, dan index memakan disk. Index juga tidak selalu dipakai — WHERE status = 'PAID' yang cocok dengan hampir semua baris tetap memakai Seq Scan, karena membaca seluruh tabel lebih murah daripada melompat lewat index ratusan ribu kali.

Contoh di dunia backend

Tim bisnis meminta laporan: semua pelanggan beserta jumlah order dan total belanjanya, termasuk yang belum pernah belanja (nilai 0), diurutkan dari yang terbesar. Query seperti ini nanti dijalankan dari Java lewat JDBC:

SELECT c.name,
       count(o.id)               AS jumlah_order,
       coalesce(sum(o.total), 0) AS total_belanja
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_belanja DESC, c.name;

      name      | jumlah_order | total_belanja
----------------+--------------+---------------
 Budi Santoso   |            3 |     410000.00
 Sari Wulandari |            2 |     410000.00
 Andi Pratama   |            0 |             0
 Dewi Lestari   |            0 |             0

Tiga detail penting: LEFT JOIN agar pelanggan tanpa order tetap muncul; count(o.id) (bukan count(*)) agar pelanggan tanpa order dihitung 0, bukan 1; dan coalesce(sum(o.total), 0) karena sum atas nol baris menghasilkan NULL. Kolom c.name ditambahkan di ORDER BY sebagai penentu urutan saat total sama — Budi dan Sari sama-sama 410.000, dan tanpa itu urutan keduanya bisa berubah antareksekusi. Untuk endpoint "riwayat order pelanggan" yang dipanggil ribuan kali, index idx_orders_customer_id dari bagian sebelumnya adalah yang membuatnya tetap cepat.

Pertanyaan interview

1. Apa beda INNER JOIN dan LEFT JOIN?

INNER JOIN hanya mengembalikan baris yang punya pasangan di kedua tabel. LEFT JOIN mengembalikan semua baris tabel kiri; bila tidak ada pasangan, kolom tabel kanan bernilai NULL. LEFT JOIN dipakai saat data induk harus tetap tampil walau tidak punya data anak, misalnya pelanggan tanpa order.

2. Apa beda WHERE dan HAVING?

WHERE menyaring baris sebelum pengelompokan, jadi tidak bisa memakai fungsi agregat. HAVING menyaring kelompok setelah GROUP BY, sehingga bisa memakai count, sum, dan sejenisnya. Syarat yang tidak melibatkan agregat sebaiknya tetap di WHERE agar baris dibuang sedini mungkin.

3. Kapan index membantu dan kapan merugikan?

Index membantu query yang memfilter, join, atau mengurutkan berdasarkan kolom yang selektif (hasilnya sedikit dari banyak baris), mengubah Seq Scan menjadi Index Scan. Kerugiannya: setiap penulisan harus memperbarui index, ada biaya disk, dan index pada kolom yang tidak selektif sering diabaikan planner. Buat index berdasarkan query nyata dan buktikan dengan EXPLAIN ANALYZE.

Jebakan umum

• UPDATE/DELETE tanpa WHERE mengubah semua baris. Uji syaratnya dengan SELECT dulu dan baca jumlah baris yang dilaporkan psql.
• Membandingkan dengan = NULL. Hasilnya tidak pernah true; pakai IS NULL / IS NOT NULL.
• Mengandalkan urutan tanpa ORDER BY, termasuk LIMIT tanpa ORDER BY untuk "data terbaru".
• count(*) setelah LEFT JOIN menghitung pelanggan tanpa order sebagai 1. Pakai count(kolom_tabel_kanan).
• Menyimpan uang di double precision/real. Pakai numeric(p,s) — dan BigDecimal di Java.
• Menambah index di semua kolom "untuk jaga-jaga". Setiap index memperlambat penulisan; tambahkan berdasarkan bukti EXPLAIN.

Ringkasan

• docker run ... postgres:17 + docker exec -it <nama> psql -U <user> -d <db> cukup untuk database belajar.
• DDL (CREATE TABLE) mendefinisikan struktur; pilih bigint untuk ID, numeric(12,2) untuk uang, timestamptz untuk waktu kejadian.
• DML (INSERT/UPDATE/DELETE) mengubah data; UPDATE/DELETE hampir selalu butuh WHERE, RETURNING mengembalikan hasil.
• SELECT + WHERE/ORDER BY/LIMIT membaca data; NULL dicek dengan IS NULL.
• JOIN menggabungkan tabel; LEFT JOIN mempertahankan baris kiri; GROUP BY + HAVING untuk ringkasan per kelompok.
• EXPLAIN ANALYZE menunjukkan Seq Scan vs Index Scan; index mempercepat baca tetapi membebani tulis.

Bacaan lanjutan

• PostgreSQL 17 — The SQL Language (tutorial)
• PostgreSQL 17 — Using EXPLAIN
• Docker Hub — image resmi postgres