Open Sourcing AutoDBA for PostgreSQL

Introduction

We created AutoDBA motivated by the desire to democratize access to PostgreSQL expertise. We believe everyone who runs PostgreSQL should have easy access to the expertise needed to run it reliably and efficiently, regardless of their team size, budget, or prior experience with PostgreSQL. We are taking an important step toward realizing this vision: releasing AutoDBA as open source software under the Apache 2.0 License. We encourage you you to visit the AutoDBA GitHub repository.

Unfortunately, the need for database expertise can outpace the team’s bandwidth and skills at organizations of all sizes. The problem takes various forms—one day, a developer might be chasing down a bad query plan or a missing index; another day, it might be a platform engineer guessing at the right instance size to support growth. Even in teams with dedicated DBAs, these people can get so caught up in maintenance that they have little time to help the team add new features.

Managed database services are a step in the right direction—but sadly they make you do too much management. Services such as Amazon RDS, Google Cloud SQL, or Azure Database for PostgreSQL have automated some of the error-prone and time-consuming aspects of running a database. You can rely on them for initial installation, configuring backups, and handling failures. However, you remain responsible for many of the hard problems. These include deciding how many resources to provision to meet growth needs, tuning query plans and providing optimal indexing to ensure performance and efficiency, tuning housekeeping jobs such as vacuuming to ensure stability and consistent performance, configuring redundancy to meet reliability objectives, and setting hundreds of configuration parameters that can have interacting impacts.

If these problems could be solved with straightforward scripts, the managed database services would already have done so. The managed services have left these problems to you and your team because they involve context-dependent decisions. Relevant details include details of the application, such as the exact queries processed by the database, how often they run, and how the database content grows over time. Many choices also pose trade offs, for example, between performance and availability. Translating these various inputs into management decisions is AutoDBA’s job.

AutoDBA for PostgreSQL is ambitious, but its aims are entirely within the reach of modern AI. We are building toward this future in the open, and welcoming feedback and collaboration.

Architecture

We designed AutoDBA to manage PostgreSQL anyplace this database runs. You can use it with cloud managed databases such as Amazon RDS, Google Cloud SQL, Azure SQL, and others. You can use it with PostgreSQL running on a cloud server, Kubernetes, an on-prem server, or your local development environment.

AutoDBA connects to the PostgreSQL instance under management via a dedicated monitoring user to obtain access to PostgreSQL information, including statistics collected in internal views. When connected to PostgreSQL running in a cloud managed service, AutoDBA uses the cloud APIs to obtain system metrics. In other situations, an agent running on the database server collects these metrics directly from the operating system.

The metrics that AutoDBA processes involve four main categories:

• System metrics. Generally exposed through the operating system, these metrics tell AutoDBA about activity on the server. Examples include CPU utilization, memory utilization, network activity, and storage activity.
• Database metrics. PostgreSQL maintains numerous internal views containing counters measuring successful transactions, errors, or breakdowns of I/O activity, among other things. There are also reports of vacuum subsystem activity, buffer cache state, and per-query statement statistics.
• Database activity. Every 15 seconds AutoDBA probes the database to see what work is currently running. Collecting many such point-in-time measures allows it to build up a detailed picture of activity on the database. This data often makes bottlenecks such as lock contention readily apparent.
• Logs. Logs are a source of critical information about error conditions. When configured to do so, PostgreSQL can also log information about slow-running queries, which is useful for later analysis.

Observability first

The first releases of AutoDBA provide a foundation of observability. It logs and stores information about the database activity, but it does not yet include the AI-based analysis that CrystalDB has been developing.

Even though AI does not need graphical visualizations to see what is going on in the database metrics, we decided to add human-friendly dashboards to AutoDBA. This helps ensure that you, too, can understand what is happening in the system.

AutoDBA’s visualizations also help fill the gap between the quality of PostgreSQL observability available in commercial software and that available in open source.

It might have been more appropriate to release the first few versions of this AutoDBA under a different name, one signifying the emphasis on observability and absence of automation. We considered this approach but decided that maintaining a single project would make more sense. These initial releases fall far short of AutoDBA’s promise, but we promise this shortfall will be short-lived.

AI-based recommendations for PostgreSQL management

Before allowing AutoDBA to operate fully autonomously, you will want to build trust that its actions are reliably correct.

In the first AI releases, AutoDBA will be limited to read-only access to the database and the cloud environment. Instead of acting independently, it will tell you, “Here’s what AutoDBA would do.” You will then have a chance to review the proposed action and can apply it if you choose.

We will share a lot more about how AutoDBA uses AI to make recommendations in the future. The details are out of scope for this post and we are continuing to validate our approach.

We will share that if you are looking for LLM-based techniques, you will have to wait a little bit. Large language models (LLMs) are well-suited to many database management problems. They excel at manipulating code, including SQL, they are good at processing instructions or providing explanations in natural language, and are great at storing vast knowledge bases and making them accessible. However, LLMs are not the best tool for all jobs, and they are not the only cutting-edge AI technology today—notably, autonomous vehicles have achieved success without LLMs.

We are focused on a model-based approach to managing PostgreSQL that will allow us to guarantee that AutoDBA is reliable enough to manage mission-critical database infrastructure. We are running experiments to map out the safe operating conditions for PostgreSQL and developing modeling techniques to map these onto any workload and system configuration.

As a simple example of this, we know that there are severe consequences if a database tries to use more memory than is available in the system. PostgreSQL can use memory in many ways: caching, buffering, work memory, and process memory of various kinds. AutoDBA must never tell PostgreSQL to use more memory than it has available. This is the sort of behavior that regression models can learn and reliably predict.

Protecting sensitive information

PostgreSQL databases often store sensitive data, such as personally identifiable information (PII), which is usually subject to strict regulations. Just as human DBAs don’t need to see this information to do their job, AutoDBA follows the same principle.

To ensure that AutoDBA does not process or store PII, we take a blanket approach to filtering that preserves the structure of tables and queries but filters out the specific values within them. We do this using the time-tested open source libraries developed by pgAnalyze.

Contributing

CrystalDB welcomes collaborators on the AutoDBA project—we have no monopoly on good ideas or the ability to make them happen.

There are many features that it will take our team some time to get to, even though they are important to some users. These are good candidates for outside contributions. Examples include support for various on-prem PostgreSQL configurations, non-Linux operating systems, and configuration of various PostgreSQL extensions.

We also welcome contributions to our library of workloads. By adding your use case, you ensure that we continue to optimize against it on an ongoing basis.

Open source vs commercial product

We want to be transparent about how we keep AutoDBA freely available under an open source license while paying back the investors who funded its development.

Extending the open source product, CrystalDB builds a hosted version of AutoDBA that contains additional valuable features and support in enterprise settings. Examples include fleet management across multiple regions, enterprise-grade compliance and user management, backstop support from human PostgreSQL experts, and AI models optimized for high-end hardware.

Summary

We developed AutoDBA with the vision of democratizing access to PostgreSQL expertise, making it accessible to everyone who runs PostgreSQL, regardless of their team size, budget, or prior experience with PostgreSQL.

Our open source software, released under the Apache 2.0 License, aims to provide the expertise needed to run PostgreSQL reliably and efficiently.

AutoDBA connects to PostgreSQL wherever it runs: cloud managed database services, cloud virtual machines, Kubernetes, on-prem servers, or local development environments.

Our initial releases includes observability and basic human-friendly dashboards for understanding system performance. This is the foundation for our subsequent releases, which will deliver AI-powered recommendations.

We are open to feedback and collaboration as we continue to develop and incorporate AI-based analysis. Please visit the AutoDBA GitHub repository—try it, star it, and give us feedback.