Date: 2026-02-05
Purpose: Research multi-tenancy patterns for Boundary Framework design decision
Target: ADR-004 Multi-tenancy Architecture Design
After extensive research across PostgreSQL documentation, Rails/Django ecosystems, and modern SaaS architectures (2025-2026), three primary multi-tenancy database patterns emerge:
tenant_id)Recommendation for Boundary: Schema-per-tenant provides the optimal balance of isolation, scalability, and operational simplicity for a Clojure/PostgreSQL framework targeting mid-to-large SaaS applications.
┌─────────────────────────────────────┐
│ Single Database │
│ │
│ ┌───────────────────────────┐ │
│ │ Shared Schema (public) │ │
│ │ │ │
│ │ users │ │
│ │ ├─ id │ │
│ │ ├─ tenant_id ← Filter │ │
│ │ ├─ name │ │
│ │ └─ email │ │
│ │ │ │
│ │ orders │ │
│ │ ├─ id │ │
│ │ ├─ tenant_id ← Filter │ │
│ │ ├─ amount │ │
│ │ └─ ... │ │
│ └───────────────────────────┘ │
└─────────────────────────────────────┘
Every query MUST include: WHERE tenant_id = ?
WHERE tenant_id = ? → data breachPostgreSQL's RLS can enforce tenant_id filtering at the database level:
-- Enable RLS on table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- Create policy to enforce tenant isolation
CREATE POLICY tenant_isolation ON users
USING (tenant_id = current_setting('app.current_tenant')::uuid);
RLS Pros:
RLS Cons:
app.current_tenant per request)Best for:
Not suitable for:
┌──────────────────────────────────────────────────────┐
│ Single Database │
│ │
│ ┌──────────────────┐ ┌──────────────────┐ │
│ │ Schema: tenant_a │ │ Schema: tenant_b │ ... │
│ │ │ │ │ │
│ │ users │ │ users │ │
│ │ orders │ │ orders │ │
│ │ products │ │ products │ │
│ └──────────────────┘ └──────────────────┘ │
│ │
│ ┌──────────────────┐ │
│ │ Schema: public │ ← Shared/global tables │
│ │ tenants │ (tenant registry) │
│ │ users (login) │ (user accounts) │
│ └──────────────────┘ │
└──────────────────────────────────────────────────────┘
Tenant Identification: Subdomain, JWT claim, or header
Schema Switching: SET search_path TO tenant_a, public;
search_path per requestSchema Creation:
-- Create tenant schema
CREATE SCHEMA tenant_abc123;
-- Grant usage to application role
GRANT USAGE ON SCHEMA tenant_abc123 TO app_user;
GRANT ALL ON ALL TABLES IN SCHEMA tenant_abc123 TO app_user;
Schema Switching (per request):
-- Set search path for current session
SET search_path TO tenant_abc123, public;
-- Now all queries default to tenant_abc123 schema
SELECT * FROM users; -- Uses tenant_abc123.users
Shared Tables (in public schema):
tenants table)users table with tenant_id)Per-Tenant Migrations:
(defn run-migration-for-all-tenants [migration-fn]
(doseq [tenant (get-all-tenants)]
(with-tenant-schema tenant
(migration-fn))))
Migration Approaches:
Best for:
Not suitable for:
Installation:
# Gemfile
gem 'apartment'
# config/initializers/apartment.rb
Apartment.configure do |config|
config.excluded_models = %w{ User Tenant } # Shared tables
config.use_schemas = true
config.tenant_names = -> { Tenant.pluck(:schema_name) }
end
Usage:
# Switch tenant
Apartment::Tenant.switch!('tenant_abc123')
# All queries now use tenant_abc123 schema
User.all # SELECT * FROM tenant_abc123.users
# Create new tenant
Apartment::Tenant.create('tenant_xyz789')
Key Learnings from Rails Ecosystem:
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
│ DB: tenant_a │ │ DB: tenant_b │ │ DB: tenant_c │
│ │ │ │ │ │
│ users │ │ users │ │ users │
│ orders │ │ orders │ │ orders │
│ products │ │ products │ │ products │
└─────────────────┘ └─────────────────┘ └─────────────────┘
┌──────────────────────────────┐
│ Master DB: control_plane │
│ │
│ tenants (registry) │
│ users (authentication) │
└──────────────────────────────┘
Best for:
Not suitable for:
| Aspect | Shared Schema | Schema-per-Tenant ⭐ | Database-per-Tenant |
|---|---|---|---|
| Isolation | Low (app-level) | Medium (schema-level) | High (physical) |
| Data Leak Risk | High | Low | Very Low |
| Setup Complexity | Low | Medium | High |
| Operational Cost | Low | Medium | High |
| Scalability | 10,000+ tenants | 1,000-10,000 tenants | <1,000 tenants |
| Query Performance | Degrades with scale | Good | Excellent |
| Noisy Neighbor | High risk | Medium risk | No risk |
| Custom Schema | No | Yes | Yes |
| Migrations | Easy (one schema) | Medium (iterate schemas) | Hard (many DBs) |
| Backups | Easy (one DB) | Medium (one DB, many schemas) | Hard (many DBs) |
| Compliance | Difficult | Good | Excellent |
| Connection Pool | Shared | Shared | Per-database |
| Cross-Tenant Analytics | Easy | Medium | Hard |
| Monitoring | Easy | Medium | Complex |
tenant-a.myapp.com → tenant_a schema
tenant-b.myapp.com → tenant_b schema
Pros:
Cons:
myapp.com/tenant-a/... → tenant_a schema
myapp.com/tenant-b/... → tenant_b schema
Pros:
Cons:
{
"user_id": "user-123",
"tenant_id": "tenant-abc",
"roles": ["admin"]
}
Pros:
Cons:
X-Tenant-ID: tenant-abc
Pros:
Cons:
Recommendation for Boundary: Subdomain + JWT claim fallback
tenant.myapp.com)Key Features:
Best Practices from Rails Community:
Approach: Schema-per-tenant with middleware-based switching
Key Features:
search_path managementLessons Learned:
;; Schema-per-tenant with next.jdbc
(defn with-tenant-schema [datasource tenant-id f]
(jdbc/with-transaction [tx datasource]
(jdbc/execute! tx [(str "SET search_path TO " tenant-id ", public")])
(f tx)))
(with-tenant-schema datasource "tenant_abc"
(fn [tx]
(jdbc/execute! tx ["SELECT * FROM users"])))
Challenge: Schema switching requires setting session variable Solution: Use connection pooling wisely, don't cache connections too aggressively
;; HikariCP configuration
{:maximum-pool-size 20 ; Per-application (not per-tenant)
:minimum-idle 5
:connection-timeout 30000}
(defn migrate-all-tenants []
(doseq [tenant (get-all-tenants)]
(let [config {:store :database
:migration-dir "migrations/"
:init-in-transaction? true
:migration-table-name "schema_migrations"
:db (assoc db-spec :schema (:schema_name tenant))}]
(migratus/migrate config))))
Schema-per-Tenant Safeguards:
Example Safeguard (Clojure):
(defn ensure-tenant-schema-set [conn]
(let [current-schema (-> (jdbc/execute-one! conn ["SHOW search_path"])
:search_path)]
(when (= "public" current-schema)
(throw (ex-info "Tenant schema not set - potential data leak!"
{:type :security-error})))))
GDPR Compliance:
HIPAA Compliance:
SOC2 Compliance:
| Pattern | 100 rows | 10k rows | 1M rows | Notes |
|---|---|---|---|---|
| Shared Schema | 2ms | 50ms | 1200ms | Query planner uses global statistics |
| Schema-per-Tenant | 2ms | 45ms | 800ms | Per-schema statistics, smaller tables |
| DB-per-Tenant | 2ms | 40ms | 750ms | Dedicated resources |
(Source: Debugg.ai Postgres Multitenancy 2025)
| Pattern | 10 tenants | 100 tenants | 1000 tenants |
|---|---|---|---|
| Shared Schema | 5s | 5s | 5s |
| Schema-per-Tenant | 30s | 5min | 50min |
| DB-per-Tenant | 1min | 10min | 100min+ |
Rationale:
Architecture for Boundary:
┌────────────────────────────────────────────────────┐
│ PostgreSQL Database │
│ │
│ ┌──────────────┐ ┌──────────────┐ │
│ │ Public │ │ Tenant │ ... │
│ │ Schema │ │ Schemas │ │
│ │ │ │ │ │
│ │ tenants │ │ tenant_abc │ │
│ │ users* │ │ users │ │
│ │ │ │ orders │ │
│ └──────────────┘ └──────────────┘ │
└────────────────────────────────────────────────────┘
↑
│
Boundary
Framework App
(Clojure)
*users in public schema: authentication only (username, password, tenant_id)
Phase 1: Foundation
public schemaPhase 2: Migration Support
Phase 3: Operations
Phase 4: Advanced Features
Postgres Multitenancy in 2025: RLS vs Schemas vs Separate DBs
Source: Debugg.ai (2025-09-07)
URL: https://debugg.ai/resources/postgres-multitenancy-rls-vs-schemas-vs-separate-dbs-performance-isolation-migration-playbook-2025
Multi-Tenancy Database Patterns: Schema vs Database vs Row-Level Comparison
Source: dasroot.net (2026-01-20)
URL: https://dasroot.net/posts/2026/01/multi-tenancy-database-patterns-schema-database-row-level/
PostgreSQL row-level security patterns for multi-tenant apps
Source: AppMaster (2025-03-03)
URL: https://appmaster.io/blog/postgresql-row-level-security-multitenant-patterns
Multi-tenancy and Database-per-User Design in Postgres
Source: Neon (2024-08-29)
URL: https://neon.tech/blog/multi-tenancy-and-database-per-user-design-in-postgres
Apartment Gem Documentation (Rails schema-per-tenant pattern)
Django django-tenants Package
Multi-Tenant RAG Applications With PostgreSQL
Source: TigerData (2024-10-11)
URL: https://www.tigerdata.com/blog/building-multi-tenant-rag-applications-with-postgresql-choosing-the-right-approach
Shipping multi-tenant SaaS using Postgres Row-Level Security
Source: Nile (2022-07-26)
URL: https://www.thenile.dev/blog/multi-tenant-rls
Multi-Tenant Databases with Postgres Row-Level Security
Source: Midnyte City (2024-12-18)
URL: https://www.midnytecity.com.au/blogs/multi-tenant-databases-with-postgres-row-level-security
public schemaDocument Version: 1.0
Last Updated: 2026-02-05
Status: Complete - Ready for ADR-004
Can you improve this documentation?Edit on GitHub
cljdoc builds & hosts documentation for Clojure/Script libraries
| Ctrl+k | Jump to recent docs |
| ← | Move to previous article |
| → | Move to next article |
| Ctrl+/ | Jump to the search field |