PgConf.Russia 2020

Walk-through of extending PostgreSQL with a user-defined type. The journey begins from the basics, from creating simple domain types over existing types, and continues to implementing a full-blown datatype from scratch in C.

PostgreSQL's advanced index types, GiST, GIN, and SP-GiST, are covered in enough detail to give an understanding of what each of them is good for. Support functions for each of them are shown for the example 'color' datatype.

In the report, we want to talk about the experience of migrating one large system from Oracle to PostgreSQL. The system itself was built on the PHP + Oracle stack; its distinguishing feature was that all business logic was implemented in PL / SQL code. In a DBMS, there are more than 3000 packages with 4-10 functions (procedures) in each. In PHP - more than 10,000 forms with inserts of anonymous blocks used to receive data, process and save results in Oracle.

To solve this extremely voluminous work, we took ANTLR4 (a powerful parser generator for reading, processing, executing, or translating structured text), PL/SQL grammar, and created an automatic converter that allows you to convert all objects in the schema and our system from Oracle to working code for PostgreSQL.

Open-source maintainers face many challenges as projects grow. How to write more required features, fix more issues and have time to watch more pull requests? On the example of WAL-G(backup tool for PostgreSQL) I will tell you about how we solved these problems by launching a course of Open-source development at Ural Federal University, what we achieved and what will we do next.

From the very beginning, PostgreSQL in Avito has been solving very important tasks. All the main architecture components are built around DBMS. For over 10 years, the project has been actively evolving, and the infrastructure and architecture have changed a lot.

The talk will start with an overview of how PostgreSQL infrastructure and architecture have advanced in Avito over time and which challenges have been successfully resolved.

Then we'll discuss PostgreSQL usage scenarios in Avito in 2020: microservice architecture, sharding, hosting multiple databases on a single server instance, DBaaS (Database discovery, access control, failover, backup, archive, resource sharing, etc.), integration, and team evolution.
And finally, I'll share our backlog and wishlist.