PGConf.Online 2021

Формирование отчетов и аналитики в реальном времени с PostgreSQL.

In today's world, operational reporting and real-time analytics are becoming a basic need. There are a huge number of tools, practices, and approaches, which in turn require different expertise and resources. In this talk, I will tell you how to transform your analytics using PostgreSQL. We'll discuss pitfalls when using different schemes. We'll talk about data quality and performance. I expect this talk to be of interest to both beginners and seasoned practitioners with many years of experience (Discussions and questions after the talk will be highly appreciated). The talk outline is as follows. 1. The evolution of reporting - migration from OLTP to OLAP. 2. Data delivery to DWH and related challenges. 3. Scaling architecture in response to growing data volumes. 4. Data quality issues. 5. Maintaining stability with a large number of changes. 6. Different approaches to organizing the work of the DWH team. 7. And, finally, we'll list the challenges we have successfully responded to with various solutions (pgAgent, PGWatch, working with the file system, the new reading of postgresql.conf).

Новое в учебных курсах Postgres Professional

Our company's educational projects are intended to facilitate learning of PostgreSQL. Last year, we focused on courses for application developers: we updated DEV1 basic course and released a brand new DEV2 course. What has changed in training materials representation, how we see the further development of the courses and what else do we have besides the courses, whether the courses for DBAs will be updated and how this will affect the certification and that's what I will talk about.

Построение автоматического консультанта и инструментов настройки производительности в 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.

Продвинутые техники pg_upgrade

When it comes to performing postgres upgrades between major versions the command line utility pg_upgrade is the most popular method used today. But as with all things wonderful, there are caveats. One of the more critical issues is what to do when there's a failure. The purpose of this talk is to present those little covered "tricks" of the trade that allows one to improve and enhance the upgrade experience.

We'll start out by reviewing its basic mode operation. We'll then learn what makes it so darn fast when upgrading multi-terabyte datacluster in a matter of minutes. Finally, you will be introduced to those feared failures and how to deal with them with confidence and certainty.

Herein is a summary of the topics in this talk:

• How pg_upgrade works: the big picture
• About pgupgrade (comand line invocation)
  • arguments and options

• The steps performing an upgrade
• About the replication ROLE
  • with replication
  • with login

• Upgrade options: COPY vs Hard Link
• Post-upgrade
  • about performance
  • aboutanalyze
  • repack
  • reindexing

• When something goes wrong at the point of no return (or is it?)
• Upgrading the REPLICA
  • default method: pg_basebackup
  • the cool method:
    • - leveraging rsync
    caveat; about vacuum wraparound