How query execution history can help with re-execution

Postgres is able to build optimal query plans for most practical cases. However, sometimes, for objective reasons, for complex queries or because of open issues in the planner itself, it can make mistakes and produce a suboptimal plan. Because of this, the execution time of such a request can increase tenfold. If the query is executed frequently, then from time to time this query takes longer than it could, and the DBMS as a whole produces a lower TPS. If the planner is able to record his mistakes and take them into account in the subsequent planning of the same query, then this will improve the characteristics of the DBMS during its operation. We present the results of the development of a PostgreSQL DBMS extension that stores the query execution history and implements the planner recommendation mechanism. We show how knowledge about previously executed queries can improve the performance of subsequent ones.

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.

Avoiding, Detecting, and Recovering From Corruption

PostgreSQL databases can become corrupted for a variety of reasons, including hardware failure, software failure, and user error. In this talk, I’ll talk about some of my experiences with database corruption. In particular, I’ll mention some of the things which seem to be common causes of database corruption, such as procedural errors taking or restoring backups; some of the ways that database corruption most often manifests when it does occur, such as errors indicating inconsistencies between a table and its indexes or a table and its toast table; and a little bit about techniques that I have seen used to repair databases or recover from corruption, including some experiences with pg_resetxlog. This talk will be based mostly on my experiences working with EnterpriseDB customers; I hope that it will be useful to hackers from the point of view of thinking about possible improvements to PostgreSQL, and to end users from the point of view of helping them avoid, diagnose, and cope with corruption.

Backup Best Practices with pgBackRest

Backups are a critical part of any enterprise database solution but they are often done poorly or skipped altogether, which can lead to data loss in the event of hardware failure or some other disaster.

In this talk we'll cover database backup best practices and how to implement them with pgBackRest, including:

• WAL archiving and retention
• Backup frequency and retention
• How to meet recovery time/point objectives
• Configuration options
• Performance considerations