Why
A while ago I wrote this article: Diagnosing and fixing critical PostgreSQL performance issues: A deep dive. In it, I share my experience diagnosing and fixing a PostgreSQL database with serious performance issues. I walk through several hypotheses until reaching the solution. I recommend giving it a read if you're into case studies and relational databases.
After that work, I got a bit thoughtful. It was an avoidable problem. With the right monitoring, it would have been noticed earlier that operations were causing overhead on the database. This would have saved a good few hours of work from developers diagnosing and fixing the issue.
I looked into some open-source software, but none solved 100% of what I wanted: a self-hosted observability and monitoring tool with a unified data catalog. Something close to what pganalyze delivers, but that the team itself could run and control.
I found it odd not to find this. It's the kind of tool that I, as a data engineer, would love to have. Not to replace an investigation, but to help understand what the database is saying before rushing to change configurations, create an index, or scale the machine. That's when I decided to write PGWarden.
I chose PostgreSQL because it's the database I'm most familiar with under the hood. The project's goal is to observe the database: collect signals, keep history, relate the workload to the schema, and bring together in one place information that is usually scattered across pg_stat_* views, dashboards, migrations, and different people's heads.
In this article, I won't justify technical or architectural decisions, nor will I go over the development challenges. I'll show you how to get PGWarden up and running, what it observes, and, most importantly, what kind of questions each part of it helps answer.
The data in the prints are not mocks or sensitive.
Initial setup
To run PGWarden, you need Docker and Docker Compose, plus network connectivity between the machine where the Collector will run and the PostgreSQL instances that will be monitored.
The basic path is this:
git clone https://github.com/pedrohgoncalvess/pgwarden.git
cd pgwarden
cp .env.example .env
docker compose up -d --build
Before spinning it up, configure the .env file. The most important values are ENCRYPTION_KEY and JWT_SECRET_KEY, used to protect credentials stored by PGWarden and the platform's authentication. Once the stack is up, the dashboard will be at http://localhost:3000 and the API docs at http://localhost:8080/docs.
After that, simply register a PostgreSQL connection. The Collector discovers the databases in that instance and starts collecting the configured data.
Low overhead and no modifications to the monitored database
This is one of the project's most important philosophies: PGWarden must not change the database it monitors. It doesn't create extensions, tables, triggers, indexes, roles, or any other objects in the target PostgreSQL. It also doesn't execute INSERT, UPDATE, DELETE, ALTER, CREATE, DROP, VACUUM, or ANALYZE on it. Collection is based on reading metadata and statistics exposed by PostgreSQL itself, meaning DQL operations.
This has practical consequences. PGWarden can be connected to a database for an investigation, removed later, and the database remains exactly as it was. It also means that a recommendation from Analytics doesn't become an automatic change. If the project suggests investigating an index, for example, it's pointing out a hypothesis to be validated, not running a CREATE INDEX in production.
Low overhead doesn't mean zero cost. Querying pg_stat_activity every few seconds, collecting active queries, and keeping a history has a cost. That's why collectors can be configured individually. Sessions, locks, and active queries can be collected at short intervals when you need to closely watch a critical database. Table, column, and index metadata can be updated less frequently. The idea is to choose the level of observation that makes sense for that specific environment, rather than using the same configuration for every database.
Customizing the services
You also don't need to run all services to use PGWarden. The Collector brings in metrics and metadata. Analytics adds query analysis, table/column usage, and recommendations. The Notifier comes in when you want to receive alerts via Slack, Discord, Teams, or email.
In a dev environment, it might make sense to just use the catalog and some metrics every now and then. In production, it might make sense to collect locks, sessions, and queries more frequently, because a wait of just a few minutes can already have an impact. The project was designed with these differences in mind.
If you feel it doesn't make sense to collect a certain piece of data so often, you can change this via the interface or the config.yaml. Values are in seconds. For example, this server uses a more conservative collection: metadata every hour, sessions and locks every 10 seconds, active queries every 5 seconds, and host metrics at larger intervals.
servers:
- name: production
host: postgres.internal
port: 5432
username: pgwarden
password: ${PGWARDEN_DATABASE_PASSWORD}
databases:
- app
collector:
table_collector:
interval: 3600
enabled: true
column_collector:
interval: 3600
enabled: true
index_collector:
interval: 3600
enabled: true
database_stat_collector:
interval: 60
enabled: true
lock_metric_collector:
interval: 10
enabled: true
session_metric_collector:
interval: 10
enabled: true
native_query_collector:
interval: 5
enabled: true
cpu_collector:
interval: 60
enabled: true
ram_collector:
interval: 60
enabled: true
io_collector:
interval: 30
enabled: true
disk_collector:
interval: 3600
enabled: true
On a database where the focus is on investigating real-time contention and workload, those same collectors can be more frequent. This generates fresher data, but increases the number of observation queries the Collector makes against the target. Keeping the other server fields the same, the collector snippet could look like this:
servers:
- name: critical-production
collector:
database_stat_collector:
interval: 3
enabled: true
lock_metric_collector:
interval: 1
enabled: true
session_metric_collector:
interval: 1
enabled: true
native_query_collector:
interval: 0.5
enabled: true
io_collector:
interval: 5
enabled: true
You can also turn off a type of collection that doesn't make sense for your case. If you don't want to retain observed queries, for example, you can keep the rest of the observability and just disable that specific collector:
servers:
- name: production
collector:
native_query_collector:
enabled: false
The default values, along with the conservative and aggressive profiles, are documented in presets.collector.yaml. I'd put this in the same conversation as any monitoring decision: higher frequency isn't always better; it depends on what you need to see and the cost you're willing to pay to see it.
Architecture
PGWarden has six main services:
- Frontend: interface to browse metrics, schema, documentation, and alerts.
- API: authentication, configuration, and data access.
- Collector: connects to the monitored PostgreSQL instances and collects metrics, metadata, sessions, locks, and active queries.
- Analytics: processes collected data and relates queries to tables and columns.
- Notifier: evaluates rules and sends notifications.
- TimescaleDB: stores configuration, metadata, and time series for PGWarden itself.
Besides those, there is the migrations service, responsible for preparing and evolving the platform's internal schema.
The Collector watches the databases and writes to TimescaleDB. Based on what was collected, Analytics builds relationships and Notifier evaluates thresholds. API and Frontend just organize this for querying. The monitored database is not used as storage by PGWarden.
What the database is telling you
PostgreSQL operational view
The first level is operational: CPU, memory, disk, I/O, connections, database size, waiting sessions, locks, table growth, vacuum, and index usage.
This data doesn't directly answer why a query got slow, but it helps separate out a few classes of problems. If an API's latency increased, for instance, it's worth knowing if the host is saturated, if the database has too many waiting sessions, if there are ungranted locks, if the cache hit ratio dropped, if autovacuum isn't keeping up with the write volume, or if a table grew a lot since last week.
These are different hypotheses. High CPU might indicate a heavier load, but also a bad query plan multiplying the work. Blocked locks can explain timeouts even with low CPU. A table full of dead tuples can worsen scans, but the problem could also be outdated statistics. The value of this page is putting enough signals side-by-side so that the investigation starts with a verifiable hypothesis.
Metadata, documentation, and schema evolution
In the database from the previous article, I didn't feel the lack of a data catalog so much, but I wanted to include this layer in PGWarden because it becomes more important as the product and teams grow.
Metadata provides context beyond the physical structure. It helps answer which table holds a specific concept, which columns participate in a relationship, which index exists on a table, which schema an object is in, and who should be familiar with that data. Without this, simple questions end up turning into digging through migrations, repositories, or asking whoever has been on the team the longest.
The catalog brings together databases, schemas, tables, columns, and indexes, with documentation associated with those objects. You can also use tags for things like business domain, personal data, responsible team, and criticality. A practical case: before making a table available to a BI team, you can document the meaning of each column and flag which fields carry personal information. Before altering a payments table, you can see that it was classified as critical and belongs to a specific domain.
Schema view
The schema view is a visualization of the database tables and the relationships declared by foreign keys. Each table shows columns, types, nullability, primary keys, foreign keys, uniqueness, and indexes. The links between them form a navigable map: you can drag tables, zoom in, filter by schema, and open details for a table or column.
It's useful because reading a large schema via DDL or a table list makes you lose track of dependencies. When someone needs to understand the path between orders, order_items, payments, and customers, for example, the map quickly shows which relationships are explicit and where a change might propagate. It also helps identify models that have become hard to navigate because they've accumulated association tables, circular relationships, or a massive number of foreign keys on a single entity.
It has an important limitation: it shows what the database declares. If two tables are related only by naming convention, by a key the application controls, or by a relationship maintained outside PostgreSQL, that link won't show up. The goal isn't to guess the domain model, but to make the model the database knows visible.
In the same view, the table details panel lets you check schema changes and, when Analytics is active, the queries that use the table or each column. This is useful for going from a visual relationship to its operational impact.
PGWarden also records schema evolution: creation, alteration, and removal of tables, columns, and indexes go into a timeline. Migrations remain the source of truth for how a change was applied. The timeline serves to look at the change in the context of the database and provide a reference for why it happened—whether an issue or an architectural decision.
This becomes relevant for problems that appear long after a change. An index removed today might not cause an impact until the table grows or until a rarely used query runs again. A column added for an abandoned feature might sit in the database for years without anyone knowing if it can be removed. The history doesn't answer this by itself, but it preserves the sequence of changes that needs to be investigated.
Semantic search in the catalog
Finding a table by name is useful. Discovering where a concept is represented in the database is even more useful. Semantic search scans the metadata of databases, schemas, tables, columns, indexes, and tags, sorting the results by similarity.
It's helpful when the person investigating knows the business rule but isn't familiar with the vocabulary used in the database. Searching for "user," "email," "primary key," or "timestamp" can lead to related objects even if the actual name is something else. The search can also be limited by server, database, and object type.
It's not a replacement for understanding the data model. In large schemas, it's a starting point to narrow down the search space and get faster to the objects that need to be read carefully.
Queries and table/column usage
Collecting an active query is just the beginning. Analytics stores observed queries and interprets their structure to relate each query to the tables and columns it touches. It registers whether a column appears in a SELECT, in a filter or condition, or in a join.
This gives a bit more context than just a list of SQLs. If a table is taking a heavy load, you can see which query patterns use it. If a column is going to be removed or have its type changed, you can check the observed history to see if it appears in filters, joins, or just projections. If it's used in joins, changing its type can have very different consequences compared to a rarely used column in a reporting SELECT.
It also helps differentiate types of costs. A 20ms query might be irrelevant in isolation, but very expensive overall if executed thousands of times a minute. A query taking a few seconds might run infrequently, but cause connection pool queues when it holds onto a transaction. The queries screen lets you look at frequency, total duration, observed duration, and when each query was last seen to start telling these apart.
This analysis is based on what was collected. Infrequent jobs, traffic that didn't occur during the collection window, and queries executed through other paths might not show up. It doesn't prove a column is safe to remove. It's operational evidence that should be combined with code, domain knowledge, and, when necessary, a longer observation period.
Query regression and query planner regression
A query planner regression happens when the optimizer starts choosing a worse plan for a query that used to be stable. This can happen after data growth or a change in distribution, outdated statistics, index removal, parameter changes, version upgrades, or simply because the parameter values now lead to a different plan. The symptom is usually a query that seemed fine and, without any apparent change to the SQL, suddenly costs much more.
PGWarden doesn't run EXPLAIN, or EXPLAIN ANALYZE, or any command that alters the monitored database. So it doesn't claim a planner regression happened, nor does it try to fix one. What it can do is detect a performance regression: it compares the current duration of a query against its history, using the median of the last seven days and requiring at least five samples. When the duration is three times higher than this median, it creates a finding for investigation. At ten times higher, the finding is critical.
This finding isn't a conclusion; it's the start of an investigation. If a query jumped from 50 ms to 1.5 s, it could be the planner, but it could also be locks, I/O, a cold cache, CPU contention, or a change in data volume. PGWarden helps raise questions: Did the table grow? Are statistics outdated? Was an index recently removed? Are there waits or locks at that time? The confirmation needs to be made by the person responsible, running EXPLAIN (ANALYZE, BUFFERS) carefully and comparing row estimates, the chosen plan, and buffers.
There's also a correlation with schema changes: if a query touching a certain table regressed and an index on that table was removed within the observed window, Analytics points this out. Even so, it's a correlation, not causation. The removed index might not be relevant to that query, and a bad plan could have another origin.
Indexes, statistics, and data growth
As volume grows, small problems get expensive. PGWarden tracks database and table sizes, index statistics, number of scans, cache efficiency, live and dead tuples, and vacuum and analyze info.
This data helps build the context that is usually missing in a performance analysis. An orders table might have gone from 10 million to 100 million rows; a query doing an index scan might have started doing a more expensive scan; a write load might be leaving many dead tuples; or a large index might not be getting scanned and yet increasing the cost of INSERT, UPDATE, and DELETE because every index needs to be maintained.
Analytics can point out unused indexes and patterns where queries filter columns without a compatible index prefix. This doesn't mean the right action is to create or drop an index. An apparently unused index might serve a monthly routine, a constraint, or a retention requirement. A suggested index might worsen writes. The project's role here is to show the evidence and the hypothesis; validating with real workload and EXPLAIN remains mandatory.
Recommendations
Recommendations bring some of these signals together into persisted findings for review. Today, they cover, among other things, potential vacuum needs, large indexes with no scans, filter patterns that might lack a compatible index, and CPU and memory capacity signals. Each recommendation brings severity, confidence, evidence, and a suggested next step.
This exists because isolated data is rarely enough. Knowing an index has zero scans is interesting, but knowing it's large, isn't primary or unique, and is on a table with heavy write volume changes the conversation: keeping this index might be taxing INSERT, UPDATE, and DELETE without bringing any read benefits. Likewise, a high dead tuple rate isn't automatically a reason to run manual vacuum; it needs to be read alongside the last autovacuum, the write pattern, and the traffic window.
A recommendation isn't a command. It doesn't create indexes, drop indexes, or run maintenance. The idea is to register a hypothesis with enough evidence for someone to evaluate, mark as resolved, or discard knowing exactly why they discarded it.
Time travel
In PGWarden, time travel doesn't mean restoring the database to a point in time. It's an area to investigate a historical window using the data collected during that period.
It brings together the most expensive queries, server CPU, memory, disk, and I/O, database size, largest tables, index usage, and schema events. This is useful when someone asks, "what changed when latency started going up?" The answer usually requires comparing different signals: query behavior, data growth, waits, machine usage, and structural changes.
For example, you can select the week the database started degrading and see if there was an increase in a table's size, an index removal, a schema change, and an increase in a query's total duration. It's a way to organize a historical investigation. It's not a database replay, it doesn't reconstruct the exact state of a transaction, and it doesn't replace logs or backups.
Alerts
The Notifier lets you create rules per server, database, table, or index, with warning and critical thresholds, an evaluation window, and cooldown. Currently, it can alert on CPU, memory, and disk usage; database or table growth; cache hit ratio; deadlocks; long queries; waiting sessions; blocked locks; changes to tables and indexes; dead tuple count; and index hit rate.
Alerts aren't meant to say the database is bad; they serve to warn that there's a signal that deserves attention. A blocked lock alert, for example, might be the first sign of a forgotten transaction. A table growth alert can anticipate a conversation about storage, partitioning, or retention. A lower-than-usual cache hit ratio might not be an incident on its own, but it could be relevant if it comes with high I/O and query degradation.
Notifications can go to Slack, Discord, Microsoft Teams, or email via SMTP. Thresholds need to be defined by the database's context. Alerting on CPU above 85% on a server that normally runs sustained at that level might just generate noise. The usefulness lies in choosing signals, windows, and people responsible that make sense for the actual workload.
Wrapping up
PGWarden doesn't solve the problem of scaling a PostgreSQL database to billions of rows on its own. Decisions regarding modeling, indexes, workload, vacuum, partitioning, and architecture still need to be made. It also doesn't make decisions for the team and doesn't make changes to the monitored database.
What I want with this project is to make it easier to read the signals that already exist in PostgreSQL. When a query changes behavior, when a table grows too much, when a lock starts blocking work, or when the schema drifts from what people understand, the database is usually trying to tell you something. Having this historical, related, and accessible information doesn't eliminate the investigation work, but it changes the quality of the questions we can ask.
If you'd like to contribute, the project is under active development and is available at PGWarden - Github.
Thanks for reading.
Top comments (0)