Tuning managed PostgreSQL, where you do not control the machine

The call comes in during a busy period. The database is slow, the dashboard shows high CPU, and someone has already proposed moving to the next instance size up. It will work, briefly, because doubling the hardware masks a query doing a sequential scan on a four-million-row table. Two months later the same conversation happens at twice the cost.

On a managed service you cannot touch the kernel, the filesystem or most of the configuration, and that is fine, because almost none of the wins are there. They are in queries, indexes, connections and vacuum.

Start with the statistics extension, always

pg_stat_statements is the first thing to enable and the first place to look. It aggregates execution statistics per normalised query, and it turns "the database is slow" into a ranked list.

Sort by total execution time rather than by mean. The query taking two hundred milliseconds and running forty thousand times an hour is costing far more than the one taking four seconds twice a day, and teams consistently optimise the second because it feels slower.

Look at the top ten by total time and ask three questions of each: how often does this run, does it need to run that often, and what does its plan look like. Caching or batching the frequent one is frequently a larger win than optimising it.

Then enable slow query logging with a threshold, and the extension that samples execution plans if your provider offers it, because the plan from production is the one that matters and it often differs from the plan you get locally with a small dataset.

Indexes, in both directions

Missing indexes are the single most common cause of a database performance incident. Find them by looking for sequential scans on large tables in the statistics views, and by reading the plans of the top queries.

Three things people miss. A composite index's column order matters, and it must match the query's filter and sort pattern, so an index on two columns serves queries filtering on the first but not on the second alone. A partial index with a WHERE clause is much smaller and faster when most rows are irrelevant, for example indexing only the rows that are not soft-deleted. And a covering index that includes the selected columns lets the database answer without touching the table at all.

Unnecessary indexes are the other half and get ignored. Every index is written on every insert, update and delete. A table with eleven indexes is doing eleven times the write work plus the heap, and its bloat and vacuum cost rise accordingly. The statistics views tell you which indexes have never been scanned; drop them, carefully and one at a time, in a maintenance window you can reverse.

Always build indexes concurrently on a live table. The non-concurrent version takes a lock that blocks writes, which is how an index intended to fix a performance problem causes an outage.

Connections, and the pool you are probably missing

PostgreSQL uses a process per connection, which is expensive. Every connection consumes memory whether or not it is doing anything, and beyond a few hundred the scheduling overhead degrades everything.

Modern application frameworks default to a pool per instance. Ten instances with a pool of twenty each is two hundred connections, and an autoscaling group or a serverless function fanning out makes that number unbounded, which is the most common way a database falls over during a traffic spike.

The answer is a connection pooler in transaction mode between the application and the database, either the managed proxy your provider offers or PgBouncer. It multiplexes many client connections onto few server connections, and it is the difference between a database that survives a spike and one that does not.

The catch with transaction mode is that session-level features stop working as expected: prepared statements, advisory locks, session variables and LISTEN/NOTIFY. Check what your ORM does before switching, because the failure is subtle rather than loud.

Autovacuum is almost always undersized

PostgreSQL does not overwrite rows in place. An update writes a new version and leaves the old one dead, and autovacuum reclaims that space. When it cannot keep up, tables bloat, indexes bloat, plans degrade, and eventually the system starts warning about transaction ID wraparound.

The default settings are conservative and were chosen for a much smaller database. On a table with heavy update or delete traffic, the scale factor means vacuum waits until a fixed proportion of the table is dead, which on a large table is a very large number of rows.

Tune per table rather than globally. For the handful of high-churn tables, lower the scale factor so vacuum runs more often on smaller amounts, and raise the cost limit so it works faster when it does run. Leave the rest alone.

Monitor dead tuple counts and the age of the oldest transaction. Both are available in the statistics views and both are leading indicators of a problem that presents much later as inexplicable slowness.

Read replicas, and the lag that catches you

A read replica moves read traffic off the primary and is the obvious scaling move. It also introduces replication lag, and lag is where the bugs are.

The pattern that breaks: a write followed immediately by a read of the same data, routed to a replica that has not yet received it. The user creates something and it is not there. This is not rare, it is the default behaviour under load, and it has to be handled explicitly by routing read-after-write to the primary or by waiting for the replica position.

Monitor lag as a first-class metric with an alert, and decide what the application does when lag is high. Silently serving stale data is a decision; making it deliberately is better than discovering it.

Replicas do not help write-heavy workloads at all, and teams reach for them when the actual problem is write amplification from too many indexes.

Instance sizing, last

Once queries, indexes, connections and vacuum are addressed, sizing is a real lever and a smaller one than expected.

Two practical points on managed services. Storage performance is frequently provisioned separately from capacity, and a database that has exhausted burst credit on a general-purpose volume produces a latency incident that looks like a CPU problem; check the volume's provisioned throughput before adding cores. And memory matters more than cores for most workloads, because it determines how much of the working set stays in cache, which is why the flexible shapes discussed in controlling Oracle Cloud cost are useful when a workload is memory-heavy.

Make the sizing decision with the cost consequence visible, since a database instance class is a decision with an exit cost rather than a monthly rental, as noted in cost is decided in the pull request.

The things people forget

• Statistics go stale after a bulk load. Run ANALYZE after a large import, or the planner chooses badly using out-of-date row estimates.
• Long-running transactions block vacuum. An idle-in-transaction session holds the horizon open and prevents cleanup across the whole database.
• Schema migrations take locks. Adding a column with a default, changing a type or adding a constraint can lock a table for the duration. Set a lock timeout and use the non-blocking forms.
• Major version upgrades reset the plan cache and can change plans. Test on a copy of production data, not on an empty schema.
• Extensions are limited on managed services. Check availability before designing around one.
• The managed service does backups; you still owe a restore test, which is the discipline in RTO, RPO and the restore test.

What to do this week

Enable pg_stat_statements if it is not on, wait a day, and pull the top ten queries by total execution time. Take the first one and read its plan. In nearly every engagement we have run, that single query is responsible for a large share of the load, and the fix is an index rather than an instance. We start the database workstream of a cloud engagement with exactly that list.