BlogSharding

Sharding

Sharding - Why am I splitting my database?

Testudo currently runs on a single PostgreSQL instance. One single database. Every table, every queue, every cache entry, every analytics query — all on the same box. And it works perfectly fine. Orders match in sub-millisecond time. The journal syncs. Imports trickle in. Nobody's complaining about latency. I have about 4 users.

So why am I spending time on database sharding? I dunno but I watched [https://www.youtube.com/watch?v=BdnCiGS9W4Q] and the great and powerful Vasilios Syrakis reflected upon hes experiences at Atlassian and it led me to think about how I have setup my database and what potential issues I might have if by some stroke of luck I get a gazillion users.

So prior to this is the best time to do stuff. If a user imports 50,000 historical trades at the same moment someone else is trying to close a position, that might not be ideal.

The Single-Instance Trap

Here's what Postgres is doing for Testudo:

  • OLTP — live order placement, trade execution, session management. Needs low-latency, high-concurrency connections.
  • Job queues — four pg_queue tables using SELECT ... FOR UPDATE SKIP LOCKED. The engine polls these in a tight loop.
  • Pub/sub — LISTEN/NOTIFY for real-time messaging between the router, engine, and WebSocket fan-out.
  • Caching — an UNLOGGED table for API response caching (tickers, orderbooks, klines).
  • Analytics — journal stats, equity curves, coach reports, Dignitas scoring. Long-running aggregate queries.
  • Trade imports — CSV and API-sourced historical trade data, sometimes 50,000 rows at a time.

Every one of these workloads competes for the same buffer pool, the same WAL, the same IOPS budget. Postgres is good at handling mixed workloads — until it isn't.

The specific scenario I'm worried about: a heavy trade import floods queue_imports while live order matching is polling queue_orders. Both tables live in the same database. The import saturates the WAL, spikes checkpoint IO, and suddenly your order fill latency jumps from sub-millisecond to "why is this taking 200ms?".

The Fix: Shard by Concern, Then Shard by Symbol

The plan has three layers.

Layer 1: Isolate Imports (The Noisy Neighbor Fix)

The cheapest win. queue_imports gets its own Postgres instance. A new DATABASE_IMPORTS_URL env var points to it. If the imports DB is down, the system falls back to the OLTP instance — trading never stops, it just temporarily re-accepts the noisy-neighbor risk. This is a configuration change, not a code rewrite. The QueueRepository already knows which queue is which; it just needs to resolve a different pool.

One container. One connection string. One class of problem gone.

Layer 2: The Routing Table (CQRS Without the Ceremony)

Before I can shard the engine, the router needs to know where each order should go. The answer is a routing table:

asset_pair     | engine_instance
---------------|----------------
SOL_USDC       | engine-a
BTC_USDC       | engine-b
(default)      | engine-default

For trading, you cannot afford an external network hop to a lookup table on every execution tick. The CQRS pattern solves this: the routing table lives in Postgres as the source of truth, but the router maintains a local, in-memory copy using DashMap — a lock-free concurrent hash map already in the codebase. When a routing change occurs, pg_notify('routing_change', ...) pushes an invalidation signal, and a background listener mutates the in-memory map. The execution path resolves routes with a single DashMap::get — sub-microsecond, no external hop.

I barely know what any of this means but its the knowledge I am distilling from reading about CQRS and I am running with it.

If the listener misses a notification (connection drop, restart), it reloads the full routing table from Postgres on reconnect. Eventually consistent, always available.

Layer 3: Shard the Engine by Asset Pair

The matching engine is currently a single Tokio actor — one mailbox, one orderbook set, one balance map. This works because the engine matches entirely in memory (no database round-trips during matching), but it's a hard ceiling on throughput. You can't run two engine instances behind a load balancer because orderbook consistency requires a single source of truth per asset pair.

But here's the insight: SOL_USDC and BTC_USDC orderbooks never interact. State isolation between distinct asset pairs is absolute. This is the canonical sharding boundary for an execution engine. Instead of one EngineHandle, the router holds a HashMap<String, EngineHandle>. When an order arrives, the router resolves SOL_USDC → engine-a, grabs that handle, and dispatches. Unknown pairs fall back to a default engine.

No new dependencies. Every primitive is already in Cargo.toml: DashMap, sqlx::PgPool, pg_queue::ListenerService.

Why Now?

I could wait until the single Postgres instance actually becomes a bottleneck. But the cost of doing this now — while the system is small and the architecture is malleable — is lower than doing it under pressure.

The seams I'm building are configuration boundaries, not code complexity. When we need to scale, the operator adds a new Postgres container, inserts a row in the routing table, and starts a new engine process. No incident and happy days.

TLDR - make scaling be boring, not exciting'.

Built with LogoFlowershow