One tenant, one shard — hiding Postgres sharding from the whole app

The default answer is one big database. Every customer’s rows go in the same tables, kept apart by a tenant_id column, and you move on. That’s where I started too — it’s genuinely fine for a while.

But the multi-tenant recommendation platform I work on started picking up larger and larger customers, and “one database for everyone” started to feel less like a decision and more like a liability I was carrying around. So I split customers horizontally across several separate Postgres databases. Each customer (each tenant) lives entirely on one of them — that database is their shard. The interesting part isn’t the split itself; it’s that almost none of the application had to change. Two small pieces do all the routing work and keep the sharding invisible to everything above them.

Why bother

More moving parts means it has to earn its place. Four things made it worth it here:

Blast radius. A bad migration, a runaway query, a corrupted index — in a single shared database, one customer’s mishap is everyone’s outage. One customer having a bad time shouldn’t mean all of them do.

Noisy neighbours. Our largest customers have orders of magnitude more data than our smallest. In a shared database they compete for the same buffer cache and the same connections. A heavy retraining read for one tenant was making another tenant’s dashboard slow in ways that were genuinely hard to explain to either of them.

Scaling in chunks. When a shard fills up, I add another database and point new tenants at it. That’s a lot easier to reason about than trying to make one database infinitely large.

Data residency. Some customers must have their data in a particular region. When a shard is a database in a specific region, placement is a deployment decision, not a schema problem.

None of this is exotic. What I cared about was keeping it from bleeding into the thousands of lines of business logic that just want to read and write a tenant’s data without knowing any of this exists.

The shape: one pool per shard, one map

Two objects carry the whole scheme. An EngineManager owns a list of SQLAlchemy engines — one engine per shard — plus a map from tenant to shard index.

An “engine” in SQLAlchemy isn’t just a connection string; it owns a live pool of open connections to a database. So one engine per shard means one connection pool per shard, built once at startup and kept:

class EngineManager:
    def __init__(self, shard_dsns: list[str]) -> None:
        self.engines: list[Engine] = [
            create_engine(dsn, pool_size=50, pool_recycle=3600, pool_pre_ping=True)
            for dsn in shard_dsns
        ]
        self.tenant_to_shard: dict[str, int] = {}

The map is what makes a query land in the right place. Given a tenant, look up its shard index, grab that engine. Any single request touches exactly one database.

Making the routing invisible

The rest of the codebase never calls EngineManager directly. It asks for a session for a tenant and gets back an ordinary SQLAlchemy session already pointed at the right database:

@contextmanager
def tenant_session(tenant_id: str) -> Iterator[Session]:
    """Hand back a session already bound to this tenant's shard."""
    engine = engine_manager.engine_for(tenant_id)
    session = Session(bind=engine, expire_on_commit=False)
    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()

A caller — the ingestion service, the training service, the recommender, any of them — looks like this:

with tenant_session(tenant_id) as db:
    profile = db.query(UserProfile).filter_by(user_id=uid).one()

No shard number, no routing key, no if tenant in group_a. The day I add a fifth database, this call site doesn’t change. Pushing all the sharding knowledge into tenant_session — and not letting it leak past that — is what keeps the system from turning every query into a routing puzzle.

Implementation details

1. Build each engine once, never throw it away

The most important rule is boring: build each engine once and keep it for the life of the process. Early on it’s tempting to spin up an engine when you need it and drop it when you’re done — it feels tidy. It’s a trap. An engine is a warm pool of connections, and each connection costs a TCP round-trip and a TLS handshake to open. Throw the engine away and you pay that cost again on the next request. Under any real load you end up spending more time opening connections than running queries.

So the engines live in EngineManager for the entire process and every request borrows a connection from an already-warm pool. pool_pre_ping=True quietly checks a connection is still alive before handing it over — databases and load balancers love to drop idle connections. pool_recycle=3600 retires connections older than an hour so they never go stale.

2. A lazy, self-healing tenant map

The obvious approach is a dedicated mapping table — tenant_id → shard_index — but that creates two places that need to agree: the mapping table and wherever the tenant’s data actually lives. If they ever drift, requests go to the wrong database.

The simpler approach: don’t maintain a separate mapping at all. Every tenant’s row already exists on exactly one shard, so that shard is the answer. When you need to know where tenant X lives, scan all shards and ask each one which tenants it’s holding. The in-memory dict is just a cache of what you find — built lazily, on the first miss:

def engine_for(self, tenant_id: str) -> Engine:
    try:
        return self.engines[self.tenant_to_shard[tenant_id]]
    except KeyError:
        self._refresh_map()
        try:
            return self.engines[self.tenant_to_shard[tenant_id]]
        except KeyError:
            raise TenantNotFound(tenant_id)

def _refresh_map(self) -> None:
    mapping: dict[str, int] = {}
    for idx, engine in enumerate(self.engines):
        with Session(bind=engine) as s:
            for (tenant_id,) in s.query(Tenant.id).all():
                assert tenant_id not in mapping
                mapping[tenant_id] = idx
    self.tenant_to_shard = mapping

It’s self-healing: onboard a new tenant on any process, and the other processes discover it automatically the first time they’re asked for it and miss. No cache-invalidation broadcast needed.

The assert is small but load-bearing. The entire scheme rests on a tenant living on exactly one shard. If one ever showed up on two, I want a loud crash, not silently split data.

3. A deliberate wall for cross-shard queries

Ninety-nine percent of the work is tenant-scoped, which means single-shard. But some things are genuinely global — an admin view that counts tenants across the whole fleet, say. For those I use SQLAlchemy’s ShardedSession, which fans a query out over every shard and stitches results together. It’s wired up with a “chooser” that decides, per object, which shard it belongs to:

def _shard_chooser(mapper, instance, clause=None) -> str:
    if isinstance(instance, Tenant):
        return str(engine_manager.shard_of(instance.id))
    if isinstance(instance, (Region, SchemaVersion)):
        return "0"
    raise NotImplementedError(f"no cross-shard path for {type(instance).__name__}")

The NotImplementedError is intentional. A couple of small reference tables are copied identically onto every shard, so reading them from shard 0 is fine. But if some new model tries to route itself across shards, I want the code to stop and make me think rather than quietly firing N queries behind my back. The cross-shard path is narrow and guarded.

4. New tenants land on the emptiest shard

When a new customer is onboarded, something has to choose where they go. The rule is simple: whichever shard has the fewest tenants right now.

def least_loaded_shard(self) -> Engine:
    self._refresh_map()
    counts = Counter(self.tenant_to_shard.values())
    idx = min(range(len(self.engines)), key=lambda i: counts[i])
    return self.engines[idx]

Tenant count isn’t the same as tenant size, so this is crude. But it’s predictable, it spreads load well enough in practice, and it’s one small function — when count stops being good enough I know exactly the one place to change it.

What it costs

Sharding isn’t free.

Cross-shard queries are painful, by design. Anything that needs to see all tenants at once has to fan out to every shard and combine results in application code — no JOIN spans databases. I kept this rare on purpose, but “rare” is doing real work there.

Migrations fan out. A schema change isn’t one alembic upgrade — it’s one per shard, and it has to succeed on all of them or you’ve got databases on different schema versions:

def migrate_all_shards() -> None:
    for idx, engine in enumerate(engine_manager.engines):
        run_migrations(engine)

Rebalancing is a project. If one shard gets hot, moving a tenant to a quieter one means physically copying their data and updating where they resolve. There’s no automatic redistribution.

These are the flip side of the isolation I wanted. The same boundary that stops one tenant’s problem from spreading is the boundary a cross-shard query has to climb over.

When to reach for it

A tenant_id column is the right call when your tenants are small and similar and you value the convenience of one database. Horizontal shards start to matter when isolation, independent scaling, or data residency become real requirements — not theoretical ones.

The part that makes it liveable isn’t the sharding itself. It’s discipline about where the sharding is allowed to be known. Keep it inside an engine cache and a tenant-aware session helper, forbid it everywhere else, and the rest of the code gets to keep believing there’s just one database.