PostgreSQL
PostgreSQL in Practice #7 Partitioning: Splitting Giant Tables by Time
Declarative partitioning, the standard prescription for tables in the hundreds of millions of rows. What partitioning actually solves (deleting old data becomes a single DROP, partition pruning, spreading the VACUUM burden) and what it does not, the RANGE syntax for monthly tables, the constraint that the partition key must be part of the primary key, what query conditions enable pruning, the operational duty of creating future partitions and pg_partman, and the criteria for when to adopt it.
PostgreSQL in Practice #6 Locks and Concurrency: Deadlocks, DDL Locks, SKIP LOCKED
The territory MVCC does not solve: locking. The two layers of row locks (write-vs-write conflicts) and table locks (DDL), tracing lock waits with pg_locks and pg_blocking_pids, the structure that produces deadlocks (cross-order updates) and the prevention rule (always lock in the same order), making read-decide-write safe with SELECT FOR UPDATE, and the standard work-queue pattern FOR UPDATE SKIP LOCKED.
PostgreSQL in Practice #5 VACUUM and autovacuum: How Dead-Tuple Cleanup Works, and Tuning It
VACUUM sits at the center of PostgreSQL operational knowledge. The dead tuples MVCC leaves behind and VACUUM's three jobs (marking space reusable, refreshing the visibility map, preventing transaction ID wraparound), why autovacuum is usually enough and where its default thresholds fail (bigger tables get cleaned later), per-table scale factor tuning, the long-running transactions that block cleanup, bloat inspection queries, and why VACUUM FULL is dangerous.
PostgreSQL in Practice #4 Advanced Index Strategy: Partial, Composite, Covering — and GIN, BRIN
Building strategy on top of basics chapter 4's B-tree fundamentals: partial indexes that bake a WHERE condition into the index, covering indexes (INCLUDE) and the conditions for an Index Only Scan, choosing beyond B-tree (GIN, GiST, BRIN), finding unused indexes with pg_stat_user_indexes and cleaning them up, and index bloat with REINDEX CONCURRENTLY.
PostgreSQL in Practice #3 The Performance Diagnostics Routine: Finding Slow Queries with pg_stat_statements
A standard routine from "the database is slow" to the culprit query. pg_stat_activity for what is happening right now (active queries, waits, long-running transactions), installing pg_stat_statements and its key columns (total_exec_time, mean_exec_time, calls) for finding expensive queries in cumulative statistics, why top-by-total and top-by-mean point at different culprits, configuring the slow query log (log_min_duration_statement), and the handoff to EXPLAIN.
PostgreSQL in Practice #2 Connection Pooling: The max_connections Misconception and PgBouncer
The connection problem every growing service hits. Why a PostgreSQL connection is expensive (each is a process), why raising max_connections is not the answer (too many connections actually lower throughput), the division of labor between application pools and external poolers like PgBouncer, what PgBouncer transaction mode buys and what it forbids (session-state features), and a working sense of pool sizing.
PostgreSQL in Practice #1 Schema Migrations: Changing Tables Without Stopping the Service
The practice series opens with the craft of changing tables that are live in production. Why migrations must be managed as code (ordering and reproducibility), PostgreSQL's strength of transactional DDL, telling dangerous changes from safe ones by lock strength and duration, splitting NOT NULL into stages, CREATE INDEX CONCURRENTLY, and solving column renames and type changes with the expand-contract strategy.
PostgreSQL Basics #9 Roles, Permissions, Backup Fundamentals: The Minimum Discipline of Operations
Closing out the basics with the minimum operational discipline. The PostgreSQL permission model that unifies users and groups into a single role concept, the practical sequence for creating a least-privilege account instead of handing the application a superuser (GRANT and default privileges), the public schema caveat, logical backups with pg_dump and pg_restore and why restore rehearsals matter, plus a retrospective of the nine basics and a preview of the practice series.
PostgreSQL Basics #8 Views, CTEs, Window Functions: Keeping Complex Queries Readable
Three tools for structuring queries that have outgrown one screen: views that give a repeated query a name and materialized views as their physical-copy sibling, WITH clauses (CTEs) that let a query read top to bottom, the basic anatomy of window functions (OVER, PARTITION BY) and the canonical ROW_NUMBER pattern for top-N per group, how window functions differ from aggregates (rows are not collapsed), and LAG for month-over-month comparisons.
PostgreSQL Basics #7 JSONB: Schema Flexibility Inside a Relational Database
JSONB is what let PostgreSQL reach into NoSQL territory. The difference between json and jsonb (and why jsonb is the default), the extraction operators (->, ->>, #>>) and the existence and containment operators (?, @>), the GIN index that backs JSONB search, the nature of updates (jsonb_set), and most importantly the design boundary of what stays a column and what goes into JSONB.
PostgreSQL Basics #6 Transactions and MVCC: The Fundamentals of Concurrency
How PostgreSQL preserves consistency when many connections touch the same data at once. The basics of BEGIN, COMMIT, and ROLLBACK and atomicity, the core of MVCC (multi-version concurrency control) — readers never block writers — the fact that UPDATE actually creates a new row version and the dead tuples that foreshadows, what the default Read Committed isolation level means and its trap, and when to raise to Repeatable Read or Serializable.
PostgreSQL Basics #5 Reading EXPLAIN: Diagnosing Queries with Execution Plans
How to confirm why a query is slow with the execution plan instead of guesswork. The difference between EXPLAIN and EXPLAIN ANALYZE (the latter actually executes), reading the plan tree from the innermost node outward, what Seq Scan, Index Scan, Index Only Scan, and Bitmap Scan mean, comparing cost and actual time and estimated vs actual rows, the remedy when estimates are badly off (ANALYZE), and the habit of checking read volume with BUFFERS.