10 Tips PostgreSQL yang Bikin Query Kamu 10x Lebih Cepat

10 Tips PostgreSQL untuk Performa Optimal

PostgreSQL adalah pilihan database terbaik untuk sebagian besar web application. Tapi banyak developer yang hanya pakai sebatas INSERT, SELECT, UPDATE, DELETE — padahal ada banyak fitur powerful yang bisa dramatisasi performa query.

1. Index yang Tepat

Index adalah hal pertama yang harus dicek kalau query lambat.

-- Cek query yang lambat
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Lihat index yang ada
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public';

-- Buat index untuk kolom yang sering di-WHERE
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at DESC);

-- Composite index kalau sering filter 2 kolom sekaligus
CREATE INDEX CONCURRENTLY idx_orders_user_status 
ON orders(user_id, status);

2. EXPLAIN ANALYZE — Teman Terbaik Kamu

-- Selalu pakai ini untuk debug query lambat
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'pending'
ORDER BY created_at DESC;

-- Yang perlu diperhatikan:
-- Seq Scan = tidak pakai index (biasanya masalah)
-- Index Scan = pakai index (bagus)
-- Hash Join vs Nested Loop (tergantung data size)

3. Partial Index untuk Data Terfilter

-- Kalau sering query order yang pending saja
-- Jangan index semua status, buat partial index
CREATE INDEX idx_pending_orders 
ON orders(created_at) 
WHERE status = 'pending';

-- Index ini jauh lebih kecil dan lebih cepat
-- karena hanya meng-index rows yang relevant

4. Gunakan CTE untuk Query Kompleks

-- Tanpa CTE - susah dibaca dan di-maintain
SELECT u.name, COUNT(o.id), SUM(oi.price)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE u.created_at > NOW() - INTERVAL '90 days'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 0;

-- Dengan CTE - bersih dan mudah debug
WITH new_users AS (
  SELECT id, name FROM users
  WHERE created_at > NOW() - INTERVAL '90 days'
),
completed_orders AS (
  SELECT user_id, id FROM orders
  WHERE status = 'completed'
),
order_totals AS (
  SELECT o.user_id, COUNT(o.id) as order_count,
         SUM(oi.price) as total_spent
  FROM completed_orders o
  JOIN order_items oi ON o.id = oi.order_id
  GROUP BY o.user_id
)
SELECT u.name, ot.order_count, ot.total_spent
FROM new_users u
JOIN order_totals ot ON u.id = ot.user_id;

5. JSONB untuk Data Semi-Terstruktur

-- Kolom JSONB dengan index GIN
CREATE TABLE products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  metadata jsonb DEFAULT '{}'::jsonb
);

CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- Insert data
INSERT INTO products (name, metadata) VALUES
('Laptop', '{"brand": "Asus", "specs": {"ram": "16GB", "storage": "512GB"}}');

-- Query dengan JSONB operators
SELECT name, metadata->'brand' as brand
FROM products
WHERE metadata @> '{"specs": {"ram": "16GB"}}';

6. Connection Pooling dengan PgBouncer

Setiap koneksi PostgreSQL butuh ~5MB RAM. Kalau aplikasimu punya 100 concurrent users, itu sudah 500MB hanya untuk connections.

Solusinya: connection pooler seperti PgBouncer atau Supabase Pooler.

# Dengan Supabase, ganti connection string dari:
postgresql://user:pass@db.supabase.co:5432/postgres

# Ke pooler:
postgresql://user:pass@db.supabase.co:6543/postgres?pgbouncer=true

7. Materialized View untuk Laporan Berat

-- Dashboard laporan yang berat, jangan query real-time
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT 
  DATE_TRUNC('month', created_at) as month,
  COUNT(*) as total_orders,
  SUM(total) as revenue
FROM orders
WHERE status = 'completed'
GROUP BY 1
ORDER BY 1;

-- Refresh setiap jam (pakai pg_cron atau cron job)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

-- Query dashboard sekarang instant
SELECT * FROM monthly_revenue WHERE month > NOW() - INTERVAL '12 months';

8. Proper Data Types

-- JANGAN pakai varchar untuk semua hal
-- PAKAI tipe yang tepat:

CREATE TABLE users (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- bukan integer
  email       text NOT NULL,                               -- bukan varchar(255)
  age         smallint,                                    -- bukan integer untuk angka kecil
  balance     numeric(12,2),                              -- bukan float untuk uang!
  is_active   boolean DEFAULT true,                        -- bukan integer 0/1
  metadata    jsonb DEFAULT '{}'::jsonb,                 -- bukan text untuk JSON
  created_at  timestamptz DEFAULT now()                    -- bukan timestamp (pakai timezone!)
);

9. Soft Delete yang Benar

-- Soft delete dengan index yang efisien
ALTER TABLE posts ADD COLUMN deleted_at timestamptz;

-- Partial index hanya untuk data yang belum dihapus
CREATE INDEX idx_posts_active ON posts(user_id, created_at)
WHERE deleted_at IS NULL;

-- Query otomatis exclude deleted rows
-- Atau bisa pakai RLS policy di Supabase:
CREATE POLICY 'hide_deleted' ON posts
  FOR SELECT USING (deleted_at IS NULL);

10. VACUUM dan Maintenance

-- Cek table bloat (tanda perlu VACUUM)
SELECT 
  schemaname, tablename,
  n_dead_tup as dead_rows,
  n_live_tup as live_rows,
  ROUND(n_dead_tup::numeric/NULLIF(n_live_tup+n_dead_tup,0)*100, 2) as dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Manual VACUUM untuk tabel yang bloated
VACUUM ANALYZE orders;

-- Autovacuum biasanya cukup, tapi kalau ada bulk delete/update
-- jalankan manual VACUUM setelahnya

Implementasikan tips-tips ini secara bertahap di project kamu, mulai dari yang paling mudah: pastikan semua kolom yang di-filter punya index yang sesuai.