Training objectives
- Create databases
- Get used to the command line interface: psql
- Learn how to get data into and out of PostgreSQL
- Understand extensions in PostgreSQL
- Understand monitoring tools
- Learn how the Cost Based Optimizer optimizes the access to your data
- Know everything about execution plan operations (sequential scans, index access, sorts, etc.)
- Monitor the performance with standard PostgreSQL tools and extensions
- Learn the tuning of queries via features available to the developer and/or DBA
- Understand the PostgreSQL index types
Content
- PostgreSQL architecture: processes, cluster, instance, database
- Optimizer statistics
- Vacuum
- MVCC: Multiversion concurrency control
- ACID
- psql, the flexible command line interface
- Extensions: pl/pgsql, pl/perl, pl/python
- Memory Sizing: shared_buffers, work_mem, …
- CBO, cardinality, cost, optimization, execution plan, parameters
- Statistics
- Sequential scans and indexing
- Sorts and groups
- Join methods
- pg_stat_statements, pg_buffercache
- Connection handling
- Transaction isolation levels
- Performance relevant logging parameters
Method
- Presentation with many practical exercises
- Case studies, live demos and discussions
- Networking during coffee breaks, lunch, and apéro on the last day (included in price, except for on-site/online workshops)
- Graphical tool courtesy of DB Visualizer
Target group
- Developers and DBAs using PostgreSQL as a service, no matter if in a public or private Cloud
Language & documentation
- Workshop held in English or German
- Documentation: content details, scripts, case study solutions as PDF file (in English)