Chiara B.
Sample Version notes appreciated
Clear notes on what changed in PostgreSQL 18, such as buffers in EXPLAIN ANALYZE and skip scan.
SQL performance tuning: indexes, query plans and EXPLAIN
Find slow PostgreSQL queries, read EXPLAIN ANALYZE output, design the right indexes and rewrite queries for predictable performance
Created by Irina Sokolova
- Updated September 2026
- English
- Advanced
What you will learn
- Explain how the planner uses statistics to choose a plan
- Read EXPLAIN ANALYZE output and spot misestimates
- Find the most expensive queries with pg_stat_statements
- Design multicolumn, covering, partial and expression indexes
- Choose GIN, GiST or BRIN indexes for suitable data
- Rewrite queries to avoid N+1 patterns and slow pagination
- Diagnose vacuum, locking and connection problems
Course content
How PostgreSQL executes a query
Finding the queries that matter
- Article: pg_stat_statements 11 min
- Article: auto_explain and slow query logging 9 min
- Article: Benchmarking changes properly 9 min
Indexing strategies
- Article: Multicolumn indexes and column order 11 min
- Article: Covering indexes with INCLUDE 8 min
- Article: Partial and expression indexes 10 min
- Article: GIN, GiST and BRIN indexes 11 min
- Article: Index maintenance and bloat 9 min
Rewriting queries
- Article: Join algorithms 11 min
- Article: The N+1 query problem 9 min
- Article: Pagination that scales 9 min
- Article: Sargable predicates 8 min
- Article: CTEs, subqueries and materialisation 9 min
Beyond a single query
- Article: Vacuum and autovacuum 10 min
- Article: Locks and contention 10 min
- Article: Connection pooling 8 min
- Article: Configuration settings that matter 9 min
Putting it together
- Article: Tuning case study 12 min
- Article: A routine for ongoing performance 8 min
- Quiz: Final check 6 min
Requirements
- Comfortable writing SQL with joins and aggregates
- Basic understanding of B-tree indexes
- A local PostgreSQL 17 or 18 installation or Docker
Description
Most database performance problems come from a small number of queries. Finding them, understanding why they are slow and fixing them is a skill that pays off every week for anyone who builds or runs applications on a relational database. This course teaches that skill with PostgreSQL, using a realistic dataset large enough for performance to matter.
What you will learn
- How the PostgreSQL planner chooses a plan, and the role of statistics
- Reading EXPLAIN ANALYZE output, including buffers, row estimates and timings
- Sequential, index, index only and bitmap scans, and when each is chosen
- Finding expensive queries with pg_stat_statements and auto_explain
- Index design: column order, covering indexes, partial, expression, GIN, GiST and BRIN indexes
- Join algorithms, N+1 queries, keyset pagination and other rewrites
- Vacuum, locks, connection pooling and the configuration settings that matter most
Dataset and setup
You will work with an orders database containing millions of rows, generated by a provided script so you can reproduce every example locally. All examples run on PostgreSQL 17 and 18; where behaviour differs between versions, the lesson says so.
Who it is for
This is an advanced course for backend developers, database engineers and SREs who already write SQL comfortably and understand basic indexing. If you have ever added an index that did not help, or watched a query go from milliseconds to seconds as a table grew, this course explains why and what to do about it.
Who this course is for
- Backend developers responsible for database performance
- Database engineers and DBAs
- SREs investigating slow services
Instructor
Irina Sokolova
Database engineer teaching PostgreSQL, data modelling and query performance
- 4.3 instructor rating
- 21 reviews
- 6,990 students
- 3 courses
- Sample
I work as a database engineer, mostly with PostgreSQL. My job covers schema design, migrations, indexing, query tuning, backups and helping application developers understand why their queries are slow.
I teach databases as a practical engineering skill. We model real domains, write SQL by hand, read EXPLAIN output together and break things on purpose so you can see how constraints and transactions protect your data. I explain the theory, such as normal forms and isolation levels, only as far as it changes what you do.
All examples run on a local PostgreSQL install or a Docker container, and every course includes the scripts so you can reproduce each step.
Student reviews
Nikolai P.
Sample Finally read plans with confidence
The loops and estimates explanations changed how I look at EXPLAIN output. Found a 40 times misestimate in our main query the same day.
Grace E.
Sample Start with pg_stat_statements first
Starting from total time instead of the slowest single query reshaped our tuning work. Huge win from fixing two frequent queries.
Hassan R.
Sample Excellent indexing section
Covering and partial indexes explained with real plans. I would like even more on partitioning.
Kenta M.
Sample Locks lesson saved an outage
We identified a blocking migration pattern thanks to the locks and timeouts lesson.
Ben A.
Sample Dense in places
Very valuable, but some lessons pack a lot in. I rewatched the join algorithms lesson twice.
Julia N.
Sample Advanced and practical
Keyset pagination and N+1 lessons were directly applicable to our API. Dataset script was a nice touch.
Keep exploring
All Databases courses
More in Databases
$79.99
Enroll now