POSETTE 2024 is a wrap! Missed it? Watch all 42 talks
Speaker:
Luigi Nardi
Founder & CEO at DBtune
Luigi Nardi is the founder and CEO of DBtune (www.dbtune.com), a leading company driving advancements in AI, database systems, and cloud computing. Previously an associate professor of machine learning at Lund University and a research staff at Stanford University, Luigi's expertise centers around black-box optimization theory and practice. Luigi's journey includes a post-doctoral position at Imperial College London and a role as a software engineer at Murex S.A.S., following his Ph.D. program in applied mathematics at Université Pierre et Marie Curie in Paris in 2011.
POSETTE 2024 Talk
Autotuning PostgreSQL on Azure Flexible Server
We'll explore the intricate world of PostgreSQL parameter tuning, where the database management system reveals a multitude of configurable parameters that govern its runtime behavior. As the system evolves, the increasing number, the non-linear relationship and the complexity of these parameters highlight the importance of their correct configuration in optimizing application performance and cloud resource utilization.
This talk delves into various tuning approaches. We'll start with the traditional, meticulous method of manual tuning, relying on expert analysis of metrics like query response times and resource usage. We'll then explore heuristic-based tools like PGTune and PostgreSQL Configurator, offering one-size-fits-all solutions. Finally, we'll set our sights on the future: auto-tuning with machine learning. We'll showcase how these more sophisticated methods can be customized to a specific workload and machine resources.
We'll highlight the challenges and the advantages of transforming PostgreSQL into a more self-driving database management system and show a proof-of-concept on auto-tuning PostgreSQL Azure Flexible Server with some preliminary performance results.
Speaker Interview
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.
Subscribe to notifications to keep up with POSETTE news
Join the conversation
Use the hashtag #PosetteConf
The Postgres team at Microsoft is proud to be the organizer of POSETTE: An Event for Postgres (formerly Citus Con).