PGConf.Russia 2018

«KNOPKA» («The button»), the largest outsourcing of accounting, will tell you about your experience of transferring 500Gb of 1C Fresh databases to PostgreSQL. The report touches on the choice of DBMS and the fight against stereotypes, where we started and how developed the use of PostgreSQL, the approaches to backup and recovery of information security, our current performance of DBMS and 1C, a look into the future. Let us share why we believe that 1C + PostgreSQL is simple, reliable and fast.

Avito is the biggest classified site of Russia, and the third largest classified site in the world (after Craigslist of USA and 58.com of China). In Avito, ads are stored in PostgreSQL databases. At the same time, for many years already the logical replication is actively used. With its help, the following issues are successfully solved: the growth of data volume and growth of number of requests to it, the scaling and distribution of the load, the delivery of data to the DWH and the search subsystems, inter-base and internetwork data synchronization etc. But nothing happens "for free" - at the output we have a complex distributed system. Hardware failures can happen - it is natural - you need to be always ready for it. There is plenty of samples of logical replication configuration and lots of success stories about using it. But with all this documentation there is nothing about samples of the recovery after crashes and data corruptions, moreover there are no ready-made tools for it. Over the years of constantly using PgQ replication, we have gained extensive experience, rethought a lot, implemented our own add-ins and extensions to restore and synchronize data after crashes in distributed data processing systems. In this report, we would like to show how our experience can be shifted to a new logical replication subsystem in 10th version of PostgreSQL. In the current implementation, these are only non-trivial solutions - there is a number of issues for the community, that come down to implementing simple recovery mechanisms - as simple as configuring the replication in 10th version.

"And telling GIN from SP-GIST was quite beyond his wit, we found", said the classic. Can you? This masterclass is about not-so-often used index types (compared to conventional B-tree) which however can do a great job for you. We will look into internal mechanics of these indexes and discuss cases where they can be successfully applied. Also we will talk about some peculiarities of PostgreSQL index access. To spend time efficiently, listeners are required to have basic knowledge of PostgreSQL and should be used to read plans of simple queries.

Materials of the master class

• Slides
• Demo 1
• Demo 2
• Demo 3

Backup copy of the database with demo data can be downloaded here:

• Recovery with pg_restore (338 MB)

What is a real cost of not paying for Windows for 1C-user? Is there life without COM? Addressing and other issues for the bunch of PostgreSQL. Scheduling disk resources. How to overcome OS CentOS crash.