Denormalization
When to intentionally duplicate data for read performance. Tradeoffs with normalization, update anomalies, and application-level denormalization strategies.
Posts tagged with #query-optimization
Index Design Clinic: Composite Indexes, Covering Indexes, and Partial Indexes
Master composite index column ordering, covering indexes for query optimization, partial indexes for partitioned data, expression indexes, and selectivity.
Database Indexes: B-Tree, Hash, Covering, and Beyond
A practical guide to database indexes. Learn when to use B-tree, hash, composite, and partial indexes, understand index maintenance overhead, and avoid common performance traps.
Understanding SQL JOINs and Database Relationships
Master SQL JOINs with this practical guide covering INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins. Learn how relationship types between tables shape your queries.
Materialized Views
Precomputed query results stored as tables. PostgreSQL refresh strategies, indexes on materialized views, and when to use them vs views or denormalization.
Query Execution Plans: Reading EXPLAIN Output Like a Pro
Learn to decode PostgreSQL EXPLAIN output, understand sequential vs index scans, optimize join orders, and compare bitmap heap scans with index-only scans.
Query Rewriting: Subqueries, CTEs, Joins, Window Functions
Learn when to use subqueries vs joins vs CTEs, effective CTE patterns, IN vs EXISTS, OR to UNION rewrites, and avoiding SELECT *.