title

text

Julien Rouhaud
Julien Rouhaud
15:30 02 March
22 мин

Don't fear your next glibc upgrade

PostgreSQL relies on the system collation libraries, such glibc or ICU, for text ordering. One know caveat is that when the library change its sort order for a collation, any index created using the old order is likely to be corrupted when the new version of the library is installed.

In this talk, we'll see the improvements done in PostgreSQL 14 to keep track of the collation versions, detect and fix possible index corruption due to library upgrades and the work currently being done to further improve this area.

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

Видео

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

  • Nikolay Samokhvalov
    Nikolay Samokhvalov Nombox LLC
    45 мин

    Automated database migration testing

    In a heavily-loaded project, any change implies non-zero risks of downtime or performance degradation. We constantly see how system complexity, number of database nodes, deployments per week constantly grow. How grows the level of automatization of various activities in CI/CD pipelines, containers, Kubernetes.

    Meanwhile, when we look at the topic of testing of database changes–from trivial index creation to complex, almost "surgical" operations like converting int4 PK to int8 one in a multi-terabyte database under load–here we observe an obvious lag in technology and methodology development. In the best case, the changes are verified and approved visually, and here it all depends on the level of experience and tiredness of the reviewer.

    In this talk, we will look at how we (Postgres.ai) solve this problem using our solution, Database Lab:

    • instant provisioning of independent thin clones of multi-terabyte databases, ready to be used for testing,
    • integration with existing CI/CD solutions and workflow,
    • collection of metrics that are the most useful for decision support on whether or not each database migration has to be approved (and even fully automated rejection of the most dangerous actions).

  • Henrietta Dombrovskaya
    Henrietta Dombrovskaya Braviant Holdings
    45 мин

    NORM - No ORM Framework

    It's a well-known fact, that although the database performance is great, and each query is executed in milliseconds, the overall application response time may be slow, making the users wait for a response for an extended period of time. We know that the problem is not the database, but the way the application developers communicate with the database. Specifically, we are talking about ORMs - Object-Relational Mappers. Database developers hate them, but application developers love them because they allow developing applications without any knowledge of database internals. As a result, the system performance is often unacceptably slow.

    The only way to change this behavior is to provide application developers with a tool, which is as easy to use, as an ORM, but which will allow escaping the common ORM pitfalls. That's why we developed NORM - No-ORM Framework. During this presentation, we will go over examples of code from https://github.com/hettie-d/NORM repo and learn how to build "transport objects" for efficient data transfer between applications and databases

  • Kohei KaiGai
    Kohei KaiGai HeteroDB
    45 мин

    GPU version of PostGIS and GiST-Index

    This talk introduces GPU version of PostGIS and GiST-Index that we have developed as a new feature of PG-Strom.

    Nowadays, our devices (like mobile phones) generate geolocational data time-by-time, and it is often utilized for area-marketing, push-delivery, disaster notification, and so on. People often use GIS technology to pick up users based on their current location. Even if area definitions are complicated polygons, PostGIS functions can generate right intersections, however, it is often highly computing intensive workloads.

    GPU is designed for massive parallel computing workloads, with more than thousands cores per chip. And, we have developed PG-Strom extension to run a part of SQL workloads on GPU devices. At the upcoming PG-Strom v3.0, it newly supports several PostGIS functions and GiST-index for the computing intensive geolocational workloads.

    In this talk, we will introduce the technology background, usage, implementation and benchmark result of GPU version of PostGIS and GiST-Index.

  • Nikolay Samokhvalov
    Nikolay Samokhvalov Nombox LLC
    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?