PgConf.Russia 2020

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.

Odyssey is a new PostgreSQL connection pooler designed for the high-load environment. It can significantly scale processing performance by specifying the number of additional worker threads. This allows to increase SSL/TLS performance and provide more balanced pooling control using by sharing global server connection pools. In this talk I will answer 4 questions: 1. When you should use connection pooler? When Odyssey is the most optimal choice? 2. How to set up a cluster with Odyssey? 3. How to sleep well and be sure that everything works? 4. How does the architecture of Odyssey look like? How to implement or propose feature that you want?

· Enterprise network traits, known problems and workarounds.

· Year-long background of maintaining 24x7 1C + PostgreSQL systems

· Pros and cons in comparison with MS SQL from DBA point

· Stories of 1C systems migration from MS SQL to PostgreSQL

To build a decent query plan, the optimizer has to understand statistical characteristics of underlying data. It is interesting to observe how the structure of the collected information became more complicated over time: what the optimizer relied on back in its early days and what is at his disposal now with the release of the 12th version. We will also talk about how and when statistics are collected, how to manage this process and whether it is necessary to think about it at all.