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

On September 1? Avto hosted a mip-up, where we talked about scaling applications on PostgreSQL. Today I want to share materials with him - videos, presentations from speakers, show photos. Also under the cut I publish the analysis of questions the quiz , which we conducted here on Habr, in front of the Mitap. And I tell you about my impressions from the meeting.
 
Materials from the meeting # RuPostgres - videos, presentations, analysis of the quiz and photo report Presentation
 
References:
 
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 version of Postgres.
 
Scaling an application on PostgreSQL in Avito: tips and tricks. Konstantin Evteev, Avito
 
I made a presentation about the scaling of the application on PostgreSQL in Avito and shared our tips and tricks.
 

 
Presentation
 
References:
 
An interesting approach: in which Kostantin very interestingly and intelligibly explained what problems can be encountered when working with data in a microservice architecture, and also proposed solutions for scaling IP. Saga remembered :)
 
Logical replication and isolation levels of PostgreSQL transactions. Mikhail Tyurin
 
Mikhail prepared a report on logical replication and isolation levels of PostgreSQL transactions.
 

 
Presentation
 
References:
 
Michael highlighted the subtle moments of transactions, which are not immediately visible not only to beginners. Everybody should know about this.
 
OZO - asynchronous type-safe header-only library client PostgreSQL for C ++ 17. Sergey Khandrikov, Yandex
 
Sergey told the audience how OZO, the asynchronous type-safe header-only PostgreSQL client library for C ++ 1? was created and invited to join it.
 

 
Presentation
 
References:
 
The author in my opinion managed to solve the problems in existing libraries and solve the problems in new C ++ libraries in a short time. Therefore I will be glad if these libraries will develop in opensource, the more basic things are already implemented, which can not but rejoice.
 
Answers to the questions of the quiz
 
Before Mitap, we asked you to answer questions on Postgres. Today I want to show the right answers. They are under spoilers (just in case).
 
There is an empty table without entries users ("UserId" int, "balance" int). What will be returned as a result of the query?
 
with ins as (
insert into users
select
.gs, gs * 10
from
.get_series (? 4) gs
where
.gs% 2 = 0)
select * from users;

 
Answer [/b]

Nothing.


 

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


 
Answer [/b]

ERROR: column "userid" does not exist.


 

Defined Enum CREATE TYPE status AS ENUM ('wait', 'init', 'run', 'stop'); What command can I delete the value of 'init'?


 
Answer [/b]

There is no standard way to remove a value from enum.


 

How can I get a list of functions in PostgreSQL?


 
Answer [/b]

select * From pg_proc;


 

What will be returned as a result of the query?


 
    select null = null, null is null, 1 :: smallint :: boolean is true, null :: bigint> 1    

 
Answer [/b]

ERROR: can not cast type smallint to boolean.


 

Junior-developer Vasya was instructed to write a query that displays all records from table parent , for which there are no entries in table child .


 

Scheme of the dаta:


 
    create table parent (parent_id serial primary key, payload text);
create table child (child_id serial primary key, parent_id integer unique references parent (parent_id));

 

Vasya tried very hard and did not want to hit the dirt in the face, so he came up with eight different requests to solve the problem:


 
    - 0
select
p.parent_id, p.payload
from
parent p
where
not exists (select from child c where c.parent_id = p.parent_id);
- 1
select
p.parent_id, p.payload
from
parent p
where
not (array[p.parent_id]&& array (select c.parent_id from child c));
- 2
select
distinct p.parent_id, p.payload
from
parent p full join child c
on (c.parent_id = p.parent_id)
where
c.parent_id is null;
- 3
select
p.parent_id, p.payload
from
parent p
where
p.parent_id not in (select c.parent_id from child c);
- 4
select
p.parent_id, p.payload
from
parent p left join child c
on (c.parent_id = p.parent_id)
where
c.parent_id is null;
- 5
with w_child_with_parents as (
select
c.parent_id,
(select count (*) from parent p where c.parent_id = p.parent_id) = 1 as parent_exists
from child c)
select
p.parent_id, p.payload
from
parent p
where
p.parent_id in (select pc.parent_id from w_child_with_parents pc where not pc.parent_exists);
- 6
select
p.parent_id, p.payload
from
parent p full join child c
on (c.parent_id = p.parent_id)
group by
p.parent_id,
p.payload
having count (c) = 0;
- 7
select
p.parent_id, p.payload
from
parent p
where
p.parent_id in (select p2.parent_id from parent p2 except all select c2.parent_id from child 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 difference in performance is not significant (or even imperceptible). However, you as a more experienced developer noticed that, perhaps, not all requests solve the task. List the requests that do not solve the task (and explain why).


 
The short answer is [/b]

The task is not solved by requests ? 3 and 5 (in some cases, also query 1).


 
The answer to the result of the experiment [/b]

Test dаta:


 

"Incorrectness" of behavior is manifested when there are entries in the child table with parent_id is null.


 
    insert into parent
(parent_id, payload)
values ​​
(? 'payload 1'),
(? 'payload 2'),
(? 'payload 3'),
(? 'payload 4'),
(? 'payload 5');
insert into child
(child_id, parent_id)
values ​​
(? 1),
(? 3),
(? null),
(? 5);

 
On the given test data
 
 
Query ? depending on whether the extension intarray is installed may or may not work.
 
Query 2 returns an extra string (null, null).
 
Queries 3 and 5 return an empty rezultet.
 
 
Interpretation of experimental results
 
Query 1: in case the intarray extension ( https://www.postgresql.org/docs/current/static/intarray.html ) Is installed in the database, the request falls with the error "ERROR: array must not contain nulls ". This behavior is due to the fact that the extension overrides standard operators and changes the behavior for arrays containing null elements.
 
The documentation says:
 
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.
Query 2: because of the full join, the result is an extra string (null, null).
 
Query 3: Returns an empty rezultet because there are null elements in the set formed by the subquery.
 
Documentation ( .https://www.postgresql.org/docs/current/static/functions-subquery.html#FUNCTIONS-SUBQUERY-NOTIN ):
 
Note that if the left-hand expression yields null, or if there is no left-hand value, the value of the output will be null, not true. This is in accordance with SQL's normal rules for Boolean combinations of null values.
Query 5: returns an empty rezultet because the w_child_with_parents section is invoked, from the child table and parent_id is empty or not reflected in the section at all.
 
On all quiz questions correctly answered three participants. One we handed the prize to Mitap, two more sets of souvenirs went by mail.
 

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

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

 
In conclusion, I want to thank my colleagues from Yandex, Postgres Professional and, of course, Avito for the wonderful reports that we heard. Thank you to the guests who came to us on this Saturday and to the spectators of the live broadcast. And of course, the #RuPostgres community for their trust.
 
Playlist with all the reports here .
 
Photo reports we laid out on facebook and VKontakte .
 

 
Until next time!
+ 0 -

Comments 1

Offline
Jacobe
Jacobe 28 September 2018 19:28
It's a tough call for you because bootstrap needs experience if you know how to connect then you do it. The mybookwidget.com shares a few methods which you can use to do this work and try some good things.

Add comment