PostgreSQL performance tuning
You measure bottlenecks with pg_stat_statements, auto_explain and pgbench, tune memory, checkpoint, autovacuum and parallel query settings for the workload and improve plans.
Level 401 · 18 lessons · 15 practice labs
Lessons
Measuring
- A baseline with pgbench
- The most expensive queries with pg_stat_statements
- Catching slow plans with auto_explain
- Wait events
- Reading EXPLAIN (ANALYZE, BUFFERS)
- Short task: find and explain the five costliest queries
Server settings
- shared_buffers, work_mem and effective_cache_size
- Checkpoint and WAL settings
- Tuning autovacuum and per-table settings
- Parallel query
- Planner costs and the statistics target
- Short task: tuning for a write-heavy workload
Plans and schema
- Extended statistics
- Index-only scans and the visibility map
- BRIN and bloom indexes
- Partition pruning
- Lock contention and long transactions
- Capstone: the database that slows down on campaign day
Practice labs
- List the five costliest queries: Return the five costliest queries by total time from pg_stat_statements with call count and mean.
- Capture slow plans automatically: Log plans of queries over two seconds with auto_explain and interpret one of them.
- Read the bottleneck from wait events: Sample wait events from pg_stat_activity under load and report what the server is waiting for.
- Find disk reads in BUFFERS output: Compare blocks read and hit in cache on first and second runs with EXPLAIN (ANALYZE, BUFFERS).
- Keep a spilling sort in memory: Find a sort spilling to disk in a plan and fit it in memory with a session-level work_mem.
- Fix a join with a bad estimate: Fix a query whose row estimate collapses because of two correlated columns using extended statistics.
- Calculate memory settings for a server: Choose shared_buffers, effective_cache_size and work_mem for a 16 GB server with reasons.
- Clear the frequent checkpoint warning: Clear the too-frequent checkpoint warning in the log with max_wal_size and measure write spikes.
- Tune for a write-heavy workload: Change WAL, checkpoint and commit settings for Nehir Enerji's meter-data load and prove the gain with pgbench.
- Watch dead rows pile up: Track the dead row count and last autovacuum time of a heavily updated table from statistics views.
- Give one table its own autovacuum settings: Fix late vacuuming of a big queue table with table-level threshold and scale settings.
- Prove a long transaction blocks cleanup: Show how an open report transaction makes VACUUM useless and prevent it with a timeout.
- Add BRIN to a time-ordered table: Compare B-tree and BRIN indexes on a log table for size and query time.
- Get an index-only scan: Move a query that goes back to the table because the visibility map is stale to an index-only scan with VACUUM.
- Bring parallel query into play: Find why a large aggregate query does not run in parallel, change the settings and measure the effect of worker count.
About this course
You measure bottlenecks with pg_stat_statements, auto_explain and pgbench, tune memory, checkpoint, autovacuum and parallel query settings for the workload and improve plans.
What you will learn
- Measuring
- Server settings
- Plans and schema
Prerequisites
You should have practised the topics of the same path up to level 301.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 401; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.