Materials from the meeting #RuPostgres - videos, presentations, quiz analysis and photo report

    On September 15, Avito held a mitap at which we talked about scaling applications on PostgreSQL. Today I want to share materials from him - videos, presentations from speakers, show photos. Also under the cut I publish the analysis of the quiz questions that we conducted here on Habré, in front of the mitap. And talk about my impressions of the meeting.


    Distributed transactions and time travel. Stas Kelvich, Postgres Professional

    Stas told about distributed transactions and time travel.



    Stas and his team did an excellent job! I hope their decision will be approved by the community and we will see this solution in the new Postgres version.

    Scaling an application to PostgreSQL in Avito: tips and tricks. Konstantin Evteev, Avito

    I gave a talk about scaling the application on PostgreSQL in Avito and shared our tips and tricks.



    An interesting approach: in which Kostantin very fascinatingly and intelligibly clarified what problems can be encountered when working with data in the microservice architecture, and also suggested ways to solve when scaling IP. Saga remembered :)

    Logical replication and PostgreSQL transaction isolation levels. Mikhail Tyurin

    Michael prepared a report on logical replication and PostgreSQL transaction isolation levels.



    Michael highlighted the subtle moments of transactions, which are not immediately visible not only to beginners. Everyone needs to know about it.

    OZO is an asynchronous type-safe header-only PostgreSQL client library for C ++ 17. Sergey Khandrikov, Yandex

    Sergey told the audience about the structure of OZO, an asynchronous type-safe header-only PostgreSQL client library for C ++ 17, and invited a contributor to it.



    The author, in my opinion, managed in a very short time to sufficiently review problems in existing libraries and solutions in new C ++ libraries. Therefore, I will be glad if these libraries will be developed in opensource, all the more basic things have already been implemented, which is good news.

    Answers to quiz questions

    Before the mitap, we suggested you answer questions about Postgres. Today I want to show the correct answers. They are under the spoilers (just in case).

    There is an empty table with no users ("UserId" int, "balance" int) records. What will be returned as a result of the request?

    with ins as (
            gs, gs * 10from 
            generate_series(1, 4) gs     
            gs%2 = 0) 
    select * fromusers;



    What will return the query select * from users where UserId = 10;when accessing the users table after the previous task?


    ERROR: column "userid" does not exist.

    Enum CREATE TYPE status AS ENUM ('wait', 'init', 'run', 'stop') is defined; Which command can remove the value of 'init'?


    Стандартного способа удаления значения из enum нет.

    How can I get a list of functions in PostgreSQL?


    select * From pg_proc;

    What will be returned as a result of the request?

    selectnull = null, nullisnull, 1::smallint::booleanistrue, null::bigint > 1


    ERROR: cannot cast type smallint to boolean.

    Junior developer Vasya was instructed to write a query that displays all the records from the table parentfor which there are no records in the table child.

    Data scheme:

    createtableparent (parent_id serial primary key, payload text);
    createtablechild (child_id serial primary key, parent_id integeruniquereferencesparent (parent_id));

    Vasya tried very hard and did not want to lose his face, so he invented eight different requests to solve the problem:

    -- 0select 
       p.parent_id, p.payload
    fromparent p
    wherenotexists(selectfromchild c where c.parent_id = p.parent_id);
    -- 1select 
       p.parent_id, p.payload
    fromparent p
    wherenot (array[p.parent_id] && array(select c.parent_id fromchild c));
    -- 2selectdistinct p.parent_id, p.payload
    fromparent p fulljoinchild c 
          on (c.parent_id = p.parent_id)
       c.parent_id isnull;
    -- 3select 
       p.parent_id, p.payload
    fromparent p
       p.parent_id notin (select c.parent_id fromchild c);
    -- 4select 
       p.parent_id, p.payload
    fromparent p leftjoinchild c 
          on (c.parent_id = p.parent_id)
       c.parent_id isnull;
    -- 5with w_child_with_parents as (
            ( selectcount(*) fromparent p where c.parent_id = p.parent_id) = 1as parent_exists
        fromchild c)
        p.parent_id, p.payload
    fromparent p
        p.parent_id in (select pc.parent_id from w_child_with_parents pc wherenot pc.parent_exists);
    -- 6select 
       p.parent_id, p.payload
    fromparent p fulljoinchild c 
          on (c.parent_id = p.parent_id)
    havingcount(c) = 0;
    -- 7select 
       p.parent_id, p.payload
    fromparent p
       p.parent_id in ( select p2.parent_id fromparent p2 except all select c2.parent_id fromchild c2);

    Vasya presented his options to you so that you could help him choose the best one. He argues that all requests work in the same way: tables are placed in memory and the performance difference is not significant (or even invisible). However, you, as a more experienced developer, have noticed that perhaps not all requests solve the problem. List the requests that do not solve the problem (and explain why).

    Short answer

    Поставленную задачу не решают запросы 2, 3 и 5 (в некоторых случаях так же запрос 1).

    The answer to the result of the experiment

    Тестовые данные:

    «Некорректность» поведения проявляется, когда существуют записи в таблице child с parent_id is null.

       (parent_id, payload)
       (1, 'payload 1'), 
       (2, 'payload 2'), 
       (3, 'payload 3'), 
       (4, 'payload 4'), 
       (5, 'payload 5'); 
       (child_id, parent_id)
       (1, 1), 
       (2, 3), 
       (3, null), 
       (5, 5);

    На приведенных тестовых данных

    • Запрос 1 в зависимости от того установлено ли расширение intarray может работать или не работать.
    • Запрос 2 возвращает лишнюю строку (null, null).
    • Запросы 3 и 5 возвращают пустой резалтсет.

    Интерпретация результатов эксперимента

    Запрос 1: в случае, если в базе данных установленно расширение intarray (, запрос падает с ошибкой "ERROR: array must not contain nulls". Данное поведение связано с тем, что расширение переопределяет стандартные операторы и изменяет поведения для массивов, содержащих null-элементы.

    Документация говорит следующее:

    The operators &&, @> and <@ are equivalent to PostgreSQL's built-in operators of the same names, except that they work only on integer arrays that do not contain nulls, while the built-in operators work for any array type. This restriction makes them faster than the built-in operators in many cases.

    Запрос 2: из-за full join в результате появляется лишняя строка (null, null).

    Запрос 3: возвращает пустой резалтсет из-за того, что во множестве, формируемым подзапросом есть null-элементы.

    Документация (

    Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true. This is in accordance with SQL's normal rules for Boolean combinations of null values.

    Запрос 5: возвращает пустой резалтсет потому, что в секции w_child_with_parents производится заход, с таблицы child и parent_id оказывается пустым или не отражается в секции вовсе.

    Three quiz questions correctly answered all the quiz questions. One we handed the prize at the mitap, two more sets of souvenirs went by mail.


    More than a hundred people came to the mitap. It was very nice to meet such an audience. According to the survey, more than 60% of guests of the mitap have more than five years of experience working with databases. And it is very nice when reports receive such a lively response from the audience:

    On the sidelines of the meeting they talked a lot about the fact that PostgreSQL is becoming an increasingly common tool. It really is. Taking this opportunity, I will say that we in Avito plan to expand the DBA team, and if you are interested in ambitious tasks on a large project, look at the vacancy on My Circle or write to me.

    And in conclusion, I want to thank my colleagues from Yandex, Postgres Professional and, of course, Avito, for the wonderful reports that we heard. Thanks to the guests who came to us on this Sabbath day and live viewers. And of course, the #RuPostgres community for their trust.

    Playlist with all reports here .
    Photo reports we posted on Facebook and VKontakte .

    See you again!

    Also popular now: