Faysal AzizEnquire
FA-0353Data & Analytics

PostgreSQL Performance Engineering

Advanced planner insight, query tuning, and time-series architecture for high-performance PostgreSQL systems - 2 days

Introduction

Why this course

This advanced two-day hands-on course is designed for experienced SQL and PostgreSQL professionals who want to go beyond writing correct queries and learn how to make them fast, predictable, and scalable. The course focuses heavily on PostgreSQL optimization, especially planner behavior, execution internals, indexing strategy, statistics, and performance tuning techniques used in real production systems.

It also covers TimescaleDB from both an implementation and architecture perspective, including hypertable-based design and the role of columnar storage for analytical workloads. The instructor brings over 30 years of industry experience and teaches using practical, industry-demanded content rather than an academic approach.

Learning outcomes

Learning outcomes

  • Read and interpret advanced PostgreSQL execution plans confidently
  • Identify root causes of poor query performance
  • Understand planner behavior, costing, and row estimation issues
  • Tune queries using indexing, statistics, SQL rewrites, and configuration
  • Distinguish key plan nodes and know when each is beneficial or problematic
  • Design PostgreSQL databases with TimescaleDB implementation in mind
  • Use TimescaleDB hypertables, compression, and columnstore appropriately
  • Make better architecture decisions for high-ingest and analytical workloads
Prerequisites

Prerequisites

  • Strong SQL expertise
  • Hands-on PostgreSQL experience
  • Familiarity with indexes, joins, vacuum, analyze, and partitioning
  • Comfortable reading basic EXPLAIN plans
Training outline

12 modules

·
01PostgreSQL performance tuning mindset3 topics
  1. Performance goals and bottleneck identification
  2. Query-level tuning versus workload-level tuning
  3. Benchmarking and tuning workflow
02PostgreSQL internals that affect performance2 topics
  1. MVCC, heap storage, visibility, and tuple lifecycle
  2. WAL, checkpoints, autovacuum, and bloat
03Planner and executor deep dive6 topics
  1. Parse, rewrite, plan, execute
  2. Cost-based optimization fundamentals
  3. Planning time versus execution time
  4. Expected row count versus actual row count
  5. Filtered rows and rows removed by filter
  6. Join order and join strategy selection
04Reading execution plans effectively4 topics
  1. Cost, rows, width, loops, actual time
  2. Buffers, I/O, timing, and memory clues
  3. Finding the first bad estimate
  4. Diagnosing plan instability and regression
05Understanding critical plan nodes7 topics
  1. Sequential Scan
  2. Index Scan and Index Only Scan
  3. Bitmap Index Scan and Bitmap Heap Scan
  4. Nested Loop, Hash Join, and Merge Join
  5. Sort, Hash, Aggregate, and Materialize
  6. Gather, parallel nodes, and Memoize
  7. Filter versus Join Filter
06Advanced indexing strategy5 topics
  1. Composite indexes and column order
  2. Partial indexes
  3. Expression indexes
  4. BRIN for large time-oriented datasets
  5. Index bloat, redundancy, and maintenance
07SQL tuning patterns5 topics
  1. SARGability and predicate design
  2. Join rewrites and pre-aggregation
  3. Window function performance
  4. CTE and subquery performance considerations
  5. Pagination and top-N query strategies
08Memory, parallelism, and execution behavior3 topics
  1. Hash and sort memory usage
  2. Shared buffers and cache effects
  3. Parallel query behavior
09PostgreSQL architecture for performance4 topics
  1. Scale-up versus scale-out decisions
  2. Connection management and pooling
  3. Read replicas and workload separation
  4. Partitioning strategy and pruning
10Database design assuming TimescaleDB7 topics
  1. Designing for hypertables from the start
  2. Time column and partitioning strategy
  3. Chunk interval design
  4. Indexing hypertables effectively
  5. High-cardinality dimension design
  6. Retention-aware schema design
  7. Designing for continuous aggregates
11TimescaleDB optimization6 topics
  1. Hypertables and chunk pruning
  2. Ingestion performance patterns
  3. Compression and columnstore usage
  4. Continuous aggregates and refresh strategy
  5. Retention policies and lifecycle management
  6. Query patterns for recent versus historical data
12Columnar capabilities and hybrid storage5 topics
  1. PostgreSQL row-oriented fundamentals
  2. TimescaleDB columnstore for analytical workloads
  3. When columnstore helps
  4. When rowstore remains the better choice
  5. Designing hybrid hot-and-cold data strategies

A programme built around your team.

Share your training goals and requirements.

Training enquiry

PostgreSQL Performance Engineering
FA-0353

Share your requirements for this programme.

Training enquiry

PostgreSQL Performance Engineering