EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

PostgreSQL Performance Tuning

Course outline

3 days
Discuss this programme →

Duration

PostgreSQL Performance Tuning

3 days

Module 1: Introduction to PostgreSQL is an introductory course designed to provide an overview of the PostgreSQL database system. It covers topics such as installation, configuration, database objects, SQL syntax, and basic performance tuning techniques. This module is ideal for those who are new to PostgreSQL and want to learn the basics of the system.

Lessons

  • Overview of PostgreSQL
  • PostgreSQL Architecture
  • PostgreSQL Installation and Configuration
  • PostgreSQL Security
  • PostgreSQL Performance Tuning Basics
  • PostgreSQL Indexing Strategies
  • PostgreSQL Query Optimization
  • PostgreSQL Database Monitoring
  • PostgreSQL Backup and Recovery
  • PostgreSQL Troubleshooting and Debugging

After completing this module, students will be able to:

  • Understand the basic concepts of PostgreSQL and its architecture.
  • Be able to install and configure PostgreSQL.
  • Be able to create and manage databases and tables.
  • Be able to write basic SQL queries and understand the query execution plan.

Module 2: Understanding Database Performance is a module in the PostgreSQL Performance Tuning course that focuses on understanding the fundamentals of database performance. It covers topics such as query optimization, indexing, and query execution plans. It also provides an overview of the PostgreSQL query planner and how to use it to identify and address performance issues.

Lessons

  • Introduction to PostgreSQL Performance Tuning
  • Analyzing Database Performance with PostgreSQL
  • Optimizing PostgreSQL Queries
  • Indexing Strategies for PostgreSQL
  • PostgreSQL Configuration Parameters
  • PostgreSQL Monitoring and Troubleshooting
  • PostgreSQL Caching and Memory Management
  • PostgreSQL Replication and High Availability
  • PostgreSQL Security and Auditing
  • PostgreSQL Backup and Recovery

After completing this module, students will be able to:

  • Identify and troubleshoot common database performance issues.
  • Analyze query plans and identify areas for improvement.
  • Utilize PostgreSQL tools and techniques to optimize query performance.
  • Implement best practices for database indexing and query optimization.

Module 3 of the PostgreSQL Performance Tuning course covers the configuration of PostgreSQL for optimal performance. It covers topics such as setting up the PostgreSQL configuration file, configuring memory, disk, and network settings, and tuning the database for specific workloads. It also covers advanced topics such as replication, high availability, and security.

Lessons

  • Introduction to PostgreSQL Configuration
  • Configuring PostgreSQL for Performance
  • Understanding PostgreSQL Memory Settings
  • Configuring PostgreSQL for High Availability
  • Optimizing PostgreSQL for Read/Write Performance
  • Configuring PostgreSQL for Security
  • Troubleshooting PostgreSQL Performance Issues
  • Monitoring PostgreSQL Performance
  • Automating PostgreSQL Configuration
  • Advanced PostgreSQL Configuration Techniques

After completing this module, students will be able to:

  • Understand the architecture of PostgreSQL and how to configure it for optimal performance.
  • Identify and troubleshoot common performance issues in PostgreSQL.
  • Utilize PostgreSQL tools and techniques to monitor and analyze performance.
  • Implement best practices for configuring PostgreSQL for optimal performance.

Module 4 of the PostgreSQL Performance Tuning course covers indexing strategies for optimizing query performance. It covers topics such as index types, index design, index maintenance, and index usage. It also provides practical examples of how to create and use indexes to improve query performance.

Lessons

  • Understanding Index Types and Usage
  • Analyzing Index Performance
  • Creating and Managing Indexes
  • Indexing Strategies for Different Query Types
  • Indexing Strategies for Large Tables
  • Indexing Strategies for Joins
  • Indexing Strategies for Aggregate Queries
  • Indexing Strategies for Subqueries
  • Indexing Strategies for Partitioned Tables
  • Indexing Strategies for Materialized Views

After completing this module, students will be able to:

  • Understand the different types of indexing strategies available in PostgreSQL and how to choose
  • the most appropriate one for a given query.
  • Analyze query plans to identify opportunities for index optimization.
  • Implement indexing strategies to improve query performance.
  • Monitor and maintain indexes to ensure optimal performance.

Module 5 of the PostgreSQL Performance Tuning course focuses on query optimization. It covers topics such as query analysis, query optimization techniques, and query optimization tools. It also provides hands-on exercises to help students practice and apply the concepts they have learned.

Lessons

  • Understanding Query Execution Plans
  • Analyzing Query Performance
  • Indexing Strategies for Query Optimization
  • Using EXPLAIN to Debug Queries
  • Using EXPLAIN ANALYZE to Monitor Performance
  • Using EXPLAIN VERBOSE to Analyze Queries
  • Using pg_stat_statements to Monitor Performance
  • Using pg_stat_activity to Monitor Performance
  • Using pg_stat_user_tables to Monitor Performance
  • Using pg_stat_user_indexes to Monitor Performance
  • Using pg_stat_user_functions to Monitor Performance
  • Using pg_stat_replication to Monitor Performance
  • Using pg_buffercache to Monitor Performance
  • Using pg_locks to Monitor Performance
  • Using pg_settings to Monitor Performance
  • Using pg_stat_all_tables to Monitor Performance
  • Using pg_stat_all_indexes to Monitor Performance
  • Using pg_stat_all_functions to Monitor Performance
  • Using pg_stat_activity_snapshot to Monitor Performance
  • Using pg_stat_statements_snapshot to Monitor Performance

After completing this module, students will be able to:

  • Understand the fundamentals of query optimization in PostgreSQL.
  • Analyze query plans and identify potential performance bottlenecks.
  • Utilize PostgreSQL features such as indexes, views, and materialized views to improve query
  • performance.
  • Implement best practices for query optimization in PostgreSQL.

Module 6 of the PostgreSQL Performance Tuning course covers the monitoring and troubleshooting of PostgreSQL databases. It covers topics such as monitoring tools, performance metrics, and troubleshooting techniques. It also provides an overview of the PostgreSQL query planner and how to use it to optimize queries. Finally, it provides an introduction to PostgreSQL replication and how to use it to improve performance.

Lessons

  • Introduction to PostgreSQL Performance Monitoring
  • Analyzing PostgreSQL Performance Metrics
  • Identifying and Resolving PostgreSQL Performance Issues
  • PostgreSQL Performance Tuning Strategies

PostgreSQL Performance Tuning Tools

PostgreSQL Performance Tuning Best Practices

PostgreSQL Performance Tuning Troubleshooting

PostgreSQL Performance Tuning Tips and Tricks

PostgreSQL Performance Tuning Case Studies

PostgreSQL Performance Tuning in the Cloud

After completing this module, students will be able to:

  • Identify and diagnose performance issues in PostgreSQL databases.
  • Utilize PostgreSQL tools and techniques to monitor and troubleshoot database performance.
  • Implement strategies to improve PostgreSQL database performance.
  • Analyze and optimize PostgreSQL query plans to improve query performance.

Module 7 of the PostgreSQL Performance Tuning course covers advanced performance tuning techniques. It covers topics such as query optimization, indexing, caching, and other techniques to improve the performance of PostgreSQL databases. It also covers how to use the PostgreSQL EXPLAIN command to analyze query plans and identify potential performance bottlenecks.

Lessons

  • Analyzing PostgreSQL Performance with EXPLAIN
  • Optimizing PostgreSQL Queries with Indexes
  • Using PostgreSQL Statistics to Improve Performance
  • Advanced PostgreSQL Configuration Parameters
  • PostgreSQL Query Planner and Optimizer
  • PostgreSQL Performance Monitoring and Tuning
  • PostgreSQL Caching and Buffering
  • PostgreSQL Logging and Auditing
  • PostgreSQL Partitioning and Sharding
  • PostgreSQL Replication and High Availability

After completing this module, students will be able to:

  • Understand the principles of query optimization and how to apply them to PostgreSQL.
  • Analyze query plans and identify areas of improvement.
  • Utilize PostgreSQL's built-in performance tuning tools.
  • Implement advanced techniques such as indexing, partitioning, and materialized views to improve
  • query performance.

Module 8 of the PostgreSQL Performance Tuning course covers Database Partitioning, a technique used to improve the performance of PostgreSQL databases. This module will teach you how to partition your database tables, how to create and manage partitions, and how to use partitioning to improve query performance. Additionally, you will learn how to use PostgreSQL's built-in partitioning features, as well as how to use third-party tools to partition your database.

Lessons

  • Introduction to Database Partitioning
  • Benefits of Database Partitioning
  • Types of Database Partitioning
  • Implementing Database Partitioning in PostgreSQL
  • Managing Partitions in PostgreSQL
  • Performance Tuning with Database Partitioning
  • Troubleshooting Database Partitioning Issues
  • Best Practices for Database Partitioning in PostgreSQL

After completing this module, students will be able to:

  • Understand the concept of database partitioning and its benefits
  • Implement partitioning strategies in PostgreSQL
  • Analyze the performance of partitioned tables
  • Optimize queries for partitioned tables in PostgreSQL

Module 9 of the PostgreSQL Performance Tuning course covers Database Replication, a powerful tool for improving the performance of PostgreSQL databases. This module will teach you how to set up and configure replication, as well as how to monitor and troubleshoot replication issues. You will also learn how to use replication to improve scalability and availability of your PostgreSQL databases.

Lessons

  • Overview of Database Replication
  • Setting up Replication in PostgreSQL
  • Configuring Replication for Performance
  • Troubleshooting Replication Issues
  • Strategies for Optimizing Replication Performance
  • Monitoring Replication Performance
  • Advanced Replication Techniques
  • Automating Replication Tasks
  • Security Considerations for Replication
  • Best Practices for Database Replication

After completing this module, students will be able to:

  • Understand the fundamentals of database replication and its importance in PostgreSQL.
  • Configure and manage replication in PostgreSQL.
  • Monitor and troubleshoot replication issues.
  • Implement strategies to optimize replication performance.

Module 10 of the PostgreSQL Performance Tuning course covers backup and recovery strategies for PostgreSQL databases. It covers topics such as backup types, backup strategies, backup tools, and recovery techniques. It also provides an overview of the PostgreSQL recovery process and how to use the pg_dump and pg_restore utilities.

Lessons

  • Introduction to Backup and Recovery Strategies
  • Understanding the Different Types of Backups
  • Planning and Implementing a Backup Strategy
  • Automating Backup and Recovery Processes
  • Disaster Recovery Planning
  • Point-in-Time Recovery
  • Backup Compression and Encryption
  • Backup and Recovery Tools for PostgreSQL
  • Monitoring and Troubleshooting Backup and Recovery
  • Best Practices for Backup and Recovery

After completing this module, students will be able to:

  • Understand the importance of backup and recovery strategies for PostgreSQL databases.
  • Implement backup and recovery strategies for PostgreSQL databases.
  • Analyze the impact of backup and recovery strategies on PostgreSQL performance.
  • Troubleshoot and optimize backup and recovery strategies for PostgreSQL databases

Programme scope, delivery arrangements and suitability are confirmed during enquiry.