The training is addressed both to administrators and developers responsible for diagnosing and troubleshooting SQL query performance in PostgreSQL 16 environment.
During the training the participants will acquire knowledge:
- Query Planner functioning,
- interpreting and modifying query execution plans,
- the use of data indexing techniques,
- the use of temporary and partitioned tables,
- the use of materialized views,
- parallel SQL query execution,
- configuring buffer memory,
- generating extended statistics,
- detecting performance problems,
- performance tests automation.
Knowledge of SQL language, knowledge of PostgreSQL database server architecture.
- Training: English
- Materials: English
- 2 days of work with a trainer
- Trainer’s supervision
- Contact with community
- Coursebook
- Lab environment
Training method
- lecture
- workshops
- Introduction to Query Planner (functioning, configuring)
- Analyzing query execution plans
- Collecting and expanding statistics for Query Planner
- Index structures (B*-tree, hash, BRIN, GIN, GiST, partial, function-based)
- Advanced table structures (partitioned, unlogged, temporary, materialized views)
- Configuring buffer memory (buffer cache, work memory, maintenance work memory)
- Parallel queries (execution plans, configuration)
- Diagnosing performance problems (logs, pg_top, auto_explain, pg_stat_statements, cumulative statistics)
- Using pgBench to implement simple system performance tests
- Other techniques of performance tuning (prepare/execute, hints, sorting records physically, calculated columns, fillfactor)