Carnegie Mellon team claims vector-based system can turbocharge PostgreSQL

Automated database systems based on vector embedding algorithms could improve the performance of default settings on common PostgreSQL database services by a factor of two to ten, according to a database researcher.

Speaking to The Register, Andy Pavlo, associate professor at Carnegie Mellon University Database Group, explained that the problem of automating the options for database tuning and optimization – a long cherished skill of DBAs – related to the fact that it was difficult for a single model to get to grips with all the parameters in one go.

While experienced DBAs might have the experience to tune system performance, developers building modern systems tend to call on a database service from a popular hyperscaler – AWS's relational database service (RDS), for example – and are less likely to understand how to tune it.

Databases offer those building systems a huge number of choices to try to get better performance. They come in four main groups, Pavlo said. They include system knobs such as runtime parameters and memory caching policies, physical design such as the data structure or index types, query tuning options that control how a database is going to execute a query, and lastly life cycle management involving long-term decisions over when to upgrade software or hardware.

While machine learning techniques have built agents to try to solve these problems individually, addressing them as a whole leads to a phenomenal number of choices and combination of choices, many of which are interdependent. Earlier studies have tried to figure out an optimal sequence for these tunings, but they found the solution can depend on workload, and the choices made along the path to a solution mean the best one can be missed.

Pavlo's team wanted to tune all the agents at once, but the problem space is so huge, any system runs out of compute time before it figures it out, not least because the system has to run the queries before it finds out if it is getting to an optimal solution.

To solve this problem, the team looked at a 2016 Google paper that proposed using prior information about the actions to embed them in a continuous space upon which it can generalize. This was dubbed the Wolpertinger architecture, named after a mythical creature from German folklore, and it uses vector embeddings to measure the similarity of earlier actions, in the same way LLMs use them to judge the similarity of words.

Taking this approach, Pavlo's team built a so-called holistic tuning agent branded Proto-X to try to get an optimal output for all possible choices for tuning the database all at once, and not have to run all the individual tuning agents.

"You create an encoder to convert the configuration of the database into a feature vector and put that in a high-dimensional latent space. You also train a decoder that then can take that feature vector that the embedding comes from and put it back into the database configuration," he said.

The reinforcement learning algorithm can learn how to rank the database tuning choices and decide to do more exploration or exploit what it has already seen before, converging on a better configuration, Pavlo said.

Running the Proto-X tool might take 12 hours to come up with "amazing" results, Pavlo said. But that can be reduced by employing an LLM-based "booster," which takes the training data of other similar databases and identifies the performance profiles that are similar to the database in question.

"Our new LLM boosting provides the knowledge transfer to cut that 12-hour time down to around 50 minutes," Pavlo said.

• Lance takes aim at Parquet in file format joust
• AI devs close to scraping bottom of data barrel
• Fork yeah: Valkey 9 sharpens edge against Redis
• Google fuses SQL, Python, and Spark in Colab Enterprise push

The subject of a recent paper, the LLM booster can also respond to time limits and the current state of the database in urgent situations.

"If your database is on fire, you don't want to run an algorithm that might take an hour to compute some fix for it," Pavlo said. "You want to run something right away to try to mitigate the problems. Then once things are stabilized, it can then run a long-term algorithm – the holistic one using LLM booster – that takes more time, but provides the preventive maintenance you need to make sure problems don't happen in the future. So that's the big game-changer."

The idea of a self-driving database might be critical, not just for developers with little experience of database management, but as the concept of "vibe coding" spreads.

"I'm confident that with the addition of LLM boosting, we're at the point where we can achieve fully self-driving database systems that don't need any human touch," Pavlo said. "That's absolutely critical in the vibe coding era where there's a bunch of agents generating applications that humans never need to look at."

Pavlo is establishing a new company, which will start by providing a technology for PostgreSQL database services to offer both holistic tuning and LLM boosting. Called So You Don't Have To (SYDHT), it is expected to launch next year.

With the Wolpertinger-based Proto-X, users could get a 10x performance boost on standard database service settings for PostgreSQL, he said. ®