PostgreSQL Tutorial with Examples.

EXPLAIN vs EXPLAIN ANALYZE

EXPLAIN shows what the planner guesses a query will cost; EXPLAIN ANALYZE actually runs it and shows what really happened. Confusing the two means trusting an estimate that may be wrong.

Indexing Strategy: When an Index Actually Helps

Indexing a low-selectivity column barely moves the needle — 13ms to 13ms — while indexing a high-selectivity one drops a query from 14ms to 1ms. Selectivity, not the presence of an index, is what determines the payoff.

JSONB Containment Queries Need a GIN Index, Not a B-tree

A B-tree index on a jsonb column compiles, exists, and does absolutely nothing for a containment query — the operator has to match the index type, not just the column.

VACUUM Doesn't Shrink Tables — VACUUM FULL Does, at a Cost

Deleting 80% of a table's rows doesn't shrink it by a single byte, and neither does a plain VACUUM. Only VACUUM FULL actually returns space to the OS — by rewriting the whole table under an exclusive lock.

A Real Deadlock, and Why Lock Order Fixed It

One error inside a transaction poisons every statement after it until ROLLBACK — and two transactions locking the same two rows in opposite order produces a real 'deadlock detected' error, not a hang.

The WAL Durability Tradeoff: 0.30s vs 0.02s for the Same 200 Inserts

Every committed write has to hit durable storage before COMMIT returns — turning that guarantee off doesn't change what gets written, only when Postgres waits for it, and the difference is measured, not asserted.

Advanced

Production-grade patterns for experienced engineers

1 lesson

postgresql advanced

Point-in-Time Recovery: Undoing a Bad DELETE That Already Committed

A base backup plus archived WAL segments can restore a database to the exact second before a mistake — but only if the WAL covering that second was actually archived first, which is the real failure this lesson walks through.

Also part of PostgreSQL

Taught in other topics. Each link opens the lesson in its home topic.

7 lessons

sql intermediate

JSON in SQL

Query JSONB in PostgreSQL with real output: -> vs ->>, the text comparison trap, paths, jsonb_set, containment with @>, and GIN indexes.

Query Optimization

Read real PostgreSQL EXPLAIN ANALYZE plans, compare scan and join types, spot index-killing queries, and find slow ones with pg_stat_statements.

Functions, Procedures, and Triggers

Write PostgreSQL functions, procedures and triggers with real output: PL/pgSQL, RAISE and exceptions, RETURN QUERY, CALL, and an audit trigger.

Database Security

Secure PostgreSQL with real output: roles, GRANT and REVOKE, column privileges, the locked public schema, row-level security, and SQL injection.

Replication and High Availability

Build a real PostgreSQL primary, pg_basebackup standby and logical subscriber in Docker, then monitor lag, slots, PgBouncer pooling and failover.

sql intermediate

Views and Materialized Views

Use PostgreSQL views with real output: updatable views, WITH CHECK OPTION, stale materialized views, REFRESH CONCURRENTLY, and view privileges.

sql intermediate

Transactions

See PostgreSQL transactions on real output: BEGIN/COMMIT, aborted transactions, autocommit, savepoints, READ COMMITTED vs REPEATABLE READ, lock timeouts.