Disclaimer: This post is based on my own experience and engineering work while building and evaluating the CRM architecture. I used AI to help polish, structure, and improve the readability of my thoughts, but the problems, experiments, measurements, decisions, and lessons described here come from my own work and experience.
A real-world journey from database-per-tenant to schema-per-tenant in a multi-tenant CRM — and what thousands of tenant schemas, connection pools, schedulers, and a performance audit taught me about choosing architecture.
There is a particular kind of architecture discussion that every engineer eventually experiences.
You bring a problem.
You explain the constraints.
You show the numbers.
You draw the architecture.
And someone says:
“We are cloud providers. Just add another server.”
At that point, you know you’re not having a database architecture discussion anymore.
You’re having a faith-based discussion about infrastructure.
I recently found myself in exactly that situation.
My organization has built several products around the database-per-tenant model. It became the default answer to almost every multi-tenancy question:
New customer? Give them a database.
More customers? Create more databases.
More load? Add another server.
More databases? Add another server.
Still slow? Add another server.
The architecture worked.
And that’s actually what made the conversation difficult.
It wasn’t a terrible architecture.
It was a useful architecture that had gradually become a universal architecture.
And that’s where things started getting interesting.
The CRM That Started the Argument
I was working on an agentic CRM that the organization had been trying to build for quite some time.
The existing architecture followed a database-per-tenant model:
PostgreSQL
│
┌──────────────┼──────────────┐
│ │ │
▼ ▼ ▼
Tenant A Tenant B Tenant C
Database Database Database
Every tenant had its own database.
At first glance, this is beautiful.
Tenant A cannot accidentally query Tenant B’s tables.
You get a strong isolation boundary.
Backups can be tenant-specific.
Restores can be tenant-specific.
Dropping a tenant is conceptually simple.
The mental model is extremely clean:
Tenant A → Database A
Tenant B → Database B
Tenant C → Database C
And honestly, for some workloads, database-per-tenant is a perfectly valid design.
The problem starts when you stop asking:
“Does this architecture work?”
and start asking:
“What happens when this architecture has to scale?”
The “Just Add Another Server” Problem
Whenever I tried to raise concerns about the database-per-tenant model, the response was essentially:
“We’re cloud providers. We can add another server.”
And yes.
You can.
But infrastructure scaling doesn’t magically eliminate architectural costs.
Suppose you have:
1 tenant
→ 1 database
→ 1 connection pool
Seems fine.
Now imagine:
100 tenants
→ 100 databases
→ potentially 100 pools
Then:
1,000 tenants
→ 1,000 databases
→ potentially 1,000 pools
And eventually:
10,000 tenants
→ 10,000 databases
At that point, the question isn’t:
“Can PostgreSQL handle this?”
The question becomes:
“Why are we making PostgreSQL manage thousands of independent database resources when our application is logically one product?”
And this is where I realized something important:
There is no universal architecture.
Database-per-tenant isn’t bad.
Schema-per-tenant isn’t automatically better.
Shared tables aren’t automatically better either.
Each one gives you different trade-offs.
The mistake is assuming one model can solve every product’s requirements.
The Problem I Finally Put in Front of Everyone: Connections
The breakthrough wasn’t an abstract argument about scalability.
It was connection mathematics.
In the old architecture, the natural implementation tends toward:
Tenant A
↓
Pool A
↓
Database A
Tenant B
↓
Pool B
↓
Database B
Tenant C
↓
Pool C
↓
Database C
Now imagine a connection pool with:
max = 10
For 1,000 tenants:
1,000 pools × 10 connections
=
10,000 potential PostgreSQL connections
PostgreSQL isn’t going to politely accept that because the architecture diagram looked clean.
Connection exhaustion doesn’t care how beautiful your tenant isolation model is.
And even if you don’t actually open all those connections immediately, you’ve created an architecture where tenant count directly affects your database connection-management complexity.
Then come the other resources:
- database creation
- migrations
- schema objects
- connection management
- monitoring
- backups
- provisioning
- scheduler fan-out
- connection lifecycle
- operational tooling
- deployment complexity
That’s when the conversation became much easier.
Instead of saying:
“I don’t like database-per-tenant.”
I could say:
“Let’s measure what happens when we actually push this architecture.”
And numbers are much harder to argue with.
The Architectural Decision
Eventually, I got the opportunity to redesign the CRM around:
Schema-per-tenant.
The new architecture became:
PostgreSQL
Database
teampage
│
┌─────────────┴─────────────┐
│ │ │
▼ ▼ ▼
public tenant_acme tenant_xyz
schema schema schema
│ │ │
│ │ │
Registry CRM data CRM data
Now the relationship becomes:
1 PostgreSQL database
↓
1 shared tenant connection pool
↓
N tenant schemas
This was the biggest conceptual shift.
Previously:
Tenant → Database
Now:
Tenant → Schema
And that one change has enormous consequences for connection management.
One Database Does Not Mean One Connection
One of the things I had to make extremely clear during the migration was the distinction between:
- database
- pool
- connection
- tenant
They’re not the same thing.
We now have:
PostgreSQL
└── teampage
├── public
├── tenant_a
├── tenant_b
├── tenant_c
└── ...
And the application has a shared pool:
Tenant Pool
│
├── Connection 1
├── Connection 2
├── Connection 3
├── ...
└── Connection 10
max: 10 means:
This pool can have up to 10 PostgreSQL connections.
It does not mean 10 pools.
It does not mean 10 tenants.
And it does not mean we need 10 databases.
A connection can serve:
Transaction 1
→ tenant_a
Transaction 2
→ tenant_b
Transaction 3
→ tenant_xyz
The key is transaction-local tenant context.
The Magic Is SET LOCAL search_path
The tenant context is established inside the transaction:
BEGIN;
SET LOCAL search_path = tenant_acme, public;
-- queries
COMMIT;
Then another transaction can use the same physical PostgreSQL connection:
BEGIN;
SET LOCAL search_path = tenant_google, public;
-- queries
COMMIT;
The important word is:
LOCAL
The search path belongs to the transaction.
When the transaction ends, the setting disappears.
That means a shared connection can safely move between tenants.
This became one of the foundational rules of the new architecture:
Get connection
↓
BEGIN
↓
SET LOCAL search_path = tenant_x
↓
Execute tenant work
↓
COMMIT / ROLLBACK
↓
Release connection
The connection does not belong to a tenant.
The transaction belongs to a tenant.
That distinction is fundamental.
The New Connection Architecture
We ended up with two application pools.
Platform pool
Used for global infrastructure:
public.tenants
platform metadata
tenant registry
system-level operations
Shared tenant pool
Used for all tenant schemas:
tenant_a
tenant_b
tenant_c
...
tenant_1000
tenant_2000
Conceptually:
PostgreSQL
teampage
│
┌─────────────┴─────────────┐
│ │
Platform Pool Tenant Pool
max = 5 max = 10
│ │
▼ ▼
public schema tenant schemas
So with one API instance:
Platform pool = 5
Tenant pool = 10
Maximum application connections ≈ 15
Notice what does not appear in that equation:
Number of tenants
Whether I have:
10 tenants
or:
2,000 tenants
I don’t suddenly have:
2,000 connection pools.
That’s one of the biggest operational wins of the migration.
But Migration Wasn’t Just “Change Database to Schema”
This is where the project became much more interesting.
A multi-tenant database migration touches almost everything.
The database layer is only one piece.
I had to revisit:
Database
↓
Tenant registry
↓
Connection management
↓
Tenant context
↓
Repositories
↓
Services
↓
Schedulers
↓
Provisioning
↓
Authentication
↓
API routing
↓
Migrations
↓
Testing
↓
Operational tooling
The architecture had to become schema-aware from end to end.
Tenant Registry
One of the important pieces became a central tenant registry.
Something conceptually like:
public.tenants
organization_code
subscription_key
schema_name
status
...
The application first knows:
Who is this tenant?
Then resolves:
tenant → schema
Then executes work inside the tenant transaction.
So the request flow became:
HTTP Request
│
▼
Authentication
│
▼
Organization / Tenant resolution
│
▼
Tenant registry
│
▼
Schema resolution
│
▼
Shared tenant pool
│
▼
BEGIN
│
▼
SET LOCAL search_path
│
▼
Repository
│
▼
COMMIT
That became the core runtime path.
The Pool Problem Didn’t Disappear
This is an important lesson.
Changing from database-per-tenant to schema-per-tenant doesn’t magically make connection management perfect.
It changes the shape of the problem.
We now had:
One shared pool
which is much better than:
Thousands of tenant pools
But we still had to determine:
How big should that pool be?
Our initial tenant pool limit was around:
DB_POOL_MAX = 10
And this is where performance engineering started becoming more interesting than architecture diagrams.
Because:
Bigger pool ≠ automatically faster application.
We Tested It Instead of Guessing
One of the most useful parts of the migration was the decision to stop arguing from intuition and start measuring.
We ran controlled concurrency tests.
We measured:
- throughput
- p50 latency
- p95 latency
- connection acquisition time
- transaction phases
- event-loop delay
- CPU
- memory
- PostgreSQL activity
At 30 concurrent requests, for example, connection acquisition itself was taking roughly:
Mean ≈ 97ms
P50 ≈ 99ms
P95 ≈ 146ms
That was a much more useful observation than:
“Maybe we need more connections.”
So we tested increasing the pool.
”Just Increase the Pool” Didn’t Save Us
We tested:
DB_POOL_MAX = 10
versus:
DB_POOL_MAX = 20
And the result was fascinating.
Throughput was essentially flat:
~84 req/s
versus:
~86 req/s
The pool got bigger.
The application did not meaningfully get faster.
This was one of the best reminders from the entire migration:
A queue doesn’t necessarily disappear because you make the queue longer.
If PostgreSQL or the underlying machine is the actual limiting resource, giving the application more connections simply allows more work to compete for the same bottleneck.
So we explicitly rejected:
“Let’s just increase DB_POOL_MAX.”
Based on measurement.
Then We Found an Actual N+1 Problem
During the performance audit, we found something much more concrete.
The list endpoints weren’t suffering from the classic N+1 pattern we’d initially expected.
They were already doing:
1 list query
+
1 batched tags query
+
in-memory join
Good.
But bulk writes were different.
Several bulk operations were doing something closer to:
N permission checks
+
1 mutation
+
N timeline inserts
That meant roughly:
2N + 1
database operations.
We found this pattern across seven methods in:
- Leads
- Opportunities
- Tickets
We measured it.
Before the fix, roughly:
N = 10 → ~332–378ms
N = 100 → ~675–730ms
The slope was approximately:
~3.8–3.9ms / record
That is the kind of number that makes a performance engineer suddenly stop drinking coffee.
We Batched the Work
The fix was straightforward conceptually.
Instead of:
for each record:
check permission
update
create timeline event
we moved toward:
fetch all required records
↓
batch permission check
↓
validate everything
↓
perform mutation
↓
batch timeline writes
Importantly, authorization happened before mutation, preserving the all-or-nothing behavior.
After the fix:
Tickets
The N=10 → N=100 scaling became effectively flat compared with the previous behavior.
Leads
The scaling component dropped by roughly half.
And we validated the whole test suite:
196 / 196 suites
2749 / 2749 tests
This was a good example of why performance work shouldn’t be:
“Optimize everything.”
It should be:
Find the measured expensive path, change it, measure again.
Then We Looked at the Scheduler
Changing the tenant model also meant rethinking tenant-wide scheduled work.
A scheduler that previously thought:
tenant → database
now had to think:
tenant → schema
We audited the schedulers.
There were 19 of them.
We found one scheduler that wasn’t using the same graceful interval mechanism as the others.
That was fixed.
Then we tested tenant sweep behavior with actual provisioned tenant schemas.
10 tenants
Provisioning ≈ 49.4s
Sweep ≈ 161ms
100 tenants
Provisioning ≈ 12.5 min
Sweep ≈ 2.646s
The interesting part?
Provisioning was expensive.
The actual sweep mechanism was comparatively cheap.
That distinction matters.
Then We Tried 500 Tenants
Because nothing says “let’s validate our architecture” like asking your database to create hundreds of tenant schemas.
The 500-tenant provisioning test eventually crashed around tenant 110 with:
Connection terminated unexpectedly
And this is where the honest part of engineering comes in.
We didn’t write:
“The architecture scales to 500 tenants.”
We didn’t write:
“500 tenants failed because PostgreSQL is bad.”
We wrote:
We don’t know yet.
The root cause wasn’t isolated in that pass.
The synthetic tenants were cleaned up.
The result remained an open investigation.
That is a much more useful engineering conclusion than pretending the test never happened.
Thousands of Schemas
The final scalability validation pushed the architecture toward the real question:
What happens when tenant count gets large?
And this is where schema-per-tenant changed the operational model.
Instead of:
1,000 tenant databases
we could have:
1 PostgreSQL database
1,000 tenant schemas
The important distinction isn’t that schemas are magically free.
They aren’t.
Every tenant still has:
- tables
- indexes
- enums
- constraints
- metadata
- migrations
The audit found that a tenant schema contains roughly hundreds of database objects.
So provisioning still has a real cost.
But the operational unit is now much more manageable.
Then Came PgBouncer
Naturally, once I had redesigned the connection architecture, someone could ask:
“Why not just put PgBouncer in front of PostgreSQL?”
Good question.
So we tested it.
Not theoretically.
Actually.
The architecture became:
NestJS
↓
node-postgres pool
↓
PgBouncer
↓
PostgreSQL
We used transaction pooling.
The application was already compatible because tenant context was established using:
SET LOCAL search_path
rather than session-level state.
Then we measured it.
And PgBouncer lost.
Badly.
Direct PostgreSQL:
83.95 req/s
p50 ≈ 356ms
PgBouncer:
36.21 req/s
p50 ≈ 800ms
Another clean trial:
43.5 req/s
p50 ≈ 672ms
So roughly:
1.9–2.3× worse throughput
and approximately double the latency.
Was PgBouncer bad?
No.
Was it useful for this workload at this scale?
No.
And that’s another lesson:
Don’t deploy infrastructure because the infrastructure is popular. Deploy it because your measured bottleneck requires it.
Our one-API-instance deployment simply wasn’t constrained by PostgreSQL’s physical connection count.
So PgBouncer was adding another hop and another queue without solving the actual bottleneck.
The Database-per-Tenant Argument Changed
At the beginning, the argument was:
Database-per-tenant doesn’t scale.
That’s too simplistic.
After going through the migration, I’d phrase it differently:
Database-per-tenant scales for certain requirements, but its operational and connection-management costs grow with tenant count.
And:
Schema-per-tenant doesn’t eliminate scaling problems. It changes where those problems live.
That’s a much more honest statement.
What We Actually Gained
The migration gave us several things.
Connection management became tenant-count independent
Instead of:
N tenants
→ potentially N pools
we have:
N tenants
→ shared tenant pool
Tenant isolation remains explicit
We still have:
tenant_a
tenant_b
tenant_c
rather than one giant shared table where every query needs:
WHERE tenant_id = ?
Database operations became easier to centralize
Migrations, connection lifecycle, pooling, monitoring and database configuration now operate primarily around the shared database rather than thousands of database instances.
Horizontal scaling became easier to reason about
Now we can calculate:
API instances
×
pool capacity
instead of:
tenants
×
pool capacity
That is a very different scaling curve.
But Schema-per-Tenant Has Its Own Problems
I don’t want this article to become:
“Schema-per-tenant is the answer to everything.”
That would make the entire article ironic.
Schema-per-tenant introduces its own challenges.
Schema proliferation
Thousands of schemas mean thousands of sets of:
- tables
- indexes
- constraints
- metadata
Migration fan-out
A schema migration now has to be applied across every tenant.
Migration
│
├── tenant_a
├── tenant_b
├── tenant_c
├── ...
└── tenant_5000
Provisioning cost
Creating a tenant is no longer just:
CREATE DATABASE ...
but a full schema provisioning process.
Search-path correctness
One incorrect session-level setting can create a very serious tenant-isolation bug.
That’s why:
SET LOCAL search_path
isn’t just a performance optimization.
It’s a security boundary.
Scheduler fan-out
Every tenant-aware background job needs careful connection and transaction management.
PostgreSQL limits still exist
You haven’t escaped PostgreSQL’s resource constraints.
You’ve just stopped multiplying connection pools by tenant count.
The Most Important Lesson
The biggest thing I learned wasn’t:
“Schema-per-tenant is better than database-per-tenant.”
It was:
Architecture is a set of trade-offs, not a religion.
Database-per-tenant can be exactly right when you need strong database-level isolation, independent backups/restores, tenant-specific database lifecycle, or regulatory boundaries.
Schema-per-tenant can be attractive when you have a large number of tenants and want strong logical isolation without multiplying database instances and connection infrastructure.
Shared-table multi-tenancy can make sense when tenant counts are enormous and operational simplicity is more important than database-level isolation.
And hybrid models can make sense when your customers have different requirements.
There is no:
┌───────────────┐
│ UNIVERSAL │
│ MULTI-TENANT │
│ ARCHITECTURE │
└───────────────┘
button.
Sadly.
It would make architecture meetings much shorter.
What I Would Ask Before Choosing a Tenancy Model Today
Instead of asking:
“Which multi-tenancy architecture is best?”
I’d ask:
1. How many tenants do we expect?
10?
1,000?
100,000?
The answer changes the economics.
2. What isolation level do we actually need?
Database?
Schema?
Row?
Regulatory isolation?
3. What does connection management look like?
This one is often ignored.
Calculate:
tenants
×
pools
×
max connections
before you celebrate your beautiful architecture.
4. How are migrations performed?
If you have 5,000 tenants:
5,000 schema migrations
is a real operational problem.
5. What happens during provisioning?
Creating a tenant isn’t free.
Measure it.
6. What happens when a scheduler touches every tenant?
A scheduler that runs once per minute across 10 tenants is very different from one that runs once per minute across 50,000 tenants.
7. What happens when you horizontally scale the API?
Calculate:
API replicas
×
pools per replica
×
max connections
before discovering PostgreSQL’s max_connections in production.
The Architecture I Ended Up With
After the migration, the high-level design became:
┌───────────────┐
│ Load Balancer │
└───────┬───────┘
│
┌───────┴───────┐
│ │
API #1 API #N
│ │
└───────┬───────┘
│
┌────────────┴────────────┐
│ │
Platform Pool Tenant Pool
│ │
│ ┌─────────┼─────────┐
│ │ │ │
▼ ▼ ▼ ▼
public tenant_a tenant_b tenant_n
schema schema schema schema
│ │ │ │
└───────────────┴─────────┴─────────┘
│
PostgreSQL
And every tenant request follows the same basic lifecycle:
Request
↓
Authenticate
↓
Resolve organization
↓
Resolve tenant schema
↓
Acquire shared connection
↓
BEGIN
↓
SET LOCAL search_path
↓
Execute business logic
↓
COMMIT / ROLLBACK
↓
Release connection
Simple.
Explicit.
Measurable.
And most importantly:
the number of tenants no longer directly determines the number of connection pools.
What I Would Tell My Younger Self
Don’t walk into an architecture discussion saying:
“Your architecture doesn’t scale.”
That’s too broad.
Instead say:
“Let’s define what scaling means, model the resource, measure the current system, and find the point where it breaks.”
Then bring numbers.
Because:
Opinion
↓
Argument
↓
Counter-argument
↓
Architecture religion
is exhausting.
Whereas:
Hypothesis
↓
Experiment
↓
Measurement
↓
Evidence
↓
Decision
is engineering.
The migration from database-per-tenant to schema-per-tenant wasn’t successful because schema-per-tenant is some magical superior architecture.
It was successful because we finally stopped asking:
“Which architecture is universally correct?”
and started asking:
“Which architecture fits this product, this tenant count, these workloads, these operational constraints, and these measured bottlenecks?”
That is the question I wish we had started with.
And yes, we are still cloud providers.
We can still add another server.
But now, at least, we know why we’re adding it.