Luigi Nardi

About the Talk

• What will your talk be about, exactly? Why this topic?

  The talk will focus on auto-tuning PostgreSQL on Azure Flexible Server. Tuning PostgreSQL includes several aspects, including query tuning, database management system (DBMS) parameter tuning, operating system parameter tuning, and index tuning. This presentation will specifically address DBMS parameter tuning, focusing on parameters such as work_mem, random_page_cost, and max_parallel_workers_per_gather. I will present an auto-tuning approach that leverages machine learning to automate this process. While this topic has been under exploration for many years, this presentation will showcase a proof of concept for PostgreSQL Azure Flexible Server. The presentation will also delve into how to extend PostgreSQL Flexible Server with parameter auto-tuning capabilities and showcase promising preliminary performance results.

• What is the audience for your talk?

  The talk is targeted at anyone who is interested in performance engineering for PostgreSQL will be the primary target for this talk. We anticipate that the audience will be particularly engaged by the application of machine learning automation especially within the context of a popular database-as-a-service (DBaaS) offering such as Azure Flexible Server.

• What existing knowledge should the attendee have?

  While a beginner audience can still benefit from attending this talk, those with an intermediate understanding of PostgreSQL will likely appreciate the deep dive into parameter tuning and find the overall topic more relevant to their skill set.

• Which other talk at this year’s conference would you like to watch?

  I'm looking forward to Hans-Jürgen Schönig's presentation about uncommon PostgreSQL performance tips; I'm also curious about Tomas Vondra's presentation which gives insights on where the performance cliffs come from due to small changes in data.

About PostgreSQL

• What inspired you to work with PostgreSQL?

  PostgreSQL is an undeniably impressive project. PostgreSQL is not only the database of the year, but also boasts a very strong community, leading to remarkable adoption. PostgreSQL is becoming the dominant database management system. One of its most remarkable features is the extension mechanism, which allows the PostgreSQL engine to become customized to fit a wide range of needs.

• What is your favorite PostgreSQL extension or tool? And why?

  At DBtune, the PostgreSQL extension that proves to be most beneficial for our automated database tuning process is pg_stat_statements. This extension plays a pivotal role in our operations by providing crucial insights into query performance and resource utilization. By capturing and tracking statistics, such as number of calls and total execution time, about executed SQL statements, pg_stat_statements empowers DBtune’s AI agent to make informed decisions about runtime optimization and resource allocation.

  The collected data allows us to identify hardware bottlenecks and potential areas for improvement within the database environment. This, in turn, enables us to fine-tune to allocate resources more effectively, resulting in enhanced database performance and responsiveness. Overall, pg_stat_statements serves as an invaluable tool in our arsenal, enabling us to deliver optimal database tuning solutions to our clients, ensuring their PostgreSQL instances operate at peak efficiency.

• Could you share a memorable experience or challenge you faced while working with PostgreSQL?

  Benchmarking remains a significant challenge. As a community, we urgently need more realistic benchmarks that reflect the actual behaviors of production PostgreSQL systems. Throughout my career, I have created four benchmark suites in other application areas (SLAMBench, DAWNBench, LASSOBench, CATBench), and I understand the immense effort involved. However, the widespread adoption and appreciation within those communities demonstrate the value of such endeavors.

  Benchmarking is an excellent way to hone your PostgreSQL expertise and contribute to the development of autotuning database functionalities. As database management systems become increasingly autonomous, new benchmarks and datasets are crucial for experimentation and innovation. The PostgreSQL community will greatly benefit from a new benchmark suite specifically tailored to these evolving systems.

About POSETTE & Events

• What other PostgreSQL events in 2024 are you excited about?

  We’re excited to be part of the following upcoming events this month:

  • PGDay France (June 11-12, 2024): The DBtune team will be there and sponsoring the event! We're leading a training session on optimizing PostgreSQL performance through parameter tuning.
  • Swiss PostgreSQL Conference (June 27-28, 2024): We'll be sponsoring this event and sharing our insights in a talk titled "Lessons learned from auto-tuning PostgreSQL."

  We also can’t wait to connect with the European PostgreSQL community at the 14th Annual PostgreSQL Conference Europe in Athens! We're thrilled for the opportunity to learn from fellow enthusiasts, and collaborate on shaping the future of PostgreSQL.

• Could you share a memorable moment from a previous PostgreSQL conference you attended or spoke at?

  We’re proud of our commitment to fostering PostgreSQL knowledge within the community. Our free training initiatives, offered at numerous PostgreSQL events throughout the year, have equipped hundreds of developers with valuable skills and insights. By empowering individuals to excel in their roles and contribute effectively to the open-source ecosystem, this aligns perfectly with our mission to make a meaningful impact on the PostgreSQL community.