Data & SQL

Guidelines for SQL optimization, database performance, and data analysis.

When to Use

• Writing or optimizing SQL queries
• Database performance issues
• Schema design decisions
• Data analysis and insights
• Index strategy planning

Query Optimization

Before Writing Queries

1. Clarify the business objective
2. Understand data volume and patterns
3. State any assumptions clearly
4. Consider cost and performance

Optimization Techniques

Problem	Solution
Subqueries	Replace with JOINs
Complex logic	Use CTEs for readability
Self-joins	Use window functions
Row-by-row	Batch operations
SELECT *	Specify columns
COUNT for existence	Use EXISTS

N+1 Query Problem

Before (N+1):

-- 1 query for users, then N queries for posts
SELECT * FROM users WHERE active = true;
-- Then for each: SELECT * FROM posts WHERE user_id = ?;

After (Single Query):

SELECT u.id, u.name,
  JSON_AGG(JSON_BUILD_OBJECT('id', p.id, 'title', p.title))
  FILTER (WHERE p.id IS NOT NULL) AS posts
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.active = true
GROUP BY u.id, u.name;

Index Strategy

Index Types

Type	Best For	Example
B-Tree	Equality, range, sort	Primary keys, dates
Composite	Multi-column WHERE	(status, created_at)
Partial	Filtered queries	WHERE status = 'active'
Covering	Index-only scans	Include all SELECT columns
GIN/GiST	Full-text, JSON	Text search

Index Checklist

• Primary keys indexed
• Foreign keys indexed
• Frequent WHERE columns indexed
• Composite indexes match query patterns
• No unused indexes (check stats)
• Index bloat monitored

Find Unused Indexes (PostgreSQL)

SELECT indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Performance Diagnosis

Slow Query Analysis

-- PostgreSQL: Find slow queries
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Common Issues

Symptom	Cause	Fix
Seq scan on large table	Missing index	Add appropriate index
Index not used	Stale stats	ANALYZE table
Lock contention	Long transactions	Reduce scope
Table bloat	Dead tuples	VACUUM or pg_repack

EXPLAIN ANALYZE

Always analyze execution plans:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;

Schema Design

Normalization vs Denormalization

Normalize when:

• Data integrity is critical
• Write-heavy workload
• Storage cost matters

Denormalize when:

• Read:write ratio > 10:1
• Query performance critical
• Joins are bottleneck

Schema Checklist

• Primary keys defined
• Foreign keys with proper constraints
• Appropriate data types (not oversized)
• Indexes for query patterns
• Timestamps for auditing
• Migration scripts reversible

Caching Strategies

When to Cache

• Expensive queries
• Frequently accessed data
• Semi-static data (tolerate staleness)

Cache Layers

Layer	Tool	Use Case
Application	Redis, Memcached	Query results
Database	Query cache	Identical queries
Materialized View	PostgreSQL	Pre-computed aggregates

Materialized View Example

CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) as post_count, MAX(created_at) as last_post
FROM posts
GROUP BY user_id;

CREATE INDEX ON user_stats(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

Data Analysis Output

Findings Format

## Summary
Key insight in 1-2 sentences.

## Key Metrics
| Metric | Value | Trend |
|--------|-------|-------|

## Insights
- Finding 1 with supporting data
- Finding 2 with supporting data

## Recommendations
1. Action item with expected impact
2. Suggested follow-up analysis

Migration Best Practices

Safe Migration Template

-- Migration: Add index for performance
-- Date: 2025-12-23
-- Ticket: DB-456

-- Forward (non-blocking)
CREATE INDEX CONCURRENTLY idx_posts_user_created
ON posts(user_id, created_at DESC);

-- Rollback
DROP INDEX CONCURRENTLY idx_posts_user_created;

-- Validation
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 1 ORDER BY created_at DESC;

Migration Checklist

• Tested on production-like data
• Rollback script ready
• Zero-downtime (CONCURRENTLY for indexes)
• Performance impact measured
• Monitoring in place

Decision Priority

When optimizing:

1. Impact - Measured improvement
2. Safety - Reversible, tested
3. Maintainability - Understandable
4. Scalability - Works at 10x
5. Cost - Resource implications