Schema design, query optimization, production operations
Day 1
Schema Design & SQL Mastery
- Normalization: 1NF through 3NF/BCNF — when to normalize and when to deliberately denormalize
- Primary keys: serial vs identity vs UUID — trade-offs
- Foreign keys, ON DELETE behaviors, deferred constraints
- Index types: B-tree, Hash, GiST, GIN — choosing the right one
- Partial and expression indexes
- EXPLAIN ANALYZE: reading query plans, identifying sequential scans
- Window functions: ROW_NUMBER, RANK, LAG, LEAD, PARTITION BY
- CTEs (Common Table Expressions) and recursive queries
- Full-text search: tsvector, tsquery, GIN index
- JSONB: querying, indexing, operators
- Transactions: isolation levels, MVCC, deadlock detection
Day 2
Performance, Pooling & Operations
- Autovacuum: bloat, dead tuples, tuning autovacuum parameters
- pg_stat_statements: identifying slow queries in production
- Index bloat and REINDEX CONCURRENTLY
- Connection pooling with PgBouncer: session vs transaction vs statement mode
- Connection limits and max_connections tuning
- Partitioning: range, list, hash — when it helps and when it hurts
- Logical replication: publication, subscription, streaming replication
- Backup strategies: pg_dump, pg_basebackup, WAL archiving
- Point-in-time recovery
- Monitoring: pg_stat_user_tables, pg_stat_activity, Prometheus postgres_exporter
What your team walks away with
Backend developers and DBAs who can design schemas that stay maintainable, write queries the optimizer can execute efficiently, and operate PostgreSQL in production.
- Design normalized schemas with correct index strategies for the query patterns they serve
- Write advanced SQL using window functions, CTEs, and JSONB operators
- Read EXPLAIN ANALYZE output and identify the cause of slow queries
- Configure PgBouncer for connection pooling and set up monitoring with postgres_exporter
Book the PostgreSQL training
Available as a focused 1-day query optimization workshop or a full 2-day course from schema design through production operations.
Get in touch