SQL Fundamentals
Why Your Benchmark Measured the Cache
A query run five times in a row reports the speed of memory, not of the system. Which layer answered the request is the first thing to establish.
· 3 min
articles
All articles
Every article on SQLPress — query performance, database internals, operations and the day-to-day craft of working with data.
10 articles
SQLite
WAL Mode: One Writer, Many Readers
SQLite's default journal blocks readers during a write. WAL mode does not, and that single difference decides whether SQLite suits a concurrent service.
· 2 min
NoSQL
Document or Relational: Read-Time or Write-Time Cost
Relational modeling describes the entities and lets queries follow. Document modeling inverts that, and the inversion is the whole trade.
· 4 min
PostgreSQL
Connection Pool Sizing: Why More Connections Make It Slower
Past the point where the server can execute work in parallel, adding connections adds queueing rather than throughput — and moves the queue somewhere worse.
· 4 min
SQL Fundamentals
Composite Indexes: One Ordering, Not Three
Column order decides which queries a composite index can serve. The rule follows from the structure, and so do the cases where it does not hold.
· 3 min
MySQL
Why the Primary Key Is in Every Secondary Index
InnoDB stores the table inside its primary key. Almost every MySQL indexing consequence, good and bad, follows from that single decision.
· 3 min
PostgreSQL
Transaction Pooling Breaks Only Under Load
Transaction pooling is what makes PgBouncer worth deploying, and it silently removes session state your application may depend on.
· 3 min
PostgreSQL
EXPLAIN, ANALYZE, BUFFERS: Three Different Questions
Which variant you ran decides whether the numbers in front of you are the planner's estimate or a measurement, and whether the pages came from cache.
· 4 min
SQL Fundamentals
N+1 Queries: What the ORM Is Actually Doing
One query for the list, then one more for every item in it. The cost is per statement, which is why the profiler shows a fast database and a slow request.
· 3 min
PostgreSQL
Why an Index Is Not Used: the Estimate, Not the Index
An index the planner ignores is usually a row-estimate problem. The fix starts with what the optimizer expected, not with the index definition.
· 3 min