Engineering  //  Architecture

Multi-Tenant SaaS Architecture: Lessons from Building a Retail ERP

Multi-tenancy looks like a database decision. It isn't — it's a constraint that propagates through your application code, connection pool, background workers, cache and analytics pipeline. Here's what we learned designing a multi-tenant retail ERP on Django and PostgreSQL, including the choices we'd make again and the one we reversed.

By Integer3 EngineeringUpdated June 202614 min readDhaka · Brisbane

Context: lessons from building I3 RetailCloud on Django 5 + PostgreSQL 16.

Why a retail ERP makes multi-tenancy hard

Retail is an unforgiving multi-tenancy case. Each tenant is a retail business — often with several branches — generating high-volume transactional load through point-of-sale and inventory movements. They share an identical schema, but they hold financial data that absolutely cannot bleed across tenant boundaries, and they each want a little customisation: their own tax rules, branch structure, product catalogue and pricing. High write volume, strict isolation, light per-tenant variation. That combination is exactly where naive multi-tenancy breaks.

And the cost of getting the foundational choice wrong is brutal. Migrating from a shared model to dedicated databases after you already have hundreds of customers is a 6–12 month re-architecture, not a sprint. This is a decision to make deliberately, on day one.

The decision everything hangs on: how you isolate tenant data

There are three primary isolation models, plus a fourth that combines them. None is universally correct — the right pick depends on tenant count, compliance exposure, pricing model and how fluent your team is with PostgreSQL.

The industry's centre of gravity in 2026 has moved firmly toward shared-schema as the default for new B2B SaaS, with dedicated databases reserved for tenants who genuinely require physical isolation. As a sense of scale, large platforms run enormous tenant counts on this model — Notion, for instance, partitions by workspace across hundreds of logical shards on a few dozen physical PostgreSQL databases.

What we chose — and why we changed our mind

We started where many Django teams start: drawn to schema-per-tenant. It's the natural path the ecosystem nudges you toward, it feels clean, and the isolation story is easy to explain to a client. But as we modelled thousands of small retail tenants, two problems surfaced. Migrations became the tax: every schema change has to run across every tenant schema, and that grows slow, fragile and hard to make zero-downtime as tenant counts climb. And the per-tenant customisation that justifies separate schemas turned out to be better served by configuration and feature flags than by structurally divergent schemas.

So we reversed the default. We moved to shared-schema with a tenant_id on every table, isolation enforced in two independent layers, and a hybrid path: the long tail of small and mid retailers live in the shared schema for density and simple operations, while large chains with compliance or scale needs get a dedicated database. Crucially, we designed the tenant resolver to support that dedicated tier from day one — because switching a tenant from shared to dedicated later is a migration project, not a config toggle. Your pricing tiers and your architecture have to be designed together.

Isolation is a system-wide concern, not a database setting

The most important lesson: tenant isolation has to be enforced at every layer a request touches. In a shared schema, the entire risk reduces to one sentence — a single query that forgets WHERE tenant_id = ? is a data breach. You cannot rely on developers remembering. We enforce it three ways, each a backstop for the others.

The database backstop is worth getting exactly right, because the failure modes are subtle:

Three traps cost teams real incidents here. First, forgetting FORCE ROW LEVEL SECURITY — without it, the table owner (usually your application's own database role) bypasses every policy, so your isolation silently does nothing. Second, connection poolers: PgBouncer in transaction mode reuses connections across requests, so a tenant context set for the session leaks into the next tenant's transaction. Setting the context as transaction-local (that trailing true) is what makes pooling safe. Third — and this is why RLS is a backstop, never the sole mechanism — a 2024 PostgreSQL CVE showed RLS policies could be bypassed under certain subqueries with pooling, which would have exposed every tenant at once for anyone relying on RLS alone. Defence in depth isn't paranoia; it's the only safe posture when one leak ends the business.

The hard problems nobody warns you about

The database model is the part everyone debates. These are the parts that actually bite in production.

• Background workersCelery tasks have no request context. A job that runs in the wrong tenant's context is the same breach as a missing WHERE clause. We pass tenant identity into every task explicitly and re-establish the database context at the top of the worker — never inherit it.
• The cache trapA shared Redis cache is a cross-tenant leak waiting to happen. Every key must be namespaced by tenant. One un-prefixed cache key serving one tenant's data to another is among the easiest and scariest bugs to ship.
• Noisy neighboursOne tenant's bulk import or heavy report can starve everyone else. Memory-heavy work causes more cross-tenant interference than CPU-heavy work, so we partition queues, rate-limit per tenant, and cap expensive operations rather than trusting fair-share.
• IndexingPut tenant_id first. A composite index like (tenant_id, created_at) belongs on every high-volume table from day one — retrofitting it at ten million rows is a painful, locking ordeal.
• PartitioningPartition large tables by tenant_id once they grow big enough to warrant it (sales, stock movements), while leaving small reference tables alone.
• Audit logsKeep the audit trail outside tenant-scoped policies and make it immutable. When a customer disputes a deletion, you need evidence that the isolation itself can't have hidden.

Deploying and observing across many tenants

Two operational practices saved us repeatedly. For deploys, we never ship to all tenants at once: a change goes to a small canary group, gets validated against metrics for a window, then rolls forward with automated rollback if anything regresses — with per-tenant feature flags so an enterprise client can preview a change before it reaches their production.

For observability, aggregate metrics lie. A p99 latency that looks healthy across the fleet can hide one enterprise tenant experiencing three-second responses. Metrics, logs and traces all have to be segmented by tenant, or you'll learn about a tenant-specific problem from an angry email instead of a dashboard.

Scaling: the progression that actually works

When write volume climbs, there's a well-trodden order of operations — and the temptation to jump straight to the dramatic last step is exactly the mistake to avoid:

• Add read replicas to take reporting and read load off the primary.
• Partition your largest tables by tenant_id.
• Isolate your heaviest enterprise tenants onto dedicated databases.
• Shard by tenant_id only as a genuine last resort.

The first three steps cover the overwhelming majority of platforms; most never need to shard. And keep customer-facing analytics off your transactional database entirely — stream changes to a separate analytical store with tenant identity preserved, so a tenant's dashboard query never competes with another tenant's checkout.

Frequently asked questions