🏠 Home / Hub

🐘 PostgreSQL Lesson 05 — Indexes & Transactions

← Back to PostgreSQL Menu  |  🏠 Hub

1. Indexes — Query Speed Up

Index = Book ရဲ့ index page လိုပဲ — full scan မလုပ်ဘဲ မြန်မြန် find နိုင်
Primary Key + UNIQUE constraints = auto create index
Foreign key columns, WHERE clause columns = manual index add ပါ 
-- Create index
CREATE INDEX idx_users_email    ON users(email);
CREATE INDEX idx_posts_user_id  ON posts(user_id);
CREATE INDEX idx_posts_created  ON posts(created_at DESC);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_uniq ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_posts_user_date ON posts(user_id, created_at DESC);
-- Good for: WHERE user_id = ? ORDER BY created_at DESC

-- Partial index (subset of rows)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Only indexes active users — smaller, faster!

-- GIN index for JSONB and ARRAY
CREATE INDEX idx_products_meta ON products USING GIN(metadata);
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
-- Enables fast: WHERE metadata @> '{"brand":"Dell"}'

-- Full-text search index
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', title || ' ' || body));

-- List indexes
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

-- Drop index
DROP INDEX idx_users_email;

2. EXPLAIN ANALYZE — Query Performance

-- See query execution plan
EXPLAIN SELECT * FROM users WHERE email = 'ko@test.com';
-- "Index Scan using idx_users_email on users (cost=0.28..8.30 rows=1)"

-- Actual runtime stats
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'ko@test.com';
-- Execution Time: 0.045 ms

-- WITHOUT index: "Seq Scan" (full table scan) — slow!
-- WITH index:    "Index Scan"                  — fast!

-- Expensive operations to watch for:
-- Seq Scan  → add index on that column
-- Hash Join → usually OK for small tables
-- Nested Loop → might need index
-- Sort      → add index on ORDER BY column

-- Enable timing
\timing on
💡 Query slow ဖြစ်ရင် EXPLAIN ANALYZE → Seq Scan ပြရင် index add ပါ

3. Transactions — ACID

-- ACID: Atomicity, Consistency, Isolation, Durability
-- Transaction = multiple operations ကို all-or-nothing group

-- Bank transfer example
BEGIN;
  -- Deduct from sender
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;

  -- Verify balance OK
  DO $$ BEGIN
    IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN
      RAISE EXCEPTION 'Insufficient funds!';
    END IF;
  END; $$;

  -- Add to receiver
  UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

COMMIT;   -- ← all success → save permanently

-- If anything fails: ROLLBACK
BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
  -- Error occurs here...
ROLLBACK;   -- ← undo everything — both accounts unchanged!

-- SAVEPOINT — partial rollback
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 5000);
  SAVEPOINT order_created;

  INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 5, 2);
  -- Error: product 5 out of stock
  ROLLBACK TO order_created;   -- ← undo items but keep order

  INSERT INTO order_items (order_id, product_id, qty) VALUES (1, 3, 2);
COMMIT;

4. Views & Materialized Views

-- VIEW — saved query (runs every time)
CREATE VIEW active_user_summary AS
SELECT
  u.id,
  u.name,
  u.email,
  COUNT(p.id) AS post_count,
  MAX(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE u.active = true
GROUP BY u.id, u.name, u.email;

-- Use like a table
SELECT * FROM active_user_summary WHERE post_count > 10;

-- MATERIALIZED VIEW — cached result (fast read, manual refresh)
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(total) AS revenue,
  COUNT(*) AS orders
FROM orders
GROUP BY month;

-- Fast read (cached)
SELECT * FROM monthly_revenue;

-- Refresh cache
REFRESH MATERIALIZED VIEW monthly_revenue;
-- Schedule: pg_cron extension or application scheduler

← PostgreSQL 04  |  Next: PostgreSQL 06 → Node.js + pg →

📌 Study Checklist