PGConf.Russia 2022

About 7 years ago, I moved from Windows-only development lead by an irresistible desire to add some parts to PostgreSQL. In this talk, I would like to cover things that were not obvious to me when I started working with the source code, the PG build and testing system. I'm going to talk about the simplest issues - routine IDE tasks, navigation, build and release process, and similar stuff. What I will share isn't the one and only truth. Some topics might relate to dummy-level problems :) I would be glad if other developers also shared the secrets of their development life. We can arrange a discussion of grep vs IDE :)

Usage of temporary tables in PostgreSQL causes additional issues like high server resources consumption and low query performance. However, sometimes you need to rely on them especially when you need to migrate your code from MS SQL, and your initial code employed them, and you had some logic implemented at the database level. This talk covers issues related to usage of temporary tables when migrating from MS SQL, and the ways to resolve them using built-in PostgreSQL features based on the scenarios contained in the code.

The plv8 extension is very underestimated, though it can help simplify development for a variety of tasks. It's been a year since we started using this extension in our projects. The main reason for its low popularity is the complexity of building. Moreover, building plv8 requires a lot of traffic and disc space for temporary files. That's why we decided to build plv8 and provide ready-made binaries freely to anyone interested, not just as an offering for our customers. First, we made the binaries available on GitHub. Then we created a Docker image for Postgres with pre-installed plv8 based on Debian. It looked too heavy to us, and we decided to check Alpine, however, building it for Alpine appeared to be more complicated than we expected. I'll explain what difficulties we faced and how we managed to overcome them. I will also list the tasks that can be solved with plv8 and provide advice on how to make the development process more convenient. I will also share the links to our Docker images for the latest Postgres 13 and Postgres 14 releases, that are compact and can be used for any types of tasks.

Every DBMS stores its data on a hard disc, so you may face a situation when your data on disc gets corrupted. This can happen due to a controller failure, logical or physical data corruption; there are also other reasons. If it is just the index file that gets corrupted, the index recreation command will enable you to restore data consistency in your DBMS. If a table file or a file of a system section gets corrupted, data restore is impossible. In this case, you need to invent workarounds. You can try to restore this data from backup files, copy it from the corrupted table, or find another way to solve this problem. In this talk, we'll tackle several cases of data corruption on disc and describe the options for restoring the data from the corrupted tables.