NorthEast Ohio Oracle Users Group

April 18, 2023
Full Day PostgreSQL Tuning Workshop

Bruce Momjian, a co-founder and core team member of the PostgreSQL Global Development Group, will present the day's topics. The day's schedule will be -

Check In8:30 AM - 9:00 AM
Workshop9:00 AM - 12:00 PM
Lunch Break12:00 PM - 1:00 PM
Workshop Continues1:00 PM - 5:00 PM

Featured Presentations

PostgreSQL Performance Tuning

Covers server settings, caching, sizing operating system resources, optimizer processing, problem queries, storage efficiency. This topic 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.

Explaining the Postgres Query Optimizer

The optimizer is the "brain" of the database, interpreting SQL queries and determining the fastest method of execution. Uses the explain command to show how the optimizer interprets queries and determines optimal execution. The session will assist developers and administrators in understanding how Postgres optimally executes queries and what steps they can take to understand and perhaps improve its behavior.

Beyond Joins and Indexes

Explaining the Postgres Query Optimizer covers the details of query optimization, optimizer statistics, joins, and indexes. This session 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.

Flexible Indexing with Postgres

When considering database indexing, many people are confused by the many Postgres indexing structures available, and the many data-type-specific index lookup methods. For example, brin allows for efficient indexing of many columns. gin indexing specializes in the rapid lookup of keys with many duplicates — an area where traditional btree indexes perform poorly. This is particularly useful for json and full text searching. GiST allows for efficient indexing of two-dimensional values and range types. This talk explores the various indexing features of Postgres and when to use them.

Dissecting Partitioning

Declarative partitioning allows for improved performance and simpler data management for large data sets. This talk explains the purpose of declarative partitioning and its various features using illustrative SQL queries.

Database Hardware Selection Guidelines

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