Shared Tables, Schema per Tenant, or Database per Tenant: Choosing a PostgreSQL Isolation Model

Every multi-tenant product decides where one customer's data ends and another's begins. With PostgreSQL there are three common answers: shared tables with a tenant_id column, a schema per tenant inside one database, or a separate database (sometimes a separate server) per tenant. Each moves cost and risk to a different place, and the right answer often differs between your smallest and largest customers.

This guide compares the three on the things that actually differ in operation: how a mistake leaks data, how connections and migrations scale, and what you can restore for one tenant. It corrects two claims that circulate widely, including in an earlier version of this page: that a separate database gives you point-in-time recovery per tenant, and that there are standard tenant counts or per-tenant costs for each model. Neither holds in general. A lab then measures the main reason shared tables are popular: in a simulated month of 200 tenants, one shared pool needed 76% less capacity than databases sized for each tenant's own peak.

The three models

AWS calls these the pool, bridge, and silo models in its whitepaper SaaS Tenant Isolation Strategies, which is a good companion for non-database resources.

Shared tables

All tenants share each table, and every row carries a tenant_id. This is the cheapest model to operate: one schema, one migration, one connection pool, and capacity shared across tenants whose busy hours differ.

The risk is a query that forgets the tenant filter. Defend in layers: a data access package that requires a tenant, PostgreSQL row-level security as a backstop, and cross-tenant tests. The row-level security guide covers the policy and its bypass rules, and the cross-tenant leak guide covers the other layers, with a lab on linting for missing filters.

Restoring one tenant means extracting that tenant's rows from a backup into a staging database and copying them back, which needs tooling you should build and rehearse before a customer asks.

Schema per tenant

Each tenant gets a schema with the same tables. Application code sets search_path per transaction, or qualifies table names with the tenant's schema.

Two cautions. First, search_path must be set per transaction (for example with SET LOCAL or set_config(..., true)), or a pooled connection carries one tenant's path into the next request. Second, PostgreSQL's schemas documentation warns that adding a schema to search_path effectively trusts every user with CREATE privilege on that schema; tenant schemas must not be writable by anyone but the migration role.

The catalog grows with tenants times tables. With 80 tables, 1,000 tenants is 80,000 tables plus their indexes, sequences, and TOAST tables. Every migration loops over all of them, and catalog-wide operations such as pg_dump and some monitoring queries slow down as the catalog grows. There is no universal threshold at which this becomes a problem; it depends on your PostgreSQL version, hardware, and table count. Create a test database at your expected tenant count, run your migration and backup tooling against it, and measure before committing.

Per-tenant restore is a logical dump and restore of one schema with pg_dump --schema.

Database per tenant

Each tenant has its own database. Accidental cross-tenant reads become much harder, because a query can only see one database. Misrouting a connection, a shared superuser credential, or a shared backup can still cross the boundary.

Connections multiply. PostgreSQL connections are separate server processes, and poolers keep separate pools per database and user. As illustrative arithmetic: 1,000 tenant databases, a minimum of 5 pooled connections each, and 3 application replicas is 15,000 server connections, far beyond what a single PostgreSQL server is normally configured for. Designs that work at that scale open pools lazily, close idle ones quickly, route each tenant to a small set of application instances, and cap connections per tenant; see PgBouncer's configuration for the pool settings involved.

Restores are less independent than they look. You can restore one database with pg_dump and pg_restore. But PostgreSQL's physical backups and point-in-time recovery work on the whole cluster: the PITR documentation states that this method can only restore an entire database cluster, not a subset. To roll one tenant back to 14:00 without touching others, either that tenant needs its own cluster (for example its own managed database instance), or you restore the cluster elsewhere and copy that tenant's database back.

Noisy neighbors remain unless the database runs on dedicated compute. Separate databases on one server still share its CPU, memory, and disk.

Lab: how much capacity sharing saves

Dedicated databases are sized for each tenant's own busiest minute. A shared pool is sized for the busiest minute of everyone combined, and tenants rarely peak together, especially across time zones. The program estimates the difference for 200 simulated tenants over 30 days at one-minute resolution. Each tenant has business-hours load in one of eight time zones, quieter weekends, random noise, and occasional 10 to 60 minute bursts such as a report or an import. Tenant sizes follow a long tail, so a few are large and most are small. Every parameter is in the code; change them to match your own traffic.

Run it with go run .; it uses only the standard library and a fixed random seed.

// Lab: how much capacity does sharing a database save?
//
// 200 simulated tenants for 30 days at one-minute resolution. Each tenant has
// a business-hours curve in its own time zone, quieter weekends, minute-level
// noise, and occasional bursts (a report or an import). Sizes follow a long
// tail: a few large tenants, many small ones. All parameters are invented and
// stated below; the point is the comparison, not the absolute numbers.
//
// Dedicated: each tenant gets capacity for its own peak minute (and for its
// own 99.9th-percentile minute). Shared: one pool sized for the peak (and
// 99.9th-percentile) minute of the combined load.
package main

import (
	"fmt"
	"math"
	"math/rand/v2"
	"sort"
)

const (
	tenants = 200
	minutes = 30 * 24 * 60
)

type tenant struct {
	size     float64 // average load units at business-hours peak
	tzOffset int     // hours from UTC
	burstP   float64 // chance per minute that a burst starts
}

func quantile(xs []float64, q float64) float64 {
	s := append([]float64(nil), xs...)
	sort.Float64s(s)
	return s[int(math.Ceil(q*float64(len(s))))-1]
}

func main() {
	rng := rand.New(rand.NewPCG(19, 84))
	zones := []int{-8, -5, 0, 1, 5, 8, 9, 10} // tenants spread over these UTC offsets
	ts := make([]tenant, tenants)
	for i := range ts {
		ts[i] = tenant{
			size:     math.Exp(rng.NormFloat64()*1.1) * 2, // long tail
			tzOffset: zones[rng.IntN(len(zones))],
			burstP:   0.0005 + rng.Float64()*0.002,
		}
	}
	total := make([]float64, minutes)
	var dedicatedPeak, dedicatedP999 float64
	largestShare := 0.0
	var sumSizes float64
	for _, t := range ts {
		sumSizes += t.size
	}
	burstLeft := 0
	for _, t := range ts {
		load := make([]float64, minutes)
		for m := 0; m < minutes; m++ {
			hourUTC := (m / 60) % 24
			local := (hourUTC + t.tzOffset + 24) % 24
			day := (m/1440 + 0) % 7
			curve := 0.15 + 0.85*math.Max(0, math.Sin(math.Pi*float64(local-7)/12)) // 07:00-19:00 hump
			if day >= 5 {
				curve *= 0.35 // weekend
			}
			v := t.size * curve * (0.8 + 0.4*rng.Float64())
			if burstLeft == 0 && rng.Float64() < t.burstP {
				burstLeft = 10 + rng.IntN(50) // a 10-60 minute report or import
			}
			if burstLeft > 0 {
				v += t.size * 2.5
				burstLeft--
			}
			load[m] = v
			total[m] += v
		}
		peak := quantile(load, 1)
		dedicatedPeak += peak
		dedicatedP999 += quantile(load, 0.999)
		if t.size/sumSizes > largestShare {
			largestShare = t.size / sumSizes
		}
		burstLeft = 0
	}
	sharedPeak := quantile(total, 1)
	sharedP999 := quantile(total, 0.999)
	var avg float64
	for _, v := range total {
		avg += v
	}
	avg /= minutes

	fmt.Printf("%d tenants, %d minutes; largest tenant = %.1f%% of all load\n\n", tenants, minutes, 100*largestShare)
	fmt.Printf("%-44s %10s %12s\n", "", "peak", "99.9th pct")
	fmt.Printf("%-44s %10.0f %12.0f\n", "dedicated: sum of each tenant's own need", dedicatedPeak, dedicatedP999)
	fmt.Printf("%-44s %10.0f %12.0f\n", "shared: need of the combined load", sharedPeak, sharedP999)
	fmt.Printf("%-44s %9.0f%% %11.0f%%\n", "capacity saved by sharing", 100*(1-sharedPeak/dedicatedPeak), 100*(1-sharedP999/dedicatedP999))
	fmt.Printf("\naverage combined load: %.0f units; average utilization if sized at peak: dedicated %.0f%%, shared %.0f%%\n",
		avg, 100*avg/dedicatedPeak, 100*avg/sharedPeak)

	fmt.Println("\nConnections with a minimum pool of 5 per database on 3 application replicas:")
	for _, n := range []int{50, 200, 1000} {
		fmt.Printf("  %5d tenant databases -> %6d server connections held open\n", n, n*5*3)
	}
}

Output recorded on November 3, 2026 (Go 1.26.5, windows/amd64):

200 tenants, 43200 minutes; largest tenant = 5.1% of all load

                                                   peak   99.9th pct
dedicated: sum of each tenant's own need           2482         2398
shared: need of the combined load                   594          560
capacity saved by sharing                           76%          77%

average combined load: 316 units; average utilization if sized at peak: dedicated 13%, shared 53%

Connections with a minimum pool of 5 per database on 3 application replicas:
     50 tenant databases ->    750 server connections held open
    200 tenant databases ->   3000 server connections held open
   1000 tenant databases ->  15000 server connections held open

What the output shows

• Sharing needed 76% less capacity at the peak (594 units instead of 2,482), and 77% less at the 99.9th percentile. Each tenant's bursts and busy hours mostly land when others are quiet.
• Utilization tells the same story. Sized for the peak, dedicated capacity would be 13% used on average, the shared pool 53%. That gap is the infrastructure cost of strong isolation.
• Connections add their own cost. With a minimum pool of 5 per database on 3 application replicas, 1,000 tenant databases keep 15,000 server connections open before any traffic arrives.

The model ignores per-database overhead (memory, background processes, backups), which makes dedicated databases look better than they are, and it assumes no tenant is large enough to dominate the pool; the largest here was 5.1% of all load. Replace the invented curves with a month of your own per-tenant query time to get your own number. The cost attribution guide shows how to measure that per-tenant usage.

Mixing models

Many products use shared tables for most tenants and dedicated databases for a few. The pieces that make this workable:

1. A placement table that maps each tenant to a database (and schema, if used). The tenant routing guide shows how to resolve the tenant early in each request; placement is the next lookup.
2. One data access interface that works against any placement, so application code does not branch on the model.
3. A tested move procedure: copy the tenant's rows, keep them in sync while the copy catches up, switch the placement, verify counts and checksums, and keep the old copy until the move is confirmed.
4. Written criteria for who gets a dedicated placement: contract terms, data residency, measured load, or restore requirements. Plan or contract value alone is a weak rule, because a small, quiet tenant with residency needs may need its own database more than a large one without them.

The tenant isolation planner scores the three models against your answers to these questions and shows the reasoning, which is a useful starting point for a design review.

What to measure before deciding

• Connections: peak active tenants times pool size per tenant, compared with what your server and pooler allow.
• Migration time: one migration across your expected number of schemas or databases.
• Restore: time to restore one tenant to a given point, rehearsed end to end.
• Catalog size and backup duration at your expected tenant count, for schema-per-tenant.
• Cost: compute and storage per dedicated placement, compared with what those tenants pay; the unit economics calculator can help.

Where to start

If you are early, start with shared tables, a tenant-scoped data access package, and row-level security, and design the placement table now even if every tenant points to the same database. That keeps the option of dedicated databases open for the customers who will need them. If you already run schema- or database-per-tenant, measure the migration loop and a single-tenant restore this month, because those are the costs that grow quietly with every new customer.