PostgreSQL Advanced Optimierung and Performance Tuning

PostgreSQL Advanced Optimierung and Performance Tuning

Duration: 3 Days
Level: Professional
Audience: This training course has been designed for people who want to gain in-depth knowledge about PostgreSQL. We will deal with advanced topics related to performance tuning and optimization
Knowledge of SQL is necessary to participate in this training.
Date: English on request
28.09. – 30.09.2015 in Wr. Neustadt, Austria (German)
11.04. – 13.04.2016 in Wr. Neustadt, Austria (German)



PostgreSQL storage

  • On-disk storage
  • Heaps and disk layout
  • xlog: Functionality and layout
  • clog: The PostgreSQL commit log
  • Additional storage areas
  • Persistence
  • Tablespaces
  • Optimizing read and write access

Memory management

  • Caching
  • 2Q clocksweep vs LRU
  • Practical implications of caching
  • Sorting and aggregation
  • Temporary tables
  • Memory contexts


  • PostgreSQL index types
  • Internal index organization
  • Partial indexes
  • Functional indexing
  • Full text search
  • Fuzzy matching

Execution plans

  • Types of SQL optimization
  • Exhaustive search
  • Genetic optimization
  • Rule based optimization
  • Using EXPLAIN
  • Interpretation of EXPLAIN output
  • The PostgreSQL cost model
  • Reading pg_stats

Internal optimization

  • Views and subselects
  • Equality constraints
  • Optimization of aggregates
  • HashAggregates vs. GroupAggregates
  • Optimization of IN-statements
  • Optimization of ORDER BY-statements
  • Join order and outer joins
  • Optimizing set-operations
  • Optimizing procedures and SQL functions

Genetic optimization

  • The limits of optimization
  • GEQO

Transactions and locking

  • Transaction isolation
  • Optimizing locking


  • Efficient cleanup
  • Constraint exclusion