PgConf.Russia 2017

Database sharding enables a distribution of the database over a large number of machines, greatly improving performance. With the advent of Foreign Data Wrappers (FDW), it's possible to consider a database sharding in PostgreSQL with acceptable level of code changes using FDW. We've been working on enhancing around FDW infrastructure such as foreign table inheritance and pushing down so that PostgreSQL can execute the distributed query efficiently using FDW. In this talk, I'll cover what FDW-based sharding is and what use-cases it can cover. And then I'll demonstrate how to build sharding and describe our achievement of a FDW-based sharding in PostgreSQL community. Finally, I'll describe further enhancements to FDW such as Async Execution and Distributed Transaction Support.

Experience we've got after 5 years of developing, deploying and improving BI system http://colibri365.ru used in government. I would talk about government IT reality and our way over it. Postgres performance improvements, using of latest features, overwriting of user generated queries to help query optimizer and other tweaks and hacks to tackle limited hardware problems. These lead us to number of computer science papers and (now committed) patches to Postgres (see Andrey Borodin talks for details).

VIDEO

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.

I want to present a new custom access method, which extends the current GIN capabilities using additional information stored in posting tree/list. For example, positional information as an additional information allows new AM returns results in relevance order, which could considerably improve execution time of full text queries.