Menguasai Database dengan Postgres

Pada intinya database hanyalah cara menyimpan informasi lintas sesi. Persistensi berarti menyimpan data supaya ia bertahan bahkan setelah program yang membuatnya berhenti. Bayangkan aplikasi to-do: Anda menambah item, mencentang beberapa, menutup aplikasi, dan saat membukanya lagi, semua persis seperti yang Anda tinggalkan. Tanpa persistensi Anda mulai dari daftar kosong setiap kali dan kehilangan semua progres. Kontinuitas itu, lintas waktu dan lintas mesin, adalah intinya.

"Database" kata yang sangat luas

Dalam pengertian paling sederhana, penyimpanan terstruktur apa pun adalah database. Daftar kontak ponsel Anda adalah database. localStorage, sessionStorage, dan cookie browser, semua key-value store yang bisa Anda intip di DevTools -> Application, adalah database. Bahkan file teks polos tempat Anda mencorat-coret catatan adalah database yang sangat dasar. Benang merahnya: sistem persisten yang memungkinkan Anda membuat, membaca, memperbarui, dan menghapus data, CRUD.

Apa maksud "database" dalam konteks backend

Definisi generik itu bukan yang biasanya dimaksud engineer. Dalam konteks server dan sistem backend, "database" hampir selalu berarti database berbasis disk: data disimpan di hard disk (HDD) atau solid-state drive (SSD). Kenapa disk? Itu bagian berikutnya, dan ia gagasan fondasi terpenting di sini.

CPU Anda bisa menjangkau dua jenis memori. RAM (memori primer) sangat cepat tapi mahal dan terbatas, kebanyakan mesin punya 8, 16, 32, mungkin 64-128 GB. Disk (memori sekunder): HDD atau SSD, jauh lebih murah dan jauh lebih banyak, biasanya 512 GB sampai beberapa TB, tapi lebih lambat dibaca dan ditulis.

Jadi polanya: caching = RAM (kritis kecepatan, kecil), database = disk (kritis kapasitas, besar). Postgres, MySQL, MongoDB, semuanya menyimpan datanya di disk. Bagaimana mereka menata byte di disk adalah area yang dalam dan memukau, tapi sebagai engineer backend level aplikasi, yang esensial hanyalah kompromi ini. (Dan persis itulah kenapa index, di bagian 11, sangat penting: begitulah kita meraih kembali kecepatan dari pencarian disk yang lambat.)

Menyimpan data di disk saja tidak cukup, kita butuh cara efisien untuk mengambil, mengubah, dan menghapusnya, di ratusan gigabyte. Itu tugas DBMS (Database Management System): perangkat lunak yang satu-satunya tujuan menyimpan data dan menyediakan operasi CRUD efisien padanya. Pada tingkat tinggi DBMS punya dua tugas, menyimpan data dan melayani operasi di atasnya, plus seperangkat tanggung jawab:

Kenapa file teks polos berantakan

Sebelum perangkat lunak DBMS ada, orang mencoba menyimpan semuanya di file teks. Ia hancur karena tiga alasan konkret:

1. Parsing lambat dan rawan error

Untuk menemukan satu pelanggan Anda menulis kode aplikasi untuk membaca file, memecah setiap baris, dan membandingkan tiap field, operasi lambat, dan lebih lambat lagi di bahasa seperti JavaScript atau Python daripada di Rust. Lebih parah, parsing racikan tangan rapuh: satu kesalahan dan Anda merusak data atau memberi pelanggan record yang salah.

2. Tidak ada struktur

File teks tidak punya skema formal. Anda bisa membuang teks apa pun dalam bentuk apa pun, jadi tidak bisa menegakkan "field ini harus selalu angka". Tidak ada cara menjamin konsistensi, semuanya cuma string.

3. Tanpa kontrol konkurensi

Ini pembunuhnya. Apa yang terjadi saat dua orang memperbarui nilai yang sama bersamaan?

Tiga kegagalan ini, parsing lambat, tanpa struktur, tanpa kontrol konkurensi, persis alasan perangkat lunak DBMS ada. Coba tumbuhkan "database" file teks hari ini dan Anda akan menabrak semuanya.

Pada tingkat tinggi ada dua keluarga besar DBMS.

Relasional (SQL)

Database relasional mengorganisasi data ke tabel berisi baris dan kolom, dengan hubungan antar tabel didefinisikan foreign key. Ciri khasnya skema ketat yang sudah didefinisikan: sebelum menyisipkan apa pun Anda harus mendeklarasikan tabelnya, kolomnya, dan tipe data tiap kolom. Tidak ada yang terjadi "on the fly". Kekakuan itu membayar keuntungan besar, integritas data: kapan pun Anda bisa bertaruh pada bentuk dan tipe data Anda. Anda berinteraksi dengannya memakai SQL (Structured Query Language). Contoh: PostgreSQL, MySQL, SQL Server.

Non-relasional (NoSQL)

Database NoSQL seperti MongoDB tidak menegakkan skema. Dalam kosakata Mongo, tabel adalah collection dan baris adalah document, dan dua dokumen di collection yang sama bisa punya bentuk yang sepenuhnya berbeda. Keuntungannya fleksibilitas: hebat saat prototipe cepat atau saat Anda benar-benar tidak tahu bentuk datanya di depan. Harganya integritas yang lebih lemah: dengan skema yang tidak ditegakkan di level database, Anda harus menegakkan kebenaran di kode aplikasi, yang menambah kompleksitas dan lebih rawan error (kode aplikasi berubah terus dan bug menyusup).

Memilih dengan dua contoh

• CRM (customer relationship management) -> relasional. Kontak, peluang penjualan, dan hubungannya adalah data penting terstruktur yang menuntut integritas kuat dan query kompleks. Postgres pas sekali.
• CMS (content management system) -> non-relasional (atau JSON di Postgres). Artikel bisa berisi gambar, blok kode, embed YouTube, konten sangat beragam yang tak bisa Anda kunci ke skema tetap. "Ambil semua dan buang saja" cocok untuk document store.

Di antara semua pilihan, Postgres adalah default masuk akal untuk kebanyakan proyek, karena lima alasan:

1. Open source & gratis. Tidak proprietary, perusahaan bisa self-host di server mereka sendiri dan memeriksa source-nya.
2. Patuh standar SQL. SQL standar berjalan sama seperti di MySQL atau SQL Server, jadi bermigrasi ke mesin lain nanti berarti jauh lebih sedikit kerja ulang.
3. Ekstensibel. Dokumentasinya ±1400 halaman; ia mencakup hampir setiap kasus yang dihadapi SaaS biasa, dan punya sistem ekstensi kuat untuk mengustomisasinya.
4. Andal & skalabel. Rekam jejak terbukti di skala besar.
5. Dukungan JSON sangat baik, fitur penentunya. Alasan utama orang meraih MongoDB adalah "simpan JSON apa pun". Tapi Postgres menawarkan tipe JSON/JSONB dengan kemampuan index dan query yang kuat, jadi Anda bisa menyimpan data dinamis tanpa skema di dalam database relasional. Itu menghapus alasan biasa meninggalkan Postgres demi NoSQL store.

Setiap kolom di tabel relasional punya tipe tetap. Ini yang benar-benar akan Anda pakai, beserta keputusan yang penting.

Decimal vs float: aturan uang

Pertama, urai parameternya: decimal(10,2) berarti 10 digit total, 2 di belakang koma. Jadi Anda bisa menyimpan 12345678.90, hitung, itu 10 digit di seluruh angka, dua di antaranya di kanan koma. Coba simpan angka yang butuh 11 digit dan database menolaknya.

Angka floating-point cepat tapi direpresentasikan dengan cara yang bisa sedikit berbeda antar sistem, jadi nilai yang sama bisa tampil 89.87 di sini dan 89.9 di sana. Untuk harga, tempat angka memberi makan perhitungan dan akurasi kritis, itu tak bisa diterima, selalu pakai decimal/numeric. Untuk hal seperti ukuran luas (567.8987) yang selisih pembulatan kecil tak berbahaya, pakai float demi kecepatan.

Dan lanjutan alaminya: jika decimal selalu akurat, kenapa tidak pakai di mana-mana? Karena keeksakan itu membayar performa, float disimpan dan dihitung lebih cepat, persis kenapa domain ilmiah/pengukuran memilihnya. Aturan praktis: akurasi penting -> decimal; kecepatan penting dan selisih kecil tak apa -> float.

char vs varchar vs text: preferensi text

Ketiganya menyimpan teks; bedanya cara menangani panjang. char(10) selalu mem-padding ke panjang penuh dengan spasi: simpan "ab" dan database menambah 8 spasi di belakang sebelum menyimpan, karena field char selalu menempati panjang yang dideklarasikan. varchar(255) hanya menyimpan apa yang Anda beri (menyimpan "ab" memakai panjang 2, "abcd" memakai 4) sampai maksimal 255, lewati dan Anda dapat error level database. text adalah varchar tanpa batas panjang sama sekali (secara teknis plafon sangat tinggi, ±1 GB, yang tak akan pernah Anda dekati).

Dokumentasi Postgres sendiri merekomendasikan text, dasarnya tidak ada perbedaan performa di antara ketiganya, dan Anda bisa meng-index text dengan baik (kesalahpahaman umum yang dipinjam dari mesin lain seperti MySQL/SQL Server, yang berperilaku berbeda dan membingungkan orang yang pindah). Dua alasan praktis default ke text:

• Tanpa sakit kepala migrasi. Jika Anda men-set varchar(255) dan nanti butuh nilai lebih panjang, Anda harus menjalankan migrasi skema yang menyentuh banyak data, lebih berisiko daripada sekadar menegakkan panjang di kode aplikasi. Sebagai engineer backend kita tetap berinteraksi dengan DB lewat driver dan punya kode aplikasi, jadi tegakkan panjangnya di sana dan jaga migrasi tetap sederhana.
• Mendokumentasikan diri. Angka seperti varchar(255) tampak bermakna, jadi rekan baru membuang waktu mencari kenapa mustinya 255 (jawabannya: tidak bermakna, kebiasaan MySQL yang disalin). text terbaca bersih sebagai "field teks", tanpa angka hantu untuk dijelaskan.

Hanya pakai char(n) saat panjangnya benar-benar selalu identik, mis. kode hari dua huruf Mo, Tu, We, tempat padding tidak pernah terjadi dan Anda menghemat ruang versus overhead panjang variabel.

json vs jsonb: preferensi jsonb

json disimpan sebagai teks polos; jsonb diserialkan ke format biner Postgres. Hampir selalu pilih jsonb: ia di-query dan di-index jauh lebih cepat. (Fitur Postgres, bukan SQL standar.)

Di produksi Anda tidak sekadar membuka GUI seperti TablePlus dan mulai menjalankan query, tak ada catatan apa yang berubah, kapan, atau siapa yang melakukannya, dan tak ada cara mengendalikan state database seiring waktu. Gantinya, database memakai migrasi: version control untuk skema Anda.

Cara kerja alurnya

Anda menyimpan folder file SQL bernama berurutan. Tool baris perintah (dbmate, golang-migrate, dll.) menapakinya secara berurutan dan menerapkan SQL di dalam masing-masing ke database yang dikonfigurasi.

Tiap file punya dua bagian: migrasi up (perubahannya, CREATE TABLE, CREATE INDEX...) dan migrasi down (kebalikan persis, DROP TABLE...). Bagian down ada supaya jika perubahan memecah produksi Anda bisa roll back ke state sebelumnya dengan bersih. Tool menjaga tabel schema_migrations yang memegang versi saat ini, jadi ia tidak pernah menjalankan ulang file yang sudah diterapkan (yang akan error "table already exists").

-- migrate:up
CREATE TABLE users (
  id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE
);

-- migrate:down
DROP TABLE users;

Kenapa migrasi penting

• Riwayat terlacak. File-nya hidup di git bersama kode Anda, jadi setiap perubahan skema selama berbulan-bulan atau bertahun-tahun tercatat, bisa direview, dan bisa direproduksi di mesin atau server mana pun.
• Rollback. Migrasi down memungkinkan Anda mengembalikan perubahan buruk ke versi yang diketahui baik.

Seeding

Untuk uji lokal/dev Anda ingin data sampel untuk bekerja sebelum deploy. Itu seeding: migrasi terpisah yang menyisipkan baris uji (user sampel, proyek, dll.). Praktik terbaik menjaga data seed di file migrasinya sendiri supaya jelas beda dari perubahan skema.

Kita memodelkan platform manajemen proyek (bayangkan Jira/Linear). Dari kebutuhan kita tarik kata benda -> tabel: users, userprofiles, projects, tasks, projectmembers.

Tiga bentuk hubungan adalah jantung pemodelan data. Begini tiap bentuk diimplementasikan:

One-to-one: users <-> user_profiles

Kenapa memecah data profil ke tabel sendiri alih-alih menumpuknya ke users? Karena profil tumbuh seiring waktu (bio, telepon, avatar, tautan sosial, situs web...) dan akan memaksa migrasi terus-menerus ke tabel inti users. Mengabstraksikannya ke tabel terpisah yang bisa berevolusi independen menjaga tabel utama ringan. Karena setiap user punya tepat satu profil, Anda bahkan tidak perlu ID terpisah, jadikan user_id profil tersebut sekaligus primary key dan foreign key-nya.

One-to-many: projects <-> tasks

Satu proyek punya banyak task; setiap task milik satu proyek. Implementasinya taruh foreign key projectid di tabel tasks (bukan primary key). Banyak baris task bisa berbagi projectid yang sama, itulah sisi "banyak".

Many-to-many: users <-> projects

Seorang user bisa di banyak proyek, dan sebuah proyek bisa punya banyak user, dan Anda ingin meng-query keduanya dua arah. Saat hubungan yang sama masuk akal dari kedua sisi, ia many-to-many. Anda tidak bisa mengekspresikannya dengan satu foreign key, jadi Anda membuat tabel penghubung (projectmembers) yang memegang dua foreign key, userid dan project_id, dan menjadikan pasangannya composite primary key. Kombinasi itu secara implisit unik dan not-null, jadi "user 1 ada di proyek 2" bisa ada tepat sekali.

-- custom enum types: a fixed set of allowed values
CREATE TYPE project_status AS ENUM ('active', 'completed', 'archived');
CREATE TYPE task_status    AS ENUM ('pending', 'in_progress', 'completed', 'cancelled');
CREATE TYPE member_role    AS ENUM ('owner', 'admin', 'member');

CREATE TABLE users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email         TEXT NOT NULL UNIQUE,
  full_name     TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ONE-TO-ONE: user_id is BOTH primary key and foreign key
CREATE TABLE user_profiles (
  user_id     UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  avatar_url  TEXT,             -- optional -> no NOT NULL
  bio         TEXT,
  phone       TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE projects (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        TEXT NOT NULL,
  description TEXT,
  status      project_status NOT NULL DEFAULT 'active',
  owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ONE-TO-MANY: a project has many tasks via project_id
CREATE TABLE tasks (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title       TEXT NOT NULL,
  description TEXT,
  priority    INTEGER NOT NULL DEFAULT 1 CHECK (priority BETWEEN 1 AND 5),
  status      task_status NOT NULL DEFAULT 'pending',
  due_date    DATE,
  assigned_to UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- MANY-TO-MANY: linking table with a COMPOSITE primary key
CREATE TABLE project_members (
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  user_id    UUID NOT NULL REFERENCES users(id)    ON DELETE CASCADE,
  role       member_role NOT NULL DEFAULT 'member',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (project_id, user_id)   -- the pair is unique & not-null
);

Constraint adalah kondisi yang Anda tempelkan ke kolom supaya database sendiri melindungi data dari korup. Seluruh filosofinya: dorong pemeriksaan integritas turun ke database alih-alih memercayai kode aplikasi saja. Kode aplikasi satu lapis pertahanan; constraint database adalah jaminannya.

Primary key

Field unik yang mengidentifikasi satu baris. Ia secara implisit membawa dua constraint, not null dan unique: plus perilaku referensi foreign key. Sesuai konvensi setiap tabel punya id, sering UUID PRIMARY KEY DEFAULT genrandomuuid() supaya Postgres membuat ID acak saat Anda menghilangkannya di insert.

Not null: aturan 70%

Secara default setiap kolom Postgres mengizinkan null kecuali Anda bilang lain. Lebih dari 70% kolom Anda harus NOT NULL, karena null diam-diam menyusup, terutama lewat skrip otomatis yang punya bug, dan meninggalkan Anda dengan tabel berantakan yang tidak konsisten. Tambah NOT NULL kecuali sebuah field benar-benar opsional (seperti bio atau telepon pengguna).

Unique

Tidak boleh ada dua baris berbagi nilai itu, mis. email TEXT NOT NULL UNIQUE berarti menyisipkan email duplikat melempar error level database.

Constraint foreign key

REFERENCES users(id) menjamin apa pun yang Anda taruh di assignedto atau ownerid harus ID pengguna nyata yang ada. Loloskan UUID acak (bahkan yang tampak valid) tanpa user yang cocok dan insert-nya gagal. Anda tidak bisa mereferensikan yang tak ada.

Constraint check

Aturan kustom yang harus dipenuhi baris, transaksi hanya diloloskan saat kondisi berlaku. CHECK (priority BETWEEN 1 AND 5) memblokir siapa pun menyisipkan priority = 55.

Enum: integritas + dokumentasi

Enum adalah tipe kustom yang dibatasi ke set nilai tetap (mis. project_status dalam {active, completed, archived}). Dua kemenangan:

• Integritas data: sisipkan string lain ke kolom project_status dan Anda dapat error level database; Anda tidak harus mengandalkan validasi kode aplikasi saja.
• Mendokumentasikan diri: boleh dibilang kemenangan yang lebih besar. Rekan masa depan yang membaca migrasi langsung melihat nilai yang diizinkan. Jika kolomnya text polos, mereka harus grep seluruh kode aplikasi untuk menemukan string mana yang benar-benar valid.

Integritas referensial: perilaku ON DELETE

Saat Anda menghapus baris yang ditunjuk tabel lain, apa yang terjadi pada referensi itu? Anda mendeklarasikannya per foreign key:

Bangun query dari FROM, bukan SELECT

Kebiasaan yang layak diadopsi: saat menulis query SQL, mulai dengan klausa FROM, bukan SELECT. Putuskan dari mana data Anda berasal lebih dulu, tabel mana, di-join bagaimana, dan baru setelah itu putuskan apa yang mau ditarik. Itu menjaga Anda tetap berorientasi: Anda selalu tahu sumbernya sebelum meraih kolom. Kita akan membangun query "daftar user dengan profil" dalam urutan persis itu, sumber dulu, lalu bentuk, lalu urutan.

Meng-join tabel: dan kenapa LEFT JOIN

Untuk mengembalikan setiap user dengan profilnya dalam satu panggilan API, Anda join users ke userprofiles pada foreign key. Pakai LEFT JOIN, bukan INNER JOIN: user mungkin tidak pernah membuat baris profil, dan INNER JOIN hanya mengembalikan baris tempat kedua sisi punya kecocokan, jadi user tanpa profil itu diam-diam menghilang dari hasil, mengembalikan lebih sedikit user daripada yang ada. LEFT JOIN menjaga setiap_ baris dari tabel kiri (utama) apa pun yang terjadi, mengisi sisi profil dengan null saat tak ada kecocokan. Karena kita selalu ingin user-nya kembali, dengan atau tanpa profil, LEFT JOIN yang benar di sini. (INNER dan LEFT join mencakup mayoritas besar query nyata; yang lain langka.)

Dua detail lagi di query itu. Alias pendek (u, up) hanya membuat query panjang tetap terbaca, Anda menetapkannya sekali di FROM/JOIN dan memakai ulang di mana-mana. Dan tojsonb(up.*) adalah fungsi bawaan Postgres yang mengonversi baris profil yang cocok menjadi objek JSON, disematkan sebagai satu field profile di tiap user, jadi frontend mendapat user.profile bersarang, dalam satu round trip, alih-alih membuat panggilan kedua. Selalu akhiri query daftar dengan ORDER BY (di sini createdat DESC): hasil query datang dalam urutan arbitrer kalau tidak, dan urutan itu bisa berubah antar panggilan, jadi Anda sortir secara eksplisit, biasanya terbaru-dulu berdasarkan waktu pembuatan.

SELECT
  u.*,
  to_jsonb(up.*) AS profile          -- fold the profile row into one JSON field
FROM users u
LEFT JOIN user_profiles up           -- LEFT so users without a profile still appear
  ON u.id = up.user_id               -- the foreign-key join condition
ORDER BY u.created_at DESC;          -- newest first; never rely on default order

Query berparameter: jangan pernah menggabungkan string

Ini kebiasaan keamanan paling penting dengan database. Query berparameter menyisakan slot placeholder dan memasok nilainya terpisah; database memperlakukan apa pun yang Anda berikan sebagai data murni (string), bukan SQL yang bisa dieksekusi. Jika penyerang memasukkan '; DELETE FROM users; -- ke parameter, ia disimpan/dibandingkan sebagai string literal yang tak berbahaya, bukan dijalankan sebagai perintah.

SELECT u.*, to_jsonb(up.*) AS profile
FROM users u
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE u.id = $1;        -- $1 is a parameter slot; the value is sent separately

// database/sql, the driver substitutes the parameter safely
row := db.QueryRow(`
  SELECT u.*, to_jsonb(up.*) AS profile
  FROM users u
  LEFT JOIN user_profiles up ON u.id = up.user_id
  WHERE u.id = $1`, userID) // userID is treated strictly as data, never SQL

# psycopg, pass params as a tuple; NEVER use f-strings to build SQL
cur.execute(
    """
    SELECT u.*, to_jsonb(up.*) AS profile
    FROM users u
    LEFT JOIN user_profiles up ON u.id = up.user_id
    WHERE u.id = %s
    """,
    (user_id,),  # the driver escapes it; injection is impossible
)

Insert & update

-- POST /v1/users  -> returning the new row back to the client
INSERT INTO users (email, full_name, password_hash)
VALUES ($1, $2, $3)
RETURNING *;

-- PATCH /v1/users/:id  -> only the fields the client actually sent
UPDATE user_profiles
SET bio = $1, phone = $2
WHERE user_id = $3
RETURNING *;

Filter, sort & pagination dinamis

Endpoint daftar mendukung filtering, sorting, dan pagination, dibangun dinamis di kode aplikasi dari query param, dengan default yang masuk akal (tanpa page -> 1, tanpa limit -> 10, tanpa sort -> created_at DESC). Di level SQL:

• Filter dengan ILIKE, pencocokan pola tanpa sensitif huruf. WHERE u.full_name ILIKE $1 || '%' cocok dengan nama yang diawali huruf tertentu tanpa memedulikan huruf besar/kecil.
• Sort dari allow-list kolom yang boleh dipakai klien untuk menyortir (fullname, email, createdat) plus satu arah.
• Paginate dengan LIMIT + OFFSET. Catatan: halaman 1 di API memetakan ke OFFSET 0 database, offset-nya berbasis nol.

SELECT u.*, to_jsonb(up.*) AS profile
FROM users u
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE u.full_name ILIKE $1 || '%'   -- filter: names starting with $1 (case-insensitive)
ORDER BY u.created_at DESC           -- sort (column from an allow-list, direction chosen)
LIMIT $2 OFFSET $3;                  -- page 1 -> OFFSET 0, page 2 -> OFFSET (limit), ...

Index adalah tuas performa terbesar yang Anda miliki, dan paling baik dipahami lewat analogi buku. Indeks buku mencantumkan tiap bab dan nomor halamannya, jadi untuk sampai ke bab 4 Anda tidak membalik 50 halaman satu per satu, Anda mencarinya lalu melompat langsung ke sana.

Masalahnya: sequential scan

Ingat database menyimpan baris tersebar di disk, tidak dalam urutan rapi. Tanpa index, menemukan satu task berdasarkan ID berarti sequential scan: database memeriksa baris demi baris, lokasi demi lokasi, sampai menemukan kecocokan. Dengan enam baris itu instan; dengan sejuta atau satu miliar baris itu sangat lambat.

Solusinya: tabel pencarian

Index pada dasarnya tabel pencarian terpisah dan terurut yang memetakan setiap nilai yang di-index ke lokasi disk barisnya. Query berdasarkan field yang di-index dan database memindai struktur kompak tersortir ini (secara internal B-tree), menemukan entrinya cepat, membaca lokasinya, dan melompat langsung ke baris, tanpa scan penuh. Dua sifat yang penting:

• Pencarian langsung: nilai -> lokasi, alih-alih memeriksa setiap baris.
• Urutan: index bisa menaik atau menurun. Jika Anda sering menjalankan ORDER BY createdat DESC, bangun index-nya menurun_ supaya baris datang sudah terurut, tanpa langkah sort tambahan.

Kapan membuat index

Aturan praktisnya, index kolom jika ia dipakai di klausa WHERE, kondisi JOIN, atau ORDER BY. Satu detail penting: primary key otomatis di-index, tapi foreign key tidak: jadi foreign key yang Anda gunakan untuk join (seperti tasks.projectid, tasks.assignedto) adalah kandidat index utama.

Menapaki alasan sebenarnya di balik tiap index di skema kita membuat aturannya konkret:

• users.email -> ada pencarian "cari user berdasarkan email" dan join pada email, jadi ia muncul di WHERE/JOIN -> index.
• users.createdat DESC -> API daftar-user menyortir terbaru-dulu secara default dan dipanggil terus, jadi index menurun_ agar cocok persis dengan ORDER BY.
• tasks.projectid -> "ambil semua task sebuah proyek" join projects ke tasks pada foreign key ini. Sisi projects.id adalah primary key (sudah di-index), tapi projectid di sini foreign key dan tidak di-index, jadi index.
• tasks.assigned_to -> "ambil semua task seorang user" join pada foreign key ini. Logika sama, foreign key, tidak otomatis di-index, dipakai di join -> index.
• tasks.status -> "ambil semua task tertunda" memfilter status di klausa WHERE -> index.
• tasks.created_at DESC -> API daftar-task menyortir terbaru-dulu -> index menurun.

Secara internal B-tree menjaga entri ini tetap terurut, itulah yang membuat pencarian langsung dan scan terurut sama-sama cepat, tapi Anda tidak butuh internal untuk menerapkan aturannya; tiga pemicu (WHERE / JOIN / ORDER BY) sudah cukup untuk memutuskan.

CREATE INDEX idx_users_email        ON users (email);              -- WHERE / JOIN on email
CREATE INDEX idx_users_created_at    ON users (created_at DESC);    -- ORDER BY newest first
CREATE INDEX idx_tasks_project_id    ON tasks (project_id);         -- FK join (not auto-indexed)
CREATE INDEX idx_tasks_assigned_to   ON tasks (assigned_to);        -- FK join (not auto-indexed)
CREATE INDEX idx_tasks_status        ON tasks (status);             -- WHERE status = ...
CREATE INDEX idx_tasks_created_at    ON tasks (created_at DESC);    -- ORDER BY newest first

Setiap tabel di sini punya kolom updated_at yang harus disegarkan ke "sekarang" setiap kali baris berubah. Anda bisa men-set-nya manual di setiap UPDATE, atau Anda bisa mengotomasinya dengan trigger supaya aplikasi tidak pernah perlu memikirkannya.

Trigger menjalankan fungsi otomatis saat kondisi terpenuhi. Di sini: definisikan fungsi yang men-set NEW.updated_at = now() dan mengembalikan barisnya, lalu pasang trigger BEFORE UPDATE di tiap tabel yang memanggilnya.

-- the function: stamp updated_at, return the modified row
CREATE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- attach it before every UPDATE on each table
CREATE TRIGGER trg_users_updated
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER trg_projects_updated
  BEFORE UPDATE ON projects
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- ...and likewise for user_profiles, tasks, project_members

Sekarang setiap UPDATE, dari mana pun, kode aplikasi atau query manual, otomatis menyegarkan updated_at. Tidak ada kolom yang terlupakan, dan logikanya tinggal di satu tempat.

Kembali ke bagian 3: kita melihat kenapa file teks tidak bisa menangani dua orang mengedit nilai yang sama bersamaan, balapan lost-update. DBMS sungguhan menyelesaikannya dengan transaksi: kelompok operasi yang semuanya sukses bersama atau tidak ada. Inilah mesin yang membuat konkurensi aman, dan layak dipahami karena uang, inventaris, dan logika "baca-lalu-tulis" apa pun bergantung padanya.

Contoh klasik: transfer uang

Pindahkan INR 500 dari akun A ke akun B. Itu dua tulisan: kurangi dari A, tambahkan ke B. Jika server crash di antara keduanya, A kehilangan INR 500 yang tak pernah sampai ke B, uang lenyap. Transaksi membuat pasangannya atomik: bungkus dalam BEGIN ... COMMIT, dan keduanya terjadi atau, saat ada kegagalan, ROLLBACK mengurungkan semuanya seolah tak terjadi apa-apa.

BEGIN;                                              -- start the transaction
  UPDATE accounts SET balance = balance - 500 WHERE id = $1;  -- debit A
  UPDATE accounts SET balance = balance + 500 WHERE id = $2;  -- credit B
COMMIT;                                             -- both are now permanent
-- if anything fails between BEGIN and COMMIT -> ROLLBACK undoes it all

tx, err := db.Begin()
if err != nil { return err }
defer tx.Rollback() // safety net: a no-op if we already committed

if _, err = tx.Exec(`UPDATE accounts SET balance = balance - 500 WHERE id = $1`, a); err != nil {
	return err // deferred Rollback fires -> A is untouched
}
if _, err = tx.Exec(`UPDATE accounts SET balance = balance + 500 WHERE id = $1`, b); err != nil {
	return err // deferred Rollback fires -> both reverted
}
return tx.Commit() // only here do both writes become permanent

conn = pool.getconn()
try:
    with conn:                       # commits on success, ROLLBACKs on any exception
        with conn.cursor() as cur:
            cur.execute("UPDATE accounts SET balance = balance - 500 WHERE id = %s", (a,))
            cur.execute("UPDATE accounts SET balance = balance + 500 WHERE id = %s", (b,))
    # reaching here means both writes committed atomically
finally:
    pool.putconn(conn)

ACID: empat jaminan

Transaksi memberi Anda empat sifat, disingkat ACID:

Ini alasan besar meraih database relasional seperti Postgres saat data kritis: transaksi ACID adalah jaminan kelas satu, persis hal yang tidak pernah bisa diberikan file teks bagian 3.

Isolation & balapan lost-update, ditinjau ulang

Isolation adalah sifat yang sebenarnya memperbaiki masalah dua-penulis-pada-40 kita. Di balik layar database memakai penguncian dan MVCC (multi-version concurrency control) sehingga transaksi serentak melihat snapshot konsisten dan menyeriakkan tulisan yang bertabrakan, alih-alih membuta menimpa satu sama lain.

Khusus untuk logika baca-lalu-tulis, ada dua alat standar:

• Tulis atomik: dorong komputasinya ke SQL itu sendiri: UPDATE accounts SET balance = balance - 500 ... membaca dan menulis dalam satu langkah terkunci, jadi tidak ada celah bagi penulis lain menyusup. (Jauh lebih aman daripada membaca saldo ke kode aplikasi, menghitung, lalu menulisnya kembali.)
• Penguncian baris: SELECT ... FOR UPDATE mengunci baris yang Anda baca sampai transaksi Anda berakhir, jadi transaksi serentak harus menunggu gilirannya alih-alih balapan dengan Anda.

BEGIN;
  -- lock this account's row so no other transaction can change it until we're done
  SELECT balance FROM accounts WHERE id = $1 FOR UPDATE;
  -- ... compute safely in app code ...
  UPDATE accounts SET balance = $2 WHERE id = $1;
COMMIT;

Transkripnya berhenti di dasar-dasar. Empat topik ini tidak ada di sana, tapi Anda akan menabrak semuanya begitu database melayani lalu lintas nyata. Itulah pembeda antara "berfungsi di mesin saya dengan enam baris" dan "berfungsi di skala".

Masalah query N+1: bug performa DB paling umum

Anda mengambil daftar N proyek, lalu mengulanginya dan menembakkan satu query lagi per proyek untuk memuat task-nya. Itu 1 + N query: untuk 100 proyek, 101 round trip ke database. Tiap round trip punya overhead jaringan + perencanaan, jadi ini kematian oleh seribu luka. Sangat licik dengan ORM, tempat lazy-loading relasi di dalam loop menyembunyikan query ekstra di balik kode yang tampak polos.

Perbaikannya mengambil data terkait dalam satu query, JOIN, atau WHERE project_id = ANY($1) yang di-batch, atau agregat yang membangun JSON bersarang di sisi server. Dengan ORM, padanannya "eager loading" (mis. JOIN/preload/include) alih-alih lazy loading di dalam loop.

-- instead of: SELECT * FROM projects;  then per row: SELECT * FROM tasks WHERE project_id = ?
SELECT
  p.*,
  COALESCE(jsonb_agg(t.*) FILTER (WHERE t.id IS NOT NULL), '[]') AS tasks
FROM projects p
LEFT JOIN tasks t ON t.project_id = p.id
GROUP BY p.id;        -- one round trip, every project with its tasks nested

Connection pooling

Membuka koneksi Postgres mahal: Postgres men-spawn satu proses per koneksi, dan ada plafon keras (default ±100 max_connections). Jika setiap request web membuka koneksinya sendiri, Anda menghabiskan server dan membayar biaya penyetelan terus-menerus. Perbaikannya connection pool: sekumpulan kecil koneksi umur panjang yang dipinjam dan dikembalikan request.

Dua lapisan, sering digabung: pool bawaan driver (di Go database/sql melakukan pooling otomatis; Python memakai psycopg_pool/pool SQLAlchemy) untuk satu instance aplikasi, dan pooler eksternal seperti PgBouncer saat banyak instance aplikasi masing-masing memegang pool sendiri dan bakal membanjiri Postgres. Setel ukuran pool ke beban kerja Anda, lebih besar bukan lebih baik; terlalu banyak koneksi justru menurunkan Postgres.

EXPLAIN ANALYZE: verifikasi index benar-benar dipakai

Membuat index tidak menjamin planner memakainya. EXPLAIN menampilkan rencana query; EXPLAIN ANALYZE benar-benar menjalankan query dan melaporkan waktu nyata. Anda membacanya mencari "Index Scan" (bagus, index Anda bekerja) versus "Seq Scan" (planner membaca setiap baris).

Satu peringatan yang membingungkan orang: pada tabel kecil planner mungkin dengan benar memilih Seq Scan (membaca 6 baris langsung lebih cepat daripada round trip index). Itu bukan bug, manfaat index muncul di skala. EXPLAIN ANALYZE pada data berskala produksi adalah cara mengonfirmasi index membayar upahnya, dan cara men-debug query lambat yang seharusnya memakai index tapi tidak.

Soft delete

Sering Anda tidak ingin baris benar-benar hilang, Anda ingin ia dipertahankan untuk audit, pemulihan, atau integritas historis, tapi disembunyikan dari query normal. Itu soft delete: tambahkan kolom nullable deleted_at TIMESTAMPTZ (null = hidup, timestamp = terhapus). "Menghapus" menjadi UPDATE; setiap baca memfilter baris yang terhapus.

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;   -- null = alive

-- "delete" = mark, don't remove
UPDATE users SET deleted_at = now() WHERE id = $1;

-- every read must exclude the dead rows
SELECT * FROM users WHERE deleted_at IS NULL ORDER BY created_at DESC;

-- gotcha: a plain UNIQUE now blocks reusing a soft-deleted email.
-- a PARTIAL unique index fixes it, uniqueness only among LIVE rows:
CREATE UNIQUE INDEX uniq_active_email
  ON users (email) WHERE deleted_at IS NULL;

Komprominya nyata: setiap query harus mengingat filter WHERE deleted_at IS NULL (mudah dilupakan, kebocoran), constraint unik perlu perlakuan partial-index seperti yang ditunjukkan di atas, dan tabel hanya akan terus bertambah. Pakai soft delete saat riwayat penting (order, akun pengguna, apa pun yang bisa diaudit); pakai DELETE keras untuk data yang benar-benar sekali pakai.