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
The optimizer is the "brain" of the database, interpreting SQL queries and determining the fastest method of execution. This talk uses theexplain 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
My presentationExplaining 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
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
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
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
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 myPostgres Scaling Opportunities presentation.
Duration: 30 minutes, 45 minutes with questions
RSS