PGConf.Russia 2022

I am going to explain a sorting method added in Postgres 14 that allows to create GiST index much faster. We'll also talk about the disadvantages of this sorting method discovered during implementation of its support in PostGIS and how it will be improved in the future. Also, new features and improvements included in PostGIS 3.2 will be reviewed.

This talk is about our work on detailed vacuum workload statistics collection by the statistics collector and about problem solutions and benefits it can provide.

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.

Replication is one of the important mechanisms designed to provide database fault tolerance and scalability. In our practice, we regularly encounter the problem of low replication performance. This prompted us to investigate the factors that affect the speed of physical replication. In this presentation, I will talk about our findings. I will also demonstrate the differences in replication performance for various versions of PostgreSQL.