Everything you wanted to know about PostgreSQL. PG BootCamp Russia 2024 held in Kazan

The event was opened by Vadim Yatsenko, CEO of Tantor Labs and member of the PG BootCamp initiative group, along with Mikhail Goldberg, chairman of the PostgreSQL Code of Conduct committee and PG BootCamp initiative member. They talked about the history of the PG BootCamp movement. The Russian community of PostgreSQL DBMS professionals continues to expand. The first event in October 2023, held in Moscow, attracted approximately 1,000 attendees from 16 countries either in person or online. In April 2024, the gathering in Minsk saw more than 1,600 Russian-speaking participants. This time, the event in Kazan boasts over 2,600 registrants from 25 countries.

The audience was in for a pleasant surprise - a video address by Bruce Momjian, co-founder and core team member of the global PostgreSQL development group. In his message, Bruce welcomed the participants and expressed confidence that despite challenging times, the community's activities won't cease.

Kirill Borovikov, Lead Database Architect at Tensor, delved into useful SQL constructs in PostgreSQL. He highlighted SQL query syntax features like ARRAY array constructors, WITH ORDINALITY, IN, ANY, ALL, TABLE, CASE operators, COALESCE and NULLIF functions, UNION ALL, LATERAL and others, that contribute to writing more compact and efficient code.

In his talk, Konstantin Vashchenkov, Technical Director of XSQUARE, explored the possibility of implementing the low-code approach in Postgres: creating a database-centric application and essential paid and free PostgreSQL extensions required to transform PostgreSQL into a low-code tool. The leading player in the global LCDP market is Oracle Corporation, offering OracleDB as the core platform and four products as an add-on: Application Server, RESTfull API server, reporting server and integrated development environment, which allows the developer to program using only one language - SQL. With the right extensions, schedulers, migration tools, external data wrappers, and procedural languages, similar capabilities can be achieved in PostgreSQL. Knowledge of JSON variations is critical for effective low-code usage.

Tantor Labs developer Sergey Soloviev shared his expertise on debugging Postgres scheduler. He talked about the principles of its design and basic data structures. Scheduling is one of the stages of handling requests, broken down into Preprocess, Optimize, Find Paths and Create Plan. The theoretical part of the lecture was followed by a workshop session, and in conclusion the speaker gave helpful debugging tips.

Maxim Milyutin, openGauss and PostgreSQL contributor, shed light on the intricacies of the shared buffer, also known as the buffer manager, which is one of the most ‘hot’ spots in a heavily loaded DBMS. A shared buffer is a chunk of memory created to quickly access pages that are stored on the disc. The speaker described buffer operation based on the simplest models and the current state in Postgres, touching upon complex and cryptic code sections, examined processes around the buffer,and reviewed current and prospective monitoring and debugging tools, alongside implementation details and optimization techniques.

Alexander Nikitin, a leading database administrator at PGMechanix, presented the best practices aimed at reducing DBA's routine tasks. These include PuTTY and its more advanced versions Kitty and SuperPutty, hotkeys for automated execution of the frequently used commands, and note-taking software like Joplin and Obsidian. Alexander emphasized the importance of continual self-development using various sources of knowledge: software documentation, conferences, profile groups in Telegram, RSS subscriptions to websites, books.

Tantor Labs developer Artem Bugaenko spoke about using vector processing of data arrays to optimize Postgres performance. Unlike the classical SISD architecture, SIMD (Single Instruction Stream & Multiple Data Stream) vector architecture allows performing a single arithmetic operation on multiple data elements of a vector. The concept of SIMD dates back to the last century, with its first iteration appearing in 1997 on the Intel Pentium MMX processor. The technology has since evolved in terms of vector expansion, optimisation, and new commands have been added. MMX (MultiMedia Extensions) was succeeded by SSE (Streaming SIMD Extensions) and later by AVX (Advanced Vector Extensions). Today, SIMD is successfully used to optimize PostgreSQL performance, among other things. In the practical part of the presentation the speaker demonstrated the differences between sequential and vector processing of data arrays on CPUs, talked about specifics of using SIMD and AVX based on Postgres and demonstrated methods to achieve performance gain up to 64x.

Artem Sergienko, technical manager of Tantor Labs support department, dedicated his report to Postgres database security - auditing DBMS security and addressing identified issues. The CIS Benchmark for PostgreSQL standard helps in this, along with automated tools, such as Timescale/Pgspot for extension checks, Pgaudit and PgBadger for auditing all database events, Pgdsat for verifying PostgreSQL cluster security parameters against the standard. The speaker emphasized the significance of regularly conducting security audits in companies, ensuring proper documentation of audit results, timely updating database management systems, developing action plans, and constantly enhancing security.

Andrey Borodin, PostgreSQL contributor, head of the Yandex Cloud open source RDBMS development team, compiled all current practices of the PostgreSQL community into a single report to systematize approaches to PostgreSQL kernel development.

Participants had the opportunity to attend another series of presentations, which included reports by Maxim Ibragimov and Denis Rodionov from Tantor Labs discussing how to anonymize data using pg_anon, and by Maxim Bagirov, a DevOps engineer at Tantor Labs, who addressed deploying a fault-tolerant cluster with backup and replication to S3 cloud storage. Both sessions were held in a workshop format.

Beyond the official program, PG BootCamp Russia Kazan attendees were invited to participate in quizzes on PostgreSQL from the event’s partners, particularly from the main partner – Tantor Labs. Discussions on the intricacies of working with DBMS continued during informal gatherings.