Belajar Database Administrator - Database Design & SQL Administration

#Pendahuluan

Di episode 3 server kalian sudah hidup dan terkonfigurasi. Sekarang masuk ke bagian yang membedakan DBA yang hanya "jaga server" dengan DBA yang mencegah masalah: desain database dan administrasi SQL. Prinsip yang akan kita pegang: a bad schema is a lifetime of slow queries. Query yang lambat jarang lahir karena SQL-nya jelek — akarnya hampir selalu schema dan index yang salah dari awal.

Development DBA (episode 1) menghabiskan sebagian besar waktunya di sini: mereview migration, mengecek rencana index, dan menegakkan standar. Kalian akan memakai pengetahuan ini untuk mereview desain tim, bukan hanya mengeksekusi perintah.

#DDL & DML yang Bersih

Sebelum desain, pastikan dasar administrasi DDL/DML kalian rapi. Contoh penamaan dan struktur yang konsisten:

CREATE TABLE users (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      TEXT NOT NULL UNIQUE,
    full_name  TEXT NOT NULL,
    status     TEXT NOT NULL DEFAULT 'active'
               CHECK (status IN ('active', 'suspended')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE orders (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    total_cents BIGINT NOT NULL CHECK (total_cents >= 0),
    status     TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Poin yang wajib dipahami sebagai DBA:

• Primary key selalu: tiap tabel butuh PK yang stabil; BIGINT GENERATED ALWAYS AS IDENTITY adalah standar modern (jangan SERIAL — identity lebih aman dari privilege).
• Constraint di database, bukan cuma di aplikasi: CHECK, NOT NULL, dan foreign key di database mencegah data korup walau aplikasi punya bug.
• Money sebagai integer (total_cents), bukan FLOAT — menghindari error pembulatan yang menyakitkan.
• TIMESTAMPTZ menyimpan waktu dengan zona; TIMESTAMP tanpa zona adalah undangan untuk bug lintas waktu.

#Normalisasi: 1NF, 2NF, 3NF

Normalisasi adalah proses menghilangkan redundansi agar data tidak duplikat. Tiga tahap awal yang paling sering dipakai:

1. 1NF: tiap sel berisi satu nilai atomik — tidak ada daftar dipisah koma dalam satu kolom.
2. 2NF: hilangkan ketergantungan parsial — kolom non-key harus bergantung pada seluruh key, bukan sebagian.
3. 3NF: hilangkan ketergantungan transitif — kolom non-key tidak boleh bergantung pada kolom non-key lain.

Contoh pelanggaran 3NF: tabel orders menyimpan user_name dan user_email — jika nama user berubah, data di semua order harus di-update. Solusinya: simpan user_id, dan join ke users saat butuh nama. Normalisasi menghemat storage dan mencegah anomalies (data tidak konsisten).

#Denormalisasi yang Disengaja

Normalisasi bukan harga mati. Denormalisasi adalah keputusan sadar untuk menduplikasi data demi kecepatan baca — dengan konsekuensi yang dikelola. Kapan layak?

• Kolom ringkasan: order_count di tabel users yang di-increment tiap order — mempercepat dashboard, tapi wajib dijaga konsistensinya (ideal via trigger atau materialized view, bukan kode aplikasi).
• Embedding lookup kecil: nama negara di tabel order alih-alih join ke tabel negara — jika nama itu jarang berubah, trade-off kecil.
• Kolom cache: data agregat yang dihitung ulang periodik.

Aturan DBA: normalisasi dulu sampai 3NF, lalu denormalisasi secara sadar dan terdokumentasi — bukan karena malas membuat join. Setiap denormalisasi wajib punya alasan tertulis di review.

#Data Types & Storage Optimization

Pemilihan tipe data menentukan ukuran tabel dan performansi index:

Penghematan tiap kolom tampak kecil, tetapi di tabel dengan 100 juta baris, SMALLINT vs BIGINT menghemat 600 MB plus mempercepat scan.

#Strategi Index yang Tepat Sasaran

Index mempercepat query, tapi setiap index memperlambat INSERT/UPDATE dan memakan disk. Prinsipnya: index untuk query yang benar-benar dijalankan, bukan untuk semua kolom.

-- Query umum: WHERE status = 'pending' ORDER BY created_at
CREATE INDEX idx_orders_status_created
    ON orders (status, created_at);
 
-- Unik: menjamin tidak ada email ganda (juga jadi index)
CREATE UNIQUE INDEX idx_users_email ON users (email);
 
-- Partial: hanya index baris yang relevan (hemat storage)
CREATE INDEX idx_orders_pending ON orders (created_at)
    WHERE status = 'pending';

Pola yang kalian akan review terus-menerus:

• Index B-tree standar untuk WHERE col = value, >, <, ORDER BY, dan join.
• Prefix/suffix kehati-hatian: index (status, created_at) membantu query WHERE status = 'pending' ORDER BY created_at; index (created_at, status) tidak membantu karena filter status ada di posisi kedua.
• Partial index (WHERE status = 'pending') ideal untuk data yang mayoritas sudah "selesai" — hanya baris aktif yang di-index.
• Jangan over-index: di tabel yang sering di-write, tiap index tambahan memperlambat tiap insert.

#Studi Kasus: Schema E-commerce

Mari aplikasikan. Store orders, users, dan products. Query prioritas bisnis:

1. "Order pending user X" → WHERE user_id = $1 AND status = 'pending' → index (user_id, status).
2. "Total penjualan per hari" → agregasi created_at → butuh index (created_at) atau partial untuk periode berjalan.
3. "Cari produk berdasarkan nama" → ILIKE '%term%' tidak akan memakai B-tree biasa; di episode 21 kita bahas trigram/pg_trgm.

Review checklist sebelum approval migration:

• Setiap tabel punya PK + created_at.
• Foreign key ter-index (PostgreSQL tidak otomatis meng-index FK!).
• Tidak ada SELECT * yang dikirim tanpa batas.
• Kolom status punya CHECK atau ENUM, bukan teks bebas.
• Index mengikuti pola query nyata (dibuktikan EXPLAIN).

#Penutup

Inti yang harus dibawa pulang:

• Schema yang buruk = masa depan query lambat; constraint wajib ada di database.
• Normalisasi sampai 3NF dulu, denormalisasi hanya secara sadar dan terdokumentasi.
• Tipe data yang tepat menghemat storage dan mempercepat scan.
• Index dibuat berdasarkan bukti query (EXPLAIN), dengan partial/compound index untuk pola umum; FK wajib di-index di PostgreSQL.

Di episode 5 selanjutnya kita membahas salah satu tanggung jawab terpenting DBA: backup & recovery — perbedaan logical vs physical backup, point-in-time recovery memakai WAL, sampai budaya drill restore yang menjamin backup kalian benar-benar bisa dipakai saat bencana. Ini materi yang tidak boleh kalian lewatkan. Sampai jumpa di episode 5!