Three PostgreSQL Queries That Quietly Slow Down Payments APIs

Payment APIs are unusually friendly to three specific database mistakes. Each one is correct SQL. Each one is fast on a laptop with four hundred rows. Each one only becomes a problem after the table has real users in it, which is exactly the moment nobody is watching.

This is the checklist I run before a query reaches production. There are no latency figures here, because the numbers that matter are specific to your data and your hardware: what I can give you is how to find these three, and what to do about them.

01Query one: filter and sort in the wrong index order

Start with the most common shape in any application that shows a list of things belonging to someone:

SELECT id, amount_minor, currency, status, created_at
FROM transactions
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT $2;

This is correct, readable, and completely unprepared for growth. An index on user_id alone helps Postgres find the rows, and then it sorts them at runtime. The sort cost scales with the number of rows for that one user, so your most active users get the worst query times on your most-used page.

CREATE INDEX CONCURRENTLY transactions_user_created_at_idx
    ON transactions (user_id, created_at DESC)
    INCLUDE (status, amount_minor, currency);

Two details that matter more than the index itself:

• CONCURRENTLY does not take a write lock, so it can run against a live table. It cannot run inside a transaction block, which your migration runner has to accommodate.
• INCLUDE stores those extra columns inside the index. That is what turns an index scan into an index-only scan for a query that only selects them.

To confirm the fix, read the plan rather than the stopwatch:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at FROM transactions WHERE user_id = $1 ORDER BY created_at DESC LIMIT 50;

Seq Scan on transactions with a Filter: line is what you are looking for. When the index is right you get Index Scan using transactions_user_created_at_idx with an Index Cond:, and often Heap Fetches: 0 because the selected columns came from the index itself.

02Query two: the full scan that runs on a schedule

Any periodic worker that scans a table has a habit nobody notices, because it is never in a request path. Settlement reconciliation is the classic example. Roughly, it needs every transfer that has been paid on-chain but not yet paid out:

SELECT id, user_id, amount_minor, currency, chain_tx_signature
FROM transfers
WHERE settlement_state = 'awaiting_payout'
  AND created_at < now() - interval '15 minutes';

With no supporting index this is a sequential scan of the entire transfers table, once a minute, forever. It will not show up as a slow endpoint. It will show up as background load, shared buffer pressure, and a settlement loop that finishes late under exactly the conditions where finishing late is worst.

CREATE INDEX CONCURRENTLY transfers_settlement_pending_idx
    ON transfers (created_at)
    WHERE settlement_state = 'awaiting_payout';

That is a partial index. It contains only rows in the state that actually gets queried, so it stays small no matter how large the table grows, and it is not updated for rows that have already settled.

Worth knowing: a partial index on a state column needs no extra bookkeeping when a row leaves that state. An UPDATE that changes the indexed predicate removes the row from the index, so this stays correct on its own.

03Query three: the search that cannot use an index

This one is different, because the fix is usually not an index. Support asks for a way to find a transaction by the reference string printed on a customer's receipt:

SELECT * FROM transactions WHERE reference ILIKE '%' || $1 || '%';

A leading wildcard cannot use a B-tree index. Not "uses it inefficiently": cannot. This is a full scan plus a filter, and it scales with the size of the table rather than with the number of matching rows.

The honest question is whether this query should exist at all. If reference strings are structured identifiers rather than free text, the right answer is not a faster fuzzy search, it is a different shape:

-- The common case: exact match against a unique index.
SELECT * FROM transactions WHERE reference = $1;
 
-- The genuinely fuzzy case, if you still need one.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY transactions_reference_trgm_idx
    ON transactions USING gin (reference gin_trgm_ops);

The lesson is not "always add pg_trgm". It is that a LIKE '%x%' in application code is usually a symptom of not having modelled the thing being searched for.

04The four rules I apply now

1. Read the plan before shipping the query. EXPLAIN (ANALYZE, BUFFERS) against production-shaped data. A sequential scan on a table that will grow is a decision, not an accident.
2. If a query has a filter and a sort, write the composite index in that order. Every time. It takes ten seconds.
3. Partial indexes for state machines. Transfers, jobs, payouts: the hot state is a small minority of the table, so the index should be too.
4. A leading-wildcard search is a modelling problem. Parse the identifier, or accept the slow path and keep it off the request path.

And the one that catches everything else: watch the plans of the queries you wrote under deadline. The transaction history query above is idiomatic, and I would write it the same way again. Speed is not a property of a query; it is a property of a query and a row count.