Modern DWH with open-source PostgreSQL

PostgreSQL has a long history in DWH. Netezza, Redshift, and Greenplum have turned specific PostgreSQL releases into DWH solutions. Nowadays, with the trends in PostgreSQL towards performance improvements (better partitioning, better statistics, JIT Compilation, etc.) and advanced PostgreSQL extensions, like the Swarm64 Data Accelerator, you can create a forward-looking, no lock-in, versatile, and reliable DWH. This talk will cover the PostgreSQL and DWH trends and touch on key arguments for choosing open source PostgreSQL for DWH.

Building automatic adviser and performance tuning tools in PostgreSQL

PostgreSQL is a mature and robust RDBMS since it has 30 years of history. Over the year, its query optimizer has been enhanced and usually produces good query plans.

However, can it always come up with good query plans? The optimization process has to use some assumptions to produce plans fast enough. Some of those assumptions are relatively easy to check (e.g. statistics are up-to-date), some harder (e.g. correct indexes are created), and some nearly impossible (e.g. making sure that the statistic samples are representative enough even for skewed data repartition). For now, given those various caveats, DBA sometimes can't always realize easily that they miss a chance to get a meaningful performance improvement.

To help DBA to get a truly good query plan, we'll present below some tools that can help to fix some of those problems by providing a missing index adviser, looking for extended statistics to create, and row estimation error correction information to get appropriate join orders with join methods automatically.

• pg_qualstats: provides a new index and extended statistics suggestions to gather many predicate statistics on the production workload.
• pg_plan_advsr: provides alternative good query plans automatically to analyze iterative query executions information to fix estimation rows error.

In this talk, we will explain how those tools work under the hood and see what can be done, how they can work together. Also, we will mention what other tools also exist for related problems. Therefore, it will be useful for DBA who are interested in improving query performance or want to check whether current settings of indexes and statistics are adequate.

How to manage an open source HA RDBMS in a cloud environment

High availability solutions have become extremely popular in the past few years. They play a critical role in building reliable systems based on affordable hardware. In this presentation, we will pay attention to some of the subtle aspects of the design and maintenance of such systems. In addition, the issues of capturing changes on a HA cluster will be addressed.

Seamless SQL optimization, v2.0

There are two types of SQL query analysis:

1. "Macro": analyzing the workload as a whole (three major approaches: using metrics provided by pg_stat_statements or similar, log analysis with pgBadger or similar, and sampling of pg_stat_activity)

2. "Micro": diving into details of single query execution (EXPLAIN command being the central tool here)

And there are huge gaps between them that become noticeable at scale. The main challenges:

• Switching between "macro" and "micro" without a huge overhead
• Verifying optimization ideas reliably
• Deploying changes risk-free

Solving these tasks at a scale requires advanced DBA experience and–sometimes–intuition. Or better tools that (fortunately!) very recently started to appear.

In this tutorial, we will learn how to establish a smooth and seamless SQL optimization process in your organization: * what tools should you choose in your particular case? * how to close the gaps mentioned above?