My favorite Postgres incident

A recollection from my time at Brex, more than three years later.

A few years ago at Brex, a lot of teams started getting paged at around 6 a.m. Pacific. Different parts of the product were failing, and the investigation kept leading back to the same place: the Postgres database powering Expense, one of our core primitives.

There was no obvious code deployment or operational change to explain it. A system that had been working was suddenly struggling under its normal workload.

This is still one of the most interesting Postgres incidents I've been part of. It took hours to understand, and the eventual fix was a single index.

Everything was an Expense

At Brex, essentially anything that represented a company spending money was an expense. A card transaction was an expense. A bill payment was an expense. Different products shared this underlying concept.

That was a useful abstraction for building a financial product. It also meant that a problem with Expense could show up in a lot of places at once. Teams could be looking at different failures while following the same problem down through their dependencies.

That database ran on Amazon RDS.

First we needed some breathing room

pganalyze, RDS Performance Insights, and CloudWatch made one thing clear: the database was running out of CPU. A number of queries were performing badly.

We scaled the database up and, as best as I remember, also shed some load by turning down nonessential consumer groups. Those changes helped ease the pressure and gave us room to keep investigating. But we still couldn't explain why a workload the database had been handling was suddenly too much for it.

In this kind of incident, there are two problems to work on in parallel: get the system back into a tolerable state, and understand what changed. Extra capacity can help enormously with the first without answering the second.

The timestamp kept drawing our attention. What had happened at 6 a.m.?

The clue was in a query plan

A few hours into the incident, we were working through query plans, using EXPLAIN ANALYZE to understand where the time was going. This was before LLMs were part of our debugging workflow. A lot of the work involved people patiently reading plans and trying to make sense of them.

Eventually, someone noticed that a query wasn't using the index we expected it to use.

The index still existed. Postgres was using sequential scans for that query and a few others. Multiple slow queries had made the investigation harder. But a busy database doesn't require every query to have developed an independent problem. Expensive work can consume the CPU that other queries also need. AWS specifically documents execution-plan changes as a possible cause of RDS CPU spikes.

Once we’d narrowed the investigation to those queries, we could ask a more useful question: why was Postgres choosing sequential scans over the indexes we expected it to use?

What had changed overnight

As I remember it, we traced the change back to an automatic ANALYZE that had run overnight and refreshed the table's statistics.

Postgres uses statistics about the data to estimate the cost of different execution plans. Those estimates help it decide whether to use an index, scan more broadly, and how to combine the results of different operations. The SQL and available indexes are only part of the input to that decision.

ANALYZE refreshes those statistics. The autovacuum machinery can run it automatically after enough changes to a table; in our case, that happened overnight.

The data had evolved as the product grew. The refreshed statistics changed the planner's assessment, and a few important queries ended up with worse plans.

What stuck with me was that a seemingly routine statistics refresh could change the database’s behavior and performance, sometimes for the worse.

The other piece of the puzzle was our traffic. We were processing millions of financial transactions a day across tens of thousands of businesses, but the product was still heavily U.S.-centric. Customers were starting their workday, and load was rising with them. As normal daytime traffic arrived, the extra work exhausted the available CPU.

The beginning of an incident is a useful timestamp. It doesn't necessarily tell you when the underlying problem was introduced.

The fix was a partial index

After all that investigation, the fix was a small one: create a partial index.

A partial index contains only rows that satisfy a predicate. If a frequently used query needs a small subset of a large table, indexing that subset can give it a smaller, cheaper access path. The planner must be able to establish that the query's conditions imply the index predicate.

Here's a simplified illustration, using invented column names and conditions. Suppose most expenses are completed, but a frequent query asks for a particular company's pending expenses:

SELECT id, amount
FROM expenses
WHERE company_id = 42
  AND status = 'pending';

An index for that specific workload could be:

CREATE INDEX CONCURRENTLY expenses_pending_company_idx
    ON expenses (company_id)
    WHERE status = 'pending';

This is an example of changing the available access paths. A partial index doesn't force the planner to use it or make every estimate correct.

The CONCURRENTLY in this example permits ordinary writes during the index build. Building it still takes time and adds load, which matters on an already busy database. A small fix isn't necessarily an instant operation.

Back in the actual incident, the partial index resolved the performance problem. We were eventually able to unwind the extra capacity we'd added and return to normal.

What stayed with me

The first thing I took away was a broader interpretation of “what changed?” I still start with deployments and configuration changes, but an unchanged application can behave differently as its data and the database's understanding of that data evolve.

The second was the value of keeping evidence from when things are healthy. A baseline plan gives you something to compare with during a regression. Query statistics help identify where to look; tools such as auto_explain can record slow-query plans when configured appropriately. I'd want that history for queries supporting a table as central as Expense.

The third was to look closely at the planner's assumptions. Comparing estimated and actual row counts can expose where its picture of the data diverges from reality. EXPLAIN ANALYZE is useful precisely because it executes the query and measures the result, so it also needs care during an incident: it adds real work to the database.

None of this diminished my appreciation for Postgres. We entrusted it with an enormous amount of responsibility at Brex, and it enabled us to build a great deal. Working at that scale also gave us reasons to understand what was happening beneath a SQL query that looked perfectly ordinary.

That’s what stayed with me: a group of engineers working through a confusing failure until the behavior finally made sense. Some of my fondest engineering memories come from days like that.