PgConf.Russia 2017

ZSON is a PostgreSQL extension for transparent JSONB compression. Compression is based on a shared dictionary of strings most frequently used in specific JSONB documents (not only keys, but also values, array elements, etc). In some cases ZSON can save half of your disk space and give you about 10% more TPS.

VIDEO

In the data persistence layer, using PostgreSQL from the very start of development, we went all the way from a small cluster on a virtual machine to a multi-host system that provides near real-time processing of mixed OLTP/OLAP load. In this talk, I’m going to tell you about the main development stages of our analytical solution at the application and infrastructure levels, and describe the specifics of using PG that we encountered.

VIDEO

Currently, to execute SQL queries PostgreSQL uses interpreter, which implements Volcano-style iteration model. At the same time it’s possible to get significant speedup by dynamically JIT-compiling query “on-the-fly”. In this case it’s possible to generate code that is specialized for given SQL query, and perform compiler optimizations using the information about table structure and data types that is already known at run time. This approach is especially important for complex queries, which performance is CPU-bound.

The talk will describe how we have implemented storage of large tables (+1 billion rows per day). The project exists in production 2 years. The total amount of data - 300 Tb (25 PostgreSQL servers * 2 Data Center). I'll tell about mistakes in organization of large tables storage in the initial phase of the project, and how these mistakes were corrected. I'll also talk about how to organize the data rotation and archiving. I voiced questions about what we were missing in PostgreSQL 9.4 out of what appeared in the 9.5 and 9.6. And also, what new features we are waiting for new releases of PostgreSQL.