PgConf.Russia 2017

It's not a secret that PostgreSQL connections are expensive so you should save them. To solve this problem there are PgPool-II and PgBouncer for quite a long time. At Yandex tens of thousands of connections to a single database is not a surprise so we use pgbouncer since time immemorial. This talk gives an overview of problems we faced and ways to solve them.

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.

With big data threads, even the upload of data to a database can often be problematic – apart from the data upload itself, you need to create indexes, perform VACUUM after the upload for correct Index-only scans, etc. From this talk, you will learn how to avoid most of such problems (if not all of them).

VIDEO

1. What is an autonomous transaction?
2. An overview of autonomous transactions in "big" DBMS: Oracle.
3. Autonomous transaction logic in Postgres Pro.
4. An overview of emulation methods for autonomous transactions in PostgreSQL.
5. Comparing performance of the built-in Postgres Pro autonomous transaction mechanism and PostgreSQL emulation methods.

VIDEO