Using Agents to Hand-Tune SQL and Self-Healing Infrastructure

GoldRush serves blockchain data through APIs, but the system behind those APIs does not behave like a conventional request-response application. A single request can traverse years of history, aggregate state across billions of records, and coordinate work across a distributed warehouse. Even apparently identical requests can have radically different physical costs. A newly created wallet with five transactions and a centralized-exchange hot wallet with millions of transactions can call the same endpoint on the same chain, against the same schema and logical query, while producing entirely different scan volumes, join cardinalities, memory pressure, and execution plans.

This makes “make the query faster” an incomplete objective. We need to minimize the total cost of producing a correct API response across latency, CPU time, memory, rows scanned, and cluster capacity, without improving one class of request by creating a regression in another. That has always required judgment. Covalent's team has decades of combined experience tuning databases and distributed systems by hand: reading plans and runtime profiles, forming hypotheses about physical execution, testing rewrites, and learning, often the hard way, that cleaner SQL is not necessarily faster SQL.

We are now encoding much of that loop into agents. We call the approach agent-guided query optimization. The agent does not merely rewrite a SQL string. It works from execution context, proposes a falsifiable performance hypothesis, generates a candidate change, and helps test that candidate across representative workloads. Safe changes can move through shadow traffic and staged rollout automatically; ambiguous changes are escalated to an engineer. The long-term goal is self-healing autonomous infrastructure: a stack that can observe its own behavior, experimentally identify a better configuration, verify the result, and improve continuously.

GoldRush is not packaged SQL over Snowflake

GoldRush is not an API wrapper around SQL running in a third-party managed warehouse. Covalent owns the serving layer, the Covalent Data Warehouse, the storage and compute infrastructure, and the underlying hardware. A request travels through a system we operate end to end:

That vertical integration gives us a larger optimization surface. We can change the SQL, but we can also change session-level execution settings, application-side query construction, batching, concurrency, caching, materialization, workload routing, table layouts, storage placement, and eventually hardware allocation. It also lets us measure both sides of performance: API latency describes what the customer experiences, while CPU time, memory, rows scanned, partitions touched, and network traffic describe what the infrastructure spends.

The distinction matters. A change that reduces a response from 400 milliseconds to 320 milliseconds is visible to the caller. If the same change also removes most of the CPU work, its larger effect may be on concurrency and required cluster capacity. Conversely, an attractive p50 improvement can conceal a p99 regression for high-cardinality inputs. Because we own the complete path, we can optimize the physical cost of the response instead of treating latency as the only available signal.

SQL is only the visible representation of that work. A small syntactic change can alter partition pruning, join distribution, tablet selection, aggregation strategy, intermediate cardinality, memory allocation, and communication between execution nodes. The relevant question is therefore not whether a candidate looks simpler. It is what the engine actually does with it under the real schema, data distribution, database version, cache state, and concurrent cluster load.

Database performance is an experimental discipline

Generating a plausible rewrite is easy. Establishing that it is better is the hard part. The same query can win on a warm cache and lose on a cold one, improve a fresh-wallet request while degrading a CEX-wallet request, or reduce latency while consuming enough additional CPU to lower total system throughput. A useful optimizer must treat every proposed change as a hypothesis to test rather than an answer to accept.

Our optimization loop has six stages:

1. Observe. Assemble the evidence an experienced database engineer would want: SQL, schema, execution plan, runtime profile, partition and scan behavior, cardinality, input characteristics, latency, CPU time, memory, rows scanned, and relevant cluster state.

2. Form a hypothesis. Explain the suspected source of cost in physical terms. Examples include a redundant self-join, an avoidable large-integer cast inside an aggregation, or a general-purpose distributed join being used after the application already knows the exact keys it needs.

3. Generate a candidate. Produce the smallest coherent change that tests the hypothesis. The candidate may be a SQL rewrite, a different predicate representation, a session variable, or an application-level change in batching and query construction.

4. Benchmark workload classes. Run repeated tests across address categories selected for their different physical behavior: fresh or sparse wallets, active wallets, and high-cardinality addresses such as exchange hot wallets. We control for cluster load and cache state, then report error rate, memory used, CPU time, and rows scanned. These statistics expose both correctness and physical cost rather than reducing the experiment to one latency number.

5. Shadow production traffic. Exercise the candidate for 24 hours against production-shaped traffic and report the same four statistics: error rate, memory used, CPU time, and rows scanned. A synthetic benchmark tells us whether a change can work; shadow traffic tells us whether we should ship it.

6. Roll out or escalate. Deploy proven changes per chain rather than per endpoint. Chain history changes the physical workload: Ethereum contains roughly ten years of history, while Robinhood contains only about two months. Treating them as one rollout unit would collapse materially different data distributions into a misleading pass/fail result. If the evidence exposes a regression envelope or semantic risk on any chain, keep a human in the loop.

The last step is essential. Autonomy is earned through evidence. An optimizer that deploys every locally successful rewrite is not autonomous in a useful sense; it is merely unsupervised. A self-healing system must also know when its proof is incomplete.

The following three cases show how this works in practice. They involve different endpoints and different transformations, but each follows the same pattern: observe the physical execution, state a hypothesis, change the execution strategy, and validate the result across the workload distribution.

Case study 1: Token Holders and the cost of aggregation

The Token Holders endpoint returns a paginated list of current or historical holders for an ERC-20 or ERC-721 token. To reconstruct the latest balance for each holder at a block height, the original query grouped historical balance updates, selected the balance associated with the latest block, cast the result to an arbitrary-precision number to remove zero balances, and repeated that numeric cast for ordering. The PostgreSQL-compatible examples below are simplified representations of the production query shapes:

The agent's hypothesis was that the numeric conversions were forcing unnecessary work over the grouped result. Because balances are stored as canonical non-negative decimal strings, zero can be filtered directly. Numeric ordering can be preserved without converting every value to numeric: compare decimal-string length first, then compare the string lexicographically among values of equal length.

The important distinction is between SQL semantics and physical cost. Both forms describe the same result, but the latter avoids repeated arbitrary-precision conversion across the grouped output. The candidate was eligible for fully automated validation, 24 hours of shadow traffic, and per-chain deployment.

For the Token Holders benchmark, wall time improved modestly, from 2,472 ms to 2,335 ms, while the physical cost fell much more sharply. CPU time dropped 61%, from 31,343 ms to 12,214 ms, and peak memory dropped 17%, from 1,750 MB to 1,458 MB. Rows scanned remained fixed at 34.49 million, which isolates the gain: the rewrite did not read less data, but it processed the same data much more efficiently.

The broader lesson is that optimization requires a model of the real execution engine. A theoretically equivalent hint that the engine ignores is not an optimization, and a mathematically direct cast can be more expensive than an ordering representation designed around the stored data.

Case study 2: Approvals and the join we did not need

The Approvals endpoint finds approval events across token contracts and categorizes them by spender for a wallet's assets. The original query ranked matching log events to select the latest approval for each sender-and-spender pair. It then joined the ranked rows back to logs using block height, transaction offset, and block index to retrieve raw_log_data:

The useful question was simple: why retrieve data in a second operation when it was already available during the first? The rewritten query carries raw_log_data through the ranked common table expression and reads it directly from the winning row. It also uses the precomputed base64 topic literal, avoiding runtime conversion and giving the engine a pruning-friendly constant.

This was not an attempt to make a distributed self-join slightly faster. It removed the self-join from the plan. That reduces the amount of data the engine must revisit, distribute, and match, while preserving the endpoint's result. Like the Token Holders change, it could move through the fully automated path: benchmark across workload classes, shadow for 24 hours, then roll out with resource and error telemetry attached.

The Approvals benchmark shows the benefit across every reported resource dimension. Wall time fell from 240 ms to 84 ms, a 65% reduction. CPU time fell 17%, peak memory fell 8%, and rows scanned fell 19%. Unlike the Token Holders result, this rewrite removed physical input work as well as compute work, consistent with eliminating the self-join.

The principle generalizes: the fastest distributed join is often the one you prove you do not need. Agents are effective here because they can trace which values already exist at each stage rather than treating each clause as an isolated target for local rewriting.

Case study 3: Transactions v3 and application-known keys

Transactions v3 endpoint returns transactions involving an address together with decoded log events, in paginated form. holder_transactions is an address-centric lookup table that records which transactions involve each wallet. It contains the transaction identifiers and chain coordinates needed to locate those transactions efficiently, including block height, transaction offset, block date, and timestamp. It does not contain the full set of decoded log events; those live in logs. The original implementation joined the address's rows from holder_transactions to logs on block height, transaction offset, and block date:

This query asks the warehouse to solve a general join problem. The application, however, learns the exact log keys after selecting the relevant transactions. That information changes the problem. Instead of rediscovering matches through a distributed join, the optimized path obtains the keys first, splits them into bounded application-side batches, generates one exact-match branch per key, and combines the branches with UNION ALL:

The shape is less elegant than a single declarative join, but elegance is not the benchmark. Exact predicates expose application knowledge that a general-purpose optimizer cannot infer, allowing the engine to target the relevant log records directly. Keeping batches bounded also prevents query construction and planning overhead from growing without limit.

Transactions v3 produced two useful benchmark profiles. Under the memory-budget workload, the keyed strategy cut wall time from 7,520 ms to 460 ms, a 94% reduction, while CPU time declined only 4%. The tradeoff was material: peak memory rose from 408 MB to 898 MB, a 120% increase, and rows scanned rose slightly from 704,985 to 709,720. This is a latency win, but not a globally cheaper plan, and it explains why the optimization requires a budget-aware guardrail rather than unconditional rollout.

The rate-pivot workload showed a different envelope. Wall time improved from 24.7 ms to 16.7 ms, while CPU time fell 33%, from 109 ms to 73 ms. Rows scanned remained unchanged at 3,930, and peak-memory figures were not reported for this run.

These benchmark tables report wall time, CPU time, peak memory, and rows scanned. Error rate is also part of the 24-hour shadow-traffic acceptance criteria, but no error-rate value was included in this benchmark snapshot, so none is inferred here.

This case also demonstrates why workload variance must be part of the model. For an address with a large history, avoiding a broad join can remove substantial physical work. For a small, recent address, constructing and executing many exact-match branches, or enriching the page in a separate step, can introduce overhead that the original query did not have. The notes record a regression associated with the page-enrichment JSON change, so this case remains human-in-the-loop rather than fully automated.

That is not a failure of the approach. It is evidence that the experimental controls are working. There may be no universally winning query. The correct end state may be multiple execution strategies, with the system selecting among them using observable workload characteristics such as key count, date range, and expected cardinality. For blockchain data, “fresh wallet or CEX hot wallet?” is not an edge-case question; it is central to choosing the physical plan.

Turning database experience into software

Historically, the optimization loop at Covalent looked familiar: a production workload exposed a problem; an experienced engineer inspected the plan and runtime profile; the engineer formed a hypothesis, rewrote the query or adjusted the engine, benchmarked the result, and deployed it. The expertise lived primarily in people, and the loop ran when the expected value justified their attention.

The new loop begins with production telemetry and makes that discipline continuous. Agents assemble context, identify expensive physical behavior, propose an optimization hypothesis, produce a candidate implementation, exercise it across a workload matrix, and analyze shadow-traffic results. The system can then roll out a well-supported change or give an engineer the evidence needed to resolve an ambiguous one.

We spent decades learning how to tune databases by hand. Agents now perform much of that optimization loop for us, not by replacing the judgment embedded in the process, but by making it executable, repeatable, and always available. The three cases above illustrate the range already required: engine-specific aggregation behavior, elimination of redundant work, and an application-level execution strategy that SQL alone could not express.

Toward self-healing autonomous infrastructure

SQL is the first control surface, not the boundary. Because Covalent operates the GoldRush API, warehouse, storage, compute, and hardware, the same evidence-driven loop can expand from query selection into session configuration, caching, materialization, partitioning, table layout, concurrency, workload routing, storage placement, and hardware allocation. Each layer can expose measurements, candidate actions, safety constraints, and a rollback path.

Self-healing does not mean making unreviewed changes everywhere. It means continuously closing the loop between observed behavior and verified remediation. Some changes can be proven safe and deployed autonomously. Others should remain behind human review until the system can characterize their regression envelope. In both cases, the agent compresses the expensive part of database tuning: gathering evidence, generating hypotheses, running controlled experiments, and preserving what the system learns.

GoldRush will never be “fully optimized.” Chains change, workloads shift, distributions evolve, endpoints acquire new semantics, database engines improve, and hardware changes underneath them. A static optimum would become obsolete as soon as the environment moved.

The more useful goal is infrastructure that never stops optimizing itself.