Building a Shard Directory Service

Hash routing decides placement with a function; a directory decides it with a row. That difference is what lets one tenant move to dedicated hardware, one customer’s data stay in a region, and one hot shard split without touching anything else. This guide builds the directory, the cache that makes it fast, and the version check that makes staleness safe. It extends Application-Level Sharding Logic inside Cross-Partition Querying & Aggregation Strategies.

Prerequisites

Step 1 — Model the mapping explicitly

CREATE TABLE shard_directory (
    entity_type  text        NOT NULL,          -- 'tenant', 'account', 'region'
    entity_id    text        NOT NULL,
    shard_name   text        NOT NULL,
    region       text,
    state        text        NOT NULL DEFAULT 'active'
                             CHECK (state IN ('active','migrating','read_only')),
    version      bigint      NOT NULL,
    updated_at   timestamptz NOT NULL DEFAULT now(),
    updated_by   text        NOT NULL DEFAULT current_user,
    PRIMARY KEY (entity_type, entity_id)
);

CREATE INDEX shard_directory_shard_idx ON shard_directory (shard_name)
  WHERE state <> 'active';

-- one global version, bumped on every change, used for cache invalidation
CREATE TABLE shard_directory_version (
    id          bool PRIMARY KEY DEFAULT true CHECK (id),
    version     bigint NOT NULL,
    updated_at  timestamptz NOT NULL DEFAULT now()
);
Directory entry states and the routing behaviour of each An entry is normally active and routes reads and writes to one shard. During a move it becomes migrating, which makes the router dual-read from both shards and keep writing to the source. Immediately before the cutover it becomes read-only, so writes are rejected with a retryable error while the final catch-up runs. It returns to active pointing at the new shard. activereads + writes → one shard migratingdual-read, writes still on source read_onlywrites rejected, retryable cutover complete → active, pointing at the new shard Because the state lives in the directory rather than in code, a move is a sequence of row updates that any operator can perform and any monitor can observe — no deploy, and no ambiguity about which phase the system is in.

Operational note: The state column is what makes a migration expressible. migrating tells the router to dual-read; read_only tells it to reject writes during a cutover. Without it, every move needs a code change.

DBA tip: Keep updated_by and a history table. “Which shard was this tenant on in March?” is a question that gets asked during incident review, and only a history answers it.

Three lookup layers, and where each request actually lands Ninety-eight percent of lookups are served from an in-process cache in microseconds. Just under two percent fall through to a shared cache at about half a millisecond. A small fraction reach the authoritative store at four milliseconds. The version watcher pushes invalidations so the in-process cache does not need a short time-to-live. route(tenant)every request in-process cache98.1% · ~2 µs shared cache1.7% · ~0.4 ms directory store0.2% · ~4 ms version watcher — notifies on change invalidates the in-process cache within ~50 ms Push-based invalidation is what allows a long cache lifetime without unbounded staleness. A short time-to-live achieves the same freshness by making every process re-fetch constantly, which turns the directory into a hot dependency.

Step 2 — Cache with a version, not with a timeout alone

# directory.py — in-process cache invalidated by a version watcher
class ShardDirectory:
    def __init__(self, store, watcher):
        self._entries: dict[tuple[str, str], Entry] = {}
        self._version = store.current_version()
        self._store = store
        watcher.on_change(self._invalidate)          # LISTEN/NOTIFY or an etcd watch

    def _invalidate(self, new_version: int) -> None:
        self._entries.clear()
        self._version = new_version

    def lookup(self, entity_type: str, entity_id: str) -> Entry:
        key = (entity_type, entity_id)
        entry = self._entries.get(key)
        if entry is None:
            entry = self._store.fetch(entity_type, entity_id)
            self._entries[key] = entry
        return entry

    @property
    def version(self) -> int:
        return self._version
-- the notification, emitted by the same transaction that changes the mapping
CREATE OR REPLACE FUNCTION notify_directory_change() RETURNS trigger AS $$
BEGIN
  UPDATE shard_directory_version SET version = version + 1, updated_at = now();
  PERFORM pg_notify('shard_directory', (SELECT version::text FROM shard_directory_version));
  RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER shard_directory_changed
AFTER INSERT OR UPDATE OR DELETE ON shard_directory
FOR EACH STATEMENT EXECUTE FUNCTION notify_directory_change();

Operational note: pg_notify is best-effort and not delivered to disconnected listeners. Pair it with a slow poll — every thirty seconds — so a listener that missed a notification still converges.

DBA tip: Bump one global version rather than per-entry versions. It makes the cache invalidation trivially correct at the cost of clearing entries that did not change, which for a directory of thousands of rows is irrelevant.

Step 3 — Send the version with every request and let the shard check it

Caching is only safe if a stale entry is detectable:

def execute_on_shard(directory, entity_id, sql, params):
    entry = directory.lookup("tenant", entity_id)
    conn = pool_for(entry.shard_name).acquire()
    conn.execute("SET LOCAL app.directory_version = %s", (directory.version,))
    return conn.execute(sql, params)
-- on each shard: reject work routed with an outdated view of the world
CREATE OR REPLACE FUNCTION assert_directory_version() RETURNS void AS $$
DECLARE client_version bigint := current_setting('app.directory_version', true)::bigint;
BEGIN
  IF client_version IS NULL OR client_version < (SELECT min_accepted FROM shard_settings) THEN
    RAISE EXCEPTION 'stale shard directory (client %, minimum %)',
      client_version, (SELECT min_accepted FROM shard_settings)
      USING ERRCODE = '40001';    -- serialization_failure: clients already retry this
  END IF;
END $$ LANGUAGE plpgsql;
What the version check turns a silent bug into Without a version check, an application instance holding a stale directory writes a tenant's rows to the shard that no longer owns them. The write succeeds, and the rows are invisible to every correctly routed query. With the check, the shard rejects the write with a retryable error, the client refreshes its directory and retries against the right shard. Without the check stale client v41tenant moved at v42 writes to shard_2succeeds — no error rows invisible to correct queriesfound weeks later by reconciliation With the check stale client v41same situation shard rejects40001, retryable client refreshesdirectory → v42 retrycorrect shard One integer per request converts the worst failure mode in application-level sharding — a silent wrong-shard write — into an error the client library already knows how to retry. Use an existing retryable SQLSTATE so drivers and frameworks handle it without new code.

Operational note: min_accepted is raised on a shard only after a migration touching it completes. Raising it globally would reject every client until they all refresh, which turns a safety mechanism into an outage.

SRE tip: Count version-mismatch rejections as a metric. A steady trickle is normal during migrations; a sustained rate means some instance is not receiving invalidations at all.

Step 4 — Decide what happens when the directory is down

DIRECTORY_UNAVAILABLE_POLICY = "serve_from_cache"    # or "fail_closed"

def lookup_with_policy(directory, entity_type, entity_id):
    try:
        return directory.lookup(entity_type, entity_id)
    except DirectoryUnavailable:
        cached = directory.stale_entry(entity_type, entity_id)
        if cached and DIRECTORY_UNAVAILABLE_POLICY == "serve_from_cache":
            metrics.increment("directory.serving_stale")
            return cached
        raise

Operational note: Serving from a stale cache during a directory outage is usually right — placements change rarely, and the alternative is a total outage. It is only wrong while a migration is in flight, which is exactly when state = 'migrating' should make the router fail closed for those entities.

DBA tip: Test the outage. Block access to the directory in staging and confirm the application keeps serving; most implementations discover a hard dependency they did not intend.

Verification

Confirm the cache, the invalidation and the rejection all work:

# 1. move a tenant and watch every instance converge
psql -c "UPDATE shard_directory SET shard_name='shard_5', version=version+1
         WHERE entity_type='tenant' AND entity_id='8842'"

# 2. every application instance should report the new version within a second
for pod in $(kubectl get pods -l app=api -o name); do
  kubectl exec "$pod" -- curl -s localhost:9000/debug/directory | jq .version
done
42
42
42
42

Then confirm a deliberately stale client is rejected rather than served, and that the rejection metric increments.

Failure mode table

Failure mode Root cause SRE mitigation
Rows written to a shard that no longer owns the tenant a client cached the mapping with no version and never refreshed send the directory version with every request; have shards reject stale versions with a retryable error
Directory becomes a single point of failure the routing path calls it synchronously on every request with no cache cache in-process with push invalidation; define an explicit stale-serving policy and test it by blocking the directory
Invalidation missed by one instance pg_notify was not delivered to a listener that had reconnected pair notifications with a slow poll so listeners converge even after a missed message; alert on version skew across instances

FAQ

When is a directory better than hashing?

When placement must be a decision rather than a function: moving one large tenant to dedicated hardware, keeping a customer’s data in a specific region, or splitting a hot shard without moving anything else. Hashing gives even distribution and no control; a directory gives control at the cost of a lookup and a cache to keep honest.

Where should the directory live?

In a small, highly available store that is not one of the shards — a dedicated database, or a coordination service such as etcd. Putting it on a shard makes that shard a dependency of every request in the system, including requests destined for other shards.

How stale is a cached entry allowed to be?

For reads, as stale as your tolerance for a wrong-shard miss, which is usually seconds. For writes, not at all — which is why the version is sent with each request and the shard rejects anything carrying an older one. That check turns an unbounded staleness problem into a bounded, retryable error.

Back to Application Level Sharding Logic