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.
Posts tagged with #database
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.
Key-Value Stores: Redis and DynamoDB Patterns
Learn Redis and DynamoDB key-value patterns for caching, sessions, leaderboards, TTL eviction policies, and storage tradeoffs.
PostgreSQL Locking and Concurrency Control
Learn about shared vs exclusive locks, lock escalation, deadlocks, optimistic vs pessimistic concurrency control, and FOR UPDATE clauses.
Materialized Views
Precomputed query results stored as tables. PostgreSQL refresh strategies, indexes on materialized views, and when to use them vs views or denormalization.
Database Normalization: From 1NF to BCNF
Learn database normalization from 1NF through BCNF. Understand how normalization eliminates redundancy, prevents update anomalies, and when denormalization makes sense for performance.
Primary and Foreign Keys: A Practical Guide
Learn the difference between natural and surrogate keys, how foreign keys enforce referential integrity, cascade rules, and best practices for constraint naming.
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 *.
Read/Write Splitting
Master-slave replication for read scaling. Routing strategies, consistency lag considerations, and when this pattern helps vs hurts your architecture.