FA-0354Data & Analytics

PostgreSQL Performance Tuning

Unlock the full potential with expert tuning techniques in 1 day

Introduction

Why this course

In today's data-driven world, the performance of your database can make or break your applications. PostgreSQL, a powerful open-source database, offers robust features, but ensuring it operates at peak performance requires specialized knowledge.

This one-day course is designed for database administrators and developers who want to optimize PostgreSQL for high performance, ensuring faster queries, better resource management, and overall system efficiency.

With over 30 years of industry experience, your instructor will guide you through real-world scenarios and practical tuning techniques that are in demand across the tech industry.

Learning outcomes

Understand PostgreSQL's architecture and its impact on performance.
Master techniques to optimize queries and indexes.
Configure PostgreSQL for high performance in various environments.
Implement advanced tuning strategies for memory, disk, and CPU usage.
Learn best practices for scaling and replication in PostgreSQL.

Prerequisites

Basic understanding of PostgreSQL or another relational database.
Familiarity with SQL queries.
Experience with Linux command-line interface (CLI) is helpful but not mandatory.

Training outline

5 modules

011. Introduction to PostgreSQL Performance Tuning3 topics

Overview of PostgreSQL architecture
Understanding the performance impact of different components
Identifying common performance bottlenecks

022. Query Optimization Techniques4 topics

Analyzing slow queries with EXPLAIN and EXPLAIN ANALYZE
Using indexes effectively (B-tree, Hash, GIN, and GiST)
Techniques for optimizing SELECT, JOIN, and aggregate functions
Strategies for rewriting queries for better performance

033. Configuring PostgreSQL for High Performance4 topics

Adjusting memory settings: shared_buffers, work_mem, and maintenance_work_mem
Configuring effective_cache_size and checkpoint settings
Tuning the autovacuum daemon
Disk I/O optimization: file system considerations, WAL settings

044. Advanced Tuning Strategies4 topics

Optimizing the planner and execution engine
Parallel query execution and tuning
Using partitioning for large tables
Managing and optimizing long-running queries

055. Performance Monitoring and Maintenance4 topics

Setting up PostgreSQL logging for performance insights
Using pg_stat_statements and other extensions for monitoring
Routine maintenance tasks: VACUUM, REINDEX, CLUSTER
Analyzing and optimizing vacuum performance

This course will equip you with the skills and knowledge to ensure your PostgreSQL database performs at its best, keeping your applications fast and responsive.

A programme built around your team.

Share your training goals and requirements.