Multi Tenant Databases in Go With Schemas or Row Keys

A SaaS product serves many customers from one codebase, and every one of them expects never to see another's data. How you store that is the multi tenancy decision, and it is hard to change later because it touches every table, every query and every migration. PostgreSQL gives three realistic layouts: one set of tables with a tenant column, one schema per tenant inside a database, and one database per tenant. This article measures the first two on PostgreSQL 18.6 from Go 1.25.6 with 200 tenants and gives a verdict by tenant count and isolation need.

It is part of the database cornerstone. The database enforced version of the first layout is in row level security from Go, and the cost of changing schema across many tenants is the subject of zero downtime migrations.

The three layouts

The lab builds the first two with the same shape of data, 200 tenants with 50 projects each and 20 tasks per project (200,000 tasks), and measures what each costs.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

const tenants = 200

func must(db *sql.DB, q string, args ...any) {
	if _, err := db.Exec(q, args...); err != nil {
		log.Fatalf("%s: %v", q, err)
	}
}

func scalar(db *sql.DB, q string) (n int) {
	if err := db.QueryRow(q).Scan(&n); err != nil {
		log.Fatal(err)
	}
	return
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	_ = ctx

	// Clean up earlier runs.
	for i := 0; i < tenants; i++ {
		must(db, fmt.Sprintf("drop schema if exists t621_%d cascade", i))
	}
	must(db, "drop table if exists projects621 cascade")
	before := scalar(db, "select count(*) from pg_class")

	// Model A: one set of tables, a tenant_id column on every row.
	start := time.Now()
	must(db, "create table projects621 (tenant_id int not null, id bigserial, name text not null, primary key (tenant_id, id))")
	must(db, "create table tasks621 (tenant_id int not null, id bigserial, project_id bigint not null, title text not null, primary key (tenant_id, id), foreign key (tenant_id, project_id) references projects621 (tenant_id, id))")
	must(db, "insert into projects621 (tenant_id, name) select t, 'project ' || p from generate_series(0, $1 - 1) t, generate_series(1, 50) p", tenants)
	must(db, "insert into tasks621 (tenant_id, project_id, title) select tenant_id, id, 'task' from projects621, generate_series(1, 20)")
	rowKey := time.Since(start)
	afterA := scalar(db, "select count(*) from pg_class")
	fmt.Printf("A. tenant_id column: setup %v, catalog objects added %d, rows %d\n", rowKey.Round(time.Millisecond), afterA-before, scalar(db, "select count(*) from tasks621"))

	// The classic leak: a query that forgot the tenant.
	leak := scalar(db, "select count(*) from projects621 where name = 'project 7'")
	right := scalar(db, "select count(*) from projects621 where name = 'project 7' and tenant_id = 3")
	fmt.Printf("   forgot the tenant filter: %d rows (one per tenant); with it: %d\n", leak, right)

	// Model B: one schema per tenant.
	start = time.Now()
	for i := 0; i < tenants; i++ {
		must(db, fmt.Sprintf("create schema t621_%d", i))
		must(db, fmt.Sprintf("create table t621_%d.projects (id bigserial primary key, name text not null)", i))
		must(db, fmt.Sprintf("create table t621_%d.tasks (id bigserial primary key, project_id bigint not null references t621_%d.projects, title text not null)", i, i))
		must(db, fmt.Sprintf("insert into t621_%d.projects (name) select 'project ' || p from generate_series(1, 50) p", i))
		must(db, fmt.Sprintf("insert into t621_%d.tasks (project_id, title) select id, 'task' from t621_%d.projects, generate_series(1, 20)", i, i))
	}
	perSchema := time.Since(start)
	afterB := scalar(db, "select count(*) from pg_class")
	fmt.Printf("B. schema per tenant (%d schemas): setup %v, catalog objects added %d\n", tenants, perSchema.Round(time.Millisecond), afterB-afterA)

	// The same schema change in both models.
	start = time.Now()
	must(db, "alter table projects621 add column archived boolean not null default false")
	oneAlter := time.Since(start)
	start = time.Now()
	for i := 0; i < tenants; i++ {
		must(db, fmt.Sprintf("alter table t621_%d.projects add column archived boolean not null default false", i))
	}
	manyAlters := time.Since(start)
	fmt.Printf("   add one column: tenant_id model %v for one statement, schema model %v for %d statements\n",
		oneAlter.Round(100*time.Microsecond), manyAlters.Round(time.Millisecond), tenants)

	// A query for one tenant in each model.
	timeQ := func(label, q string) {
		start := time.Now()
		var n int
		for i := 0; i < 200; i++ {
			if err := db.QueryRow(q).Scan(&n); err != nil {
				log.Fatal(err)
			}
		}
		fmt.Printf("   %-46s %v per query\n", label, (time.Since(start) / 200).Round(10*time.Microsecond))
	}
	timeQ("one tenant's task count, tenant_id model", "select count(*) from tasks621 where tenant_id = 150")
	timeQ("one tenant's task count, schema model", "select count(*) from t621_150.tasks")

	// Cleanup so the catalog is not left bloated.
	for i := 0; i < tenants; i++ {
		must(db, fmt.Sprintf("drop schema t621_%d cascade", i))
	}
	must(db, "drop table tasks621, projects621")
}

A. tenant_id column: setup 2.925s, catalog objects added 10, rows 200000
   forgot the tenant filter: 200 rows (one per tenant); with it: 1
B. schema per tenant (200 schemas): setup 4.768s, catalog objects added 2000
   add one column: tenant_id model 1.9ms for one statement, schema model 214ms for 200 statements
   one tenant's task count, tenant_id model       800µs per query
   one tenant's task count, schema model          250µs per query

Shared tables with a tenant column

Model A is two tables whose primary keys start with tenant_id. The task table references the project with a composite foreign key (tenant_id, project_id), which makes it impossible for a task of one tenant to point at another tenant's project. That composite key is worth the extra column in every foreign key: it turns a cross tenant link from a bug into a constraint error.

Setup took 2.9 seconds including the data, and added 10 objects to the system catalog: two tables, their indexes and sequences. That number is the whole point of this model. It does not grow with tenants, so the same fixed set of tables serves ten tenants or ten million.

The leak is the weakness. The lab queries where name = 'project 7' as a developer who forgot the tenant filter would: 200 rows came back, one per tenant. With and tenant_id = 3 it returned 1. Nothing failed and nothing warned. In this layout every query of every endpoint is one forgotten condition from a data breach, and every ad hoc report, batch job and admin query shares the risk. Mitigations are real but have to be built: a data access layer that takes the tenant as a required argument, sqlc or generated queries that always include it, a linter, or PostgreSQL's row level security, which moves the filter into the database so a forgotten where returns only the current tenant's rows.

Schema per tenant

Model B creates a schema for each tenant with its own projects and tasks tables. A query never needs a tenant condition: select count(*) from t621_150.tasks can only see tenant 150's rows. Setup of all 200 schemas took 4.8 seconds and added 2,000 catalog objects, ten times as many as for the whole shared model: every schema carries its own tables, indexes, sequences and toast tables.

Two consequences show up quickly.

Migrations multiply. Adding a column to the shared table took 1.9ms as one statement. The same change across 200 schemas took 214ms as 200 statements, each of which can fail, each of which must be retried or rolled back, and any of which can leave tenants on different schema versions. The tooling has to loop over tenants, track which succeeded and run fine tuned retry logic, and on a busy system each alter needs the lock care from zero downtime migrations 200 times. At 5,000 tenants a migration is a long running job, and pg_dump and the catalog itself start to feel the object count.

Queries get simpler and sometimes faster. The count of one tenant's tasks took 800µs against the shared table and 250µs in the tenant's schema. The shared query reads one tenant's 1,000 rows out of an index over 200,000; the schema query reads a table that is that tenant's alone, with a smaller index and warmer cache. A noisy neighbour on the shared table also cannot bloat your pages, and you can drop a tenant with drop schema ... cascade, which is instant and leaves no deletes to vacuum.

From Go, per tenant schemas mean choosing the schema per request: set search_path on a connection, or qualified names built from a validated tenant identifier. Both have a trap. A search_path set on a pooled connection stays with it for the next request, exactly as a session setting does in the row level security article, so set it inside a transaction with set local, and never build a schema name from user input without checking it against the list of tenants.

The verdict

For most products, start with a tenant_id column. It has the lowest operational cost, the simplest migrations, one connection pool and room for millions of tenants. Pay for its weakness deliberately:

• Make tenant_id the first column of every primary key and every foreign key.
• Pass the tenant through every data access function as a required parameter.
• Add row level security as a second lock on the same door.
• Test isolation: a test that creates two tenants and asserts that every endpoint returns only the caller's rows.

Choose a schema per tenant when you have tens to a few hundred tenants, each large, who need hard separation, per tenant restore, or per tenant customisation of tables. Plan the migration tooling first, because that is the work.

Choose a database per tenant for the largest customers, for regulated data that must be isolated and backed up on its own, or for tenants who need to live in a region or on a server of their own. It multiplies connections and operations the most, and database/sql needs one pool per tenant database, which has to be bounded and idle ones closed.

A hybrid is common and sensible: shared tables for the long tail of small tenants and a dedicated schema or database for the few whose size or contract demands it. The router in the application decides per tenant, which is the same shape as the shard router in sharding basics.

What the numbers do not say

The lab used 200 tenants and small tables. The 2,000 versus 10 catalog objects is exact, and the timings are an order of magnitude only. Real costs depend on tenant count (the catalog is cached per connection, and thousands of schemas make every new connection slower to warm), on data distribution (one huge tenant skews the shared table's statistics) and on your migration process. Measure with your own tenant count before committing, and revisit when it changes tenfold. The lab also does not cover backups, where a schema per tenant gives per tenant dumps and shared tables need a logical extract by tenant.

PostgreSQL documents schemas and the search path in its chapter on schemas, including the usage pattern and the security caveat about a writable public schema, and row level security in the page on row security policies.