Belajar SQL PostgreSQL - Studi Kasus Production-Grade E-Commerce Database

#Pendahuluan

Selamat datang di episode 20 — episode terakhir series Belajar SQL PostgreSQL! Perjalanan kita sudah panjang: dari fondasi tabel dan query, joins, window functions, transaction dan ACID, indexing dan optimasi, keamanan, hingga backup dan replication. Sekarang saatnya ujian terakhir yang sesungguhnya: merancang database e-commerce yang siap produksi dengan menerapkan semuanya sekaligus.

Inilah momen "menyatukan puzzle": UUID dan RLS dari episode 16 melindungi data user, JSONB dan full-text search dari episode 8 dan 17 mengelola katalog, pessimistic locking dari episode 11 mencegah double spending, trigger audit dari episode 13 mencatat jejak, dan materialized view dari episode 12 menyajikan laporan analitik.

Di episode ini, kita akan merancang schema lengkap dalam empat bagian: Users & Auth, Product Catalog, Inventory & Order Processing, dan Analytics. Lalu kita tutup dengan checklist kesiapan produksi dan maintenance routine.

#1. Users & Auth: UUID PK, RLS Multi-Tenant, Hashed Credentials

UUID sebagai primary key (episode 3): identitas global yang tidak membocorkan jumlah user dan aman untuk integrasi terdistribusi.
Hashed credentials: password disimpan sebagai hash, bukan plaintext.
RLS (episode 16): isolasi baris per tenant untuk mendukung multi-toko dalam satu database.

Tabel users dengan UUID dan credential hashed

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    email TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    full_name TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'customer'
        CHECK (role IN ('customer', 'admin', 'staff')),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, email)
);

#2. Product Catalog: JSONB Flexible Attributes & Full-Text Search

Tabel products dengan JSONB dan FTS

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name TEXT NOT NULL,
    slug TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    category_id UUID,
    price NUMERIC(12,2) NOT NULL CHECK (price >= 0),
    attributes JSONB NOT NULL DEFAULT '{}',
    search_vector TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('indonesian', name || ' ' || description)
    ) STORED,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, slug)
);

Cari produk dengan FTS

SELECT id, name, price
FROM products
WHERE search_vector @@ plainto_tsquery('indonesian', 'sepatu lari')
ORDER BY ts_rank(search_vector, plainto_tsquery('indonesian', 'sepatu lari')) DESC;

#3. Inventory & Order Processing: Strict Constraints, Locking & Audit

Tabel inventory dan orders

CREATE TABLE inventory (
    product_id UUID PRIMARY KEY REFERENCES products(id) ON DELETE CASCADE,
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK (total >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Proses order dengan locking dan transaksi

BEGIN;
 
SELECT stock FROM inventory
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001'
FOR UPDATE;
 
UPDATE inventory
SET stock = stock - 1,
    updated_at = now()
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001'
  AND stock >= 1;
 
INSERT INTO orders (user_id, status, total)
VALUES ('1c2d3e4f-0000-0000-0000-000000000001', 'pending', 150000);
 
COMMIT;

#Audit Trigger untuk Orders

Audit trigger untuk orders

CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name TEXT NOT NULL,
    action TEXT NOT NULL,
    row_id UUID NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE FUNCTION log_audit()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, row_id)
    VALUES (TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id));
    RETURN NEW;
END;
$$;
 
CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_audit();

#4. Analytics: Materialized View & Automatic Refresh

Materialized view untuk daily sales report

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    DATE_TRUNC('day', o.created_at) AS day,
    COUNT(*) AS total_orders,
    COALESCE(SUM(o.total), 0) AS revenue
FROM orders o
WHERE o.status IN ('paid', 'shipped')
GROUP BY 1;
 
CREATE UNIQUE INDEX mv_daily_sales_day_idx ON mv_daily_sales (day);

#Checklist Kesiapan Produksi & Maintenance Routine

Aktivitas	Tujuan	Frekuensi
Tuning `autovacuum`	Mencegah table bloat (sisa versi lama akibat MVCC)	Konfigurasi awal + pantau
`VACUUM ANALYZE`	Bersihkan bloat, perbarui statistik untuk planner	Terjadwal / otomatis
Review `pg_stat_statements`	Temukan query lambat baru	Mingguan
Reindex strategy	Perbaiki index yang membloat	Berkala saat maintenance
Monitor table bloat	Deteksi tabel membengkak tanpa alasan	Rutin

Cek bloat dan statistik

SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

#Penutup

Inti yang harus dibawa pulang:

Desain (episode 2-3): normalisasi, constraint, dan tipe data yang tepat adalah fondasi semua yang lain.
Query (episode 4-10): dari DML, joins, subquery, JSONB, window functions, hingga CTE recursive.
Keandalan (episode 11-13): ACID, transaction, locking, view, function, dan trigger.
Performa (episode 14-15): indexing, EXPLAIN ANALYZE, dan pg_stat_statements untuk optimasi berbasis bukti.
Keamanan & produksi (episode 16-19): roles, RLS, FTS, pgvector, partitioning, backup, replication, dan PgBouncer.

Terakhir, ingat tiga prinsip yang menyatukan semuanya: data integrity tidak bisa dikompromikan, optimasi harus dibuktikan dengan pengukuran, dan backup yang tidak diuji bukan backup. Terapkan ketiganya, dan kalian akan membangun database yang bukan hanya berjalan — tapi berjalan dengan benar, aman, dan siap bertumbuh.

Selamat, kalian telah menyelesaikan series Belajar SQL PostgreSQL! Teruslah berlatih dengan proyek nyata, baca dokumentasi PostgreSQL secara berkala, dan jadikan database bukan sekadar tools, melainkan salah satu skill paling berharga dalam karier engineering kalian.