Skip to main content
Get a Quote
Course Enquiry
Contact Us
Cybertec PostgreSQL Professional

RELATED COURSES


TRAINERS FOR COURSE


Details

If you'd like to take your PostgreSQL skills further, this is the course for you. This internationally available course will cover the more advanced steps in optimising and managing your database.

This course provides an in-depth insight into advanced PostgreSQL topics such as indexing, storage parameters, optimization, replication, monitoring, etc.

It deals with the technical foundation required for replication, scaling, point-in-time recovery and the successful operation of synchronous and asynchronous replication solutions.

The 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)
Fulltime 5 days R 34,000.00
Webinar 5 days R 34,000.00

Discounts Available

Brochure:

Download Brochure


Information may change without notice.

Audience

Database administrators

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

There are currently no scheduled dates.

Please note that this course needs a minimum of 6 delegates to schedule a course. You can choose to be added to the waiting list by clicking the button below and we will contact you when we have enough delegates interested. Should we not get enough delegates, we will refund or credit your paid booking.

Add me to the waiting list

Should you need this course urgently, the following options are available:

  1. Pay for 6 delegates (whether you have them or not) and we will schedule the course as soon as possible.
  2. If you have fewer delegates and cannot pay for 6, we can negotiate a shortened course where some of the time will be spent in blended learning - watching videos and doing tutorials and exercises with some contact time with the trainer. We would want to discuss what your core needs are so that we cover those aspects. You need to have paid for 3 delegates at least.