Multi-Tenancy Database Patterns with examples in Go

Multi-tenancy is a fundamental architectural pattern for SaaS applications, allowing multiple customers (tenants) to share the same application infrastructure while maintaining data isolation.

Choosing the right database pattern is crucial for scalability, security, and operational efficiency.

Overview of Multi-Tenancy Patterns

When designing a multi-tenant application, you have three primary database architecture patterns to choose from:

1. Shared Database, Shared Schema (most common)
2. Shared Database, Separate Schema
3. Separate Database per Tenant

Each pattern has distinct characteristics, trade-offs, and use cases. Let's explore each in detail.

Pattern 1: Shared Database, Shared Schema

This is the most common multi-tenancy pattern, where all tenants share the same database and schema, with a tenant_id column used to distinguish tenant data.

Architecture

┌─────────────────────────────────────┐
│     Single Database                 │
│  ┌───────────────────────────────┐  │
│  │  Shared Schema                │  │
│  │  - users (tenant_id, ...)     │  │
│  │  - orders (tenant_id, ...)    │  │
│  │  - products (tenant_id, ...)  │  │
│  └───────────────────────────────┘  │
└─────────────────────────────────────┘

Implementation Example

When implementing multi-tenant patterns, understanding SQL fundamentals is crucial. For a comprehensive reference on SQL commands and syntax, check out our SQL Cheatsheet. Here's how to set up the shared schema pattern:

-- Users table with tenant_id
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

-- Index on tenant_id for performance
CREATE INDEX idx_users_tenant_id ON users(tenant_id);

-- Row-Level Security (PostgreSQL example)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON users
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);

For more PostgreSQL-specific features and commands, including RLS policies, schema management, and performance tuning, refer to our PostgreSQL Cheatsheet.

Application-Level Filtering

When working with Go applications, choosing the right ORM can significantly impact your multi-tenant implementation. The examples below use GORM, but there are several excellent options available. For a detailed comparison of Go ORMs including GORM, Ent, Bun, and sqlc, see our comprehensive guide to Go ORMs for PostgreSQL.

// Example in Go with GORM
func GetUserByEmail(db *gorm.DB, tenantID uint, email string) (*User, error) {
    var user User
    err := db.Where("tenant_id = ? AND email = ?", tenantID, email).First(&user).Error
    return &user, err
}

// Middleware to set tenant context
func TenantMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        tenantID := extractTenantID(r) // From subdomain, header, or JWT
        ctx := context.WithValue(r.Context(), "tenant_id", tenantID)
        next.ServeHTTP(w, r.WithContext(ctx))
    })
}

Shared Schema Pros

• Lowest cost: Single database instance, minimal infrastructure
• Easiest operations: One database to backup, monitor, and maintain
• Simple schema changes: Migrations apply to all tenants at once
• Best for high tenant count: Efficient resource utilization
• Cross-tenant analytics: Easy to aggregate data across tenants

Shared Schema Cons

• Weaker isolation: Data leakage risk if queries forget tenant_id filter
• Noisy neighbor: One tenant's heavy workload can affect others
• Limited customization: All tenants share the same schema
• Compliance challenges: Harder to meet strict data isolation requirements
• Backup complexity: Can't restore individual tenant data easily

Shared Schema Best For

• SaaS applications with many small-to-medium tenants
• Applications where tenants don't need custom schemas
• Cost-sensitive startups
• When tenant count is high (thousands+)

Pattern 2: Shared Database, Separate Schema

Each tenant gets their own schema within the same database, providing better isolation while sharing infrastructure.

Separate Schema Architecture

┌─────────────────────────────────────┐
│     Single Database                 │
│  ┌──────────┐  ┌──────────┐         │
│  │ Schema A │  │ Schema B │  ...    │
│  │ (Tenant1)│  │ (Tenant2)│         │
│  └──────────┘  └──────────┘         │
└─────────────────────────────────────┘

Separate Schema Implementation

PostgreSQL schemas are a powerful feature for multi-tenancy. For detailed information on PostgreSQL schema management, connection strings, and database administration commands, consult our PostgreSQL Cheatsheet.

-- Create schema for tenant
CREATE SCHEMA tenant_123;

-- Set search path for tenant operations
SET search_path TO tenant_123, public;

-- Create tables in tenant schema
CREATE TABLE tenant_123.users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

Application Connection Management

Managing database connections efficiently is critical for multi-tenant applications. The connection management code below uses GORM, but you might want to explore other ORM options. For a thorough comparison of Go ORMs including connection pooling, performance characteristics, and use cases, refer to our Go ORMs comparison guide.

// Connection string with schema search path
func GetTenantDB(tenantID uint) *gorm.DB {
    db := initializeDB()
    db.Exec(fmt.Sprintf("SET search_path TO tenant_%d, public", tenantID))
    return db
}

// Or use PostgreSQL connection string
// postgresql://user:pass@host/db?search_path=tenant_123

Separate Schema Pros

• Better isolation: Schema-level separation reduces data leakage risk
• Customization: Each tenant can have different table structures
• Moderate cost: Still single database instance
• Easier per-tenant backups: Can backup individual schemas
• Better for compliance: Stronger than shared schema pattern

Separate Schema Cons

• Schema management complexity: Migrations must run per tenant
• Connection overhead: Need to set search_path per connection
• Limited scalability: Schema count limits (PostgreSQL ~10k schemas)
• Cross-tenant queries: More complex, requires dynamic schema references
• Resource limits: Still shared database resources

Separate Schema Best For

• Medium-scale SaaS (dozens to hundreds of tenants)
• When tenants need schema customization
• Applications needing better isolation than shared schema
• When compliance requirements are moderate

Pattern 3: Separate Database per Tenant

Each tenant gets their own complete database instance, providing maximum isolation.

Separate Database Architecture

┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│  Database 1  │  │  Database 2  │  │  Database 3  │
│  (Tenant A)  │  │  (Tenant B)  │  │  (Tenant C)  │
└──────────────┘  └──────────────┘  └──────────────┘

Separate Database Implementation

-- Create database for tenant
CREATE DATABASE tenant_enterprise_corp;

-- Connect to tenant database
\c tenant_enterprise_corp

-- Create tables (no tenant_id needed!)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

Dynamic Connection Management

// Connection pool manager
type TenantDBManager struct {
    pools map[uint]*gorm.DB
    mu    sync.RWMutex
}

func (m *TenantDBManager) GetDB(tenantID uint) (*gorm.DB, error) {
    m.mu.RLock()
    if db, exists := m.pools[tenantID]; exists {
        m.mu.RUnlock()
        return db, nil
    }
    m.mu.RUnlock()

    m.mu.Lock()
    defer m.mu.Unlock()

    // Double-check after acquiring write lock
    if db, exists := m.pools[tenantID]; exists {
        return db, nil
    }

    // Create new connection
    db, err := gorm.Open(postgres.Open(fmt.Sprintf(
        "host=localhost user=dbuser password=dbpass dbname=tenant_%d sslmode=disable",
        tenantID,
    )), &gorm.Config{})

    if err != nil {
        return nil, err
    }

    m.pools[tenantID] = db
    return db, nil
}

Separate Database Pros

• Maximum isolation: Complete data separation
• Best security: No risk of cross-tenant data access
• Full customization: Each tenant can have completely different schemas
• Independent scaling: Scale tenant databases individually
• Easy compliance: Meets strictest data isolation requirements
• Per-tenant backups: Simple, independent backup/restore
• No noisy neighbors: Tenant workloads don't affect each other

Separate Database Cons

• Highest cost: Multiple database instances require more resources
• Operational complexity: Managing many databases (backups, monitoring, migrations)
• Connection limits: Each database instance has connection limits
• Cross-tenant analytics: Requires data federation or ETL
• Migration complexity: Must run migrations across all databases
• Resource overhead: More memory, CPU, and storage needed

Separate Database Best For

• Enterprise SaaS with high-value customers
• Strict compliance requirements (HIPAA, GDPR, SOC 2)
• When tenants need significant customization
• Low to medium tenant count (dozens to low hundreds)
• When tenants have very different data models

Security Considerations

Regardless of the pattern chosen, security is paramount:

1. Row-Level Security (RLS)

PostgreSQL RLS automatically filters queries by tenant, providing a database-level security layer. This feature is particularly powerful for multi-tenant applications. For more details on PostgreSQL RLS, security policies, and other advanced PostgreSQL features, see our PostgreSQL Cheatsheet.

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy to isolate by tenant
CREATE POLICY tenant_isolation ON orders
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);

-- Application sets tenant context
SET app.current_tenant = '123';

2. Application-Level Filtering

Always filter by tenant_id in application code. The examples below use GORM, but different ORMs have their own approaches to query building. For guidance on choosing the right ORM for your multi-tenant application, check our comparison of Go ORMs.

// ❌ BAD - Missing tenant filter
db.Where("email = ?", email).First(&user)

// ✅ GOOD - Always include tenant filter
db.Where("tenant_id = ? AND email = ?", tenantID, email).First(&user)

// ✅ BETTER - Use scopes or middleware
db.Scopes(TenantScope(tenantID)).Where("email = ?", email).First(&user)

3. Connection Pooling

Use connection poolers that support tenant context:

// PgBouncer with transaction pooling
// Or use application-level connection routing

4. Audit Logging

Track all tenant data access:

type AuditLog struct {
    ID        uint
    TenantID  uint
    UserID    uint
    Action    string
    Table     string
    RecordID  uint
    Timestamp time.Time
    IPAddress string
}

Performance Optimization

Indexing Strategy

Proper indexing is crucial for multi-tenant database performance. Understanding SQL indexing strategies, including composite indexes and partial indexes, is essential. For a comprehensive reference on SQL commands including CREATE INDEX and query optimization, see our SQL Cheatsheet. For PostgreSQL-specific indexing features and performance tuning, refer to our PostgreSQL Cheatsheet.

-- Composite indexes for tenant queries
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at DESC);
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);

-- Partial indexes for common tenant-specific queries
CREATE INDEX idx_orders_active_tenant ON orders(tenant_id, created_at)
WHERE status = 'active';

Query Optimization

// Use prepared statements for tenant queries
stmt := db.Prepare("SELECT * FROM users WHERE tenant_id = $1 AND email = $2")

// Batch operations per tenant
db.Where("tenant_id = ?", tenantID).Find(&users)

// Use connection pooling per tenant (for separate database pattern)

Monitoring

Effective database management tools are essential for monitoring multi-tenant applications. You'll need to track query performance, resource usage, and database health across all tenants. For comparing database management tools that can help with this, check out our DBeaver vs Beekeeper comparison. Both tools offer excellent features for managing and monitoring PostgreSQL databases in multi-tenant environments.

Monitor per-tenant metrics:

• Query performance per tenant
• Resource usage per tenant
• Connection counts per tenant
• Database size per tenant

Migration Strategy

Shared Schema Pattern

When implementing database migrations, your choice of ORM affects how you handle schema changes. The examples below use GORM's AutoMigrate feature, but different ORMs have different migration strategies. For detailed information on how various Go ORMs handle migrations and schema management, see our Go ORMs comparison.

// Migrations apply to all tenants automatically
func Migrate(db *gorm.DB) error {
    return db.AutoMigrate(&User{}, &Order{}, &Product{})
}

Separate Schema/Database Pattern

// Migrations must run per tenant
func MigrateAllTenants(tenantIDs []uint) error {
    for _, tenantID := range tenantIDs {
        db := GetTenantDB(tenantID)
        if err := db.AutoMigrate(&User{}, &Order{}); err != nil {
            return fmt.Errorf("tenant %d: %w", tenantID, err)
        }
    }
    return nil
}

Decision Matrix

Hybrid Approach

You can combine patterns for different tenant tiers:

// Small tenants: Shared schema
if tenant.Tier == "standard" {
    return GetSharedDB(tenant.ID)
}

// Enterprise tenants: Separate database
if tenant.Tier == "enterprise" {
    return GetTenantDB(tenant.ID)
}

Best Practices

1. Always filter by tenant: Never trust application code alone; use RLS when possible. Understanding SQL fundamentals helps ensure proper query construction—refer to our SQL Cheatsheet for query best practices.
2. Monitor tenant resource usage: Identify and throttle noisy neighbors. Use database management tools like those compared in our DBeaver vs Beekeeper guide to track performance metrics.
3. Implement tenant context middleware: Centralize tenant extraction and validation. Your ORM choice affects how you implement this—see our Go ORMs comparison for different approaches.
4. Use connection pooling: Efficiently manage database connections. PostgreSQL-specific connection pooling strategies are covered in our PostgreSQL Cheatsheet.
5. Plan for tenant migration: Ability to move tenants between patterns
6. Implement soft delete: Use deleted_at instead of hard deletes for tenant data
7. Audit everything: Log all tenant data access for compliance
8. Test isolation: Regular security audits to prevent cross-tenant data leakage

Conclusion

Choosing the right multi-tenancy database pattern depends on your specific requirements for isolation, cost, scalability, and operational complexity. The Shared Database, Shared Schema pattern works well for most SaaS applications, while Separate Database per Tenant is necessary for enterprise customers with strict compliance needs.

Start with the simplest pattern that meets your requirements, and plan for migration to a more isolated pattern as your needs evolve. Always prioritize security and data isolation, regardless of the pattern chosen.

Useful Links

• PostgreSQL Row-Level Security Documentation
• Multi-Tenant SaaS Database Architecture
• Designing Multi-Tenant Databases
• Comparing Go ORMs for PostgreSQL: GORM vs Ent vs Bun vs sqlc
• PostgreSQL Cheatsheet: A Developer’s Quick Reference
• DBeaver vs Beekeeper - SQL Database Management Tools
• SQL Cheatsheet - most useful SQL commands