Move beyond the basics and learn how to design, optimize, and operate PostgreSQL systems in real-world scenarios
🔹 Advanced SQL
Master complex querying techniques for analytical and high-performance workloads.
- Recursive CTEs
- Window functions
- Advanced aggregations
- Set operations
- Query patterns and optimization
🔹 Data Modeling
Design efficient and scalable database schemas.
- Advanced normalization
- Denormalization strategies
- Complex constraints
- Schema design trade-offs
- Many-to-many relationships
🔹 Performance Foundations
Understand how PostgreSQL executes and optimizes queries.
- EXPLAIN / EXPLAIN ANALYZE
- Query execution plans
- Statistics basics
- Index types and usage
- Query tuning fundamentals
🔹 Concurrency & Transactions
Learn how PostgreSQL handles multiple users and data consistency.
- Isolation levels
- MVCC (Multi-Version Concurrency Control)
- Locks and locking behavior
- Deadlocks and prevention
Resources:
Tutorials and Tools:
Henrietta Dombrovskaya, postgres_air example database https://github.com/hettie-d/postgres_air
🔹 Application Engineering
Build and optimize PostgreSQL-backed applications.
- Prepared statements
- Connection pooling
- ORM integration
- Database migrations
- JSON / JSONB usage
- PL/pgSQL basics
- Triggers
🔹 Operations
Understand how PostgreSQL runs and how to maintain it.
- VACUUM and autovacuum
- ANALYZE and statistics
- WAL (Write-Ahead Logging) basics
- Monitoring fundamentals
- Logging and observability
🔹 Cloud & Managed PostgreSQL
Work with PostgreSQL in cloud environments.
- Managed backups
- Point-in-Time Recovery (PITR)
- Read replicas
- High Availability (HA) basics
Resources:
Books:
- Laine Campbell and Charity Majors, Database Reliability Engineering
- Henrietta Dombrovskaya, Boris Novikov, and Anna Bailliekova, PostgreSQL Query Optimization
- Martin Kleppmann, Designing Data-Intensive Applications
- Markus Winand, Use the Index, Luke! https://use-the-index-luke.com
Videos and Podcasts:
Scaling Postgres https://www.scalingpostgres.com
🔹 Deeper Technical Topics
Prepare for advanced PostgreSQL concepts by understanding how the database works internally and how it scales.
- Parallel queries (how PostgreSQL uses multiple workers)
- Query planner behavior and optimization basics
- Extension lifecycle (installing and managing extensions)
- FDW (Foreign Data Wrapper) basics
- Full-text search fundamentals
- Upgrade awareness and version changes
Resources:
Books:
Tutorials:
Videos: