Go API query optimization, measured

The detailed pass found two different performance problems. Several Go queries prevented PostgreSQL from using indexes that already existed, while two high-level handlers repeated the same database work many times. Fixing those paths changes player search and props screening by orders of magnitude. It does not change the referee aggregate, where both APIs still spend almost all of the request inside the same database-bound work.

What changed

The props fix is a type fix, not a new cache. PostgreSQL cannot use a bigint btree efficiently when the column is coerced through a numeric[] comparison. The Go API now rejects fractional and out-of-range IDs from the SQL parameter; those values cannot match a bigint ID. An all-invalid list becomes FALSE instead of accidentally widening the request.

Player search had a similar expression mismatch. The database already had a trigram index on immutable_unaccent(lower(players.name)), but the query used a different normalization chain. The new predicate matches the indexed expression exactly. EXPLAIN (ANALYZE, BUFFERS) now shows a bitmap index scan and completes representative Arsenal and Alex searches in 1.5–1.6 ms.

Final API comparison

Both servers ran in production mode against the same local 35 GB PostgreSQL mirror. The table uses concurrency 8 because it keeps every endpoint below its timeout envelope and exposes useful throughput without comparing failed work.

The search result is the largest query-specific improvement. The final Go build completes all 2,090 requests issued at concurrency 2,048, sustaining 194.1 successful requests per second. Legacy reaches the 20-second timeout at concurrency 128 and records 300 timeouts at concurrency 512.

The props screener also remains stable through concurrency 2,048: 534.1 successful requests per second with no failures, against 438.2 for legacy. Its gain is smaller because both implementations use the same ten-connection pool and the final query is already indexed.

How the pass covered the query surface

The static inventory covered all query construction under the Go API:

• 126 Go files containing database work
• 117 NewSelect sites and 209 NewRaw sites
• 18 inserts, 19 updates, and 12 deletes
• 48 JSON row scans
• about 422 effective query construction sites after accounting for direct expressions and helper-generated statements

Every site was classified by read bound, parameter type, fan-out, ordering, and likely index path. Execution plans and endpoint timings were then taken for the high-risk shapes rather than treating source appearance as proof of cost. This is why the pass changed four query families and added four indexes, but did not add speculative indexes to every filtered column.

The batching changes preserve the legacy response. Sorted JSON from the old and new betting-trend and shots-model handlers matched exactly for the measured fixtures. Props defaults now match the legacy GET parser, including the default hit-rate and odds ranges. Search preserves the same top-ten IDs and order for the saved queries while supporting a broader set of accented names.

Load testing also exposed a concurrent panic in the shared name normalizer. A stateful text transformer had been reused across requests. Each normalization now owns its transformer, and the regression test passes under the Go race detector.

Indexes added from measured plans

The migration adds:

• system_stats(created_at DESC) for the latest-row endpoint
• partial covering indexes for positive over and under value-bet edges
• a partial players("nationalTeamId") index for non-null national-team lookups

On the mirror, the system-stats plan is now an index scan that executes in 0.099 ms and touches 17 buffers. The positive-edge count uses an index-only scan with zero heap fetches and executes in 9.0 ms.

These indexes are additive and have been verified on the mirror. The migration has not been applied to production by this report.

Test design

The first combined trial revealed an important harness failure mode: an HTTP timeout does not guarantee that the legacy database statement has stopped. Those statements can continue consuming the pool and contaminate the next endpoint. The published rows come from isolated runs. Database activity was checked between saturated runs and the next measurement started only after the active statement count returned to zero.

The explicit port is also important. Port 8080 is the shared debug server and logs every SQL statement; it is not a valid comparison with a production Next.js process. Every published Go row uses the production binary on port 8181.

Limits and remaining work

• Four-second levels locate throughput plateaus and saturation. They are not a soak test or a cloud capacity forecast.
• The client, both servers, and PostgreSQL shared one host. Absolute latency is less portable than the side-by-side result.
• /api/player/all remains an unbounded public contract. On the 6.55 million appearance-row mirror, an unfiltered request exceeds 60 seconds. Fixing it safely needs an additive pagination or required-scope contract rather than a silent limit.
• Production reports 162 never-scanned indexes totalling 3.7 GB, but this pass does not drop them. A production usage snapshot and concurrent removal plan are required before deleting schema objects.
• The referee aggregate is still the clearest read-path bottleneck. Both APIs converge at roughly 15–17 successful requests per second once the database pool is full.

Conclusion

The Go API no longer carries the two severe index-usage regressions found in this pass. Search moves from legacy's 5.8 requests per second to 191.8 at concurrency 8, and the final props endpoint closely tracks legacy while exceeding it at five of the six measured levels. Repeated query work in tournament trends and the shots model is cut roughly in half or better.

The remaining slow paths are now narrow and explicit: the referee aggregate, the unbounded player listing, and production-wide index cache pressure. They need contract or data-model changes, not another general rewrite of the Go runtime layer.