⇽ Bruce Momjian ⇽

Postgres Technical Performance Presentations


PostgreSQL Performance Tuning

This talk is designed for advanced PostgreSQL users who want to know how to maximize PostgreSQL performance.  It covers every aspect of performance: server settings, caching, sizing operating system resources, optimizer processing, problem queries, storage efficiency, and some hardware selection details.  It includes how to size shared memory, how to understand the output of the optimizer, when to restructure queries, and how to configure storage for optimal performance.
Duration: 3 hours, 4 hours with questions

Explaining the Postgres Query Optimizer

The optimizer is the "brain" of the database, interpreting SQL queries and determining the fastest method of execution. This talk uses the explain command to show how the optimizer interprets queries and determines optimal execution. The talk will assist developers and administrators in understanding how Postgres optimally executes their queries and what steps they can take to understand and perhaps improve its behavior.
Duration: 45 minutes, 60 minutes with questions

Beyond Joins and Indexes

My presentation Explaining the Postgres Query Optimizer covers the details of query optimization, optimizer statistics, joins, and indexes. This talk covers 42 other operations the optimizer can choose to handle complex queries, large data sets, and to enhance performance. These include merge append, gather, memoize, and hash aggregate. It explains their purpose and shows queries that can generate these operations.
Duration: 45 minutes, 60 minutes with questions

Database Hardware Selection Guidelines

Database servers have hardware requirements different from other infrastructure software, specifically unique demands on I/O and memory. This presentation covers these differences and various I/O options and their benefits. Topics include solid-state drives (SSD), battery-backed RAID, controllers, and caching.
Duration: 30 minutes, 45 minutes with questions

Dissecting Partitioning

Declarative partitioning is designed to improve performance and simplify data management of large data sets. This talk first covers the purpose and limitations of Postgres's declarative partitioning implementation. The bulk of the talk uses SQL queries to show the many optimizations possible with partitioning. It concludes by showing some complicated data architectures made possible by partitioning.
Duration: 60 minutes, 75 minutes with questions

Postgres Scaling Opportunities

Database scaling is the ability to increase database throughput by utilizing additional resources such as I/O, memory, cpu, or additional computers. However, the high concurrency and write requirements of database servers make scaling a challenge. Sometimes scaling is only possible with multiple sessions, while other options require data model adjustments or server configuration changes. This talk explores the multi-session, single-session, and multi-host scaling options and the workloads where these options are appropriate.
Duration: 45 minutes, 1 hour with questions

The Future of Postgres Sharding

Database sharding involves spreading database contents across multiple servers, with each server holding only part of the database. While it is possible to vertically scale Postgres, and to scale read-only workloads across multiple servers, only sharding allows multi-server read-write scaling. This presentation will cover the advantages of sharding and future Postgres sharding implementation requirements, including foreign data wrapper enhancements, parallelism, and global snapshot and transaction control. This is a followup to my Postgres Scaling Opportunities presentation.
Duration: 30 minutes, 45 minutes with questions