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, row counts in the working set cross whatever threshold the planner cares about, and the plan flips from an index scan to a sequential scan over 80 million rows. 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 an index would have fixed.

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, 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: Tony, CloudThinker's database agent, watching the same signals continuously and proposing fixes with evidence attached.

Slow queries are a compute bill

Before the mechanics, the framing that makes this worth automating: in managed PostgreSQL, query inefficiency converts directly into instance cost. The p95 latency you tolerate determines the instance class you pay for. A missing index that turns a 40 ms lookup into a 4-second scan doesn't just annoy users — it burns CPU, evicts useful pages from shared buffers, holds connections longer, and eventually justifies the upgrade from an xlarge to a 2xlarge. On RDS or Cloud SQL, that's roughly a doubling of the database line item to compensate for work the planner shouldn't be doing at all.

Run the causality backwards and you get the cheaper strategy: keep the top of pg_stat_statements honest, and the instance upgrade stops being necessary. That requires someone to be looking every day. That's the job Tony takes.

Connecting Tony: read-only, about five minutes

Tony connects to PostgreSQL the way your DBA would insist on anyway: a dedicated database user with read-only privileges — CONNECT on the database, USAGE on the schemas, and SELECT on the tables and statistics views, nothing more. No extension installs by the agent, no superuser, no write access at connection time. You create the user, 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. The whole flow takes about five minutes; it works the same against RDS, Aurora, Cloud SQL, Azure Database for PostgreSQL, or self-hosted.

If pg_stat_statements isn't enabled yet, Tony 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 Tony watches continuously

Once connected, Tony 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 from index scans to sequential scans 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 are falling behind write volume, and the tables where bloat is inflating 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 precedes the "database is down" page.

The difference from your manual audit isn't the checks — part two proved you can run every one of them yourself. The difference is cadence and memory. A regression that appears on the 28th is a finding on the 28th, with the before/after comparison already attached.

Index proposals come with evidence, not vibes

When Tony proposes an index, the proposal is a case file, not a suggestion:

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's not duplicating one;
the estimated plan change, framed as the same EXPLAIN comparison you'd build manually;
the write-side cost: 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, the index's size, and the write overhead it's been costing — because dropping an index that some quarterly job secretly depends on is how DBAs learn distrust, and the whole point is that you can check the reasoning before anything happens.

Graduated autonomy: DDL never runs itself

Every action class has an autonomy level you set per database:

Notify — Tony reports the finding. Nothing else. This is the default for everything.
Suggest — Tony proposes the specific change with projected impact and a rollback path.
Approve — Tony prepares the change and executes only after a named human approves it in chat.
Autonomous — Tony executes and reports. Teams that use this at all reserve it for reversible, low-blast-radius actions on non-production databases — and only after weeks of watching Tony be right at the Approve level.

One rule sits above all of these: no DDL executes without approval. Index creation, index drops, parameter changes — anything that alters the database requires both explicitly granted write credentials and a human decision, and every proposal, approval, and execution lands in an audit trail with its evidence and its approver. Your part-two skills aren't wasted here; you're reviewing the same EXPLAIN output and statistics you'd have gathered yourself, minus the hours of gathering.

What a first week typically surfaces

The numbers below are illustrative — a composite of what Tony's 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.

Finding	Detail	Typical impact
Query regression	Invoice report 38x slower at month end; plan flipped to seq scan as table passed ~60M rows	p95 timeouts 3 days/month
Missing FK index	Unindexed foreign key on 80M-row child table; every parent delete scans it	Multi-second locks on delete paths
Unused indexes	14 indexes, 38 GB total, zero scans in 60 days	Write amplification + storage
Duplicate indexes	3 pairs with identical leading columns	Redundant maintenance cost
Table bloat	2 hot tables above 40% dead-tuple ratio; autovacuum lagging write volume	Inflated scans, wasted cache
Connection saturation	App pool spikes to 96% of `max_connections` during deploys	Intermittent connection errors
Cache hit dip	Nightly batch evicts working set; hit ratio drops from 99% to 91%	Morning latency complaints

Notice the shape: nothing exotic. The value isn't clever findings — it's that this table refreshes itself daily, each row arrives with a proposed and approval-gated fix, and the month-end regression gets caught in month one instead of after the second instance upgrade.

Prompts to try in your first session

Tony is conversational — you ask in plain language and get answers cited against the statistics views, so you can verify rather than trust:

"Tony, show the top 10 queries by total execution time this week, and flag any that regressed versus the previous 30 days."

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

"Draft a CREATE INDEX CONCURRENTLY statement for the invoice reporting query, show me the expected plan change, and don't execute anything."

What Tony will not do

Worth stating plainly, because "AI agent connected to prod Postgres" should make you ask:

It is read-only by default. The connection user has no write privileges. Remediation requires you to explicitly grant scoped write access and raise the autonomy level above Notify.
It never executes DDL without approval. No index is created or dropped, no parameter changed, without a named human approving that specific action.
It does not touch anything unfindable in the audit trail. Every proposal and execution is logged with evidence and approver.
It does not replace judgment on schema design. Tony will show you that a table needs partitioning before it needs another index; whether to partition is an engineering decision, and it stays yours.

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, with the biggest single win usually being the regression that gets caught the week it appears.

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