Summary

# PostgreSQL summary

You can now:

- Design schemas with the right types (text, numeric, jsonb, arrays, enums)
- Write joins, CTEs, window functions, and lateral joins
- Read EXPLAIN ANALYZE and reason about index choices
- Build B-tree, GIN, BRIN, and partial indexes deliberately
- Use transactions with the right isolation level
- Stream changes with logical replication
- Tune autovacuum and watch for bloat
- Back up with pg_dump and PITR

# Your next step - partial + expression indexes + EXPLAIN

-- Partial index: only the active users (much smaller than full table)
CREATE INDEX users_active_by_email
  ON users (lower(email))
  WHERE deleted_at IS NULL;

-- GIN index for full-text search over a tsvector
CREATE INDEX articles_fts
  ON articles
  USING GIN (to_tsvector('english', title || ' ' || body));

-- Verify the planner uses the partial index
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, name
FROM users
WHERE lower(email) = lower('Ada@Example.com')
  AND deleted_at IS NULL;

-- Look for:
--   Index Scan using users_active_by_email
-- not:
--   Seq Scan on users  (this would mean the planner ignored the index)

# Reading the plan

- Index Scan + low rows + low buffers = good
- Seq Scan on a big table = bad; add an index or rewrite the query
- Bitmap Heap Scan = the planner combined several indexes
- 'Rows Removed by Filter' high = the index did not narrow as much as you thought

-- Next: pg_stat_statements, logical replication, pgvector for embeddings.