Get in Touch
 Duration 14 hours

Course Outline

1. Grasping the PostgreSQL Query Planner

  • Execution plans and Planner algorithms (classic, genetic)
  • Interpreting execution plans (data retrieval and join techniques)
  • Guiding plan selection (configuration settings, pg_hint_plan)

2. Query Planner Statistical Data

  • Estimating execution plan costs
  • Standard statistical models
  • The ANALYZE process and extended statistics

3. Leveraging Indexes

  • B-tree indexes (single column, composite, function-based, partial)
  • Hash indexes
  • BRIN indexes
  • GiST and GIN indexes

4. Implementing Advanced Table Structures

  • Partitioned tables
  • Unlogged tables
  • Temporary tables
  • Materialized views

5. Utilizing Cache Memory

  • Buffer Cache
  • Work Memory
  • Maintenance Work Memory

6. Parallel Query Execution

  • Architectural overview
  • Relevant configuration parameters
  • Reviewing parallel query execution plans

7. Workload and Performance Oversight

  • Capturing slow query logs
  • Implementing the auto_explain extension
  • Leveraging the pg_stat_statements extension
  • Cumulative statistics

8. Performance Benchmarking with PgBench

Requirements

  • Successful completion of PostgreSQL Server Administration or comparable expertise
  • Practical experience with SQL and PostgreSQL operations

Target Audience

Database Administrators, DevOps Engineers, and Developers tasked with the tuning and upkeep of PostgreSQL in production settings.

Number of participants


Price per participant

Testimonials (3)

Upcoming Courses

Related Categories