Concurrency Design Under High TPS: Moving Wallet Counters Off Database Triggers

Four automation wallets, each targeted by roughly 500 automation tasks landing in the same block, was enough to stall the indexer for three to four days. That's the incident that forced a redesign of how SupraScan counts wallet transaction totals, and the redesign that followed it is a good example of how a fix motivated by one incident ends up exposing a second problem nobody was originally looking for.

What the trigger-based design got wrong under load

Wallet transaction counts were originally maintained by database triggers: insert a transaction row referencing a wallet, and a trigger on that table incremented the wallet's stored count automatically. It's a clean pattern in isolation, and it works fine until the same wallet row is targeted by many concurrent writers at once. When automation tasks fire in bulk against a small number of wallets in the same block, every one of those inserts tries to lock and update the same handful of wallet rows through the trigger, and the database has to serialize all of that contention. Four wallets absorbing roughly 2,000 automation-triggered transactions in a single block was enough to create a multi-day backlog, because the trigger contention on those specific rows became the bottleneck for the entire indexing pipeline, not just for those four wallets.

The fix for the insert side moved wallet-count maintenance out of the database trigger and into application-level logic, giving the code direct control over how and when those counts get updated, instead of leaving it to a mechanism that serializes on row locks by default.

The mirror work on delete, and the review that caught what testing didn't

Fixing the insert side solved the immediate incident, but the same tables also had delete-side triggers doing the equivalent decrement, and those carried the identical contention risk. The mirror work replaced three separate delete triggers, on the sender, receiver, and fee-payer tables, with application-level decrement logic, following the same pattern already proven on the insert side.

What's worth calling out here isn't the mechanism, it's the process around shipping it. Three real correctness bugs were found and fixed during code review, before merge, and two of those three were caught by a reviewer, not by the person who wrote the change. That's a detail easy to leave out of a case study because it isn't flattering in the usual sense, but it's exactly the kind of evidence that matters: a change this close to core data integrity got a second set of eyes that found problems the first pass missed, and the process worked the way it's supposed to.

The rollout itself was deliberately cautious given what was at stake. Processing was fully paused, a drift query ran against the still-paused system comparing the stored wallet totals against a value freshly recomputed from the source transaction tables, the trigger-removal migration ran, the same drift query ran again to confirm nothing had changed, and only then did processing resume. QA testnet went first. The full rollout, across every QA environment and both production networks, shipped in early August 2026, with the triggers confirmed dropped and the drift check clean on every environment.

A separate, unrelated bug turned up during this same work: a configuration flag governing in-loop transaction-info removal was found live in production, in a way that violated its own documented pairing rule with the partition-management flag from the storage-scaling work. It was flagged, not fixed immediately, a reasonable call given it wasn't the thing actively causing an incident.

The bigger question this raised, and the assumption that turned out to be wrong

While this work was underway, a separate initiative was considering something much larger: building a full write-ahead-log-based counting system to replace the interim application-level fix entirely, with its own dedicated architecture and integration specs. The stated motivation wasn't the load already observed, it was a theoretical worst-case burst ceiling, on the order of 500,000 transactions per second under a maximum block rate and block size, versus a sustained load at the time closer to six transactions per second. The concern was hot-wallet row contention under a burst nowhere near what had actually happened yet.

Midway through that discussion, a direct check against the live production deployment configuration turned up something that changed the shape of the entire argument: the production indexer was running as a single instance, not the two instances that existing architecture documentation had claimed. That distinction matters enormously for a concurrency argument. A single instance can coordinate its own internal work without needing distributed locking, because there's no second process to race against. The primary justification for building a distributed, Redis-coordinated write-ahead-log system, protecting against cross-instance concurrent writers, simply didn't apply to the system as it was actually deployed. What remained of the WAL system's value after that correction was observability and per-module statistics, useful, but a different and much smaller problem than the one it was originally scoped to solve.

Two more trigger removals, and one that both prior efforts missed

The same interim pattern, application-level counting instead of a database trigger, was later mirrored for automation execution counts on a related table, shipped to QA first with production following shortly after. A third trigger, tied to automation registration counts on the same underlying entity, was found only later, and only because of a full, systematic audit across every live database rather than trusting that two prior rounds of "complete" migration had actually been complete. Two separate migration efforts, each believed finished when it shipped, had both missed a related trigger sitting on the same table family. The audit that finally caught it didn't rely on anyone remembering to check; it re-verified live state directly, which is the only thing that actually caught what two rounds of careful, reviewed work had both missed. Two of those three migration passes, the insert-side and delete-side removals on the sender, receiver, and fee-payer tables, are confirmed shipped and verified clean across every environment. The third, the registration-count trigger this same audit surfaced, was implemented in application code but its merge and production rollout weren't independently reconfirmed as of this writing, worth stating plainly rather than rounding up to 'done' before the same kind of live check that caught it in the first place gets run against it again.

Why this mattered beyond the indexer's own stability

This work wasn't prioritized purely for the indexer's own sake. A separate team building automation tooling had privately validated their own system could handle 100,000 concurrent tasks on a private test network, but had explicitly agreed not to raise the live, on-chain task-capacity limits on the real network until the indexer's concurrency handling was confirmed ready for that scale. The chain's own configuration allows those capacity limits to be raised with no hard ceiling in the underlying code, only a governance action, which meant the actual blocker to a real capacity increase wasn't a protocol limit, it was confidence that the indexer wouldn't repeat the four-wallet incident at ten or a hundred times the concurrent load. This trigger-removal and concurrency-hardening work was the literal, agreed-upon gate that team was waiting on, not preparation for a hypothetical future.

What this demonstrates

The most useful thing about this sequence isn't the trigger-to-application-logic pattern itself, which is a known technique. It's what surrounded it: an incident that motivated an immediate fix, a review process that caught real bugs a test suite hadn't, a live-configuration check that overturned an architectural assumption a much larger initiative had been built on, and an audit discipline that treated a prior migration's own claim of completeness as something to verify, not trust. Concurrency bugs at this scale rarely announce themselves cleanly. Finding them consistently is less about any one clever fix and more about refusing to assume the last fix, or the existing documentation, already got it right.