Postgres

Postgres Default Privileges: How ALTER DEFAULT PRIVILEGES Solves the Forgotten-Grant Problem

GRANT applies to objects that exist now. ALTER DEFAULT PRIVILEGES applies to objects created in the future. The distinction explains why role permissions work for two weeks and then quietly break the day someone runs a migration.

Postgres

Postgres deadlock_timeout vs lock_timeout: Two Knobs That Defend Against Different Failure Modes

The deadlock_timeout is how long Postgres waits before running the deadlock detector. The lock_timeout is how long a statement waits for any lock before failing. They look similar in the config and they defend against very different problems.

Postgres

Postgres pg_locks Wait Modes: Lock Mode Hierarchy Explained

The eight lock modes Postgres uses, what they conflict with, the cases each one comes from, and how to read pg_locks for diagnosis. The lock-vs-lwlock distinction.

Postgres

Postgres pg_prewarm: Loading the Buffer Cache Before the First Query Hits

After a Postgres restart, the buffer cache is empty and every query that touches a previously-hot table pays cold-cache latency. pg_prewarm lets you push specific tables and indexes into shared_buffers before traffic arrives, with autoprewarm preserving the cache across restarts.

Postgres

Postgres VACUUM ANALYZE: When to Run It by Hand and When to Trust Autovacuum

Autovacuum handles most Postgres maintenance correctly, but a small set of cases benefits from manual VACUUM ANALYZE: large bulk loads, post-migration backfills, statistics drift after schema changes. The trick is knowing which case you are in before you run anything.

Postgres

Postgres GIN vs GiST: Choosing the Right Index Type for Containment Queries

GIN and GiST are the two Postgres index access methods that handle the queries B-tree cannot: containment, overlap, full-text search, JSON path. They have different cost profiles. Picking wrong produces orders-of-magnitude difference in workload behavior.

Postgres

Postgres FOR SHARE vs FOR UPDATE: The Locking Mode Most Developers Skip

SELECT FOR UPDATE is the row-locking mode every developer eventually learns. SELECT FOR SHARE is the weaker variant most developers never reach for. Knowing when each is right is the difference between a queue that scales and a queue that thrashes.

Postgres

Postgres pg_wait_sampling: Profiling What Your Database Is Waiting For

pg_stat_activity shows the wait events for currently-active sessions at the moment you query it. pg_wait_sampling captures them continuously by sampling at high frequency and aggregating. The view that results is one of the most useful diagnostic surfaces for a busy database.

Postgres

Postgres random_page_cost: The Planner Knob That Shapes Index Decisions

random_page_cost is the single most important storage-cost parameter the planner uses. Its 4.0 default reflects spinning rust. On SSD and NVMe storage, the right value is between 1.0 and 1.5, and changing it shifts plan choices for thousands of queries at once.

Postgres

Postgres track_io_timing: Measuring How Much Time Queries Spend Waiting for Disk

track_io_timing turns I/O time into a per-query observable. Off by default because of historical clock-read cost. On modern hardware the cost is negligible and the data is essential for distinguishing CPU-bound from disk-bound queries.

Postgres

Postgres pg_cancel_backend vs pg_terminate_backend: Stopping Queries Without Breaking the Application

pg_cancel_backend interrupts a query while keeping the connection alive. pg_terminate_backend kills the connection entirely. The distinction matters during incidents and the wrong choice can convert a slow query into a stampede of reconnects.

Postgres

Postgres pg_stat_statements_reset: When and Why to Throw Away Query Statistics

pg_stat_statements_reset() looks like a clean slate function but throwing away cumulative query statistics destroys trend visibility. The right discipline is rare, scoped resets paired with snapshot capture.