​

Duration : 3 Days

 

Modules:

 

Day 1:

Foundations of PostgreSQL Performance Tuning

Objective: Understand the fundamentals of PostgreSQL architecture, indexing, and basic performance principles.

 

Session 1: Introduction to PostgreSQL Architecture

- Topics:

- PostgreSQL processes (background processes, client-server model)

- Buffer management

- Disk I/O and memory architecture

- WAL (Write-Ahead Logging)

- Hands-on Exercise:

- Analyze the database processes and memory usage in a sample PostgreSQL installation.

 

Session 2: Query Execution and Optimization Basics

- Topics:

- Query execution plan and its components (Seq Scan, Index Scan, etc.)

- `EXPLAIN` and `EXPLAIN ANALYZE` commands

- Basic cost-based optimization

- Identifying slow queries

- Hands-on Exercise:

- Use `EXPLAIN ANALYZE` on sample queries to understand query performance.

 

Session 3: Indexing Strategies

- Topics:

- Types of indexes in PostgreSQL (B-tree, Hash, GIN, GiST, BRIN)

- When and where to use indexes

- Multi-column indexes

- Index maintenance and performance considerations

- Hands-on Exercise:

- Create different types of indexes and analyze their impact on query performance.

 

Day 2:

Advanced Query Optimization Techniques

Objective: Dive deeper into advanced query optimization techniques and common performance bottlenecks.

​

Session 1: Understanding PostgreSQL Planner and Optimizer

- Topics:

- Planner and optimizer internals

- Cost estimates and statistics

- Parameter tuning for planner (e.g., `random_page_cost`, `cpu_tuple_cost`)

- Hands-on Exercise:

- Fine-tune planner parameters and analyze the performance changes.

​

 

Session 2: Optimizing Joins and Aggregations

- Topics:

- Join strategies (Nested Loop, Hash Join, Merge Join)

- Optimizing joins (reordering, batching, reducing joins)

- Optimizing aggregate functions and window functions

- Hands-on Exercise:

- Work with complex queries involving multiple joins and aggregations, analyze and optimize performance.

​

Session 3: Common Performance Bottlenecks and Their Solutions

- Topics:

- Slow queries due to inefficient joins, functions, and subqueries

- Locking and deadlock issues

- Transaction management and performance impact

- Hands-on Exercise:

- Identify and resolve performance bottlenecks in a sample database.

​

​

Day 3:

 Advanced Tuning and Real-World Scenarios

Objective: Learn to fine-tune PostgreSQL settings, and perform optimizations in real-world scenarios.

​

Session 1: Server Configuration Tuning

- Topics:

- Tuning memory parameters (e.g., `shared_buffers`, `work_mem`, `maintenance_work_mem`)

- Disk and I/O performance tuning (`effective_io_concurrency`, `checkpoint_segments`)

- Autovacuum settings for maintenance

- Hands-on Exercise:

- Tune PostgreSQL configuration for various workloads and analyze the impact.

​

Session 2: Partitioning and Sharding

- Topics:

- Table partitioning strategies (range, list, hash)

- When to use partitioning

- Introduction to sharding in PostgreSQL

- Hands-on Exercise:

- Implement partitioning on a large dataset and analyze its effect on performance.

​

Session 3: Monitoring and Maintenance Best Practices

- Topics:

- Using tools for monitoring (pg_stat_statements, pgBadger, pg_top)

- Routine maintenance tasks (VACUUM, ANALYZE)

- Optimizing backups and restore processes

- Hands-on Exercise:

- Set up monitoring tools and perform maintenance tasks on a live PostgreSQL instance.

​

Session 4: Real-World Case Studies and Q&A

- Topics:

- Discuss real-world scenarios of performance tuning in PostgreSQL

- Q&A session with participants to solve practical tuning challenges

​

​