#PostgreSQL
11 posts tagged with this topic. ← All tags
-
The query planner: why the same query runs differently on different data.
PostgreSQL's query planner chooses how to execute a query based on table statistics. Understanding that process explains why query performance changes as data changes.
-
Connection pooling math: how to calculate the right pool size.
More connections isn't always better. Database connections are expensive resources, and the optimal pool size has a formula — here's how to work it out.
-
Partial indexes: smaller, faster indexes for filtered queries.
A partial index only indexes rows that match a condition. For queries that always filter on a specific value, a partial index is smaller, faster, and cheaper to maintain.
-
Index-only scans: the PostgreSQL optimization that skips the heap.
When all the columns a query needs are in an index, PostgreSQL can answer the query from the index alone — without touching the table at all.
-
MVCC: how PostgreSQL lets reads and writes coexist.
PostgreSQL's MVCC keeps multiple versions of each row so readers never block writers and writers never block readers. Here's how the mechanism works.
-
Write-ahead logging: the mechanism behind database durability.
WAL is how PostgreSQL guarantees that committed transactions survive crashes. Understanding it explains database performance characteristics and replication.
-
Optimistic concurrency: handling conflicts without locking rows.
Pessimistic locking serializes access to data and creates contention. Optimistic concurrency lets transactions proceed freely and detects conflicts only when they actually occur.
-
Connection pooling at scale: why PgBouncer exists.
How PgBouncer pools database connections to handle high concurrency, the three pooling modes, and how to configure it.
-
Database replication: read replicas and why they help.
How PostgreSQL replication works, when read replicas make sense, and how to route reads and writes in your application.
-
Database connections in serverless: the problem and the solutions.
Why traditional database connection pooling breaks in serverless environments, and the patterns that actually work.
-
Offset pagination breaks at scale. Here's how cursor pagination fixes it.
OFFSET-based pagination is easy to implement but produces inconsistent results as data changes. Cursor pagination is stable, performant, and the right default for most APIs.