/search endpoint timing out (524) while core ingestion pipeline is healthy

[JakubIvacek]

The /search endpoint on the public API (api.opendota.com/api/search) hangs indefinitely for any query longer than 2 characters, instead of returning results or an error. Eventually Cloudflare times out the connection with a 524 (Origin Timeout).

Steps to reproduce

curl -s --max-time 30 "https://api.opendota.com/api/search?q=Miracle"

never returns, even after 30s

curl -s -D - "https://api.opendota.com/api/search?q=Miracle" --max-time 60

eventually: HTTP/2 524, body: "error code: 524"

Compare with a 1-2 character query, which returns instantly:
curl -s "https://api.opendota.com/api/search?q=ab"

-> [] (200 OK, near-instant)

Additional testing

Reproduced consistently (8+ attempts over several minutes) across multiple query strings: Miracle, abc, kuro, sumail, puppey — all hang/timeout. Other endpoints on the same API are unaffected and respond normally in the same timeframe:

curl -s -o /dev/null -w "%{http_code} %{time_total}\n" "https://api.opendota.com/api/heroStats"

200, ~0.8s

curl -s -o /dev/null -w "%{http_code} %{time_total}\n" "https://api.opendota.com/api/players/1"

200, ~0.15s

curl -s -o /dev/null -w "%{http_code} %{time_total}\n" "https://api.opendota.com/api/explorer?sql=SELECT%201"

200, ~0.3s

This isolates the problem specifically to /search, not the API/Cloudflare/rate limiting in general.

Expected behavior

/search?q= returns matching players (or an empty array) within a reasonable time, as it does for short queries.

Actual behavior

Any query longer than 2 characters hangs and never returns a response, ultimately surfacing as a Cloudflare 524.

Environment

• Endpoint: https://api.opendota.com/api/search?q=...
• No API key used (public/unauthenticated request)
• Observed: 2026-07-12 (reproducible over multiple minutes, not a one-off blip)

---

[howardchung]

We've recently seen a large increase in usage and are in the process of scaling up servers to meet the demand.

[pd241008]

Hey @howardchung — dev and Dota player here, comfortable in TypeScript, and I've shipped a few production sites. Happy to dig into this if it's open for a contribution.
Quick hypothesis based on the repro: since queries ≤2 chars return instantly but anything longer hangs indefinitely (not just "slow" — actually hangs), this looks less like a missing index and more like something that never resolves/rejects at all — e.g. a DB call whose promise/callback is never settled for a certain code path, or a query hitting a connection pool that's exhausted and just queues forever. A pure missing-index issue would usually show up as slow (many seconds), not an infinite hang with no timeout of its own until Cloudflare kills it at the edge.
I'd like to check routes/api (or wherever the /search handler lives now) for:

whether the search query has an explicit statement/connection timeout
whether there's a code path for longer query strings that skips a .catch/error handler
what the actual SQL looks like for the player-name search (trigram/ILIKE) and whether it's using an index for substrings ≥3 chars

Can you point me to the current search route file, or say if this is up for grabs? Happy to send a PR.

[JakubIvacek]

Hey @howardchung — dev and Dota player here, comfortable in TypeScript, and I've shipped a few production sites. Happy to dig into this if it's open for a contribution. Quick hypothesis based on the repro: since queries ≤2 chars return instantly but anything longer hangs indefinitely (not just "slow" — actually hangs), this looks less like a missing index and more like something that never resolves/rejects at all — e.g. a DB call whose promise/callback is never settled for a certain code path, or a query hitting a connection pool that's exhausted and just queues forever. A pure missing-index issue would usually show up as slow (many seconds), not an infinite hang with no timeout of its own until Cloudflare kills it at the edge. I'd like to check routes/api (or wherever the /search handler lives now) for:

whether the search query has an explicit statement/connection timeout whether there's a code path for longer query strings that skips a .catch/error handler what the actual SQL looks like for the player-name search (trigram/ILIKE) and whether it's using an index for substrings ≥3 chars

Can you point me to the current search route file, or say if this is up for grabs? Happy to send a PR.

its here svc/util/queries.ts line 433-452
// Set similarity threshold
// await db.raw('SELECT set_limit(0.5)');
let rows = [];
const trim = query.trim();
if (trim.length >= 3) {
const personaNameMatch = await db.raw(
SELECT account_id, avatarfull, personaname, last_match_time, similarity(?, personaname) as sml FROM players WHERE personaname ilike ? ORDER BY sml DESC, last_match_time DESC NULLS LAST LIMIT 50;,
// replace spaces with % so we trigram search around them
[trim, %${trim.replaceAll(" ", "%")}%],
);
rows = personaNameMatch.rows;
}
// Later versions of postgres have strict_word_similarity / <<% which may be more accurate
return [...accountIdMatch, ...rows];

[pd241008]

Found it — cloned the repo and checked svc/util/queries.ts + svc/store/db.ts. There's actually already a trigram GIST index on personaname, so that's not the gap. The real issue is that two safety valves are commented out:

In svc/store/db.ts:

statement_timeout: 600000, // 10 min — way past Cloudflare's ~100s 524 timeout
// afterCreate: (conn, done) => {
//   conn.query('SET statement_timeout TO 60000;', ...)
// }

In svc/util/queries.ts:

// Set similarity threshold
// await db.raw('SELECT set_limit(0.5)');

Without set_limit(), common/short substrings match a large chunk of the players table via ILIKE, and similarity() then gets computed and sorted over all those matches before the LIMIT 50 applies. That's slow enough on a multi-million-row table that Postgres is still running it well past when Cloudflare gives up and returns 524 — it's not actually hanging forever, just taking longer than the edge timeout allows, and the connection-level timeout that should cap it at 60s is disabled.

Proposed fix: re-enable set_limit() (tune the threshold, maybe 0.3–0.4) and the 60s statement_timeout override in the pool config, then confirm with EXPLAIN ANALYZE on a slow query string that the GIST index is actually being hit. Happy to open a PR with that plus a benchmark against a slow query if you're good with the approach.

[howardchung]

Ideally we should just have enough hardware to meet the demand rather than hitting the timeout, I think. It's still not great if we're hitting the adjusted timeout

[pd241008]

@howardchung Totally fair — agreed the timeout tweak alone is just moving the failure point, not fixing the cost of the query. Scaling hardware helps with overall throughput, but this specific query has a structural cost problem that will keep biting as usage grows: similarity() is computed and sorted over every ILIKE match before LIMIT 50 trims it, so cost scales with how many rows match the substring, not with how many results are returned.

That's what set_limit() is actually for — it's not a timeout workaround, it's a filter. Re-enabling it lets Postgres use the trigram index's similarity threshold to cut the candidate set before scoring/sorting, so the query does less work per request regardless of hardware. That should reduce load on the DB itself, which helps the scaling effort too rather than working against it.

Concretely, I'd propose:

1. Re-enable set_limit() with a tuned threshold (e.g. 0.3–0.4) so cheap/short substrings don't force a huge candidate set.
2. Run EXPLAIN ANALYZE before/after on a known-slow query (e.g. q=Miracle) to confirm the GIST index is actually being used to pre-filter, not just scanned post-hoc.
3. Keep a sane statement_timeout as a backstop, not the fix — so a pathological query fails fast with a clear error instead of hanging until Cloudflare kills it.

Happy to put together a PR with the set_limit() change plus before/after EXPLAIN ANALYZE numbers so you can see the actual cost difference before deciding if it's worth merging alongside the scaling work.