PGConf.Russia 2021

When PostgreSQL was at its formation stage, its transaction ID was chosen to be 32-bit. Back then, no one could imagine that someday we may need more than 4 billion transactions. However, ongoing technical progress and digitization pushed some Postgres instances towards their transaction ID limit. The Postgres community reacted to this with a wraparound of the transaction ID counter. However, constantly growing data volumes exposed PostgreSQL to new challenges. In my presentation, I will cover these challenges and explain how they can be solved with a 64-bit transaction ID, what the consequences will be like, why now it is a good time to implement 64x IDs, and why this hasn't been done previously.

What can be more important in the data load process than speed? Data migration is one of the most requested features in DBeaver. So the performance issue was highly important for us, in regard to PostgreSQL, as well as Greenplum, Redshift and other Postgres-based databases. We are ready to share our tiny secrets about 10x, 100x, 1000x, and even 10,000x performance improvements for data imports without any magic.

When working with PostgreSQL, we regularly encounter queries that take suspiciously long to be executed. What if it's too late to enable statistics or auto explain, and cancellation of the query doesn't look sensible, but you still need to find out what caused the issue or see at what stage execution the query is? pg_query_state is an open source PostgreSQL extension, which can give you access to the query execution plan in real time. It also makes available statistics for each node, including the number of processed rows.

Finding the nearest neighbor can be required for various tasks. For example, when you need to find the closest object to a given point on the map. This task looks trivial to non-programmer (a person can easily cope with it if they have a map). In a software developer's reality, this task doesn't have a common solution available to everyone. To get rid of this headache, programmers often create ad hoc solutions also known as "crutches". These workarounds don't look nice and often ruin the mood of a creative programmer who needs to go to a beer pub to cope with the cognitive dissonance :)

Indeed, while a person has a typical field of view and a map with a certain scale, the programmer has only one given point and a huge number of other points (i.e. billions of stars). This multitude of points gets a lot of incoming requests, including the write requests, not just read ones. You can write a perfect query in SQL, however, the real-world query execution plan will be depressingly long. To find the closest neighbor, you will have to read the entire table, compute all the distances from the given point and return the given number of good enough results. Indexing doesn't help in this case, as you will have to fully scan the search tree and read the entire table in random order. This will take much longer than simple table reading. In reality, tasks, where you need to efficiently find nearest neighbors, aren't limited to spatial search. It can also be used for classification tasks, finding typos, data clustering, and deduplication. All such tasks will benefit from efficient nearest neighbor search in DBMSs that are now a de facto standard for storing the data. What do we mean by "efficient search"? It means that our search is fast, concurrent, scalable, and supports various data types (most likely, non-standard ones). We implemented such KNN search in PostgreSQL 11 years ago. I will cover its implementation, today's state and share some use cases for KNN.