The Postgres-focused SQLAlchemy 2.0 textbook. For DB-agnostic patterns, see the companion volume .
Pairs with FastAPI , Pydantic v2 , and Alembic .
Postgres-focused SQLAlchemy: asyncpg vs psycopg v3, connect_args, server settings, dialect specifics, and choosing your driver.
Postgres types in SQLAlchemy: JSONB queries, ARRAY operations, range types, citext, intervals, INET — with the dialect helpers.
Postgres index types via SQLAlchemy: B-tree, GIN, GiST, BRIN, HASH, partial, expression, INCLUDE indexes, and concurrent creation.
Practical full-text search in Postgres via SQLAlchemy: tsvector, GIN, websearch_to_tsquery, ranking, and stemming.
Postgres locking patterns: SELECT FOR UPDATE, NOWAIT, SKIP LOCKED, advisory locks, application locks, deadlock retry.
LISTEN/NOTIFY patterns: real-time event delivery, queue wake-up, payload limits, and combining with the outbox pattern.
Postgres declarative partitioning via SQLAlchemy: range/list/hash partitions, pg_partman automation, indexes per partition.
Postgres RLS for multi-tenant SQLAlchemy: defining policies, setting tenant context per request, and integration with FastAPI.
Postgres-specific bulk operations: ON CONFLICT, RETURNING, bulk insert via execute, asyncpg COPY for huge data.
Streaming replication, read replica routing, failover handling in SQLAlchemy, and using pgvector for embeddings.