Automating PostgreSQL Performance Tuning with an AI Database Agent

There's a reporting query in your database that behaves perfectly for 27 days a month. Then month-end close arrives, the finance team hammers the invoicing tables, and the plan flips. A query that took 400 ms takes fifteen seconds. Dashboards time out, the app's connection pool saturates, and someone proposes the fix that always gets approved under pressure: a bigger instance.

Nobody is doing PostgreSQL performance tuning during month-end close. That's the structural problem. The moments your database needs tuning attention most are exactly the moments your engineers are busiest. So the slow query never gets a root cause, the instance class ratchets up, and the bill quietly absorbs what a five-second fix would have solved.

This is part three of our PostgreSQL performance tuning series. Part one mapped the metrics that matter: slow queries, cache hit ratio, index bloat, sequential scans, connection saturation and autovacuum lag. Part two ran the full DIY audit with native tools: the slow query log, pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS), and the pg_stat_user_indexes queries that expose dead weight.

If you did that audit, you have a snapshot. This article is about replacing the snapshot with a process: CloudThinker watching the same signals continuously, testing hypotheses the way a DBA would, and proposing fixes with the evidence attached.

---

Slow queries are a compute bill

In managed PostgreSQL, query inefficiency converts directly into instance cost. The p95 latency you tolerate decides the instance class you pay for. A missing index that turns a 40 ms lookup into a 4-second scan burns CPU, evicts useful pages from shared buffers, holds connections longer, and eventually justifies the move from an xlarge to a 2xlarge. On RDS or Cloud SQL that's roughly a doubling of the database line item, paid to compensate for work the planner shouldn't be doing.

Run the causality backwards and you get the cheaper strategy: keep the top of pg_stat_statements honest, and the upgrade stops being necessary. That needs someone looking every day, which is the part no team staffs for.

Why the tuning sprint can't keep up

The part-two audit is correct. What breaks is the model around it.

Regressions have dates, snapshots don't. A quarterly audit sees a slow query. It doesn't see that the query was fast until the 28th, which is the single most useful fact for finding the cause.

Plans change without deploys. A table crosses a size threshold, statistics go stale, a parameter changes on the provider's side. No pull request, no changelog, nothing for a code review to catch.

The expertise sits with one or two people. Reading plans and weighing an index against its write cost is senior work. When those people are in the month-end firefight, nobody else does it.

Infrastructure alarms point at the wrong fix. CPU and IOPS alarms describe the instance. Every alarm-driven fix is therefore an instance-sized fix.

---

Connecting: read-only, about five minutes

01Database

PostgreSQL

• pg_stat_statementsQuery shapes, calls, time, buffers
• pg_stat_user_tables and indexesScans, dead tuples, index usage
• pg_stat_activityConnections, waits, idle in transaction

Read-only role

02Analysis

CloudThinker

• Track regressionsEach query against its own history
• Test hypothesesStatistics, plans, locks, cache
• Propose the changeDDL with evidence and a rollback

No extension installs

03Where the team works

Chat and tickets

• SlackFindings and approvals in the thread
• Microsoft TeamsSame flow for Teams workspaces
• JiraApplication-side fixes with the plan attached

Nothing new to watch

CloudThinker connects to PostgreSQL the way your DBA would insist on anyway: a dedicated database role with read-only privileges. That's CONNECT on the database, USAGE on the schemas, and SELECT on the tables and statistics views, nothing more. A typical setup looks something like this:

CREATE ROLE cloudthinker_ro WITH LOGIN PASSWORD '********';
GRANT CONNECT ON DATABASE app TO cloudthinker_ro;
GRANT USAGE ON SCHEMA public TO cloudthinker_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cloudthinker_ro;
GRANT pg_read_all_stats TO cloudthinker_ro;  -- see other roles' query text in pg_stat_statements

No extension installs by the agent, no superuser, no write access at connection time. You create the role, paste the connection details, and the first analysis starts. The PostgreSQL connection guide has the exact GRANT statements, so your security review sees precisely what is granted and what isn't. It works the same against RDS, Aurora, Cloud SQL, Azure Database for PostgreSQL or self-hosted.

If pg_stat_statements isn't enabled yet, CloudThinker detects that on the first pass and tells you. Enabling it is a parameter-group change and a restart (covered in part two), and it's the single highest-value prerequisite.

What it watches continuously

Once connected, CloudThinker runs the part-two audit as a standing process rather than an afternoon project:

• Slow query trends. The top of pg_stat_statements by total and mean execution time, tracked over time, so a query that regresses 40x at month boundaries is flagged as a regression with a date, not a permanent fixture nobody remembers being fast.
• Plan behavior. Queries whose execution profile shifts as tables grow, correlated with the table statistics that explain why.
• Index health. Unused indexes (zero scans over the observation window) that cost write amplification and storage; duplicate and redundant indexes; missing indexes implied by sequential-scan-heavy access patterns, including the classic unindexed foreign key on a large child table.
• Bloat and autovacuum. Dead-tuple ratios per table, autovacuum runs that fall behind write volume, and the tables where bloat inflates both storage and scan times.
• Cache and connections. Buffer cache hit ratio dips tied to specific workloads, and connection counts approaching max_connections, the saturation pattern that usually comes before the "database is down" page.

Part two proved you can run every one of these checks yourself. The difference is cadence and memory. A regression that appears on the 28th is a finding on the 28th, with the before-and-after comparison already attached.

---

One regression, start to finish

Here's the month-end report from the opening, walked through the way CloudThinker handles it. Table names and numbers are illustrative.

01Signal

Regression

A query shape runs 38x slower than its own 30-day baseline.

02Hypotheses

Test each one

Stale statistics, missing index, lock waits, cache eviction.

03Evidence

Plan and stats

Estimate against actual, last analyze, waits, buffer reads.

04Output

Proposal

The cause, the fix, its write-side cost and the rollback.

The signal. On the 28th, one query shape in pg_stat_statements (the invoice summary report) shows a mean execution time of 15.6 seconds against a 30-day baseline of 410 ms. Its shared_blks_read per call is up about 50x. Nothing deployed in the last 48 hours.

Four readings, four predictions. A regression like this usually has one of four causes, and each leaves a different fingerprint:

Checking each one. The lock hypothesis goes first because it's cheapest to rule out:

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
GROUP BY 1, 2
ORDER BY 3 DESC;

Sampled over the report's window, the active sessions are almost all on IO waits, with no Lock waits worth mentioning. Not contention. The database-wide cache hit ratio is down only slightly, and other top queries are at their baselines, so it isn't general cache pressure either: this one query is doing the reading.

Next, the table's statistics:

SELECT relname, n_live_tup, n_mod_since_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('invoices', 'invoice_lines');

invoice_lines holds about 60 million rows, has 5.9 million modifications since its last analyze, and was last auto-analyzed eleven days ago. That's the clue. Autoanalyze triggers at autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × rows, which with the defaults (50 and 0.1) is just over 6 million changes on this table. The whole month's inserts haven't crossed it yet, so the planner's histogram still ends before this month started.

The plan confirms it:

Nested Loop  (cost=1.13..9840.55 rows=1150 width=48)
             (actual time=0.09..15211.40 rows=2304117 loops=1)
  ->  Index Scan using invoice_lines_period_idx on invoice_lines l
        Index Cond: (period = '2026-09'::text)
  ...

The planner expected 1,150 rows for the current period and got 2.3 million. A nested loop is the right plan for 1,150 rows and a terrible one for 2.3 million. The missing-index hypothesis is out too: the index exists and is being used. It's being used for a plan built on the wrong numbers.

The finding. Stale statistics on invoice_lines, caused by the default analyze scale factor on a 60-million-row append-heavy table. The proposal has two parts:

-- Immediate: refresh statistics. Takes a SHARE UPDATE EXCLUSIVE lock,
-- so reads and writes continue.
ANALYZE invoice_lines;

-- Durable: analyze after ~1% change instead of ~10% on this table.
ALTER TABLE invoice_lines SET (autovacuum_analyze_scale_factor = 0.01);

Rollback for the second statement is ALTER TABLE invoice_lines RESET (autovacuum_analyze_scale_factor);. Neither change needs a bigger instance or a new index. That's the point of testing hypotheses before reaching for a fix: the obvious fixes (more CPU, another index) would both have been wrong.

---

Index proposals come with evidence

When the cause is a missing index, the proposal is a case file:

• the exact queries from pg_stat_statements that would benefit, with their current total execution time and call counts;
• the table's row count, size and existing indexes, so you can see it doesn't duplicate one;
• the expected plan change, framed as the same EXPLAIN comparison you'd build by hand;
• the write-side cost, because every index taxes INSERT and UPDATE, and a proposal on a hot table says so;
• the DDL itself, written as CREATE INDEX CONCURRENTLY so applying it doesn't lock the table.

The same evidence standard applies in the other direction. A "drop this index" proposal shows zero scans across the full observation window on every node, the index's size, the write overhead it has been costing, and the CREATE INDEX statement to restore it. Dropping an index that some quarterly job secretly depends on is how DBAs learn distrust, so you can check the reasoning before anything happens.

No DDL that you haven't allowed

CloudThinker acts within Auto Mode, so you decide what runs on its own and what waits for approval. Changing the database also needs explicitly granted write credentials, and every proposal, approval and execution lands in an audit trail with its evidence and its approver. Your part-two skills aren't wasted: you're reviewing the same EXPLAIN output and statistics you'd have gathered yourself, without the hours of gathering.

What a first week typically surfaces

The numbers below are illustrative, a composite of what a first analysis tends to return on a mid-market setup: one primary around 400 GB on a 4-vCPU instance class, a read replica, a few hundred tables. Yours will differ.

Nothing exotic. The value is that this table refreshes itself daily, each row arrives with a proposed fix gated by approval, and the month-end regression gets caught in month one instead of after the second instance upgrade.

Why this beats the tuning sprint

The deeper difference is memory. A person running the audit sees the database as it is today. CloudThinker sees each query against its own history, and "this got slow on the 28th" narrows the search from every possible cause to the few that changed that day.

Prompts to try in your first session

CloudThinker is conversational. You ask in plain language and the answers cite the statistics views, so you can verify rather than trust:

"Show the top 10 queries by total execution time this week, and flag any that regressed against their previous 30 days."

"The invoice report got slow this morning. Check statistics freshness, the current plan, lock waits and cache reads for that query, and tell me which explanation the evidence supports. Propose only, change nothing."

"Which indexes on the orders database have had zero scans in the last 60 days on every node, and what are they costing in write overhead and storage?"

"Draft a CREATE INDEX CONCURRENTLY statement for the slowest query on the orders table, show me the expected plan change and the write cost, and don't execute anything."

What it won't do

"AI agent connected to production Postgres" should make you ask. The answers:

• Read-only by default. The connection role has no write privileges. Remediation needs you to grant scoped write access explicitly and every change goes through Auto Mode.
• No DDL you haven't allowed. No index is created or dropped, and no parameter changed, unless you've allowed it in Auto Mode.
• Nothing outside the audit trail. Every proposal and execution is logged with its evidence and approver.
• No schema decisions. CloudThinker will show you that a table needs partitioning before it needs another index. Whether to partition is an engineering decision, and it stays yours.

Related reading

• Part one: the 6 PostgreSQL metrics that matter
• Part two: hands-on tuning with native tools only
• Automating Neon Postgres tuning with an AI database agent
• Automating MySQL slow query triage
• Auto Mode

From audit to habit

You know which PostgreSQL metrics matter, and you know how to audit them with native tools. What's left is cadence, and cadence is an automation problem, not an effort problem. Teams that move from occasional tuning sprints to continuous, approval-gated tuning typically avoid one to two instance-class upgrades a year and claw back meaningful storage and write throughput from dead indexes. The biggest single win is usually the regression that gets caught the week it appears.

Try CloudThinker free with 100 premium credits and no card required, and follow the PostgreSQL connection guide to see your own first findings table within the hour.