ScyllaDB schema and engine tuning

Installing Ecosystem gets ScyllaDB running and Operations tells you it is not in any backup the platform takes. Neither says what is actually in it, or how to make the matching engine read it well. This page does both.

Everything here concerns the trading keyspace, named by SCYLLA_KEYSPACE. The Futures addon has its own, SCYLLA_FUTURES_KEYSPACE, created by the same client with a separate table list.

How the keyspace is created

On the first successful connection the backend checks system_schema.keyspaces, and creates the keyspace if it is absent:

CREATE KEYSPACE IF NOT EXISTS trading
  WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'};

The session is deliberately left with no default keyspace. One client serves both trading and futures, and USE <ks> retargets only the single pooled connection that served it — which is how ecosystem order-book writes were once measured landing in futures.orderbook on roughly half of all calls, leaving filled orders resting in the book. Every statement in the addon is keyspace-qualified, so any future unqualified one fails loudly with "No keyspace has been specified" instead of quietly hitting the wrong one.

Tables are created with CREATE TABLE IF NOT EXISTS, which never alters a table that already exists. New columns are added by an explicit ALTER TABLE migration list that runs on every boot and is safe to repeat; new primary keys are not, and cannot be. See the shape drift below.

The seven tables

Four materialized views exist alongside them: latest_candles, orders_by_symbol ((symbol, "userId")), orderbook_by_symbol, and stop_orders_by_status ((status), which is what lets the stop monitor load every active stop in one bounded query at boot).

One view is dropped at boot if it is present: open_orders, keyed ((status, "userId")). It had no readers, and a materialized view is not free to keep — Cassandra and Scylla maintain one by reading the base row before every mutation, so an unused view is a permanent tax on every order created, filled and cancelled. The drop is announced once, on the boot that removes it, with the statement to recreate it in that release's patch notes.

Why orders alone cannot answer "the book"

orders is partitioned by ("userId"). The matching engine's question is "every OPEN order on this symbol, for all users, in price-time priority", and against that partitioning it can only be asked as:

SELECT * FROM orders WHERE status = 'OPEN' AND symbol = ? ALLOW FILTERING;

That names no partition key. It is a cluster-wide scan, run once per market, at boot and on every resync — the I/O ceiling that binds long before a million orders.

The obvious fix is a view keyed ((status, symbol)), and it is illegal:

Cannot include more than one non-primary key column 'symbol'
in materialized view primary key

A view's primary key may promote at most one column that is not already in the base table's key, and status and symbol are both non-key columns here. That single restriction is why the two shipped views are shaped as they are — orders_by_symbol spends its allowance on symbol and cannot then drop "userId" from the partition. No view can answer this query, so the denormalisation has to be a real table maintained by the write path. That table is open_orders_by_market.

Its invariant is one line: a row exists in open_orders_by_market if and only if the corresponding orders row has status = 'OPEN'. Every write is emitted in the same logged batch as the orders write it mirrors, so the two land together or not at all. The two failure directions are not symmetric: an index row missing for an OPEN order means the matcher never sees it and the order rests forever on the customer's money; an index row present for a closed order is harmless, because the matcher filters on status === "OPEN" and the stale row carries its own stale status.

The five knobs

None of these are in .env.example. All are added by hand to the project root .env, and all need a backend restart.

ECO_BOOK_SOURCE

Only the exact word legacy turns the index off. Any other value — including a misspelt tabel — logs a warning and keeps the default, because a typo was plainly an attempt to have it on and silently doing the opposite is the worst of both:

ECO_BOOK_SOURCE="tabel" is not a value this platform recognises. The only values
are "table" (the default) and "legacy". Continuing with the default.

The flag brings its own migration. On the first boot in table mode the leaseholder walks the orders ledger once, writes the index and records a marker; every boot after that reads the marker and goes straight to partition reads. That build runs in the background, after the engine is up and never awaited, so a scan of a large ledger cannot delay the platform coming up. The boot that builds it reads from the ledger, exactly as every release before the index did.

If the backfill fails, index reads are disarmed for the life of that process and the engine falls back to the ledger. That is deliberately not clearable at runtime: a process that fell back stays fallen back until it restarts. The log distinguishes the three reasons it can happen, because they need three different answers.

ECO_BOOK_WINDOW_PER_SIDE

The engine holds the orders nearest the market, per side, per symbol, not every open order on the platform. Matching only ever touches the best price level, so an order resting 30% away does not need to be in memory — and the window slides toward it as price moves.

Orders outside the window are not lost. They are in the ledger, and they are loaded as price reaches them. The line that tells you a symbol is windowed is:

BTC/USDT: resident window is 25000 bid(s) / 25000 ask(s) out of a deeper book.
Orders beyond the window are safe in the ledger and are loaded as price reaches
them; the aggregated book beyond it is left untouched by reconciliation. Raise
ECO_BOOK_WINDOW_PER_SIDE to hold more.

The right value is a memory decision, not a correctness one: roughly 1–2 KB per order, so 25,000 a side is tens of megabytes per busy symbol. Raise it on a big box with one very deep market; lower it when running many markets on a small one.

Two details that matter when you read a windowed symbol's book. The boundary price level is dropped whole rather than half-held, so "resident" is always exactly "price at least as good as the boundary" — otherwise the reconciler would rebuild a level from six of the nine orders resting at it and delete the other three's depth. The exception is a level bigger than the entire window, which is kept and flagged so reconciliation leaves it alone.

MAX_OPEN_ORDERS_PER_SYMBOL — not a knob, but the number behind one

50,000 is a hard ceiling compiled in, and the two read paths treat it in opposite ways:

• Table mode reads each side best-price-first, so the cap discards the orders furthest from the touch — the ones that cannot match until price travels to them. That is a resident window.
• Legacy mode reads in whatever order the cluster-wide scan produces, so it stops loading and drops an arbitrary set of funded orders. On a deep book that routinely means throwing away the top of the book and keeping dust 40% away. That is data loss.

Same number, opposite meaning. The legacy path says so, loudly:

BTC/USDT has more than 50000 OPEN orders, and this boot is reading them with the
table scan, which returns them in NO PARTICULAR ORDER — so the orders dropped at
the cap are arbitrary rather than the ones furthest from the price. […] A market
with this many resting orders usually means market-maker orders are accumulating
instead of being cancelled and replaced.

That last sentence is usually the real finding. pnpm eco:mm:orders surveys accumulated AI market-maker orders; run its --apply form with the backend stopped.

ECO_INDEX_BUILD_PAUSE_MS

The backfill runs beside a process that is serving requests, so it deliberately trades finishing quickly for finishing quietly — a pause between pages hands the Scylla connection pool and the allocator back to live traffic. pnpm eco:index:repair, which an operator runs deliberately, passes no pause and goes flat out.

Raise it if the build is visibly competing with trading; lower it (or use the script) when you want it done.

ECO_TRADE_TAPE_TTL_DAYS

trades is keyed ((symbol), "createdAt", id) — one partition per market, growing for the life of the install, at two rows per real fill. A market maker quoting once a second writes millions of rows a year into a single partition, which is the classic way to make a Cassandra-family cluster slow in a way no query can be blamed for. The TTL bounds it.

It is safe to expire because this table is a display tape, not a record. Its only reader asks for the last few dozen prints. The permanent record of every fill is the trades JSON on the orders themselves, which never expires, and the price history is in candles. Nothing auditable lives only here.

The tape is also written through one bounded writer, not one insert per fill. Fire-and-forget inserts on a cycle that filled thousands of orders launched thousands of concurrent requests and hit the driver's per-connection ceiling:

BusyConnectionError: All connections to host 127.0.0.1:9042 are busy,
2048 requests are in-flight on each connection

Once the pool is saturated it is saturated for everything — the futures mark sweep and the open-positions read failed on an exhaustion the tape caused. Rows are now buffered and drained one batch at a time, round-robin across symbols, so this path holds at most one in-flight request no matter how many fills arrive.

SCYLLA_LOCAL_CONNECTIONS

This is the lever for BusyConnectionError. The driver caps in-flight requests at 2048 per connection, so four connections is a ceiling of 8192 for the whole process.

Raising it buys headroom, not a fix: the bounded writer above is what actually removed the cause, and a bigger pool only buys a higher number to blow through. Size it to the box — a shared 2-core VPS wants fewer, a dedicated cluster more.

The rest of the SCYLLA_* variables are in Environment reference.

The backfill marker

eco_index_state holds one row that decides whether the engine trusts the index:

The per-symbol rows exist because the build used to write one marker, at the very end. Anything that stopped the process before that threw away every symbol's work — and on one 718,000-order install being restarted every ten seconds by a supervisor nobody had noticed, the index was never built, not once, across hours of restarts, while every boot paid the full cluster-wide scan again. Progress is checkpointed per market now, so a restart re-does at most one. The per-symbol rows are deleted once the main marker is written, so clearing the main marker to force a genuine rebuild gets a clean run rather than a no-op.

Three commands manage it. All are safe to run against a live install, and the first is read-only:

pnpm eco:index:check      # verify; exits non-zero on drift, prints what disagrees
pnpm eco:index:repair     # rebuild from the orders ledger, and prune stale rows
pnpm eco:index:mark       # record the backfill as complete WITHOUT re-scanning

eco:index:check is designed to be a deployment gate — it answers "is it right?" with a number rather than an opinion. eco:index:mark is for the narrow case where the index has already been proven correct and all that is missing is the row the backend reads; re-running a whole backfill to set one boolean would cost another full pass over the ledger for nothing.

The script is plain Node, not tsx, on purpose: a diagnostic that only runs on a healthy machine is no diagnostic, so it parses .env itself and needs exactly one package. It reads the live table definition out of system_schema first and refuses to run if the column set or the primary key is not what it was written against — so schema drift fails loudly instead of quietly writing rows with a column missing.

The engine keeps checking on its own too: a sample of up to five markets at boot, with a 15-second budget, and then one market an hour on rotation while it runs. A market holding more than 100,000 indexed orders is skipped by the boot check and named in the log, because comparing it against the ledger needs a COUNT with ALLOW FILTERING that the cluster will simply time out.

The one shape that drifted

CREATE TABLE IF NOT EXISTS never alters an existing table, and one table's primary key changed after release. Fresh installs have:

orderbook  PRIMARY KEY ((symbol, side), price)

An install created before that shape keeps its original — PRIMARY KEY (symbol, price, side), partitioned by symbol alone — and no upgrade has ever changed it, because nothing in the boot DDL can. The two need different queries, and a query written for one is not merely slower on the other, it is refused:

Failed to fetch the order book for MO/USDT: Cannot execute this query as it might
involve data filtering … use ALLOW FILTERING

which is why this failed on customer servers and passed on every fresh install and every development machine. The addon now asks the cluster what the key actually is, once, and shapes the query to it. Nothing is required of you — but if you are writing your own CQL against orderbook, check system_schema.columns for your install rather than assuming the shipped shape.

Backups, briefly

What losing it costs is bounded, and worth knowing before you size the risk: balances, wallets and the transaction ledger are all in MySQL. Losing Scylla costs trading history and resting orders, not customer money. See Operations for the full store-by-store table.

Related

• Environment reference — every SCYLLA_* variable and the rest
• The order lifecycle — what actually reads these tables
• Candles, tickers and the trade tape — the candles and trades tables in use
• Stop orders — the stop_orders table in use
• Operations — repair scripts, engine placement, backups