title

text

Nikolay Samokhvalov
Nikolay Samokhvalov Nombox LLC
16:00 01 March
180 мин

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?

Видео

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

  • David Steele
    David Steele Crunchy Data
    45 мин

    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

  • Sushant Pandey
    Sushant Pandey Microsoft
    Alicja Kucharczyk
    Alicja Kucharczyk Microsoft
    22 мин

    The Story About The Migration

    In this talk we want to present how Microsoft team composed of people from two different teams approached the project and solved the migration issues using ora2pg and was able to prove that Postgres Single Server can perform equally well as Oracle Exadata. We will present our ways of working and also some main technical challenges that we faced including migration of BULK COLLECT’s, hierarchical queries, refcursors and others more complicated Oracle constructs.

    The story about a challenging PoC that proved that Postgres can achieve the same performance as Oracle Exadata. The schema that was migrated wasn’t the simplest one you might see. It was quite the opposite. The code was loaded with dynamic queries, BULK COLLECT’s, nested loops, CONNECT BY statements, global variables and lot of dependencies. Ora2pg did a great job converting the schema but left a lot of work to do manually. Also estimates produced by the tool were highly inaccurate since the logic required not the migration but total re-architecture of the code. In this talk we want to present how Microsoft team composed of people from two different teams approached the project and solved the migration issues using ora2pg and was able to prove that Postgres Single Server can perform equally well as Oracle Exadata. We will present our ways of working and also some main technical challenges that we faced including:

    • How estimates do (not) work
    • How we handled BULK COLLECT’s
    • Why we got rid of refcursors
    • How we got stuck with testing of one the packages and how the help from a friend solved the problem
    • How we handled hierarchical queries and drilling down the hierarchy

  • Pavel Borisov
    Pavel Borisov Postgres Professional
    45 мин

    Speed up your fast text search queries with RUM index

    Fast text search queries can be made even faster with indexing on lexemes inside compound records of tsvector format. RUM index is an open-source PostgreSQL extension. It represents a big improvement of GIN index and it can index lexemes with additional information e.g. tsvector lexeme weight-mark. So it can support tsvector capabilities more.

    Until recently it was needed to recheck results of weight-containing queries by table. My modification (2020) is to make the processing of this kind of queries index-only and therefore much faster.

    Also, I will describe and provide benchmarks for different usage cases for fast text search. We'll see how RUM index can improve the performance and compare it with PostgreSQL internal GIN index.

  • Alexey Fadeev
    Alexey Fadeev Sibedge
    22 мин

    Multicorn Foreign Data Wrapper vs plpython

    Multicorn technology allows you to develop FDWs in Python, which is much easier and faster than creating FDWs in C. However, there is a downside, Multicorn FDWs work well with primitive WHERE conditions, but more complex cases cause difficulties, which I will talk about. Cases will be considered on the example of my Multicorn FDW for getting OpenStreetMap data. I will also show examples of using the same code in Multicorn FDW and plpython functions, including performance comparison. In conclusion, I will share my findings on when it is better to use plpython, and when Multicorn FDW is more preferable.