Home
About NEOOUG
Membership
Events
Presentations
Annual Conference
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 In
8:30 AM - 9:00 AM
Workshop
9:00 AM - 12:00 PM
Lunch Break
12:00 PM - 1:00 PM
Workshop Continues
1: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.