My first query

Create a small table, seed two rows, SELECT them back, and inspect the plan Postgres uses.

SELECT · EXPLAIN

Introduction to JOIN

See why splitting data across tables matters, and use JOIN to combine it back together.

JOIN

Schemas, tables, and types

Explore schemas, list tables, inspect columns, and understand database shape.

SCHEMA · TABLES · TYPES

An introduction to indexes

Indexes and EXPLAIN plans, comparing a sequential scan with an index scan on the same query.

INDEX · EXPLAIN

Insert, update, delete, upsert

Change stored rows with INSERT, UPDATE, DELETE, RETURNING, and ON CONFLICT upserts.

INSERT · UPDATE · DELETE · UPSERT

MVCC and row versions

Read the hidden xmin/xmax columns PostgreSQL keeps on every row, and watch them change across a real BEGIN ... COMMIT.

MVCC · XMIN · XMAX

Constraints

Use NOT NULL, UNIQUE, CHECK, primary keys, and foreign keys to protect data.

CONSTRAINTS · KEYS

Advanced indexes

Use selectivity, composite indexes, and partial indexes to shape better plans.

SELECTIVITY · COMPOSITE · PARTIAL

Aggregation intro

Summarize rows with COUNT, SUM, AVG, MIN, MAX, and GROUP BY.

COUNT · GROUP BY

NULL

Understand missing values, IS NULL, three-valued logic, and COALESCE.

NULL · COALESCE

Types of JOINs

Compare INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN on the same two tables.

JOIN · LEFT · RIGHT · FULL · CROSS

Relationships

Model one-to-many and many-to-many data with foreign keys and join tables.

FOREIGN KEY · RELATIONSHIPS

Sorting and pagination

Sort rows deterministically with ORDER BY, LIMIT, OFFSET, and indexes.

ORDER BY · LIMIT

Text search basics

Search text with LIKE, ILIKE, wildcards, and case-insensitive patterns.

LIKE · ILIKE

JSON in PostgreSQL

Store jsonb, extract fields, query nested values, and use containment.

JSONB

Transactions

Group changes with BEGIN, COMMIT, and ROLLBACK so work is atomic.

BEGIN · COMMIT · ROLLBACK

Advanced aggregation

Use HAVING, FILTER, date_trunc, and time-based grouping.

HAVING · FILTER · DATE_TRUNC

Vectors and similarity search

Search by meaning: embeddings, L2 distance, cosine similarity, and nearest neighbours, computed by hand with plain arrays.

EMBEDDINGS · DISTANCE · SIMILARITY

pgvector

The vectors lesson again, with pgvector: a real vector type, distance operators, and four ways to search many rows: exact, HNSW, IVFFlat, and smaller vectors.

PGVECTOR · HNSW · IVFFLAT · HALFVEC

Full-text search

Search words instead of characters with tsvector and tsquery: stemming, search-box syntax, ranking, highlights, and a GIN index.

TSVECTOR · TSQUERY · GIN

Fuzzy search with pg_trgm

Find text despite typos with trigram similarity, word_similarity, 'did you mean' suggestions, and trigram indexes.

PG_TRGM · SIMILARITY

Text search in Postgres, without Elasticsearch or Algolia

Compare ILIKE, fuzzy, full-text, semantic, and hybrid search on the same documents, and decide when Postgres is enough.

ILIKE · PG_TRGM · TSVECTOR · PGVECTOR

Implementing a high-concurrency system with Postgres

Model a seat reservation system and use transactions with advisory locks to close a real race condition.

ACID · TRANSACTIONS · ADVISORY LOCKS

Transaction isolation levels

See what READ COMMITTED, REPEATABLE READ, and SERIALIZABLE actually let concurrent transactions see, with a bug only SERIALIZABLE catches.

ISOLATION LEVELS · WRITE SKEW

Locks

See which locks every statement takes, what blocks what, and how NOWAIT, SKIP LOCKED, and lock_timeout keep sessions from waiting forever.

TABLE LOCKS · ROW LOCKS · SKIP LOCKED

Finding and fixing slow queries

Profile an app's queries with pg_stat_statements, find why the slowest are slow with EXPLAIN ANALYZE, and fix them with a trigram index and keyset pagination.

PG_STAT_STATEMENTS · EXPLAIN ANALYZE · PG_TRGM

Building a job queue with Postgres

Build a job queue on a plain table: hand each job to one of many workers with SKIP LOCKED, survive crashes with leases and retries, and see why exactly-once delivery is impossible but exactly-once processing isn't.

SKIP LOCKED · DELIVERY GUARANTEES · OUTBOX