Connection Pools, Database-Side: What a Connection Really Costs
Every connection costs memory, and the database answers in threads. We look at what a connection actually occupies in the server — sockets, buffers, worker processes — and trace the lifecycle from accept to idle. Then we size a pool with the math that matters: connections per core and latency under load, not the default max_connections. We show what happens when the pool is too big (context switching, lock contention) and too small (queueing under bursts), plus how transaction pooling changes the equation. The video ends with the metrics to watch: active vs. idle connections, and time in queue for pool waits.
Topics covered:
- What a connection costs server-side
- Pool sizing: connections per core and latency
- Oversized pools and lock contention
- Transaction pooling and the metrics that matter
Related articles
Connection Pooling at the Database Level
PgBouncer, connection limits, transaction pooling — managing database connections without starving the server.
Why Your Query Is Slow (And It's Not the Index)
Buffer pool misses, WAL contention, lock waits, and connection pooling — the non-index causes of database slowness.
B-Tree Indexing: Why It Wins
B-tree vs hash indexes, covering indexes, index-only scans, and why balanced trees dominate database storage engines.
More in Databases
B-Trees: The Shape of Databases
Why every major database is a tree shaped like a disk page — and how to read your index's health from its shape.
WatchPostgres Internals Tour: Processes, Buffer Pool, WAL, and MVCC
A guided tour of PostgreSQL internals — process model, buffer manager, WAL, and MVCC — the mechanisms that make Postgres behave the way it does.
DetailsSQL Joins and Execution Plans: Nested Loop, Hash, and Merge
How the database executes joins — nested loop, hash join, and merge join — and how to read execution plans to see which strategy your query gets.
DetailsStorage Engines: LSM-Trees vs. B-Trees
LSM-trees vs. B-trees — how each storage engine writes, compacts, and reads, and what that means for write and read amplification in your workload.
DetailsSharding Strategies: Partition Keys, Distribution, and Rebalancing
How sharding actually works — partition keys, data placement, cross-shard queries, and the operational reality of splitting one database into many.
DetailsReplication Explained: WAL Shipping, Lag, and Failover
How database replication actually works — the transaction log, the lag, and the failure modes of synchronous and asynchronous replication in production.
DetailsTransactions and Isolation Levels: ACID, MVCC, and Anomalies
What transactions actually guarantee — ACID mechanics, MVCC, and the real behavior behind each isolation level, demonstrated with concrete anomalies.
DetailsDatabase Indexes Visualized: B-Trees, Covering Indexes, and Planner Decisions
How database indexes actually work — B-trees, hash indexes, covering indexes, and when the planner will or won't use the index you made.
DetailsQuery Optimizer Internals: From Parse Tree to Execution Plan
What happens inside a query optimizer — parse, rewrite, join ordering, cost models, and how the planner decides the plan your query gets.
DetailsDepth, delivered weekly
One technical dispatch a week — articles and episode notes before they go public.
One technical dispatch per week. No noise.