Topic Hub
SQL and data correctness problems are rarely only about one query. Slow plans, missing indexes, ORM query explosions, connection pool pressure, stale caches, replica lag, transaction boundaries, isolation levels, locks, schema changes, migration testing, migration rollback, and worker concurrency all affect whether the system stays fast and correct under production traffic.
This hub collects CodeNotes articles for the database problems that tend to cross boundaries: diagnosing slow queries, understanding why scaling did not help, choosing transaction boundaries and concurrency controls, sizing connection pools, fixing ORM access patterns, designing cache freshness boundaries, coordinating database-backed jobs and events, testing migrations before production, and rolling out or rolling back data changes without breaking live requests.
Start from the symptom or decision in front of you, then use the article that matches that database boundary.
A production SQL query is slow and the source of pressure is unclear.
How to Find and Fix Slow SQL Queries in Production
An endpoint gets slower as each returned row loads more related data.
N+1 Query Problem in ORMs
An index exists, but the query still scans, sorts, or stays slow.
Why Database Indexes Didn’t Fix Your Slow Query
Read replicas moved traffic, but the primary database stayed hot.
Why Read Replicas Didn't Reduce Database Load
Requests are waiting for database connections before SQL starts.
Database Connection Pool Exhaustion in Production
Cached reads return stale or contradictory data after writes.
Why Caching Causes Inconsistent Data in Production
An API transaction boundary is unclear, so partial writes or long locks are possible.
Database Transaction Boundaries in Backend APIs
Concurrent transactions see data in surprising ways.
SQL Isolation Levels Explained
Several requests can update the same row or business object.
Optimistic vs Pessimistic Locking in SQL
A schema change must roll out without breaking live traffic.
Safe Database Migrations in Production
A schema change needs CI coverage against old data and mixed application behavior.
Database Migration Testing in CI: Catch Schema Bugs Before Production
A database migration may need to roll back without losing writes.
Database Migration Rollback Strategy in Production
Workers need to claim database-backed jobs without blocking each other.
PostgreSQL Job Queues with SKIP LOCKED
A database write and a published event must stay consistent.
Transactional Outbox Pattern in Microservices
These articles are grouped by the pressure they help explain: query cost, ORM query shape, scaling behavior, connection pool pressure, cache freshness, transaction boundaries, transaction visibility, locking, event consistency, and production data change and rollback.
Start here when the database is slow, replicas do not move load, or indexes do not behave the way the plan expected.
A production-oriented workflow for finding slow SQL, reading signals, and choosing the right fix.
A production workflow for finding slow SQL queries, reading EXPLAIN plans, choosing fixes, and proving the endpoint improved.
Find and fix ORM query explosions with query counts, SQL traces, eager loading, batched relation reads, and request-scoped loaders.
How to detect and fix the N+1 query problem in ORMs with query counts, SQL traces, eager loading, batched IN queries, request-scoped loaders, and production rollout checks.
Diagnose why an index exists but the plan still scans, filters, sorts, or estimates the query poorly.
Why a database index can exist and still fail to speed up a query, including selectivity, row estimates, composite index order, post-index filters, ORDER BY LIMIT behavior, partial indexes, and production verification.
How read-coupled writes, replica lag, fallback behavior, and query cost keep pressure on the primary.
Why read replicas often fail to reduce primary database load when reads are coupled to writes, replica lag triggers fallback behavior, and query cost is misunderstood.
Diagnose pool wait, fleet-wide connection budgets, leaked clients, worker contention, and when PgBouncer helps.
How database connection pool exhaustion happens under production load, how to distinguish pool wait from slow SQL, and how to size pools across instances without overloading PostgreSQL.
Understand when cache-aside, TTLs, local caches, and invalidation gaps make reads contradict the source of truth.
Why production caches return stale or contradictory data, including cache-aside races, invalidation gaps, TTL drift, local cache divergence, write-through trade-offs, and safer rollout checks.
Use these guides when the risk is not only latency, but whether the data stays correct under live traffic.
Choose where a backend API transaction starts and ends so writes stay atomic without holding locks around slow work or external calls.
How to choose database transaction boundaries in backend APIs so writes stay atomic without holding locks across external calls, slow work, retries, or post-commit side effects.
Understand how isolation levels shape what transactions can see while concurrent writes are happening.
A practical guide to SQL isolation levels, including Read Committed, Repeatable Read, Serializable, database-specific behavior, locking trade-offs, and production examples.
Choose between version checks, guarded updates, and row locks when concurrent requests can change the same data.
How to choose between optimistic locking, guarded updates, and SELECT FOR UPDATE in SQL when concurrent requests can overwrite data, oversell inventory, or serialize hot rows.
Roll out schema changes with expand-and-contract, resumable backfills, constraint staging, and rollback planning.
A practical rollout plan for safe database migrations using expand-and-contract, phased constraints, resumable backfills, rollback planning, and production monitoring.
Test schema changes against old data, previous schema fixtures, compatibility windows, and backfill behavior before production.
How to test database migrations in CI with production-shaped data, compatibility checks, backfill verification, schema drift detection, and rollback gates.
Plan rollback boundaries for schema changes with compatibility windows, forward fixes, backfill recovery, and cutover runbooks.
How to plan database migration rollback in production with compatibility windows, forward fixes, backfill recovery, and clear stop points before destructive schema changes.
Build PostgreSQL job queues with SKIP LOCKED, focused indexes, retries, and stuck-job recovery.
How to build a PostgreSQL job queue with FOR UPDATE SKIP LOCKED, including schema design, atomic claiming, indexes, retries, stuck-job recovery, cleanup, and production trade-offs.
Commit business data and publish intent together so database state and downstream events do not drift apart.
How to implement the transactional outbox pattern for reliable event publishing, including schema design, relay claiming, retries, duplicate handling, ordering, CDC, monitoring, and cleanup.
Query tuning explains where work is spent. ORM query-shape work explains when clean application code is secretly issuing one query per returned row. Indexes explain whether the database can avoid unnecessary work. Replicas explain which reads can move away from the primary. Connection pools explain how much database concurrency the application fleet is allowed to spend. Cache consistency explains when a fast answer can contradict the source of truth. Transaction boundaries explain which writes commit together and how long locks stay open. Isolation and locking explain what concurrent transactions can safely do. Migration tests explain whether old data and mixed schema states survive before deploy. Migrations explain how to change the data shape while old and new code overlap. Outbox patterns explain how committed data changes become durable downstream events without pretending the database and broker share one transaction.
The common thread is that database behavior is part of application correctness. A query that is fast in isolation, a lock that is safe in a test, or a migration that works locally can still fail when production traffic, concurrency, and data size change the shape of the problem.