PgConf.Russia 2020

This talk discusses the open source components we use at Adjust to manage a massive number (5+PB) of application log messages on PostgreSQL in a massively multi-parallel way. It provides both a use case for PostgreSQL in a big data (high volume/velocity/variety) environment, and can be used to show the power of PostgreSQL with JSONB, GIN, and more.

This talk covers the capabilities of the components in depth, sufficient to inspire similar solutions.

I like stored procedures - it is great technology. But like any other technologies it allows to write not well optimized code. It is not easy to write optimized code, sql statements in complex large applications. On second hand, there are some tools, that can be used very easily, that can help. Postgres has built-in tracking functions possibility. There are PLProfiler and plpgsql_check. With these tools is easy work to detect slow part of applications.With this knowledge, the fix of performance issue is less magic.

When migrating data from one DBMS to another, the question arises: choose a third-party tool or to program the migration yourself? Companies, trying to grow competencies within themselves, choose the second option. And they come across the "invention of their own bicycles". However, the market has powerful free data migration tools. One such tool is Pentaho Data Integration, part of the Pentaho Community Edition. The report will discuss the use of this package for data migration between Oracle and PostgreSQL. Particular attention will be paid to the problems with using this tool, and to the tasks of testing for the completeness and integrity of migrated data.

Small video illustration:

PostgreSQL is one of the most readily extensible databases in the world. Custom data types, aggregations, functions, and more can be easily and safely written in C.

This hands-on tutorial covers the basics of writing functions, data types, and aggregates in C. It is recommended (though not strictly required) that bring their own laptops and be prepared to actually try the exercises.

Topics covered include: 1. The PostgreSQL type system 2. Creating a simple custom type in C 3. Common errors for non-C programmers 4. Creating a simple aggregate in C

There will be many opportunities for questions and discussion through the tutorial.