PostgreSQL in Practice: Indexes and Performance Tuning
PostgreSQL in Practice (indexes, performance) is an advanced-level course of 5 lessons (about 3 h 36 min), with 21 exercises and 15 quiz questions. Prerequisites: Database Modeling. The course needs a full project environment, planned for a later phase of the platform.
- SQL
- Advanced
- 5 lessons
- 3 h 36 min
- 21 exercises
- Full project environment (phase 2)
The course in brief
This advanced course teaches you how to make a PostgreSQL database fast and reliable. You will use PostgreSQL-specific types and JSONB, read execution plans with EXPLAIN ANALYZE, design B-tree, partial, GIN, trigram or BRIN indexes, then optimize application queries and database upkeep. The exercises combine executable queries and hands-on work on a PostgreSQL instance.
What you will be able to do
- Choose the right PostgreSQL types and work with JSONB data
- Write atomic upserts with ON CONFLICT and RETURNING
- Read an EXPLAIN ANALYZE plan and diagnose a slow query
- Design composite B-tree indexes and sargable conditions
- Choose between a partial index, expression index, GIN, pg_trgm, BRIN and GiST
- Implement keyset pagination, eliminate N+1 queries and monitor VACUUM and pg_stat_statements
Syllabus: 5 lessons
- Choose the right PostgreSQL types (identity, numeric, text, timestamptz, uuid, jsonb)
- Query a JSONB column with ->, ->> and @>
- Write an upsert with INSERT ... ON CONFLICT and retrieve rows with RETURNING
- Find your way around psql
- 02
Reading an Execution Plan with EXPLAIN ANALYZE
- Understand the role of the planner and of statistics
- Read a plan: nodes, estimated costs, estimated and actual rows, loops
- Recognize the main scan and join nodes
- Use EXPLAIN ANALYZE safely on write queries
EXPLAINEXPLAIN ANALYZE and BUFFERSPlan nodes (Seq Scan, Index Scan, Hash Join...)Planner statistics
- 03
B-tree Indexes: Creating, Composing, Making Them Usable
- Understand how a B-tree index works and what it costs
- Choose the column order of a composite index
- Write conditions an index can use (sargable)
- Use covering indexes and non-blocking index creation
B-tree indexComposite indexSargable conditionsCovering index (INCLUDE)
- 04
Specialized Indexes: Partial, Expression, GIN, BRIN
- Create partial indexes and expression indexes
- Choose a GIN index for JSONB, arrays and full-text search
- Speed up LIKE '%...%' searches with pg_trgm
- Use BRIN for very large time-ordered tables
- 05
Optimizing Queries and Maintaining the Database
- Replace OFFSET pagination with cursor (keyset) pagination
- Eliminate the N+1 query problem
- Understand MVCC, VACUUM, ANALYZE and autovacuum
- Find the most expensive queries with pg_stat_statements
Keyset paginationN+1 problemMVCC, VACUUM and autovacuumpg_stat_statements
Final project
Performance audit of a PostgreSQL application database
You receive the schema of an online ordering application (users, products with JSONB attributes, orders, order lines, event log) and a script that fills it with several million rows. A list of ten queries from the application is judged too slow. Carry out the full audit: measurement, diagnosis, optimization and maintenance recommendations.
Deliverables
- A setup script (schema, data generated with generate_series, ANALYZE)
- For each query: the EXPLAIN (ANALYZE, BUFFERS) plan before optimization and the diagnosis
- The indexes created (composite B-tree, partial, expression, GIN or trigram, BRIN as appropriate) with the justification for each
- The rewrites applied (sargable conditions, keyset pagination, removal of an N+1)
- The plan and timing after optimization, summarized in a before / after table
- An operations note: proposed autovacuum settings, a pg_stat_statements monitoring query, a list of indexes to watch
Assessment criteria
- Each optimization is justified by a plan and measured before / after
- The type and column order of each index suit the queries
- No superfluous index: the write cost is taken into account
- Rewrites preserve exactly the same result
- Maintenance recommendations are realistic for a production database (no VACUUM FULL or long lock without justification)
Frequently asked questions
Which PostgreSQL version does this course use?
The course is based on PostgreSQL 18. The concepts presented also apply to recent earlier versions; features specific to a version are flagged.
How can I tell whether a PostgreSQL query uses an index?
With EXPLAIN ANALYZE: the plan shows an Index Scan, Index Only Scan or Bitmap Index Scan node that names the index used. A Seq Scan indicates a full table scan.
Should I create an index on every column?
No. Every index slows down writes and takes up space. You index the columns used in frequent filters, joins and sorts, starting with foreign keys, and then you check actual index usage.
What are the prerequisites for this course?
You need to be comfortable with SQL queries (joins, subqueries) and the basics of modeling: primary and foreign keys, constraints and normalization.
This course, told for your job
Say what you want to be able to do: “PostgreSQL in Practice (indexes, performance)” takes its place in your path, with examples from your field. The concepts stay those of the course.