The Scatter Gather Tax

Figma shards Postgres behind DBProxy, a query engine that parses SQL, extracts the logical shard ids, and rewrites the query for the physical database. Shard keys are hashed, so rows with adjacent ids do not sit together.

A nightly job runs this against a table sharded by FileID, spread over 64 physical shards:

SELECT id, file_id, updated_at
FROM   file_revisions
WHERE  updated_at > now() - interval '1 hour'
ORDER  BY updated_at
LIMIT  500;

A reviewer says the job is now 64 times cheaper because each shard only scans a 64th of the table. What actually happens?

Only the shards holding recent rows are touched, because updated_at correlates with insert order and therefore with placement.

DBProxy scatters it to all 64 shards and merges the answers, so the fleet still does one unsharded database's worth of work and the job's share of every shard's capacity stays constant as you add shards.

DBProxy rejects the query, because a sharded query engine cannot plan a statement that carries no shard key.

Each shard scans a 64th of the rows, so the query is roughly 64 times faster and the pattern scales fine.