Random nulls: the bug that hid behind a silent catch

The problem

I maintain a data source service. Its job is simple to describe: users register "sources" (a read-only SQL query against one of several company databases, or a JSON endpoint), and other applications ask for the data by a short code. A product configurator, for example, asks for the list of materials, the available finishes or a Yes/No list, and shows them as options.

When a configurator caches its full tree, it asks for all the sources it uses in one batch request:

POST /sources/execute/batch
{ "codes": ["SRC-001", "SRC-002", "SRC-003", … 23 codes] }

Then one day, a user opened a cached configurator and found this:

"sources": {
  "SRC-001": { "message": "OK", "data": [ … ] },
  "SRC-002": null,
  "SRC-003": null
}

Some dropdowns were simply empty. Not all of them. Not the same ones every time. And because the configurator stores its cached tree, those nulls were not a temporary glitch: they were written down and served to every user afterwards, until someone noticed and re-cached by hand.

When I looked across all cached configurators:

A bug that corrupts data silently and randomly is the worst kind. The randomness makes it hard to reproduce, and the silence means nobody even knows it happened.

The details

Step 1: the response told me nothing

Every failed source came back as exactly the same thing:

{ "message": "Server error.", "data": null }

No error code, no reason. Inside the service, the code that ran a source's query looked like this (simplified):

async function runQuery(connectionString: string, sql: string) {
  try {
    const client = new pg.Client({ connectionString });
    await client.connect();
    const result = await client.query(sql);
    await client.end();
    return { message: 'OK', data: result.rows };
  } catch {
    return { message: 'Server error.', data: null };
  }
}

That bare catch {} is the real villain of this story. Whatever went wrong (bad SQL, wrong password, network issue, server full) was thrown away. The log said nothing. The response said nothing. The caller received a well-formed answer with null inside, and stored it.

The original intent was reasonable: these execute endpoints are public, so they must not leak database details to the outside. But "don't leak details to the client" accidentally became "don't tell anyone, including us".

Step 2: make the error visible

The first change wasn't a fix. It was logging the real error, server-side only:

} catch (error) {
  logger.error(`Source ${code} failed: ${(error as any).code} ${(error as Error).message}`);
  return { message: 'Server error.', data: null };
}

Re-running the same batch immediately showed it:

Source SRC-002 failed: 53300 sorry, too many clients already
Source SRC-003 failed: 53300 sorry, too many clients already

53300 is PostgreSQL's SQLSTATE for too many connections. The external database server was refusing new connections.

Step 3: catch it in the act

Knowing the error wasn't enough. I wanted to see why the server was full, so I watched the server's connections (pg_stat_activity) while a real batch was running:

BEFORE batch :  1 session from us  |  85 on the whole server
DURING batch : 21 sessions from us | 105 on the whole server   ← over the limit of 100
AFTER  batch :  1 session from us  |  85 on the whole server   (the burst lasted ~1.2 s)
failed codes : 3

Now the whole picture was clear:

• The external server is shared by about ten applications. Its max_connections is 100.
• About 85 slots were already held by other applications. Most were idle, but an idle connection still occupies a slot.
• That left roughly 15 free slots.
• Our batch of 23 codes opened a brand-new connection for every code, all at the same moment, so 23 connections arrived when only about 15 were free.

Step 4: explain the randomness

Why different codes each time? Because all 23 connection attempts race each other. Whichever ones arrive after the free slots run out get rejected. It depends on network timing, not on the source. That's why two consecutive runs failed on different codes, and why a configurator copy could lose all 23 while the original lost 8: the free slots on the shared server were different at that moment, because other applications had their own activity.

And the design got worse with growth:

The challenges

The symptom pointed in the wrong direction

"Some values are null" suggests broken queries or missing data. The sources were fine; every single one worked when run alone. The problem only existed under concurrency, on a server I don't control, at moments that depended on other applications.

Public endpoint, private errors

The error had to become visible to us, without making the public response leak connection strings, schema names or driver messages.

The damage was already stored

Fixing the service would stop new nulls, but the configurators that had already cached null would keep serving them. The fix had to include repairing existing data, not just the code.

Not my server

I couldn't raise the shared server's connection limit or ask ten other teams to close their idle connections. The fix had to live entirely on our side.

The solution

The fix came in layers. Each one has its own post, because each is a lesson on its own. Here is the overview.

1. Never open a connection per request

Instead of new pg.Client() per source, the service now keeps a pool per database, and all pools on the same server share a fixed connection budget. A burst no longer gets rejected by the server; it waits in our own queue. Details in "One pool per database, one budget per server".

2. Classify errors, retry only what can succeed

A 53300 is temporary: a moment later, a slot is free. A syntax error is permanent: retrying can never help. Errors are now classified, temporary ones are retried with a short backoff, and the public response carries a coarse label (TOO_MANY_CONNECTIONS, QUERY_FAILED…) instead of a blank null. Details in "Transient vs. permanent errors".

3. Stop hitting the shared server on the user's path

The deepest fix: query results are stored and refreshed by a slow nightly job. A batch request now reads stored results from our own database and opens zero connections to the shared server. Details in "Stop querying live".

4. Measure again

With the pool and the error handling in place, the same 23-code batch, five times in a row:

before : 0 connections from this machine
run 1–5: 23 codes, 0 null, peak 8 connections
12 s later: 0 connections
no retry needed

5. Repair the stored damage

Finally, the affected configurators were re-cached. Configurator A went from 23 nulls to 0, configurator B from 8 to 0. A one-off repair, but a necessary part of the fix: a bug that wrote bad data isn't fixed until the bad data is gone.

Lessons learned

• An empty catch block is a data-loss bug waiting to happen. Catching is fine; forgetting is not. Always log what you caught.
• "Don't leak details" ≠ "don't record details". Keep public responses vague and private logs precise.
• Random failures usually mean contention. When different items fail each run, look at shared resources (connections, locks, rate limits) before looking at the items.
• Measure the shared resource while the bug happens. Watching pg_stat_activity during one batch turned a theory into a fact: 105 sessions on a server limited to 100.
• Idle connections still count. On a shared server, every application's idle pool is part of your budget too.
• Cached errors become permanent errors. If a failure can be stored, it must be distinguishable from real data, or refused before it is stored.
• A fix includes the cleanup. Repair the data your bug already damaged.

FAQ

What does PostgreSQL error 53300 mean?

53300 is the SQLSTATE code for too_many_connections: the server reached its max_connections limit and refused a new connection. It's usually temporary, so retrying after a short delay or queuing requests behind a connection pool solves it.

Why are empty catch blocks dangerous?

They hide the real cause of failures. The code keeps running with a default value (often null), so the failure turns into wrong data instead of a visible error, and nobody can investigate because nothing was logged.

How can I see who is using connections on a PostgreSQL server?

Query the pg_stat_activity view. It lists every connection with its database, user, application name, state (active, idle…) and current query. Grouping it by application or state shows who holds the slots.

Why do different requests fail each time under load?

When many requests compete for a limited resource at the same moment, the ones that arrive after the resource runs out fail. Arrival order depends on timing, so the failing requests change from run to run. That's a strong sign of resource contention, not broken requests.