PostgreSQL Advanced Queries: CTE, Window Functions, dan Indexing

PostgreSQL bukan cuma SELECT * FROM table. Ada fitur advanced yang bisa solve query kompleks dengan elegan: CTE (Common Table Expressions), Window Functions, dan Indexing Strategy. Artikel ini bakal bahas ketiganya dengan contoh praktis.

CTE (Common Table Expressions)

CTE bikin query kompleks jadi lebih readable dengan memecahnya jadi beberapa langkah yang dinamai.

-- Tanpa CTE (nested subquery, susah dibaca)
SELECT * FROM (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
) AS recent_orders
WHERE order_count > 5;

-- Dengan CTE (lebih jelas)
WITH recent_orders AS (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT * FROM recent_orders WHERE order_count > 5;

Recursive CTE

Untuk data hierarchical (org chart, category tree, comment thread):

-- Category tree traversal
WITH RECURSIVE category_tree AS (
  -- Base case: root categories
  SELECT id, name, parent_id, 0 as depth
  FROM categories WHERE parent_id IS NULL
  
  UNION ALL
  
  -- Recursive case: children
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

Window Functions

Window functions melakukan kalkulasi di "window" (subset) dari result set tanpa menggabungkan rows. Berbeda dengan GROUP BY yang merge rows menjadi satu.

ROW_NUMBER, RANK, DENSE_RANK

-- Top 3 produk terlaris per kategori
SELECT 
  category,
  product_name,
  total_sold,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sold DESC) as rank
FROM products
WHERE rank <= 3;

Running Total dan Moving Average

-- Running total revenue per bulan
SELECT 
  date_trunc('month', created_at) as month,
  SUM(amount) as monthly_revenue,
  SUM(SUM(amount)) OVER (ORDER BY date_trunc('month', created_at)) as cumulative_revenue
FROM orders
GROUP BY month
ORDER BY month;

LAG dan LEAD

-- Perbandingan revenue bulan ini vs bulan lalu
SELECT 
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) as growth
FROM monthly_revenue;

Indexing Strategy

Index bikin query cepat, tapi ga semua kolom perlu di-index. Berikut strateginya:

B-tree Index (Default)

-- Untuk kolom yang sering di-WHERE atau ORDER BY
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Composite Index

-- Untuk query yang filter beberapa kolom
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Cocok untuk: WHERE user_id = ? ORDER BY created_at DESC

Partial Index

-- Index hanya untuk data yang relevan
CREATE INDEX idx_orders_active ON orders(status) WHERE status = 'active';
-- Lebih kecil, lebih cepat dari full index

GIN Index untuk Full-Text Search

-- Full-text search di PostgreSQL
CREATE INDEX idx_products_search ON products USING GIN(to_tsvector('indonesian', name || ' ' || description));
SELECT * FROM products WHERE to_tsvector('indonesian', name || ' ' || description) @@ to_tsquery('indonesian', 'kaos & cotton');

Kesimpulan

PostgreSQL punya fitur advanced yang sangat powerful. CTE bikin query readable, Window Functions bikin analisis data tanpa subquery yang rumit, dan Indexing bikin query cepat. Kuasai ketiganya dan lo bisa solve kebanyakan masalah database dengan elegan.

Mau belajar database dan backend? Kunjungi everdev.pro.