It was reported to me as "search is slow and everything is down". Both halves of that were wrong in an interesting way. Nothing had been deployed, nothing was down, and the database was healthy for the entire period. What had happened is that the corpus outgrew the machine, and the way that surfaces is not a slow page. It is an empty one.
What the numbers said
Measured against production on the morning of 23 August, before touching anything:
software engineer / United States 16.3s -> 0 results
the same query, direct SQL 55s -> 1,500 rows
the same query once cached 0.3s
a cold job listing 14.6s
the same listing once cached 0.12s
the frontend itself 0.6sA query that returns 1,500 rows in 55 seconds of raw SQL and zero rows through the API is not a query problem. It is a deadline problem, and the deadline is the interesting part.
Half the corpus could never be cached
jobs is 16 GB over 2,269,206 rows with 5.5 GB of indexes on top. That is a 21.5 GB working set against 12 GB of RAM with shared_buffers at 2560 MB. Roughly half the table can never be resident, and database-wide cache hit had settled at 90.4%, which sounds fine until you look at what the remaining 10% costs.
The disk does 114 MB/s sequential. On the random reads a cold index walk actually makes, it delivered about 26 MB/s. EXPLAIN ANALYZE on a cold broad search measured 59.9 seconds, reading 1.56 GB at a 43% hit rate. That is the honest cost of the read with no deadline on it at all.
Eight seconds is where slow becomes broken
The authenticator role carries statement_timeout=8s. So the read does not take 59.9 seconds from the API's point of view. It gets killed, and routes/search.py turns a failed read into total: 0, db_error: true.
That is the whole of "search returns nothing". There was already a comment at routes/search.py:707 recording that a cold broad read was 19-32 seconds on this box against an 8-second timeout. It had been true for a while for the rare wide query. Growth pushed the common case past it.
Why it drifted rather than broke
Two things made this arrive gradually instead of all at once, and both are worth knowing.
There were 226,088 dead tuples, about 9% of the table, with autovacuum not due until roughly 453,768. The default autovacuum_vacuum_scale_factor of 0.2 is a proportion, so on a 2.27M-row table the threshold is enormous and the table spends most of its life well short of it. It is a setting that works fine until the row count makes it stop working, with no signal at the crossover.
And n_tup_hot_upd was 0 out of 464,727 updates. Twenty-three indexes on one table make heap-only tuple updates impossible, so every single update rewrites every index. Ingest adds around 90,000 rows a day, so the cached fraction shrinks every day on its own.
What I ruled out before touching anything
Each of these was checked rather than assumed, which is the only reason the diagnosis above is worth anything: disk full (40 GB of 193 GB, so the earlier resize held), a shared_buffers regression (still 2560 MB, Postgres up 23 hours, no restart), lock or connection contention (19 idle backends, one active, load 1.04), and an orphaned scrape holding things open (scrape_in_progress: false).
Raising the timeout was the obvious fix and the wrong one
The one-line change here is to raise statement_timeout. It was considered and rejected, because of what it converts the failure into: "no results in 16 seconds" becomes "results in 30 seconds", and for those 30 seconds the request holds a PostgREST worker and a slot of App Runner concurrency, against a MaxConcurrency of 8. Eight of those and the site is down for everyone rather than wrong for one person.
A timeout is not what is making the query slow. Moving it only changes which of your users pays for the slowness.
So the fix was capacity: autovacuum tuned on the table (scale_factor 0.05, analyze 0.02, cost limit 1000), a manual VACUUM (ANALYZE), then the instance from 2 OCPU / 12 GB to 4 OCPU / 24 GB with shared_buffers raised to match. The resize is free - VM.Standard.A1.Flex Always Free covers 4 OCPU / 24 GB and this was the only instance in the tenancy, so it had been running on exactly half of an allowance already paid for.
After
Same eight pairs, same session, warm:
software engineer / US 16.3s -> 0 2.0s -> 1,500 rows
nurse / UK 4.3s 0.45s
data analyst / US 5.8s 4.3s
teacher / UK 3.9s 0.80s
accountant / US 6.7s 2.1s
warehouse operative / UK 2.0s 1.9s
marketing manager / US 6.3s 3.2s
electrician / UK 2.7s 0.33sNo pair returns zero any more. Cold job listings, which is what "clicking a result is slow" actually was, went from 14.6s to 0.12-0.22s over five sampled rows. The VACUUM (ANALYZE) took 354 seconds and cut dead tuples from 226,088 (9.06%) to 1,683 (0.07%). Total database downtime was about 14 minutes.
Two things went wrong on the way, and both will recur
The START call raced the resize. UpdateInstance returns 200 while the shape change is still settling, and a START issued in the next few seconds is answered 409 and is not queued. The instance then sits STOPPED indefinitely, which is a full outage rather than a reboot. Poll until the shapeConfig reads 4/24, then retry START until the state actually leaves STOPPED.
The second is worse because it is silent. Writing the config with a heredoc nested inside an ssh '...' argument truncated the file to its comment header and dropped every setting. Had Postgres started on that file it would have come up on stock 128 MB defaults, which is the exact fault this change exists to fix, and nothing anywhere would have errored. Pipe the file in on stdin instead:
cat custom_overrides.conf | ssh box 'sudo tee /etc/postgresql-custom/conf.d/custom_overrides.conf'What this does not fix
24 GB buys headroom, not a permanent fix, and Always Free stops exactly here. At 90,000 rows a day the next lever cannot be another resize; it has to be shrinking the working set.
The specific candidate is already identified. software engineer against the United States is still the slowest query on the site, and it is a query shape problem rather than a capacity one: 80,543 rows match, and search_jobs carries the full description through both window functions before the LIMIT, so it reads 590 MB and spills 139 MB to temp against a pinned work_mem of 32 MB. The fix is late materialisation - rank on narrow columns, take the top N, join the wide ones back afterwards. That is a change to load-bearing ranking code, so it wants its own tests and its own day, not an edit made during an incident.