Mengenal PostgreSQL: Database Relasional Modern untuk Aplikasi Skala Besar

PostgreSQL (sering disebut “Postgres”) adalah database relasional open-source yang sangat powerful dan banyak digunakan oleh perusahaan besar seperti Apple, Instagram, Spotify, dan Netflix. Artikel ini akan membahas PostgreSQL dari dasar hingga kamu siap menggunakannya di project nyata.

Mengapa PostgreSQL?

Keunggulan PostgreSQL

• ACID Compliant: Menjamin integritas data
• Extensible: Bisa menambah tipe data, fungsi, dan operator custom
• JSON Support: Native support untuk data JSON/JSONB
• Full-Text Search: Pencarian teks built-in
• Scalable: Cocok untuk aplikasi kecil hingga enterprise
• Open Source: Gratis dan komunitas aktif

PostgreSQL vs MySQL

Fitur	PostgreSQL	MySQL
ACID Compliance	Full	Tergantung engine
JSON Support	Native JSONB	JSON (lebih lambat)
Full-Text Search	Built-in	Plugin
Extensibility	Sangat tinggi	Terbatas
Complex Queries	Lebih optimal	Cukup baik

Instalasi PostgreSQL

Windows

1. Download installer dari postgresql.org
2. Jalankan installer dan ikuti wizard
3. Catat password untuk user postgres

macOS

# Menggunakan Homebrew
brew install postgresql@15

# Start service
brew services start postgresql@15

Linux (Ubuntu/Debian)

# Update package list
sudo apt update

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

Verifikasi Instalasi

# Cek versi
psql --version

# Masuk ke PostgreSQL shell
sudo -u postgres psql

Konsep Dasar PostgreSQL

Database, Schema, dan Table

PostgreSQL Server
└── Database (contoh: myapp_db)
    └── Schema (contoh: public)
        └── Table (contoh: users, products)

Tipe Data Umum

Tipe	Deskripsi	Contoh
INTEGER	Bilangan bulat	42
BIGINT	Bilangan bulat besar	9223372036854775807
DECIMAL(p,s)	Angka presisi	19.99
VARCHAR(n)	String variabel	’Hello’
TEXT	String unlimited	’Long text…’
BOOLEAN	True/False	TRUE
DATE	Tanggal	’2025-12-15’
TIMESTAMP	Tanggal + waktu	’2025-12-15 10:30:00’
UUID	Unique identifier	’a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11’
JSONB	JSON binary	’{“key”: “value”}‘

Operasi Dasar

Membuat Database dan User

-- Masuk sebagai postgres
sudo -u postgres psql

-- Buat user baru
CREATE USER myuser WITH PASSWORD 'mypassword';

-- Buat database
CREATE DATABASE myapp_db OWNER myuser;

-- Berikan privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myuser;

-- Keluar
\q

Koneksi ke Database

# Format: psql -h host -U user -d database
psql -h localhost -U myuser -d myapp_db

Membuat Table

-- Table users
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table products
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    category_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table orders dengan foreign key
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    total_amount DECIMAL(12, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CRUD Operations

Create (INSERT)

-- Insert single row
INSERT INTO users (username, email, password_hash, full_name)
VALUES ('john_doe', 'john@example.com', 'hashed_password', 'John Doe');

-- Insert multiple rows
INSERT INTO products (name, price, stock) VALUES
    ('Laptop', 15000000, 10),
    ('Mouse', 250000, 50),
    ('Keyboard', 500000, 30);

-- Insert dengan RETURNING
INSERT INTO users (username, email, password_hash)
VALUES ('jane_doe', 'jane@example.com', 'hashed_password')
RETURNING id, username, created_at;

Read (SELECT)

-- Select semua kolom
SELECT * FROM users;

-- Select kolom tertentu
SELECT username, email, created_at FROM users;

-- Dengan kondisi WHERE
SELECT * FROM products WHERE price > 1000000;

-- Dengan ORDER BY
SELECT * FROM products ORDER BY price DESC;

-- Dengan LIMIT dan OFFSET (pagination)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;

-- Dengan LIKE (pattern matching)
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Dengan IN
SELECT * FROM products WHERE category_id IN (1, 2, 3);

-- Dengan BETWEEN
SELECT * FROM products WHERE price BETWEEN 100000 AND 500000;

Update

-- Update single column
UPDATE users SET is_active = FALSE WHERE id = 1;

-- Update multiple columns
UPDATE products
SET price = 14000000, stock = stock - 1
WHERE id = 1;

-- Update dengan RETURNING
UPDATE users
SET full_name = 'John Smith'
WHERE id = 1
RETURNING *;

Delete

-- Delete dengan kondisi
DELETE FROM users WHERE id = 1;

-- Delete semua (hati-hati!)
DELETE FROM products;

-- Truncate (lebih cepat untuk hapus semua)
TRUNCATE TABLE products RESTART IDENTITY;

Query Lanjutan

JOIN Operations

-- INNER JOIN
SELECT
    o.id AS order_id,
    u.username,
    o.total_amount,
    o.status
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- LEFT JOIN
SELECT
    u.username,
    COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- Multiple JOINs
SELECT
    o.id,
    u.username,
    p.name AS product_name,
    oi.quantity,
    oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

Aggregate Functions

-- COUNT
SELECT COUNT(*) FROM users WHERE is_active = TRUE;

-- SUM
SELECT SUM(total_amount) FROM orders WHERE status = 'completed';

-- AVG
SELECT AVG(price) FROM products;

-- MIN dan MAX
SELECT MIN(price), MAX(price) FROM products;

-- GROUP BY dengan HAVING
SELECT
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category_id
HAVING COUNT(*) > 5;

Subqueries

-- Subquery di WHERE
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders WHERE status = 'completed'
);

-- Subquery di FROM
SELECT avg_order.user_id, avg_order.avg_amount
FROM (
    SELECT user_id, AVG(total_amount) AS avg_amount
    FROM orders
    GROUP BY user_id
) AS avg_order
WHERE avg_order.avg_amount > 1000000;

Common Table Expressions (CTE)

-- CTE sederhana
WITH active_users AS (
    SELECT * FROM users WHERE is_active = TRUE
)
SELECT * FROM active_users WHERE created_at > '2025-01-01';

-- Recursive CTE (untuk hierarchical data)
WITH RECURSIVE category_tree AS (
    -- Base case
    SELECT id, name, parent_id, 1 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive case
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

Bekerja dengan JSON

PostgreSQL memiliki support native untuk JSON dengan tipe JSONB (binary JSON) yang lebih efisien.

-- Buat table dengan JSONB
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert JSON data
INSERT INTO events (name, metadata) VALUES
('user_signup', '{"source": "web", "browser": "Chrome", "country": "ID"}'),
('purchase', '{"product_id": 123, "amount": 50000, "currency": "IDR"}');

-- Query JSON field
SELECT * FROM events WHERE metadata->>'source' = 'web';

-- Query nested JSON
SELECT * FROM events WHERE metadata->'user'->>'age' > '18';

-- Check if key exists
SELECT * FROM events WHERE metadata ? 'source';

-- Update JSON field
UPDATE events
SET metadata = metadata || '{"processed": true}'
WHERE id = 1;

Indexing untuk Performa

Membuat Index

-- Index biasa
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;

-- Index untuk JSONB
CREATE INDEX idx_events_metadata ON events USING GIN(metadata);

Melihat Query Plan

-- EXPLAIN untuk melihat query plan
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- EXPLAIN ANALYZE untuk eksekusi + timing
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

Backup dan Restore

Backup

# Backup single database
pg_dump -U myuser -d myapp_db > backup.sql

# Backup dengan compression
pg_dump -U myuser -d myapp_db | gzip > backup.sql.gz

# Backup semua database
pg_dumpall -U postgres > all_databases.sql

Restore

# Restore dari file SQL
psql -U myuser -d myapp_db < backup.sql

# Restore dari compressed file
gunzip -c backup.sql.gz | psql -U myuser -d myapp_db

Tips Best Practices

1. Selalu Gunakan Prepared Statements

Hindari SQL injection dengan parameterized queries.

2. Gunakan Connection Pooling

Untuk aplikasi production, gunakan PgBouncer atau built-in pooling.

3. Monitor Query Performance

-- Enable query logging
ALTER SYSTEM SET log_statement = 'all';
SELECT pg_reload_conf();

4. Regular Maintenance

-- Vacuum untuk reclaim storage
VACUUM ANALYZE;

-- Reindex jika perlu
REINDEX DATABASE myapp_db;

5. Gunakan Transactions

BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- atau ROLLBACK jika ada error

Kesimpulan

PostgreSQL adalah pilihan database yang sangat solid untuk berbagai jenis aplikasi. Dengan fitur-fitur seperti:

• ACID compliance untuk data integrity
• JSON support untuk flexibility
• Extensibility untuk custom needs
• Performance yang excellent

PostgreSQL cocok untuk aplikasi dari skala kecil hingga enterprise. Mulai dengan operasi dasar, lalu pelajari fitur lanjutan sesuai kebutuhan projectmu.

---

Referensi:

• PostgreSQL Documentation
• PostgreSQL Tutorial
• Use The Index, Luke