Index

Topical index — concepts and keywords mapped to the chapter(s) where they're covered. (Ch. = chapter; App. = appendix.) For definitions, see the Glossary.

A

  • ACID — Ch. 26
  • aggregate functions (COUNT/SUM/AVG/MIN/MAX) — Ch. 7; App. C
  • ALTER TABLE — Ch. 14, 22
  • ANALYZE / statistics — Ch. 24, 28, 38; App. G
  • anti-join (NOT EXISTS / LEFT JOIN…IS NULL / EXCEPT) — Ch. 6, 9, 10; App. I
  • arrays — Ch. 16; App. D
  • atomicity — Ch. 26
  • audit trail — Ch. 21
  • autovacuum — Ch. 28, 38

B

  • backups (pg_dump, pg_basebackup, PITR) — Ch. 38; App. A
  • BASE / eventual consistency — Ch. 33, 35
  • BCNF — Ch. 19; App. F
  • bloat — Ch. 28, 38
  • B-tree — Ch. 23; App. G
  • buffer pool (shared_buffers) — Ch. 28, 38
  • bulk loading (COPY) — Ch. 31; App. C, I

C

  • CAP theorem — Ch. 35
  • cardinality (1:1/1:N/M:N) — Ch. 17
  • CASE / COALESCE / NULLIF — Ch. 8; App. C, I
  • CHECK constraint — Ch. 3, 14
  • closure (relational) — Ch. 4
  • closure table (hierarchy) — Ch. 21
  • columnar storage — Ch. 28, 34
  • composite key — Ch. 3, 18
  • concurrency control — Ch. 27
  • connection pooling (PgBouncer) — Ch. 27, 29, 38
  • constraints — Ch. 3, 14
  • COPY / \copy — Ch. 31; App. C, H
  • correlated subquery — Ch. 9
  • CREATE TABLE — Ch. 14; App. C
  • CTE (WITH) / recursive CTE — Ch. 11; App. C, I

D

  • data types — Ch. 3, 14; App. D
  • data warehouse — Ch. 34
  • deadlock — Ch. 27
  • dead tuples — Ch. 28
  • DELETE — Ch. 13
  • denormalization — Ch. 20; App. F
  • dialect differences — App. J
  • dimension / fact tables — Ch. 34
  • DISTINCT — Ch. 5
  • distributed databases — Ch. 35
  • Docker (PostgreSQL) — Ch. 2; App. A
  • document databases (MongoDB) — Ch. 33
  • DROP — Ch. 14
  • durability / WAL — Ch. 26, 28

E

  • embeddings / vector search — Ch. 36
  • entity / ER modeling — Ch. 17
  • ETL / ELT — Ch. 31, 34
  • EXPLAIN / EXPLAIN ANALYZE — Ch. 24; App. G
  • EXISTS / NOT EXISTS — Ch. 9
  • expression index — Ch. 8, 23

F

  • fan-out (one-to-many over-counting) — Ch. 6, 7
  • foreign key / referential integrity — Ch. 3, 14
  • full-text search — Ch. 16, 36
  • functional dependency — Ch. 19; App. F
  • functions (built-in) — Ch. 8; App. C
  • functions (user-defined) / PL/pgSQL — Ch. 15

G

  • GIN / GiST / BRIN indexes — Ch. 16, 23, 36; App. G
  • grain (of a join / fact table) — Ch. 6, 7, 34
  • graph databases (Neo4j) — Ch. 33
  • GROUP BY / HAVING — Ch. 7; App. C

H

  • hierarchy models — Ch. 11, 21
  • high availability — Ch. 35, 38

I

  • index (creating, types, when to use) — Ch. 23; App. G
  • index-only / covering scan — Ch. 23, 24
  • inheritance mapping — Ch. 18
  • INSERT — Ch. 13
  • isolation levels — Ch. 26

J

  • joins (INNER/LEFT/RIGHT/FULL/CROSS/self) — Ch. 6; App. C, E
  • join algorithms (nested loop/hash/merge) — Ch. 24
  • JSONB — Ch. 16; App. D, I

K

  • key-value stores (Redis) — Ch. 33
  • keys (primary/foreign/candidate/surrogate/natural) — Ch. 3

L

  • least privilege — Ch. 32
  • LIKE / ILIKE — Ch. 5
  • LIMIT / OFFSET / keyset pagination — Ch. 5; App. I
  • locks — Ch. 27
  • lost update — Ch. 27

M

  • materialized views — Ch. 15, 20
  • migrations (schema) — Ch. 22
  • monitoring (pg_stat_*) — Ch. 38; App. H
  • multi-tenancy — Ch. 21, 32
  • MVCC — Ch. 26, 28

N

  • N+1 query problem — Ch. 30
  • NewSQL — Ch. 35
  • normalization (1NF–BCNF) — Ch. 19; App. F
  • NoSQL — Ch. 33
  • NULL / three-valued logic — Ch. 3, 5

O

  • OLTP vs OLAP — Ch. 34
  • optimistic concurrency — Ch. 27
  • ORDER BY — Ch. 5
  • ORM / SQLAlchemy — Ch. 30

P

  • pages / heap / TOAST — Ch. 28
  • parameterized queries — Ch. 29, 32
  • partitioning (range/list/hash) / pruning — Ch. 25
  • pgvector — Ch. 36
  • PITR — Ch. 28, 38
  • PostGIS / spatial — Ch. 36
  • privileges (GRANT/REVOKE) — Ch. 32
  • projection (π) — Ch. 4
  • psql — Ch. 2; App. A, H
  • psycopg2 / psycopg3 — Ch. 29

R

  • recursive queries — Ch. 11
  • relational algebra — Ch. 4; App. E
  • relational model — Ch. 3
  • replication — Ch. 28, 35
  • RETURNING — Ch. 13, 29
  • row-level security (RLS) — Ch. 32

S

  • savepoints — Ch. 26
  • SELECT / FROM / WHERE — Ch. 5; App. C
  • selection (σ) — Ch. 4
  • SELECT FOR UPDATE — Ch. 27
  • set operations (UNION/INTERSECT/EXCEPT) — Ch. 10; App. C
  • sharding — Ch. 35
  • slowly changing dimensions — Ch. 34
  • soft delete — Ch. 21
  • SQL injection — Ch. 29, 32
  • staging tables — Ch. 31
  • star schema / dimensional modeling — Ch. 34
  • subqueries — Ch. 9
  • surrogate vs natural keys — Ch. 3

T

  • tagging (M:N) — Ch. 17, 21
  • time-series databases (TimescaleDB) — Ch. 36
  • TRUNCATE — Ch. 13
  • transactions — Ch. 13, 26
  • triggers — Ch. 15
  • tuning / configuration — Ch. 38; App. G
  • tuple / relation / attribute — Ch. 3

U

  • UNION / UNION ALL — Ch. 10
  • UPDATE — Ch. 13
  • upsert (ON CONFLICT) — Ch. 13; App. I

V

  • VACUUM — Ch. 28, 38
  • vector databases — Ch. 36
  • views — Ch. 15

W

  • WAL (write-ahead log) — Ch. 28
  • window functions — Ch. 12; App. I
  • work_mem — Ch. 24, 38

See also the Glossary for definitions and each chapter's key-takeaways for summaries.