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.

Reports

Distributed transactions and time travel. Stas Kelvich, Postgres Professional

Stas told about distributed transactions and time travel.

Presentation

Reviews:

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.

Presentation

Reviews:

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.

Presentation

Reviews:

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.

Presentation

Reviews:

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 (
     insertintousersselect 
        gs, gs * 10from 
        generate_series(1, 4) gs     
     where 
        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?

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

How can I get a list of functions in PostgreSQL?

What will be returned as a result of the request?

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

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)
where 
   c.parent_id isnull;
-- 3select 
   p.parent_id, p.payload
fromparent p
where 
   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)
where 
   c.parent_id isnull;
-- 5with w_child_with_parents as (
    select
        c.parent_id,
        ( selectcount(*) fromparent p where c.parent_id = p.parent_id) = 1as parent_exists
    fromchild c)
select 
    p.parent_id, p.payload
fromparent p
where 
    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)
groupby
    p.parent_id,
    p.payload
havingcount(c) = 0;
-- 7select 
   p.parent_id, p.payload
fromparent p
where 
   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).

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

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.

Afterword

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!