title

text

M
Mikhail Maslov Банк ВТБ (ПАО)
16:05 04 April
22 мин

Multi-transactions and possible issues with them

In this presentation, we cover multi-transactions, explain when they appear and how they can be monitored and checked. We also tackle the possible issue with multi-transaction ID wraparound and the problem of file absence in pg_multixact/offsets when restoring from a backup.

Материалы к докладу

Слайды

Видео

Другие доклады

  • Christopher Travers
    Christopher Travers Independent Community Member
    45 мин

    Crazy things you can do with PostgreSQL Indexes

    Of the relational databases, PostgreSQL is fairly unique in the indexing capabilities it offers. While most of us are familiar with the use of indexes to speed performance when filtering on columns, PostgreSQL indexes can do far more than this.

    This talk will focus on cases where difficult problems were solved through the creative use of indexes. Each of these cases is from an episode in my career.

    In this presentation, you will learn:

    • The general index access methods PostgreSQL supports out of the box
    • How the planner uses indexes (only on a high level)
    • Functional indexes
    • Creative use of Index Only scans.

    This is a talk for all audiences. The talk is primarily for beginner to intermediate users, but should have enough information for more advanced users to get some insight or inspiration from the talk as well.

  • Vladimir Serdyuk
    Vladimir Serdyuk Softpoint
    45 мин

    Heterogeneous distributed system - as a way to safely migrate from MSSQL Server to PostgreSQL, as well as reduce sanctions risks

    This report is primarily intended for companies, specifically their IT departments, that are running Russian systems 1C 8.x and have the ability to work with both MSSQL Server and PostgreSQL. We live in a unique time when having a heterogeneous IT system (a system with a distributed architecture where each database instance runs under different databases and/or has a different data structure) is justified both economically and in terms of potential risks. On the one hand, we store data and use databases with predictable behavior and open source code, regardless of the political situation. On the other hand, with this approach, we take advantage of all the benefits (primarily performance) of a powerful database supported by the largest vendor, albeit a hostile state to us. Now is the time to assess risks with the necessary level of paranoia. After all, data can be corrupted not only at the level of storage logic, but also by the method of additional "hidden" calls of Delete/Update constructs at the engine level of the database. Therefore, it is now relevant not only to monitor the performance of the IT system, but also to audit data and their timely integrity. It is necessary to implement data verification procedures, it is necessary to implement procedures for closing periods, procedures for fault-tolerant storage. And accordingly, to provide for various threat models in data recovery procedures. The report presents options for countering such threats and scenarios for the most seamless transition of large databases to PostgreSQL, since it is precisely for such databases that the problem of translating to a new DBMS is particularly acute.

  • Andrey Zubkov
    Andrey Zubkov Postgres Professional
    45 мин

    Workload monitoring extensions pg_profile/ pgpro_pwr. Architecture, setup, features and use cases

    The major task of workload monitoring tools is to provide measurable statistics about most resource intensive activities in the DBMS. They can't solve any issues but the points for concentrating optimization efforts can be highlighted. We’ll talk about the extensions pg_profile and pgpro_pwr, their architecture, features and use cases.

  • Anatoly Anfinogenov
    Anatoly Anfinogenov АО "ВНИИЖТ"
    22 мин

    Vacuum Therapy: Treatment of Chronic DB Disease

    Three years ago we successfully migrated our application from Oracle DBMS to vanilla PostgreSQL. After that we faced with "childhood" diseases of our application on the new DBMS, which were successfully cured, and some "chronic diseases", which took much longer to deal with. One of the most memorable problems was the problem of DB performance degradation. The source of this problems was insufficient vacuuming of our database. The experience of understanding and solving this problem is offered to your attention in the form of practical recommendations to prevent DB tables and indices bloat and setting up PostgreSQL VACUUM/autovacuum parameters.