How We Built Realtime Precomputed PnL for Polymarket

Naive Polymarket PnL is a SQL join: trades grouped by market, cash flows summed, open positions marked to the latest price. It works in a demo. It dies under real traffic.

We rebuilt the whole path. Today Struct's PnL layer is two pieces of Rust that precompute every answer before anyone asks: an ingest engine that folds the chain block by block, and a custom database that keeps 378 sorted indexes live while serving lookups over HTTP.

The short version:

• 1.6 billion trades processed in under one week of backfill, now tracking 1.9B+.
• 378 distinct sorted indexes (every leaderboard, metric, and timeframe) kept current with pagination included.
• Custom database sustains up to 2 million writes/sec during backfill and ~15,000 writes/sec live, while serving reads.
• A single read node is benchmarked at 50,000+ requests/sec, because answering is a lookup into precomputed state, not a query.
• Every holder of every position is known at every block. Price ticks re-mark current and past holders so historical rows stay correct.
• Rolling 1d / 7d / 30d / lifetime windows for every trader, market pairing, and category pairing, maintained incrementally.

This is the architecture behind Struct's trader PnL APIs, leaderboards, and the realtime surfaces in products like the Polymarket trader explorer.

Why does query-time PnL fail at scale?

The original system was Postgres materialized views over a trades table. Materialized views have a property no amount of tuning fixes: they are correct at refresh time and stale every second after.

Refreshing them means re-running an aggregation over an ever-growing trades table. The refresh gets slower as the data grows, so you refresh less often. The data gets more stale precisely as the platform gets more popular. You are on a treadmill that speeds up.

PnL makes this worse than most analytics workloads because it changes when nothing happens. A trader who hasn't touched their account in a month still has a different unrealized PnL every time a held market moves.

A single price tick on a popular market changes the correct answer for tens of thousands of accounts simultaneously. A materialized view doesn't just serve stale data here. It serves data that was never going to be fresh, because the refresh cadence cannot keep up with the rate at which the answers change.

What happened when we tried streaming SQL?

Postgres materialized views were attempt one. Attempt two was the industry's recommended fix: a widely deployed open-source streaming SQL engine that maintains views incrementally as data arrives, promising the freshness of precomputation with the convenience of declaring everything as queries. We ran our real pipeline on it, at real scale, and it taught us five expensive lessons.

It works per item; we work per block

A streaming engine pays its overhead per row. Every event moves through the dataflow graph, hits state, and triggers view maintenance individually. We fold an entire block at once, so one state batch, one index pass, one flush, and one checkpoint amortize across everything in it.

A block with a thousand trades costs them a thousand fixed overheads and costs us one. In practice that was roughly 5 blocks per second against the 200-plus our engine sustains: the difference between replaying the chain in under a week and replaying it for about eight months.

It couldn't handle the fan-outs

The defining feature of this workload is that one tiny input event implies an enormous number of output changes. A single price tick changes the unrealized PnL of every holder of that position. A market resolution touches every account that ever traded it.

Expressed as declarative joins, those become update storms inside the dataflow graph, with no way to tell the engine which updates matter, which can be deferred, or which can be spread across time. Under real load the pipeline fell behind by exactly the amount of staleness it was supposed to eliminate.

Some metrics it simply couldn't do

Plenty of what we serve translated fine to streaming SQL. We kept hitting metrics where the honest answer was "this cannot be expressed as an efficient incremental query": the ones combining sliding windows, distinct counts, per-holder state, and history at block granularity, at the scale of hundreds of millions of rows. Shipping the product minus its hardest features wasn't the assignment, and the hardest features were the point.

It only ever solved half the system

Streaming SQL is a processing layer. In the best case it hands you a firehose of freshly updated rows, and you still need something on the other end to receive that firehose, keep hundreds of sort orders current, and serve paginated pages at low latency. Nothing off the shelf did that either. Even with the processing "solved," the serving side remained a mystery until we built the answer ourselves.

It cost significantly more to run

And it cost significantly more to run. For the fraction of the workload it could handle, the infrastructure bill was several times what the final system costs us on our own hardware. Paying a multiple for a subset of the features, with worse freshness, is the kind of arithmetic that makes building your own stop sounding extreme.

So we stopped trying to make someone else's engine compute the answers, and built a system where every answer is already computed before anyone asks.

What does the system do now?

Two pieces of Rust, about 76,000 lines of code in total:

• A 47,000-line ingest engine, which consumes the blockchain block by block, maintains the complete PnL state of every trader in memory, and emits incremental updates.
• A 29,000-line custom database, built on top of RocksDB, purpose-built to receive those updates and serve them behind an HTTP API, with every sort order and every page precomputed.

Some numbers, because numbers are the whole point:

That last number deserves its own section, because it's the clearest illustration of why nothing off the shelf could serve this.

Why can't a conventional database hold 378 live indexes?

The product wants leaderboards. Top traders by realized PnL. By volume. By win rate. By profit factor. By open position value. Over the last day, the last week, the last month, all time. Per category. Top traders within a single market. Biggest single trades.

Every one of those is a sort order. If you want to paginate it cheaply, every one of them is an index.

Enumerate them honestly (crossing every sortable trader metric, category metric, and market ordering with the four timeframes, plus global leaderboards) and you land at exactly 378 distinct index orderings that need to exist and be current at all times.

Now consider what that means in a conventional database. Take Postgres, where this system started. A Postgres B-tree index is not a free lookup accelerator. It's a write obligation. Every index on a table is a separate on-disk tree, and every write to the table must synchronously update every index that covers it.

When you update one trader's row, you hit three walls:

• Write amplification: Postgres walks each relevant B-tree, finds the old entry, inserts the new one, dirties pages, splits nodes, and writes it all through the WAL. One logical row change becomes dozens of physical page writes.
• MVCC bloat: Postgres's MVCC design means an update that touches any indexed column defeats the heap-only tuple optimization. The row gets a whole new index entry in every index on the table, not just the one whose column changed.
• Vacuum debt: Dead entries pile up. Vacuum has to come along later and clean them, competing for I/O with your live traffic.

With a handful of indexes on a low-write table, this is fine. It's what Postgres is for.

With hundreds of index orderings on tables receiving thousands to millions of row updates per second, the arithmetic simply does not close. A single price tick logically touches tens of thousands of rows, each covered by dozens of orderings. You would be asking the database to perform hundreds of millions of B-tree mutations per second, all WAL-logged, all fighting for the buffer cache, all generating vacuum debt. No amount of hardware makes that graceful.

The standard escape hatches (drop the indexes, add them back after the load; refresh materialized views on a schedule) are precisely the things a realtime system cannot do.

The obvious alternative is to keep the orderings somewhere cheap, like sorted sets in an in-memory store. Plenty of leaderboard systems do exactly that. It solves the write cost and buys two new problems: the rows and the orderings now live in separate systems that can disagree, and nothing keeps a page of a leaderboard consistent with the profile it links to. Our entire freshness guarantee rests on every grain being published from one atomic per-block flush, so splitting the state across two systems was the one thing we could not do.

The custom database's answer is to move the entire index problem out of the storage engine. Rows are durable on disk, but the 378 orderings are maintained separately, in compact structures of our own design that cost next to nothing to update when a row's score changes. An index update is no longer a synchronous multi-page disk mutation with WAL overhead. It's orders of magnitude cheaper. That single design decision is most of the difference between "thousands of writes per second, degrading" and "millions of writes per second, steady."

What else breaks when you aggregate PnL at query time?

The index arithmetic is the loudest failure, but serving PnL from any query-time system breaks in quieter ways too, and each of them shaped what we built.

Popular pages are your slowest

When PnL is aggregated at query time, the cost of a trader's profile is proportional to their history. A casual trader's page scans a handful of rows. A whale with hundreds of thousands of trades scans hundreds of thousands of rows. It is exactly the whale profiles and the viral markets that everyone opens.

The worst-case latency lands on the highest-traffic pages, and it grows forever, because history only grows. Deep pagination compounds it: asking a query engine for page 100 of a leaderboard means computing and discarding the first 99 pages.

In our precomputed system, a whale's profile and a first-time trader's profile cost the same. Both are a lookup into state that was maintained as the trades happened.

Time is a workload

Rolling 1-day, 7-day, and 30-day metrics change value while nobody trades. Positions age out of the window on their own. A query-time database can only handle that by rescanning the window on every request, or by refreshing rollups on a schedule that is stale by construction. There is no native incremental sliding window, and windowed distinct counts ("markets traded this week") are a full scan every single time.

Our engine treats window boundary crossings as first-class events and keeps per-row historical snapshots. A 7-day figure is a subtraction, not an aggregation. Idle traders' windows stay correct without anyone querying them.

Endpoints disagree with each other

With query-time aggregation, the profile summary, the positions list, and the leaderboard each run at their own moment against a moving database. The total on a trader's profile won't quite match the sum of their positions, and neither will match their leaderboard rank.

Users notice, and every discrepancy report costs an investigation that concludes "it's just timing." Because our engine emits every grain from one atomic per-block flush, every endpoint answers from the same block. The numbers agree because they are the same numbers.

The churn eats the database alive

Postgres never updates a row in place: every update writes a new row version and leaves the old one behind for vacuum to collect later. Summary rows that change on every trade and every price tick generate dead-tuple debt faster than autovacuum clears it under sustained load.

Tables and indexes bloat, and the cleanup competes with live traffic for I/O. Skewed data adds plan instability on top (a query plan that suits the median trader is catastrophic for the whale). The backfill story is the final insult: replaying billions of trades through live triggers and indexes is a months-long job, and the classic workaround of dropping indexes and rebuilding afterwards takes the system offline, which a serving system cannot do.

None of these are bugs in any particular database. They are the natural consequences of asking a general-purpose, query-time system to serve answers that change faster than they are asked for.

How does the engine treat the blockchain as the database?

The engine's core bet is that the full PnL state of the platform (every trader, every position, every market, hundreds of millions of rows) can be represented efficiently enough to fold the chain deterministically, block by block, entirely in memory. To guarantee availability, the architecture allows multiple identical engine nodes to run in parallel, meaning a follower with the exact same hot state can take over instantly if a leader fails. Every event (trade, split, merge, redemption, resolution) is folded into that state incrementally.

"Incrementally" is the operative word, and it's harder than it sounds. The expensive things in this domain are the fan-outs:

• A market resolution must stamp terminal prices and win/loss outcomes onto every position that ever touched the market, including holders who sold out years earlier and will never emit another event.
• Time itself is a write load: rolling 1d/7d/30d windows mean every active row has boundary crossings to process even when the trader does nothing. The engine keeps tiered historical snapshots per row so a window value is a subtraction, not a re-aggregation, and it wakes idle rows only when their boundaries actually cross.

Everything the frontend might chart is computed here, at ingest time: per-minute/hour/day PnL candles for every trader, per-category candles, holder-count history for every position, market, and event, distinct-entity counts (markets traded, categories touched) over every window, and exit markers for every position close. None of it is derived at query time. Query time is for reading.

Durability is a checkpointed, replayable design: state persists atomically with a block watermark, updates ship through a crash-safe outbox, and every apply path is idempotent, so a crash at any point resumes byte-identically. The engine also doesn't fully trust itself: sampled background verifiers continuously re-derive slices of state from first principles and heal any drift, which is how a 47,000-line incremental system stays honest over billions of events.

What makes the custom database different?

Off-the-shelf storage engines assume they don't know your workload. We knew ours exactly, and that knowledge is worth orders of magnitude.

The defining observation: the engine re-emits the same logical row many times in a short span (a hot trader's row might be updated dozens of times across a burst of blocks), but only the latest version is ever observable by a reader. So persisting every version is wasted work. Well over 80% of writes can be elided. The database's hot path appends the batch to a durability log, applies the latest state, updates the affected indexes, and acknowledges. Persistence of the merged result happens in the background, off the critical path. Reads never wait for it. They always see the latest state, which is realtime by construction.

Everything else follows from taking the write path seriously. Every choice on it, from data layout to durability cadence to how the work spreads across cores, was measured against alternatives before it was adopted, not assumed.

The result is the asymmetry that makes the whole project work. During backfill the custom database absorbs up to 2 million writes per second, which is what turns a multi-billion-trade replay into a sub-week job. In live mode it cruises at ~15,000 writes per second at a small fraction of capacity, with headroom for the ugliest resolution bursts, while every API response comes off a precomputed, already-sorted, already-current structure.

Reads scale the same way: because no request ever executes a query, a single node is benchmarked at 50,000+ reads per second, and what it's actually spending that time on is HTTP and serialization, not data access. Latency doesn't degrade with data size, trader history, or page depth, because none of those change what a lookup costs.

Does the same playbook power the rest of Struct?

PnL is the deepest application of this strategy, but it isn't the only one. The same precompute-at-ingest strategy powers our realtime metrics across the board: market analytics, position statistics, holder data. Wherever the answer changes faster than it's asked for, we compute it as the chain moves and keep it hot, so the API's job is delivery, not derivation.

import Struct from "@structbuild/sdk";

const client = new Struct({ apiKey: "YOUR_API_KEY" });

const profile = await client.trader.getTraderPnl({
  address: "0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045",
  window: "30d",
});

const leaderboard = await client.trader.getGlobalPnlLeaderboard({
  window: "30d",
  sortBy: "realized_pnl_usd",
  limit: 100,
});

That's what lets Struct's APIs be the lowest-latency way to consume this data, and it's also why they scale so well for clients. In a query-time system, every additional consumer adds query load, and heavy consumers degrade the experience for everyone else. In ours, the work of computing an answer is done exactly once, when the underlying event happens, no matter how many clients ask for it afterwards. A client polling aggressively costs us serialization, not computation. Ten times the traffic is ten times the network, not ten times the database load, which is a scaling curve we're happy to be on.

How do we actually use RocksDB?

We didn't write our own storage engine from the disk up. Both halves of the system sit on RocksDB. But almost nothing about how we run it resembles a default deployment, and the deviations are where the performance lives.

• RocksDB is demoted to durable row archive: On the hot paths the write-ahead log is switched off entirely. We use our own append-only logs, synced once per ingest batch instead of once per row. The merged, deduplicated result reaches RocksDB later in the background. One sequential sync per batch versus thousands of logged random writes is a large fraction of the throughput story.
• Where atomicity matters, we pay for it properly: The engine's per-block flush is a single atomic write batch that carries the state changes and the block checkpoint together. A crash can never land between "state written" and "progress recorded." Recovery is just "read the checkpoint, resume."
• One instance became many: RocksDB serializes heavy writes through a single memtable-flush-compaction pipeline. We now run a couple dozen independent RocksDB instances, sharded by grain and by key hash. A compaction storm in one shard cannot stall its neighbors. They share one global write-buffer budget, and we disabled RocksDB's built-in write stalling in favor of explicit backpressure.
• Byte order is answer order: Scores use an order-preserving encoding, shared exactly between the on-disk keys and the live sorted indexes. Iterating a range on disk and paging through a live index produce identical orderings by construction, which means pagination cursors work uniformly everywhere.

What would we tell you if you're tempted?

• Precompute or perish. For data that changes faster than it's queried, query-time aggregation is a dead end. The question isn't "how do I make this query fast" but "how do I make this query unnecessary."
• Indexes are a write cost, not a read feature. General-purpose databases price every index against every write. When your product wants hundreds of orderings over a high-write dataset, that pricing model is the bottleneck, so change the model.
• Know your workload, then exploit it ruthlessly. Latest-wins semantics, idempotent replays, a known access pattern: every property our data actually had became a performance multiplier that a general-purpose system couldn't assume.
• Incremental systems drift; build the auditors in. Continuous sampled verification against first-principles recomputation is what makes "fully precomputed" trustworthy rather than terrifying.

The old materialized view answered yesterday's question slowly. This system answers every question we can think of before it's asked, for every trader, every market, every block, and it doesn't slow down as the history grows, because the history was folded in the moment it happened.

That's the whole trick. There is no query. There's only the answer.

If you want to put these numbers to work, grab an API key, read the trader PnL docs, or wire up a live trader PnL dashboard.