PgConf.Russia 2019

As any ordinary software developers, we just pursued a goal to develop a system robust for high loads, and even succeeded. The system architecture was fine, but the data volume was keeping increased and revealed the painful issues and errors that nobody had expected. We faced very strange queries seemed to be unbelievable. In my short talk I would like to share sad experience of arised-from-nothing high loads in DBMS and solving the challenge.

When you are optimizing Postgres it is usually maintenance that goes by the wayside. How do we fix autovacuum? Where did all of this bloat come from? Why am I getting IO spikes? How do I get RDS to behave?! Why are commits so slow on replication? The answer to all of these questions is understanding the relationship between proper Postgres maintenance and performance. Join us for a 3 hour jaunt through the wily world of making Postgres Go!

So you finally have your database model for your application and you fill it in with current data. How do you keep it up to date? While INSERT may still be transparent, UPDATE and DELETE will overwrite your previous data, so you won't be able to reproduce them. Cloning the whole huge content for each minor update is not an option. For rich and complex data about hundreds of thousands of power generators in Germany and worldwide, I built a model using range data types in recent PostgreSQL which allows me to insert, update and delete data while granting the full access to the whole state of the database at any historical moment. I'll present a very simplified version of the database so the audience will be immediately able to apply it for their cases. I'll also show a few tricks in Python and Psycopg2 that will allow a whole team to prepare, review, and deploy all revisions to this database without merge conflicts. And I'll give a few ideas on how to retrieve this data efficiently.

Virtualization in companies has become an alternative to the conservative "one task-one server" approach, which allows efficient use of hardware resources, centralized management of server infrastructure, saving energy and cooling resources. The report explains how to configure the VMware environment for intensive input / output PostgreSQL and profiling tools virtual infrastructure to monitor performance and resolve identified problems.