I profiled the wrong database for an hour

The report was "sign-in timed out." A staging environment: logins hanging, then the whole API going unreachable. Production, on identical code, was fine.

The root cause turned out to be dull — a burstable database instance that ran out of CPU credits. What's worth writing down is everything between the report and that finding, because I spent the first hour measuring a database the application doesn't connect to, and I only caught it because a number refused to reconcile.

The symptom made no sense

The application log said the request succeeded:

Completed 200 OK in 71729ms (Views: 1.4ms | ActiveRecord: 68208.4ms (12 queries, 0 cached))

Seventy-one seconds, sixty-eight of them in the database, across twelve queries. Later samples were worse — 166 s wall clock, 121 s of database time across 29 queries. Roughly five seconds per query.

Meanwhile the host was asleep: load average 0.02, CPU 94–98% idle, disk at literally zero IOPS on a volume provisioned for 3,000. Nothing was working hard, yet every query took seconds.

Then /up started timing out too — the Rails health endpoint, which touches no database at all. That reframed it: requests were queueing before they did any work. The app runs three Puma threads, so three stuck requests are enough to make the entire API look dead. A slow query had become an outage.

Wrong turn one: the database I was measuring wasn't the one being used

I connected to the Postgres running on the box and profiled it. One client connection out of a hundred. No query active longer than 300 ms across a hundred-second sample. Table statistics unremarkable.

So I reported that the database was idle and the problem must be elsewhere.

That was wrong, and the contradiction should have stopped me sooner: Rails claimed 115 seconds of database time while Postgres showed no query running longer than a third of a second. Both cannot be true of the same server. I chased the discrepancy through connection pools, DNS, and TCP timings before asking the obvious question — which server?

{host: "…rds.amazonaws.com", port: 5432, database: "…"}

The app was on RDS. It had always been on RDS. A Postgres still ran on the host, left over from an earlier topology, holding nothing but stale development databases — and the deploy config still described that arrangement as current:

#   - PostgreSQL   : stays on host, container reaches it via
#                    host.docker.internal (mapped to host-gateway)

That comment was true once. It cost an hour. A wrong comment is worse than no comment, because it answers the question you were about to ask and stops you verifying.

The lesson I'd generalise: when a measurement contradicts a claim, suspect your target before your instruments. I had good tools pointed at the wrong object, which produces confident, precise, useless data.

The actual cause

The database was a db.t4g.micro — two burstable vCPUs, 1 GB of RAM. Burstable instances earn CPU credits when idle and spend them when busy. The credit history told the story without ambiguity:

Jul 24 → Jul 28 12:00   288.0   (the maximum, flat for four days)
Jul 28 17:45            174.0   ← drain begins
Jul 28 23:45              5.7   ← exhausted
Jul 29 all day           5–12   ← flat-lined at baseline

Four days pinned at the ceiling, then 288 credits gone in about twelve hours, then nothing. Once the balance hits zero the instance is held at baseline CPU, and every query that used to take milliseconds takes seconds.

Production ran the same code, the same framework config, the same thread counts. The only difference was the instance class: 4 GB instead of 1 GB. That single variable was the entire delta between "fine" and "down".

One correction I had to make publicly during the investigation: I recommended a CLI flag to switch the instance into unlimited credit mode. That flag is EC2-only. RDS burstable instances are already permanently in unlimited mode with no toggle — which also means the failure would have shown up as a surprise line on the bill, not only as slowness.

Wrong turn two: I blamed the wrong query, then refuted myself

Table statistics pointed hard at one table — the background job queue:

784 million rows read sequentially, an order of magnitude above anything else. I had a tidy explanation: the job runner polls every 5 seconds, the deployment runs two schedulers, so eight pollers were scanning a 132k-row table continuously.

It was tidy and it was wrong. The queue table carries nineteen indexes, and every dequeue path is a partial index on unfinished jobs only:

(priority, scheduled_at, id) WHERE finished_at IS NULL AND locked_by_id IS NULL

Polling never touches the finished rows. And the arithmetic disagreed with me: eight pollers every five seconds over several days is millions of scans. There were 9,063, averaging 86,000 rows each. That is a handful of expensive full scans, not a continuous cheap one.

The likely culprit is the job dashboard, which the app mounts and which aggregates counts across all rows while only unfinished rows are indexed. It auto-refreshes, and someone was actively investigating that afternoon. I say "likely" because I still cannot prove it — see below.

The last row is a second correction. Nearly a million sequential scans on a 146-row table looks alarming and is completely fine: Postgres correctly prefers a sequential scan at that size, and the lookups were primary-key find_by(id:) anyway. It is a call-volume signal, not an indexing one. I nearly shipped a useless index.

The part that actually hurt: no evidence

Two gaps turned a twenty-minute diagnosis into a multi-hour one.

Query attribution didn't exist. pg_stat_statements wasn't installed and Performance Insights was disabled. So "the database was slow" could never become "this query was slow." Every mechanism above is inference from table-level counters. That is why my first explanation survived as long as it did — there was nothing to falsify it with except arithmetic.

Log retention had already deleted the incident. The deploy tool defaults to a single 10 MB JSON log file per container with no rotation. In practice:

jobs container   02:17 → 02:18   ~2 minutes
web container    22:53 → 02:18   ~3.5 hours

The report was filed roughly five hours before the earliest surviving line. I initially read the resulting silence as evidence of absence — "no errors logged, so nothing failed" — which is exactly backwards when the window is two minutes wide. Absence of evidence in a log that keeps 120 seconds is not evidence of anything.

What changed

The instance was restored onto a larger class, and it is now accruing credits at roughly 17.5/hour against a ~12/hour baseline — comfortably in surplus under the same workload that drained the smaller one. That confirms the sizing rather than just asserting it.

Beyond that, the changes worth making were small and mostly not about the database:

• Poll interval 5 s → 15 s. The value was justified in a comment as "shorter than the default 30 s". The default is 10. It was twice as aggressive as upstream, not half — a number nobody had rechecked since it was written.
• Log rotation. Keeping five 20 MB files instead of one, so the next incident still has evidence when someone reads the ticket the following morning.
• The stale topology comment, replaced with the real one and a one-line command to verify it.
• A credit-balance alarm. The drain ran from full to empty over twelve hours. An alarm at 100 credits would have fired roughly six hours before any human noticed a slow login.

I deliberately did not make the biggest available change — moving job execution entirely out of the web process, which is the framework's documented production default. Running it in-process is wasteful, but it currently doubles as a fallback executor: if the dedicated worker container dies, the web process still drains the queue. Removing that without first alerting on "no live worker" trades a known inefficiency for a silent failure mode. It's now a one-line environment variable, switched on when the alarm exists.

What I'd take from this

Verify the target before trusting the measurement. Every reading I took in the first hour was accurate and irrelevant.

Comments describing infrastructure decay silently. Code that lies gets caught by tests. A comment that lies gets believed. If a comment states a fact someone will act on, give it the command that verifies it.

Retention is a debugging feature. A log you cannot read during the incident is a log you do not have. Two minutes of retention is functionally zero.

Instrument for attribution before you need it. pg_stat_statements costs about 1% CPU and a few megabytes. Not having it turned a solvable question into a plausible story — and a plausible story is exactly the thing you should not ship as a root cause.

If you're sizing burstable database instances, the throughput calculator is a reasonable sanity check on whether your baseline is where you think it is, and the production readiness scorecard covers the observability gaps that made this one expensive. Same single-box setup as my Kamal deploy pipeline and Cloudflare tunnel posts.