COVID-19 READINESS PLAN
Leading Training is open for classroom based training as from the 10th of June. We have put strict safety measures in place, please see our COVID-19 READINESS PLAN
Those not comfortable attending classroom training are welcome to attend via webinar.
Details
This course provides an in-depth insight into advanced PostgreSQL topics such as indexing, storage parameters, optimization, replication, monitoring, etc. On the one hand, it deals with the technical foundation required for replication, scaling, point-in-time recovery and the successful operation of synchronous and asynchronous replication solutions. On the other hand, details of processing are examined, which are essential for optimization and performance tuning, so you can get the most out of your system and your queries.
Delivery Methods
| Delivery Method | Duration | Price (excl. VAT) | |
|---|---|---|---|
| E-learning | 3 days | R 0.00 | Book Now |
| Fulltime | 5 days | R 17.00 | |
| Webinar | 5 days | R 17.00 |
Discounts Available
Brochure:
Audience
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.
Pre-Requisites
- LINUX FOR POSTGRESQL DBAS
- INTRODUCTION TO SQL
Course Outline / Curriculum
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
High-availability
- 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
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
Schedule Dates and Booking
To apply for a booking, click on the relevant "Book Now" button below.
Note: places are only fully secured once payment has been made.
| Start Date | Branch | Status | |
|---|---|---|---|
| Mon 14 Sep 2020 | Not Applicable | Scheduled | |
| Mon 2 Nov 2020 | Not Applicable | Scheduled |