Postgres videos by pganalyze: I/O Optimization, Indexing, and more.

Automating Postgres Index Selection Using Constraint Programming

Get an introduction to a new approach for automatically determining which set of indexes to create for a given Postgres query workload, based on objectives chosen by the developer or DBA.

Here is what you will learn in this webinar:

Our approach for processing the Postgres query workload statistics derived from pg_stat_statements
Why we optimize index selection for a given table, not just a single query
A constraint programming optimization model that finds the mathematically optimal solution (set of index choices) based on a given set of constraints and objectives
How to find a set of indexes by minimizing the plan cost of all queries, whilst optimizing for the lowest index write overhead
How to prioritize a subset of queries over others
Controls for considering the impact of indexing to Postgres' HOT Updates
Using the model for consolidating existing indexes into a smaller set, to reduce overhead
How this compares to other approaches, such as Dexter, HypoPG, and research published on automatic index selection in recent years

Advanced Autovacuum Tuning and pganalyze VACUUM advisor

Understand the most important VACUUM metrics, concepts like xmin horizon, how to prevent transaction ID wraparound, estimating bloat, and more.

Here is what you will learn in this webinar:

How to identify ineffective VACUUMs that run but don’t actually produce any results
Tracking freezing stats to prevent TXID wraparound, and how to find problem tables
The VACUUM xmin horizon, and how it causes “Dead Tuples Not Yet Removable”, and how pganalyze alerts you of this situation
Why BUFFERS is important, and how to interpret the hits counter
How pganalyze calculates the age of transaction IDs using calendar time (not just counting TXIDs)
How pganalyze estimates bloat, and when the estimates are more vs less accurate
The new logic for scale factor and threshold tuning recommendations, and how it works behind the scenes
How VACUUMs that have multiple index phases can use a lot of resources, and how pganalyze recommends changing autovacuum_work_mem to avoid the problem
The pganalyze VACUUM Advisor

How to use the Postgres query planner to debug bad plans and speed up queries

Understand how the Postgres query planner chooses different plans for the same query, and how to debug bad plans.

Here is what you will learn in this webinar:

How to go from a slow query to a query plan that shows which exact part was slow
Understanding "Scans" and how Postgres decides which index to use
Why BUFFERS is important, and how to interpret the hits counter
How to rewrite queries based on plans to speed up performance
The different planner settings and how they influence plan choices
How to identify when plans "fall over" to a bad query plan and why this can happen
Choosing particular query plans using planner hints with pg_hint_plan
How managed cloud providers offer ways to control query plans (such as Aurora Query Plan Management), and how it compares to pg_hint_plan
Using the Postgres queryid to link pg_stat_statements, pg_stat_activity and auto_explain
Generic query plans, and how to get them from pg_stat_statements output
Looking at query plans over time with pganalyze - and how it works behind the scenes
and much more

How to tune Postgres autovacuum to improve performance and reduce bloat

Tune your autovacuum settings to ensure that VACUUM runs at the right time and is effective for your workload.

Here is what you will learn in this webinar:

The physicality of tables
The Transaction ID clock
All-visible pages and slow queries
Understanding autovacuum scheduling
Cost delay 101
Aggressive and Failsafe VACUUMs
Dead tuples not yet removable
VACUUMing in the cloud - RDS, Aurora, Cloud SQL & AlloyDB
Estimating and fixing table bloat

How To Reason About Indexing Your Postgres Database

See how Postgres chooses which index to use, and learn a methodology for analyzing queries and optimizing indexes.

Here is what you will learn in this webinar:

How Postgres chooses which index to use
Background about indexing
What is a “good enough” index?
A structured approach for finding the right indexes
A look behind the scenes: The pganalyze Indexing Engine

Optimizing Postgres I/O Performance and Costs

We dive into I/O optimization in Postgres and how tuning I/O helps reduce overall database costs.

Here is what you will learn in this webinar:

Common causes for I/O spikes in Postgres and how they affect infrastructure costs
How to map high read and/or write IOPS to database activity
Using EXPLAIN plans to find heavy hitting I/O intensive queries
Caching in Postgres, and how the buffer cache hit ratio can be improved to reduce I/O
Finding the root cause of high I/O costs of cloud databases such as Amazon Aurora
and much more