PostgreSQL Advanced Optimization and Performance Tuning

Level:

Advanced

Date:

04.04 - 06.04.18

Duration:

3 Days

Sign up for this course

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.

Content

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

Indexing

  • 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
  • ANALYZE
  • Reading pg_stats

Internal optimization

  • Views and subselects
  • Equality constraints
  • Optimization of aggregates
  • HashAggregates vs. GroupAggregates
  • DISTINCT vs. GROUP BY
  • 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

Partitioning

  • Efficient cleanup
  • Constraint exclusion
Sign up for this course