Skip to content
Back to Blog
February 28, 20268 min read

Why we chose schema-based multi-tenancy for BlueGrasshopper

BlueGrasshopper is the financial-advisory SaaS we built for independent Registered Investment Advisors (RIAs), small fiduciary firms that manage client portfolios. When we sat down to choose a multi-tenancy model, we did the opposite of what we'd done for PropertyTools.ai. PropertyTools uses shared-schema with row-level security. BlueGrasshopper uses schema-per-tenant in PostgreSQL. This post is about why those two products warranted opposite answers.

The three flavors of multi-tenancy

Every B2B SaaS hits this fork eventually. The three options:

  • Shared schema: every table has a tenant_id column and every query filters on it. Simplest infrastructure, hardest isolation, easiest migrations.
  • Schema per tenant: each tenant gets their own PostgreSQL schema (a namespace) inside the same database. Stronger isolation, more operational complexity, migrations have to fan out across N schemas.
  • Database per tenant: each tenant gets a physically separate database. Strongest isolation, most expensive, hardest to run migrations and backups uniformly.

There's no universally right answer. The right answer depends on what your data looks like, what your customers expect, what your compliance posture has to look like, and what your operational comfort is.

Why BlueGrasshopper needed schema isolation

Three things pushed us out of shared-schema territory and into schema-per-tenant for BlueGrasshopper:

1. Compliance posture.RIAs are regulated by the SEC or state-level securities authorities. Their auditors want to see evidence that one firm's client data physically can't be served to another firm. Row-level security is a strong defense, but explaining it to a compliance auditor is painful. "Each firm has its own database schema; the application connects with a schema-scoped role" is a one-sentence answer that ends the conversation.

2. Per-customer schema differences.Some customers want extra fields on their client records, a CRM-like custom-fields layer. Doing this in a shared-schema model means a generic custom_fields JSONB column with all the indexing and querying problems that creates. With schema isolation, we can add columns to a single tenant's tables without touching anyone else's.

3. Backups, exports, and offboarding.When an RIA leaves the platform, they're entitled to walk away with their data. With schema isolation, an export is pg_dump --schema=tenant_42. With shared-schema, you have to write export logic for every table and double-check that no rows leak across the boundary.

How we implemented it

We built it in Django because that's what our engineering team knew best, and because the django-tenants library does most of the heavy lifting for the public-schema/tenant-schema split. The basic shape:

  • The public schema holds the tenant registry, the user-to-tenant mapping, and a couple of cross-tenant tables (the billing audit log, the platform-wide event log).
  • Every tenant schemaholds the tenant's actual data: clients, accounts, holdings, transactions, performance history, custom fields.
  • The application uses a middleware that reads the user's tenant ID from the session and runs SET search_path TO tenant_X, publicon the database connection for the duration of the request. From then on, every query implicitly resolves to the correct tenant's tables.
  • Migrations are run twice: once against the public schema, once against each tenant schema. We have a wrapper command that fans out a migration across all tenants in parallel; on a database with ~100 tenants, this takes around 90 seconds for a typical migration.

The trade-offs we accepted

Schema-per-tenant isn't free. The things that got harder:

  • Connection pooling.Every connection has a search_path that's set per-request. We had to be careful with PgBouncer's pool mode. Transaction-level pooling works, but session-level pooling can leak search_path between requests if you're not careful. We use transaction pooling and reset search_path inside a transaction at the start of each request.
  • Backups. A full database backup is still one operation, but per-tenant point-in-time restores require some tooling we had to build. We use logical replication for hot standby and ad-hoc pg_dump --schema for tenant exports.
  • Cross-tenant queries.We almost never need them, but when we do (platform-wide stats, support tools), they're fiddly to write. We solved this by building a small set of read-only views in the public schema that union-all the relevant tenant tables, used only by the admin dashboard.
  • Schema bloat.100 tenants means 100 copies of every table. PostgreSQL handles this fine up to a few thousand schemas, but we'd revisit the model long before we hit that ceiling.

How we'd know we picked wrong

A few signals that would push us back toward shared-schema:

  • We start needing cross-tenant analytics that are too expensive to run as union views.
  • The customer base grows past a few thousand tenants and schema proliferation starts to hit Postgres' metadata performance.
  • Customers stop caring about the strict isolation we're paying for, and start caring about features that would be easier to ship with a shared schema.

We're a long way from any of those for BlueGrasshopper, so the decision continues to pay rent.

The takeaway

Don't pick a multi-tenancy model based on what's fashionable or what the latest blog post recommends. Pick it based on your actual data, your regulatory environment, your customer expectations, and your operational comfort. Two products from the same engineering team can (and should) make different decisions if the contexts are different. PropertyTools.ai got shared-schema because the data is simple, the regulatory pressure is light, and we wanted optimization for fast iteration. BlueGrasshopper got schema-per-tenant because the regulatory pressure is heavy and the export story is core to how customers think about owning their data.

If you're sitting at the same fork right now and want a second opinion, drop us a line. Multi-tenancy decisions are the kind of thing where a 30-minute conversation with someone who's done it both ways will save you months.