PostgreSQL Professional




5 days

Dates & Duration

See available dates for this course!
If there is no date and time available for a certain training you are interested in, please contact us.

See dates  >>


This course takes 5 days with 8 hours each (on site) or 4 hours each (online).
The only difference is that there will be less practical exercises in an online course. However, we will hand them over to you and you can still do them on your own and ask our consultants for feedback or help if needed.


This course provides a deep insight into advanced PostgreSQL topics like indexing, storage parameters, optimization, replication, monitoring, etc. It covers the technical foundations required for replication, scaling, point-in-time recovery, and the successful operation of synchronous and asynchronous replication solutions. Moreover, details of processing that are essential for optimization and performance tuning are highlighted, so that you can get the most out of your system and queries.

Available Languages

This course can be held in English, German, Spanish, Estonian, Polish, Ukrainian and Russian.


PostgreSQL backups

  • Backups using pg_dump
  • Recovery using pg_restore

The PostgreSQL I/O system

  • I/O cache (shared buffers)
  • Storage files and tablespaces
  • The PostgreSQL transaction log (WAL)
  • The background writer
  • Checkpoints

Point-In-Time recovery

  • Archiving the transaction log
  • Replaying transaction log

Asynchronous replication

  • Primary/ standby replication
  • Transaction log streaming / streaming replication

Synchronous replication

  • Building a synchronous cluster
  • Performance optimization

Complex setups

  • Combining synchronous and asynchronous replication

External tools

  • repmgr
  • skytools

Logical replication

  • londiste replication
  • Upgrading PostgreSQL with londiste


  • Concepts


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



Contact us to sign up for this course >>