A balance endpoint that stayed fast all day began taking three seconds, but only in the window when the settlement file landed.
No code had changed. CPU was flat. The queue was empty.
The reconciliation importer ran the whole file inside one transaction: parse it, attribute each incoming payment to a user's virtual account, update the ledger, commit at the end. Atomic, easy to reason about. It also held row locks on every account it touched for the full length of the import.
The balance read path took a write lock too: it locked the user's rows to compute a consistent number before returning it. So any user whose account showed up in that file waited behind the importer. Three seconds was just how far the import had gotten.
None of the usual dashboards show this. Handler time, query time and pool stats all looked healthy, because the query was not slow. It was waiting to start.
Three things I now do by default:
The importer commits in small chunks and touches accounts in a deterministic order, so lock windows stay short and deadlocks stop being random.
Read paths don't take write locks. If a read needs consistency, it comes from the ledger's own append-only history, not from locking rows.
Every request path gets a lock timeout. I would rather have a request fail fast with a clear reason than hang and pass itself off as a performance problem.
Missing this costs trust more than latency: a user staring at a stale balance retries the payment, and now reconciliation has a duplicate to explain.
I write these up in longer form on my blog: https://polycratia.com/c/ab86ef71
If you run batch jobs against the same tables your API reads, lock wait is probably worth measuring somewhere, and not only query time...