PGConf.Russia 2022

A modern database should be capable of storing big values. Storing itself is not a big deal, however, operations with big values or fields are a non-trivial task. PostgreSQL has several options for storing big values, but none of them is perfect. How do we respond to this challenge? Our presentation answers this question, let's see how to store big and complex values in Postgres properly, and how operate with them.

PostgreSQL has a number of peculiarities that you need to take into account not only while maintaining your database but also when designing your database schema. Experienced PostgreSQL are well aware of vacuuming process. On the web one can find tons of materials covering its internals, configurations and monitoring. Many valuable talks about vacuum were given at numerous conferences. However, we still face the common wraparound problem when the maximum possible number of transactions (xid) is reached. It happens even on databases that are relatively small in size. In my presentation, I will share a customer case that looks interesting to me. A chain of mistakes made at different stages of the database's life cycle once caused a disaster. The database fully stopped for one week, we detected a wraparound and spotted corrupted blocks. Maintenance was problematical, and we spent sleepless nights in search of a solution. We managed to achieve a local win as we finally restored the database, but it's not the end of this story, which makes it even more interesting.

The volume of data stored in Postgres Pro databases is growing rapidly. In some organizations, such databases have already exceeded tens of terabytes in size. The existing approaches to backups don't ensure timely backup and restore of data in such cases. We have supplemented the well-known and well-tested Postgres Pro backup tools with functionality that will allow users to backup and restore large databases. In addition to that, we have created our solution with the convenience of DBAs in mind.

Mamonsu is an active monitoring agent for PostgresSQL based on Zabbix. We are actively developing the agent: there are new unique metrics and visualization capabilities. But in addition to metrics collecting mechanism, Mamonsu has other useful features. I am going to briefly describe these features also known as "Mamonsu tools", the agent's advantages and the installation process.