Performance optimization guide for writing DB queries/schemas.
Key rules by priority
🔴 CRITICAL: Query performance
-- ❌ 인덱스 없는 WHERE
SELECT * FROM orders WHERE customer_email = 'user@example.com';
-- ✅ 인덱스 생성
CREATE INDEX idx_orders_email ON orders(customer_email);
- EXPLAIN ANALYZE Make it a habit — always check the execution plan
- If Seq Scan appears on a large table, an index is needed
SELECT *Prohibited → select only the necessary columns- N+1 queries → replace with JOIN or batch queries
🔴 CRITICAL: Connection management
- Use a connection pool (do not connect directly)
- Supabase → pgBouncer (transaction mode)
- Serverless environment → connection pool required
- Clean up idle connections
🟡 HIGH: Schema design
-- ❌ 무분별한 인덱스
CREATE INDEX idx_everything ON orders(a, b, c, d, e);
-- ✅ 부분 인덱스 (자주 쓰는 조건)
CREATE INDEX idx_active_orders ON orders(created_at)
WHERE status = 'active';
- Normalization vs denormalization — decide based on read patterns
- UUID vs serial → UUID is advantageous in distributed environments but has a larger index size
- JSONB → only for data that cannot be structured; if it is a query target, make it a column
🟢 MEDIUM: Concurrency/locking
- Bulk UPDATE/DELETE → batch processing
SELECT ... FOR UPDATE→ lock only the necessary rows- Minimize transactions (keep them short)
- Prevent deadlocks — consistent lock order
🔵 LOW: RLS (Row Level Security)
-- 보안 정책 예시
CREATE POLICY user_data ON user_profiles
FOR SELECT USING (auth.uid() = user_id);
- Design RLS policies together with indexes (performance impact!)
auth.uid()Minimize calls- Complex policies → check performance with EXPLAIN
Practical tips
- During development:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) - Production: Enable slow query logging
- Migration: Perform ALTER on large tables asynchronously
- Backup: pg_dump can be automated to NAS