Multi-Tenant SaaS Database Design Patterns on AWS

Choosing the right database architecture when building a multi-tenant Software as a Service (SaaS) application.

John Adelana

Senior Startups Solutions Architect at AWS

Published Feb 16, 2026

Last modified Feb 16, 2026

Introduction

Choosing the right database architecture is one of the most critical decisions when building a multi-tenant Software as a Service (SaaS) application. The pattern you select impacts everything from cost efficiency and operational complexity to security, compliance, and scalability. Let's consider three primary multi-tenant database patterns and when to use each.

The Three Core Patterns

1. Database-per-Tenant (Silo Model)

2. Schema-per-Tenant (Bridge Model)

3. Shared Database with Tenant Identifier (Pool Model)

Let's dive deep into each pattern, exploring their trade-offs, AWS implementation strategies, and real-world use cases.

Pattern 1: Database-per-Tenant (Silo Model)

Overview

In this pattern, each tenant gets their own dedicated database instance. This provides the highest level of isolation but comes with increased operational complexity and cost.

Architecture Characteristics

Isolation Level: Maximum
Operational Complexity: High
Cost Model: Higher per-tenant cost
Scalability: Horizontal (add more databases)

AWS Implementation Options

Amazon RDS with Database-per-Tenant:

This approach provisions a dedicated RDS instance for each tenant, providing maximum isolation and independent resource allocation. Use AWS Secrets Manager to securely generate and store database credentials, ensuring each tenant's database has unique, rotatable passwords that are never hardcoded in your application.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# Example: Creating tenant-specific RDS instances with AWS Secrets Manager
import boto3
import json

rds = boto3.client('rds')
secretsmanager = boto3.client('secretsmanager')

def provision_tenant_database(tenant_id, db_size='db.t3.micro'):
    # Generate a secure random password and store in Secrets Manager
    secret_name = f'tenant-{tenant_id}-db-credentials'
    
    # Create secret with auto-generated password
    secret_response = secretsmanager.create_secret(
        Name=secret_name,
        Description=f'Database credentials for tenant {tenant_id}',
        GenerateSecretString={
            'SecretStringTemplate': json.dumps({'username': 'admin'}),
            'GenerateStringKey': 'password',
            'PasswordLength': 32,
            'ExcludeCharacters': '"@/\\',
            'RequireEachIncludedType': True
        },
        Tags=[
            {'Key': 'TenantId', 'Value': tenant_id},
            {'Key': 'Purpose', 'Value': 'RDS-Credentials'}
        ]
    )
    
    # Retrieve the generated password
    secret_value = secretsmanager.get_secret_value(SecretId=secret_name)
    credentials = json.loads(secret_value['SecretString'])
    
    # Create RDS instance with secure password from Secrets Manager
    response = rds.create_db_instance(
        DBInstanceIdentifier=f'tenant-{tenant_id}-db',
        DBInstanceClass=db_size,
        Engine='postgres',
        MasterUsername=credentials['username'],
        MasterUserPassword=credentials['password'],
        AllocatedStorage=20,
        BackupRetentionPeriod=7,
        StorageEncrypted=True,  # Enable encryption at rest
        EnableCloudwatchLogsExports=['postgresql'],  # Enable audit logging
        DeletionProtection=True,  # Prevent accidental deletion
        Tags=[
            {'Key': 'TenantId', 'Value': tenant_id},
            {'Key': 'Pattern', 'Value': 'database-per-tenant'},
            {'Key': 'SecretArn', 'Value': secret_response['ARN']}
        ]
    )
    
    return {
        'database': response,
        'secret_arn': secret_response['ARN']
    }

# Retrieve credentials when needed
def get_tenant_credentials(tenant_id):
    secret_name = f'tenant-{tenant_id}-db-credentials'
    
    try:
        secret_value = secretsmanager.get_secret_value(SecretId=secret_name)
        return json.loads(secret_value['SecretString'])
    except secretsmanager.exceptions.ResourceNotFoundException:
        raise ValueError(f'Credentials not found for tenant {tenant_id}')

Amazon Aurora Serverless v2:

Automatically scales compute capacity
Pay only for resources used
Ideal for tenants with variable workloads
Cost-effective for unpredictable or intermittent workloads (may cost more than provisioned instances for steady, high-utilization workloads)

Amazon Aurora with Database Cloning:

Fast database provisioning using copy-on-write
Clone databases in minutes, not hours
Useful for tenant onboarding from templates

Advantages

Maximum Data Isolation: Complete physical separation between tenants
Compliance & Regulatory: Easier to meet data residency and compliance requirements
Performance Isolation: One tenant's workload doesn't impact others
Customization: Each tenant can have different schema versions or configurations
Backup & Recovery: Independent backup/restore per tenant
Migration: Easier to move individual tenants between regions or providers

Disadvantages

High Operational Overhead: Managing hundreds or thousands of databases
Cost: Higher infrastructure costs, especially for small tenants
Schema Management: Deploying schema changes across all databases
Monitoring Complexity: Need to monitor each database independently
Resource Utilization: Potential for underutilized resources

When to Use Database-per-Tenant

Use this pattern when:

Serving enterprise customers with strict compliance requirements (HIPAA, PCI-DSS, GDPR)
Tenants require data residency in specific regions
Customers demand contractual data isolation guarantees
Tenants have highly variable or unpredictable workloads
You have a small number of high-value tenants (< 100)
Tenants need custom schema modifications

Avoid this pattern when:

You have thousands of small tenants
Cost efficiency is critical
Operational simplicity is a priority
Tenants have similar, predictable workloads

Real-World Example: Healthcare SaaS Platform

A healthcare SaaS provider serving hospital systems uses database-per-tenant with Aurora PostgreSQL:

Tenant Profile: 50 hospital systems, each with 500-5,000 users
Compliance: HIPAA requires strict data isolation
Data Residency: Some hospitals require data in specific AWS regions
Implementation: Aurora Serverless v2 with automated backups
Cost Model: Passed through to customers as part of premium tier
Result: Zero cross-tenant data leakage incidents

Pattern 2: Schema-per-Tenant (Bridge Model)

Overview

Multiple tenants share the same database instance, but each tenant has a dedicated schema. This provides a middle ground between isolation and operational efficiency.

Architecture Characteristics

Isolation Level: Medium
Operational Complexity: Medium
Cost Model: Moderate per-tenant cost
Scalability: Vertical (scale database) + Horizontal (shard across databases)

AWS Implementation Options

Amazon RDS PostgreSQL with Schemas:

This approach uses PostgreSQL's native schema feature to create isolated namespaces within a single database instance. Each tenant gets their own schema with dedicated database users and permissions, providing logical separation while sharing the underlying database infrastructure for cost efficiency.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Create tenant-specific schemas
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- Create tenant-specific user with a secure password from AWS Secrets Manager
-- Note: In practice, retrieve the password from Secrets Manager and pass it securely
-- Example: aws secretsmanager get-secret-value --secret-id tenant-acme-user-password
CREATE USER acme_user WITH PASSWORD 'REPLACE_WITH_SECRETS_MANAGER_PASSWORD';
GRANT USAGE ON SCHEMA tenant_acme TO acme_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA tenant_acme TO acme_user;

-- Set default schema for user
ALTER USER acme_user SET search_path TO tenant_acme;

Secure User Provisioning with Secrets Manager:

Automate the creation of tenant schemas and database users with secure, randomly-generated passwords stored in AWS Secrets Manager. This approach eliminates hardcoded credentials and enables automatic password rotation while maintaining proper access control for each tenant's schema.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
import boto3
import json
import psycopg2

secretsmanager = boto3.client('secretsmanager')

def create_tenant_schema_and_user(db_connection_string, tenant_id):
    """Create schema and user with secure password from Secrets Manager"""
    
    # Generate secure password in Secrets Manager
    secret_name = f'tenant-{tenant_id}-schema-user-password'
    
    secret_response = secretsmanager.create_secret(
        Name=secret_name,
        Description=f'Database user password for tenant {tenant_id} schema',
        GenerateSecretString={
            'PasswordLength': 32,
            'ExcludeCharacters': '"@/\\\'',
            'RequireEachIncludedType': True
        },
        Tags=[
            {'Key': 'TenantId', 'Value': tenant_id},
            {'Key': 'Purpose', 'Value': 'Schema-User-Password'}
        ]
    )
    
    # Retrieve the generated password
    secret_value = secretsmanager.get_secret_value(SecretId=secret_name)
    password = secret_value['SecretString']
    
    # Connect to database and create schema + user
    conn = psycopg2.connect(db_connection_string)
    conn.autocommit = True
    cursor = conn.cursor()
    
    schema_name = f'tenant_{tenant_id}'
    user_name = f'{tenant_id}_user'
    
    # Create schema
    cursor.execute(f'CREATE SCHEMA IF NOT EXISTS {schema_name}')
    
    # Create user with secure password
    cursor.execute(f"CREATE USER {user_name} WITH PASSWORD %s", (password,))
    
    # Grant permissions
    cursor.execute(f'GRANT USAGE ON SCHEMA {schema_name} TO {user_name}')
    cursor.execute(f'GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA {schema_name} TO {user_name}')
    cursor.execute(f'ALTER DEFAULT PRIVILEGES IN SCHEMA {schema_name} GRANT ALL ON TABLES TO {user_name}')
    
    # Set default schema
    cursor.execute(f'ALTER USER {user_name} SET search_path TO {schema_name}')
    
    cursor.close()
    conn.close()
    
    return {
        'schema_name': schema_name,
        'user_name': user_name,
        'secret_arn': secret_response['ARN']
    }

Application-Level Schema Routing:

Once tenant schemas are provisioned, your application needs to route database queries to the correct schema based on the authenticated tenant. This middleware example demonstrates how to dynamically set the PostgreSQL search path for each request, ensuring tenant isolation at the application layer while retrieving credentials securely from AWS Secrets Manager.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# Python example with SQLAlchemy and Secrets Manager
import boto3
import json
from sqlalchemy import create_engine, event, text
from sqlalchemy.orm import sessionmaker

class TenantSchemaMiddleware:
    def __init__(self, secret_name, db_host, db_name):
        """Initialize with Secrets Manager secret instead of hardcoded credentials"""
        self.secret_name = secret_name
        self.db_host = db_host
        self.db_name = db_name
        self.engine = self._create_engine()
        
    def _get_db_credentials(self):
        """Retrieve database credentials from Secrets Manager"""
        secretsmanager = boto3.client('secretsmanager')
        secret_value = secretsmanager.get_secret_value(SecretId=self.secret_name)
        return json.loads(secret_value['SecretString'])
    
    def _create_engine(self):
        """Create SQLAlchemy engine with credentials from Secrets Manager"""
        credentials = self._get_db_credentials()
        db_url = f"postgresql://{credentials['username']}:{credentials['password']}@{self.db_host}/{self.db_name}"
        return create_engine(db_url)
        
    def get_session(self, tenant_id):
        Session = sessionmaker(bind=self.engine)
        session = Session()
        
        # Set schema for this session using parameterized query
        schema_name = f"tenant_{tenant_id}"
        # Use text() with bound parameters to prevent SQL injection
        session.execute(text("SET search_path TO :schema"), {"schema": schema_name})
        
        return session

# Usage - credentials retrieved from Secrets Manager
db = TenantSchemaMiddleware(
    secret_name='my-database-credentials',
    db_host='my-db.cluster-xxx.us-east-1.rds.amazonaws.com',
    db_name='mydb'
)
session = db.get_session("acme")
# All queries now execute in tenant_acme schema

Amazon Aurora with Schema Management:

Use Aurora's read replicas for read-heavy tenants
Implement connection pooling with RDS Proxy
Schema-level backup using pg_dump for individual tenants

Advantages

Good Isolation: Logical separation with database-level security
Cost Efficiency: Share database resources across tenants
Easier Management: Fewer database instances to manage
Backup Flexibility: Can backup individual schemas or entire database
Resource Sharing: Better resource utilization than database-per-tenant
Tenant-Specific Customization: Each schema can have different structures

Disadvantages

Noisy Neighbor Risk: One tenant can impact others' performance
Database Size Limits: PostgreSQL has practical limits on number of schemas
Connection Management: Need careful connection pooling strategy
Schema Migration Complexity: Must apply changes to all schemas
Monitoring Granularity: Harder to get per-tenant metrics
Scaling Limits: Eventually hit database instance limits

When to Use Schema-per-Tenant

Use this pattern when:

You have 10-500 tenants per database
Tenants need some customization but share core data model
You want balance between isolation and cost
Compliance requires logical separation (not physical)
Tenants have moderate, predictable workloads
You need tenant-specific backup/restore capabilities

Avoid this pattern when:

You have thousands of tenants (schema limits)
Tenants have wildly different workload patterns
You need maximum performance isolation
Schema customization per tenant is extensive

Real-World Example: Project Management SaaS

A project management platform uses schema-per-tenant with RDS PostgreSQL:

Tenant Profile: 200 mid-market companies, 50-500 users each
Database: RDS PostgreSQL db.r6g.2xlarge with 500GB storage
Tenants per Database: ~100 schemas per database instance
Sharding Strategy: Shard tenants across 2 database instances by region
Connection Pooling: RDS Proxy with 500 max connections

Pattern 3: Shared Database with Tenant Identifier (Pool Model)

Overview

All tenants share the same database and tables, with a tenant_id column distinguishing data. This is the most cost-efficient pattern but requires careful implementation to ensure data isolation.

Architecture Characteristics

Isolation Level: Low (application-enforced)
Operational Complexity: Low
Cost Model: Lowest per-tenant cost
Scalability: Vertical + Horizontal (sharding)

AWS Implementation Options

Table Design with Tenant ID:

The foundation of the shared database pattern is adding a tenant_id column to every table. This column acts as the discriminator that separates each tenant's data within the same physical tables. Proper indexing on tenant_id is critical for query performance, and foreign key constraints help maintain referential integrity across tenant boundaries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Core table structure with tenant_id
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

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

-- Composite indexes for common queries
CREATE INDEX idx_users_tenant_email ON users(tenant_id, email);

Row-Level Security (RLS) in PostgreSQL:

PostgreSQL's Row-Level Security feature provides an additional layer of defense by enforcing tenant isolation at the database level. RLS policies automatically filter rows based on the current tenant context, preventing accidental data leakage even if application code forgets to include tenant_id filters. This defense-in-depth approach significantly reduces the risk of cross-tenant data exposure.

1
2
3
4
5
6
7
8
9
10
11
12
-- Enable Row-Level Security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policy to enforce tenant isolation
CREATE POLICY tenant_isolation_policy ON users
    USING (tenant_id = current_setting('app.current_tenant')::UUID);

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

-- Now all queries automatically filter by tenant_id
SELECT * FROM users; -- Only returns current tenant's users

Application-Level Tenant Context:

While Row-Level Security provides database-level protection, your application layer needs a reliable way to identify and track the current tenant for each request. This middleware pattern uses Python's contextvars to maintain thread-safe tenant context throughout the request lifecycle, extracting tenant identification from subdomains, headers, or JWT tokens and making it available to all downstream query operations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# Python/Django example with tenant context
from contextvars import ContextVar

current_tenant = ContextVar('current_tenant', default=None)

class TenantMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response
    
    def __call__(self, request):
        # Extract tenant from subdomain, header, or JWT
        tenant_id = self.get_tenant_from_request(request)
        current_tenant.set(tenant_id)
        
        response = self.get_response(request)
        return response

# Query filter mixin
class TenantQuerySet(models.QuerySet):
    def for_tenant(self):
        tenant_id = current_tenant.get()
        if not tenant_id:
            raise ValueError("No tenant context set")
        return self.filter(tenant_id=tenant_id)

# Usage
users = User.objects.for_tenant().all()

Advantages

Maximum Cost Efficiency: Shared resources across all tenants
Operational Simplicity: Single database to manage
Easy Schema Changes: Deploy once, affects all tenants
Resource Utilization: Excellent resource sharing and efficiency
Scalability: Can support thousands of tenants per database
Analytics: Easier to run cross-tenant analytics and reporting

Disadvantages

Data Isolation Risk: Application bugs can leak data between tenants
Noisy Neighbor: Large tenants can impact small tenants
Compliance Challenges: May not meet strict regulatory requirements
Customization Limits: Difficult to customize per tenant
Query Complexity: Must always filter by tenant_id
Backup Granularity: Cannot backup individual tenants easily

Critical Security Considerations

Defense in Depth Strategy:

Database-Level: Row-Level Security policies
Application-Level: Tenant context validation
API-Level: JWT claims validation
Testing: Automated tests for tenant isolation
Monitoring: Alert on cross-tenant queries

Common Security Pitfalls:

Even with Row-Level Security and middleware in place, application code can still introduce vulnerabilities if queries don't properly filter by tenant. These examples illustrate the most common mistake—forgetting to include tenant context in queries—and show progressively better approaches that reduce the risk of cross-tenant data leakage through ORM-level enforcement.

1
2
3
4
5
6
7
8
9
10
11
# ❌ DANGEROUS: Missing tenant filter
def get_user(user_id):
    return User.objects.get(id=user_id)  # Can access any tenant's user!

# ✅ SAFE: Always include tenant context
def get_user(user_id, tenant_id):
    return User.objects.get(id=user_id, tenant_id=tenant_id)

# ✅ BETTER: Use ORM that enforces tenant filtering
def get_user(user_id):
    return User.objects.for_tenant().get(id=user_id)

When to Use Shared Database with Tenant ID

Use this pattern when:

You have thousands of small tenants with similar workloads
Cost efficiency is critical (startups, SMB-focused SaaS)
Tenants have predictable, similar usage patterns
Compliance doesn't require physical data separation
You need to scale to 10,000+ tenants
Cross-tenant analytics are important

Avoid this pattern when:

Serving enterprise customers with strict compliance needs
Tenants require data residency guarantees
Workloads vary dramatically between tenants
Regulatory requirements mandate physical isolation
Risk of data leakage is unacceptable

Real-World Example: Marketing Automation Platform

A marketing automation SaaS uses shared database with Aurora PostgreSQL:

Tenant Profile: 5,000+ small businesses, 1-50 users each
Database: Aurora PostgreSQL with 3 read replicas
Sharding: Tenant ID hash-based sharding across 4 writer clusters
Security: Row-Level Security + application-level validation
Monitoring: CloudWatch metrics per tenant_id using custom dimensions

Hybrid Approaches: Mixing Patterns

Real-world SaaS applications often combine patterns based on tenant tiers:

Tiered Architecture Example

Enterprise Tier (Database-per-Tenant):

Dedicated Aurora cluster per enterprise customer
Custom schema modifications allowed
Dedicated support and SLAs

Professional Tier (Schema-per-Tenant):

Shared RDS instance with dedicated schema
Standard schema with configuration options

Starter Tier (Shared Database):

Shared tables with tenant_id
Standard features only

Implementation Strategy

This routing implementation demonstrates how to dynamically direct tenant requests to the appropriate database infrastructure based on their subscription tier. Enterprise tenants get dedicated database clusters, professional tenants share infrastructure but have isolated schemas, and starter tenants share both infrastructure and tables with row-level isolation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# Tenant routing logic
class TenantRouter:
    def get_database_for_tenant(self, tenant_id):
        tenant = self.get_tenant_metadata(tenant_id)
        
        if tenant.tier == 'enterprise':
            # Database-per-tenant
            return f"tenant-{tenant_id}-db.cluster-xxx.us-east-1.rds.amazonaws.com"
        
        elif tenant.tier == 'professional':
            # Schema-per-tenant (shard by region)
            shard = self.get_shard_for_region(tenant.region)
            return {
                'host': shard.endpoint,
                'schema': f"tenant_{tenant_id}"
            }
        
        else:  # starter tier
            # Shared database (shard by hash)
            shard_id = hash(tenant_id) % 4
            return {
                'host': f"shared-db-{shard_id}.cluster-xxx.us-east-1.rds.amazonaws.com",
                'tenant_id': tenant_id
            }

AWS Service Recommendations by Pattern

Database-per-Tenant

Best AWS Services:

Aurora Serverless v2: Auto-scaling, pay-per-use
Aurora PostgreSQL/MySQL: High performance, automated backups
RDS with Reserved Instances: Cost optimization for predictable workloads

Supporting Services:

AWS Systems Manager Parameter Store: Store tenant database endpoints
AWS Secrets Manager: Manage per-tenant database credentials
Amazon EventBridge: Orchestrate tenant provisioning workflows
AWS Lambda: Automate database provisioning and management

Schema-per-Tenant

Best AWS Services:

RDS PostgreSQL: Native schema support
Aurora PostgreSQL: Better performance and availability
RDS Proxy: Connection pooling and management

Supporting Services:

Amazon CloudWatch: Per-schema monitoring with custom metrics
AWS Database Migration Service: Schema migration automation
Amazon ElastiCache: Reduce database load with caching

Shared Database with Tenant ID

Best AWS Services:

Aurora PostgreSQL: Row-Level Security support
Aurora MySQL: High performance for large tenant counts
DynamoDB: For NoSQL workloads with partition key as tenant_id

Supporting Services:

Amazon RDS Proxy: Connection pooling for high tenant counts
Amazon CloudWatch Logs Insights: Query-level tenant monitoring
AWS WAF: Protect against tenant enumeration attacks
Amazon ElastiCache: Cache tenant-specific data

Sharding Strategies for Scale

When a single database can't handle your workload, implement sharding:

Horizontal Sharding Approaches

1. Hash-Based Sharding:

Distribute tenants evenly across multiple database shards using a hash function. This approach applies a modulo operation to the tenant ID hash, ensuring predictable shard assignment and roughly equal distribution of tenants across your database infrastructure.

1
2
3
def get_shard(tenant_id, num_shards=4):
    shard_id = hash(tenant_id) % num_shards
    return f"shard-{shard_id}.cluster-xxx.region.rds.amazonaws.com"

Pros: Even distribution, simple logic
Cons: Difficult to rebalance, tenant migration complexity

2. Range-Based Sharding:

Assign tenants to shards based on ID ranges or creation timestamps. This strategy makes it straightforward to add new shards for future tenants while keeping existing tenant assignments stable, though it can lead to uneven load distribution as older shards fill up faster than newer ones.

1
2
3
4
5
6
7
# Shard by tenant creation date or ID range
def get_shard(tenant_id):
    if tenant_id < 1000:
        return "shard-0.cluster-xxx.region.rds.amazonaws.com"
    elif tenant_id < 2000:
        return "shard-1.cluster-xxx.region.rds.amazonaws.com"
    # ...

Pros: Easy to add new shards, predictable growth
Cons: Uneven distribution, hot shards

3. Geography-Based Sharding:

Route tenants to database shards based on their geographic location or region. This approach optimizes for data residency compliance and reduces latency by keeping tenant data physically close to their users, though it may result in uneven shard utilization across regions.

1
2
3
4
5
6
7
8
def get_shard(tenant_id):
    tenant = get_tenant(tenant_id)
    region_map = {
        'us': 'us-east-1.rds.amazonaws.com',
        'eu': 'eu-west-1.rds.amazonaws.com',
        'ap': 'ap-southeast-1.rds.amazonaws.com'
    }
    return region_map[tenant.region]

Pros: Data residency compliance, reduced latency
Cons: Uneven distribution, complex routing

4. Tenant-Size Based Sharding:

Dynamically route tenants to different database architectures based on their size or usage patterns. Large, high-value tenants receive dedicated database instances for performance isolation, while smaller tenants share sharded infrastructure for cost efficiency. This hybrid approach balances isolation needs with operational costs.

1
2
3
4
5
6
7
8
9
10
def get_shard(tenant_id):
    tenant = get_tenant(tenant_id)
    
    if tenant.size == 'large':
        # Large tenants get dedicated databases
        return f"tenant-{tenant_id}.cluster-xxx.region.rds.amazonaws.com"
    else:
        # Small tenants share shards
        shard_id = hash(tenant_id) % 4
        return f"shared-shard-{shard_id}.cluster-xxx.region.rds.amazonaws.com"

Pros: Isolates large tenants, optimizes costs
Cons: Complex routing, tenant migration on growth

Shard Management with AWS

Shard Registry in DynamoDB:

Store tenant-to-shard mappings in DynamoDB for fast, flexible routing decisions. This approach allows you to dynamically reassign tenants to different shards without code changes, supporting tenant migrations, load rebalancing, and multi-pattern deployments where different tenants use different database architectures.

1
2
3
4
5
6
7
8
9
10
# Store shard mapping in DynamoDB
shard_registry = {
    'tenant_id': 'acme-corp',
    'shard_id': 'shard-2',
    'endpoint': 'shard-2.cluster-xxx.us-east-1.rds.amazonaws.com',
    'pattern': 'schema-per-tenant',
    'schema_name': 'tenant_acme',
    'region': 'us-east-1',
    'created_at': '2026-01-15T10:00:00Z'
}

Benefits:

Fast lookup with DynamoDB's single-digit millisecond latency
Easy to update shard assignments
Can cache in application memory or ElastiCache
Supports complex routing logic

Monitoring and Observability

Key Metrics to Track

Per-Tenant Metrics:

Query latency (p50, p95, p99)
Connection count
Query throughput (reads/writes per second)
Error rates
Storage usage

Database-Level Metrics:

CPU utilization
Memory usage
IOPS and throughput
Connection pool saturation
Replication lag (for read replicas)

Cost Optimization Strategies

Database-per-Tenant Cost Optimization

Aurora Serverless v2: Scale to zero for inactive tenants
Reserved Instances: 1-year or 3-year commitments for stable tenants
Savings Plans: Flexible commitment across instance families
Right-Sizing: Monitor and adjust instance sizes per tenant
Storage Optimization: Use Aurora's storage auto-scaling

Schema-per-Tenant Cost Optimization

RDS Proxy: Reduce connection overhead
Read Replicas: Offload read traffic to cheaper replicas
ElastiCache: Cache frequently accessed tenant data
Vertical Scaling: Use larger instances for better price/performance
Storage Optimization: Regular vacuum and analyze operations

Shared Database Cost Optimization

Connection Pooling: Maximize connection reuse
Query Optimization: Ensure tenant_id indexes are used
Partitioning: Partition large tables by tenant_id
Archival: Move inactive tenant data to S3
Compression: Use table compression for large datasets

Security Best Practices

Network Security

VPC Configuration:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# Example VPC setup for multi-tenant databases
VPC:
  CIDR: 10.0.0.0/16
  
Subnets:
  Database:  # Private subnets for database instances
    - 10.0.1.0/24 (us-east-1a) # Primary database subnet in availability zone 1a
    - 10.0.2.0/24 (us-east-1b)
  Application:
    - 10.0.10.0/24 (us-east-1a)
    - 10.0.11.0/24 (us-east-1b)

SecurityGroups:
  Database:
    Ingress:
      - Source: Application-SG
        Port: 5432
        Protocol: TCP

Encryption

Encryption at Rest:

Enable RDS encryption with AWS KMS
Use customer-managed keys (CMK) for enterprise tenants
Separate KMS keys per tenant for database-per-tenant pattern

Encryption in Transit:

Enforce SSL/TLS connections
Use RDS certificate validation
Implement certificate pinning in applications

1
2
3
4
5
6
7
8
9
10
11
# PostgreSQL connection with SSL
import psycopg2

conn = psycopg2.connect(
    host="database.cluster-xxx.us-east-1.rds.amazonaws.com",
    database="mydb",
    user="dbuser",
    password="password",
    sslmode="require",
    sslrootcert="/path/to/rds-ca-bundle.pem"
)

Audit Logging

Enable Database Audit Logs:

1
2
3
4
5
6
7
8
-- PostgreSQL audit logging
ALTER SYSTEM SET log_statement = 'all';  -- Log all SQL statements for security auditing
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_duration = 'on';

-- Log tenant context in queries
SET application_name = 'tenant_acme';

CloudWatch Logs Integration:

Export RDS logs to CloudWatch Logs
Set up log retention policies
Create CloudWatch Alarms for suspicious activity
Use CloudWatch Logs Insights for analysis

Decision Framework

Choosing Your Pattern: A Decision Tree

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
START: How many tenants do you expect?

├─ < 100 tenants
│  └─ Do tenants need strict data isolation?
│     ├─ YES → Database-per-Tenant
│     │        └─ What's the workload pattern?
│     │           ├─ Variable/Unpredictable → Aurora Serverless v2
│     │           └─ Steady/Predictable → Aurora Provisioned or RDS with Reserved Instances
│     └─ NO → Schema-per-Tenant (RDS PostgreSQL)
│
├─ 100-1,000 tenants
│  └─ Are workloads similar across tenants?
│     ├─ YES → Schema-per-Tenant with sharding
│     └─ NO → Hybrid (large tenants: dedicated, small: shared)
│
└─ > 1,000 tenants
   └─ Do you have enterprise customers?
      ├─ YES → Hybrid approach (tiered)
      └─ NO → Shared Database with tenant_id

Evaluation Criteria Matrix

Criteria	Database-per-Tenant	Schema-per-Tenant	Shared Database
Data Isolation	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐
Cost Efficiency	⭐⭐	⭐⭐⭐	⭐⭐⭐⭐⭐
Operational Complexity	⭐	⭐⭐⭐	⭐⭐⭐⭐⭐
Scalability	⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Customization	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐
Performance Isolation	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐
Compliance	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐
Backup Granularity	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐
Schema Management	⭐⭐	⭐⭐⭐	⭐⭐⭐⭐⭐
Monitoring	⭐⭐⭐⭐⭐	⭐⭐⭐	⭐⭐⭐

Common Pitfalls and How to Avoid Them

Pitfall 1: Inadequate Tenant Isolation Testing

Problem: Data leakage between tenants due to missing tenant_id filters

Solution: Implement comprehensive automated testing to verify tenant isolation at every layer. Create test suites that attempt to access data across tenant boundaries and verify that proper authorization checks prevent cross-tenant data access. Include these tests in your CI/CD pipeline to catch isolation bugs before they reach production.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# Automated tenant isolation tests
import pytest

def test_tenant_isolation():
    # Create test data for two tenants
    tenant_a = create_test_tenant('tenant-a')
    tenant_b = create_test_tenant('tenant-b')
    
    user_a = create_user(tenant_a, 'user-a@example.com')
    user_b = create_user(tenant_b, 'user-b@example.com')
    
    # Authenticate as tenant A
    client = authenticate_as_tenant(tenant_a)
    
    # Attempt to access tenant B's data
    response = client.get(f'/users/{user_b.id}')
    
    # Should return 404 or 403, not 200
    assert response.status_code in [403, 404]
    
    # Verify tenant A can only see their data
    users = client.get('/users').json()
    assert len(users) == 1
    assert users[0]['id'] == user_a.id

Pitfall 2: Connection Pool Exhaustion

Problem: Too many tenants sharing limited database connections

Solution: Implement intelligent connection pooling at multiple layers to efficiently manage database connections across many tenants. Use RDS Proxy to handle connection multiplexing at the AWS level, combined with application-level pooling to minimize connection overhead and prevent exhaustion.

Implement RDS Proxy for connection pooling
Use application-level connection pooling (PgBouncer, HikariCP)
Monitor connection metrics and set alerts
Implement connection limits per tenant

Pitfall 3: Noisy Neighbor Problems

Problem: Large tenant impacts performance of small tenants

Solution: Implement tenant-level rate limiting and resource quotas to prevent any single tenant from monopolizing database resources. Use application-level throttling, query timeouts, and monitoring to detect and mitigate noisy neighbor scenarios. Consider implementing tenant-specific connection pools and query execution limits.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
# Implement tenant-level rate limiting
from functools import wraps
import time

class TenantRateLimiter:
    def __init__(self):
        self.tenant_requests = {}
    
    def check_rate_limit(self, tenant_id, max_requests=1000, window=60):
        now = time.time()
        
        if tenant_id not in self.tenant_requests:
            self.tenant_requests[tenant_id] = []
        
        # Remove old requests outside window
        self.tenant_requests[tenant_id] = [
            req_time for req_time in self.tenant_requests[tenant_id]
            if now - req_time < window
        ]
        
        # Check if limit exceeded
        if len(self.tenant_requests[tenant_id]) >= max_requests:
            return False
        
        self.tenant_requests[tenant_id].append(now)
        return True

rate_limiter = TenantRateLimiter()

def rate_limit(max_requests=1000, window=60):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            tenant_id = get_current_tenant()
            
            if not rate_limiter.check_rate_limit(tenant_id, max_requests, window):
                raise RateLimitExceeded(f"Tenant {tenant_id} exceeded rate limit")
            
            return func(*args, **kwargs)
        return wrapper
    return decorator

@rate_limit(max_requests=1000, window=60)
def create_user(data):
    # Create user logic
    pass

Pitfall 4: Inefficient Schema Migrations

Problem: Schema changes take hours across thousands of tenants

Solution: Use parallel migration strategies and online schema change tools to minimize downtime and speed up deployments. Implement batching, progress monitoring, and rollback capabilities to safely manage schema changes across large numbers of tenants without blocking production traffic.

Use online schema migration tools (pt-online-schema-change, gh-ost)
Implement blue-green deployments for schema changes
Batch migrations and monitor progress
Test migrations on copy of production data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# Parallel schema migration across tenants
import concurrent.futures
import boto3

def migrate_tenant_schema(tenant_id, migration_sql):
    try:
        db = get_tenant_database(tenant_id)
        db.execute(migration_sql)
        return {'tenant_id': tenant_id, 'status': 'success'}
    except Exception as e:
        return {'tenant_id': tenant_id, 'status': 'failed', 'error': str(e)}

def migrate_all_tenants(migration_sql, max_workers=10):
    tenants = get_all_tenant_ids()
    
    with concurrent.futures.ThreadPoolExecutor(max_workers=max_workers) as executor:
        futures = [
            executor.submit(migrate_tenant_schema, tenant_id, migration_sql)
            for tenant_id in tenants
        ]
        
        results = [future.result() for future in concurrent.futures.as_completed(futures)]
    
    # Report results
    success = sum(1 for r in results if r['status'] == 'success')
    failed = sum(1 for r in results if r['status'] == 'failed')
    
    print(f"Migration complete: {success} succeeded, {failed} failed")
    return results

Pitfall 5: Inadequate Monitoring

Problem: Can't identify which tenant is causing performance issues

Solution: Implement tenant-aware monitoring and observability by tagging all database queries, metrics, and logs with tenant identifiers. Use custom CloudWatch metrics and dimensions to track per-tenant performance, enabling quick identification of problematic tenants and their impact on shared resources.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
# Comprehensive tenant monitoring
import time
from functools import wraps

def monitor_tenant_query(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        tenant_id = get_current_tenant()
        start_time = time.time()
        
        try:
            result = func(*args, **kwargs)
            duration = time.time() - start_time
            
            # Log query metrics
            log_metric({
                'tenant_id': tenant_id,
                'function': func.__name__,
                'duration': duration,
                'status': 'success',
                'timestamp': time.time()
            })
            
            # Alert on slow queries
            if duration > 5.0:  # 5 seconds
                alert_slow_query(tenant_id, func.__name__, duration)
            
            return result
            
        except Exception as e:
            duration = time.time() - start_time
            
            log_metric({
                'tenant_id': tenant_id,
                'function': func.__name__,
                'duration': duration,
                'status': 'error',
                'error': str(e),
                'timestamp': time.time()
            })
            
            raise
    
    return wrapper

@monitor_tenant_query
def get_users(tenant_id):
    return User.objects.filter(tenant_id=tenant_id).all()

Performance Optimization Techniques

Indexing Strategies

For Shared Database Pattern:

1
2
3
4
5
6
7
8
9
10
-- Composite indexes with tenant_id first
CREATE INDEX idx_users_tenant_email ON users(tenant_id, email);
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at DESC);

-- Partial indexes for active tenants
CREATE INDEX idx_active_users ON users(tenant_id, email)  -- Index only active users to reduce index size 
WHERE status = 'active';

-- Covering indexes to avoid table lookups
CREATE INDEX idx_users_tenant_lookup ON users(tenant_id, email, name, created_at);

Index Maintenance:

1
2
3
4
5
6
7
8
9
-- Regular maintenance
VACUUM ANALYZE users;  -- Reclaim storage and update statistics for query planner
REINDEX TABLE users;

-- Monitor index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Query Optimization

Use EXPLAIN ANALYZE:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Analyze query performance
EXPLAIN ANALYZE  -- Shows actual execution plan with timing and row counts
SELECT u.*, o.order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE tenant_id = 'acme-corp'
    GROUP BY user_id
) o ON u.id = o.user_id
WHERE u.tenant_id = 'acme-corp'
AND u.status = 'active';

-- Look for:
-- - Sequential scans (should use indexes)
-- - High execution time
-- - Large row counts

Connection Pooling with RDS Proxy

Benefits:

Reduces database connection overhead
Improves application scalability
Handles connection failures gracefully
Supports IAM authentication

Disaster Recovery and Business Continuity

Backup Strategies by Pattern

Database-per-Tenant:

Automated daily snapshots per database
Point-in-time recovery (PITR) enabled
Cross-region snapshot copies for critical tenants
Retention: 7-35 days based on tenant tier

Schema-per-Tenant:

Database-level automated snapshots
Schema-level logical backups using pg_dump
Transaction log archival to S3
Retention: 7-30 days

Shared Database:

Automated snapshots of entire database
Continuous backup with PITR
Cannot restore individual tenants easily
Retention: 7-35 days

Disaster Recovery Architecture

This example demonstrates how to implement cross-region disaster recovery for multi-tenant databases using AWS RDS read replicas. The DisasterRecoveryManager class automates the creation of read replicas in a secondary region, enabling failover capabilities and geographic redundancy for tenant databases.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# Multi-region DR setup
class DisasterRecoveryManager:
    def __init__(self):
        self.primary_region = 'us-east-1'
        self.dr_region = 'us-west-2'
    
    def setup_cross_region_replication(self, tenant_id):
        # For database-per-tenant pattern
        primary_db = f"tenant-{tenant_id}-db"
        
        # Create read replica in DR region
        rds = boto3.client('rds', region_name=self.primary_region)
        
        response = rds.create_db_instance_read_replica(
            DBInstanceIdentifier=f"{primary_db}-dr",
            SourceDBInstanceIdentifier=primary_db,
            DestinationRegion=self.dr_region,
            CopyTagsToSnapshot=True
        )
        
        return response
    
    def failover_to_dr(self, tenant_id):
        # Promote read replica to standalone
        rds = boto3.client('rds', region_name=self.dr_region)
        
        response = rds.promote_read_replica(
            DBInstanceIdentifier=f"tenant-{tenant_id}-db-dr"
        )
        
        # Update DNS or application configuration
        self.update_tenant_endpoint(tenant_id, self.dr_region)
        
        return response

RTO and RPO Targets

Pattern	RTO (Full System Recovery)	RPO (Typical)	Single Tenant Recovery	Cost
Database-per-Tenant	60-120+ minutes (many DBs)	< 5 minutes (smaller DBs, faster backups)	15-30 minutes	High
Schema-per-Tenant	30-60 minutes	< 5-15 minutes (moderate DB size)	30-60 minutes	Medium
Shared Database	15-30 minutes (one DB)	< 5-30 minutes (larger DB, slower PITR)	Cannot isolate	Low

Key Considerations:

RTO: Shared Database recovers fastest (one DB) but affects all tenants; Database-per-Tenant slowest (many DBs) but isolated failures
RPO: All patterns can achieve < 5 min RPO with Aurora continuous backups, but practical RPO varies:
- Database-per-Tenant: Smaller databases = faster backups and point-in-time recovery
- Shared Database: Larger databases = longer backup windows and slower PITR operations
- Schema-per-Tenant: Moderate database size with balanced performance

Improving RTO/RPO:

Use Aurora Global Database for < 1 second RPO across regions
Implement automated failover with Route 53 health checks
Enable continuous backups and transaction logs
Regular DR testing and runbooks
Multi-AZ deployments for high availability (reduces RTO to minutes)

Compliance and Regulatory Considerations

Data Residency Requirements

Geographic Sharding:

This example shows how to implement data residency compliance by routing tenants to region-specific databases based on their country. The code maps tenant countries to appropriate AWS regions, ensuring data stays within required geographic boundaries to meet GDPR, data sovereignty, and other regulatory requirements.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# Route tenants to region-specific databases
REGION_REQUIREMENTS = {
    'eu-tenants': 'eu-west-1',
    'us-tenants': 'us-east-1',
    'apac-tenants': 'ap-southeast-1'
}

def get_database_region(tenant_id):
    tenant = get_tenant(tenant_id)
    
    if tenant.country in ['DE', 'FR', 'IT', 'ES']:
        return 'eu-west-1'
    elif tenant.country in ['US', 'CA', 'MX']:
        return 'us-east-1'
    elif tenant.country in ['AU', 'SG', 'JP']:
        return 'ap-southeast-1'
    else:
        return 'us-east-1'  # Default region

GDPR Compliance

Right to be Forgotten:

This example demonstrates how to implement GDPR's "Right to be Forgotten" requirement across different multi-tenant patterns. The code handles complete data deletion for each pattern: dropping entire database instances for database-per-tenant, removing schemas for schema-per-tenant, and deleting all tenant records for shared databases, while maintaining final snapshots for audit purposes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
def delete_tenant_data(tenant_id):
    # For database-per-tenant: Delete entire database
    if is_dedicated_database(tenant_id):
        rds = boto3.client('rds')
        rds.delete_db_instance(
            DBInstanceIdentifier=f'tenant-{tenant_id}-db',
            SkipFinalSnapshot=False,
            FinalDBSnapshotIdentifier=f'tenant-{tenant_id}-final-snapshot'
        )
    
    # For schema-per-tenant: Drop schema
    elif is_schema_per_tenant(tenant_id):
        db = get_database_connection(tenant_id)
        db.execute(f"DROP SCHEMA tenant_{tenant_id} CASCADE")
    
    # For shared database: Delete all tenant records
    else:
        db = get_database_connection()
        tables = get_all_tables()
        
        for table in tables:
            db.execute(f"DELETE FROM {table} WHERE tenant_id = %s", [tenant_id])
        
        # IMPORTANT: Handle table bloat after large deletions
        # In PostgreSQL (RDS/Aurora), DELETE doesn't reclaim disk space immediately
        handle_post_deletion_bloat(tenant_id, tables)
    
    # Delete from backup snapshots
    delete_tenant_from_backups(tenant_id)

def handle_post_deletion_bloat(tenant_id, tables):
    """
    Manage table bloat after deleting tenant data in shared database pattern.
    
    Table Bloat Impact:
    - PostgreSQL uses MVCC (Multi-Version Concurrency Control)
    - DELETE marks rows as dead but doesn't remove them immediately
    - Dead tuples consume disk space and slow down queries
    - Indexes also contain dead entries
    
    RDS vs Aurora Storage Behavior:
    
    RDS PostgreSQL:
    - Storage is block-based (EBS volumes)
    - Deleted data creates "dead tuples" that occupy space
    - VACUUM reclaims space within the table but doesn't shrink files
    - VACUUM FULL rebuilds table and returns space to OS (requires table lock, downtime)
    - Storage billing: You pay for allocated storage even after deletions
    - To reduce storage costs: Must run VACUUM FULL (disruptive) or pg_repack
    
    Aurora PostgreSQL:
    - Storage is virtualized and shared across cluster
    - Automatically reclaims storage after VACUUM without VACUUM FULL
    - Storage shrinks automatically as dead tuples are cleaned up
    - No need for VACUUM FULL in most cases
    - Storage billing: Automatically reduced as data is deleted and vacuumed
    - Much better for multi-tenant scenarios with frequent tenant deletions
    """
    db = get_database_connection()
    
    # Run VACUUM ANALYZE to clean up dead tuples and update statistics
    for table in tables:
        print(f"Vacuuming {table} after tenant {tenant_id} deletion...")
        db.execute(f"VACUUM ANALYZE {table}")
    
    # For RDS: Consider VACUUM FULL during maintenance window if storage costs are high
    # WARNING: VACUUM FULL requires exclusive table lock (blocks all operations)
    # Only run during scheduled maintenance windows
    if is_rds_instance() and is_maintenance_window():
        for table in tables:
            print(f"Running VACUUM FULL on {table} to reclaim disk space...")
            db.execute(f"VACUUM FULL {table}")
    
    # For Aurora: Regular VACUUM is sufficient, storage auto-shrinks
    # No VACUUM FULL needed - Aurora handles storage reclamation automatically

HIPAA Compliance

Requirements:

Encryption at rest and in transit
Audit logging of all data access
Access controls and authentication
Business Associate Agreement (BAA) with AWS

Implementation:

This example shows how to configure an RDS database instance to meet HIPAA compliance requirements. The code enables encryption at rest using AWS KMS, isolates the database within a VPC, and configures audit logging through CloudWatch to track all database access—essential controls for protecting Protected Health Information (PHI).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# HIPAA-compliant database configuration
def create_hipaa_compliant_database(tenant_id):
    rds = boto3.client('rds')
    
    response = rds.create_db_instance(
        DBInstanceIdentifier=f'tenant-{tenant_id}-db',
        DBInstanceClass='db.r6g.xlarge',
        Engine='postgres',
        EngineVersion='15.4',
        
        # Encryption
        StorageEncrypted=True,
        KmsKeyId='arn:aws:kms:region:account:key/xxx',
        
        # Network isolation
        PubliclyAccessible=False,
        VpcSecurityGroupIds=['sg-xxx'],
        DBSubnetGroupName='private-subnet-group',
        
        # Audit logging
        EnableCloudwatchLogsExports=['postgresql'],
        
        # Backup and recovery
        BackupRetentionPeriod=35,
        PreferredBackupWindow='03:00-04:00',
        
        # High availability
        MultiAZ=True,
        
        Tags=[
            {'Key': 'Compliance', 'Value': 'HIPAA'},
            {'Key': 'TenantId', 'Value': tenant_id}
        ]
    )
    
    return response

Future-Proofing Your Architecture

Planning for Scale

Growth Projections:

Use this capacity planning model to project your infrastructure needs based on tenant growth rates and usage patterns. The planner calculates future tenant counts, storage requirements, query volumes, and the number of database instances needed for each multi-tenant pattern. This helps you anticipate scaling needs and budget for infrastructure costs as your SaaS platform grows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# Capacity planning model
class CapacityPlanner:
    def __init__(self, pattern='shared'):
        self.pattern = pattern  # 'database-per-tenant', 'schema-per-tenant', or 'shared'
        self.current_tenants = 1000
        self.growth_rate = 0.20  # 20% monthly growth
        self.avg_storage_per_tenant = 5  # GB
        self.avg_queries_per_tenant = 10000  # per day
    
    def project_capacity(self, months=12):
        projections = []
        
        for month in range(1, months + 1):
            tenants = self.current_tenants * (1 + self.growth_rate) ** month
            storage = tenants * self.avg_storage_per_tenant
            queries_per_day = tenants * self.avg_queries_per_tenant
            
            # Estimate database instances needed
            if self.pattern == 'database-per-tenant':
                instances = tenants
            elif self.pattern == 'schema-per-tenant':
                instances = tenants / 100  # 100 schemas per instance
            else:  # shared
                instances = tenants / 5000  # 5000 tenants per instance
            
            projections.append({
                'month': month,
                'tenants': int(tenants),
                'storage_gb': int(storage),
                'queries_per_day': int(queries_per_day),
                'instances_needed': int(instances)
            })
        
        return projections

Evolution Path

Typical SaaS Database Evolution:

Phase 1: MVP (0-100 tenants)
- Pattern: Shared database with tenant_id
- Database: Single RDS instance
- Focus: Speed to market, feature development
Phase 2: Growth (100-1,000 tenants)
- Pattern: Schema-per-tenant or hybrid
- Database: Sharded across multiple RDS instances
- Focus: Scalability, performance optimization
Phase 3: Enterprise (1,000+ tenants)
- Pattern: Hybrid (tiered approach)
- Database: Mix of dedicated and shared
- Focus: Enterprise features, compliance, SLAs
Phase 4: Global Scale (10,000+ tenants)
- Pattern: Fully distributed, multi-region
- Database: Global database with regional sharding
- Focus: Global availability, data residency

Modernization Opportunities

Consider Aurora Serverless v2:

Automatic scaling based on workload
Pay only for capacity used
Scales to zero for inactive tenants
Ideal for variable workloads

Consider DynamoDB for Specific Use Cases:

Metadata and tenant configuration
Session management
High-throughput, low-latency requirements
Infinite scalability needs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Hybrid approach: DynamoDB for metadata, RDS for transactional data
class HybridTenantStore:
    def __init__(self):
        self.dynamodb = boto3.resource('dynamodb')
        self.tenant_table = self.dynamodb.Table('tenants')
    
    def get_tenant_metadata(self, tenant_id):
        # Fast lookup from DynamoDB
        response = self.tenant_table.get_item(Key={'tenant_id': tenant_id})
        return response.get('Item')
    
    def get_tenant_database(self, tenant_id):
        # Get database endpoint from metadata
        metadata = self.get_tenant_metadata(tenant_id)
        return metadata['database_endpoint']

NoSQL and DynamoDB Multi-Tenant Patterns

DynamoDB for Multi-Tenant Applications

While relational databases are common for SaaS applications, DynamoDB offers unique advantages for certain multi-tenant use cases, particularly when you need:

Infinite scalability without operational overhead
Single-digit millisecond latency at any scale
Predictable performance regardless of tenant count
Serverless architecture with pay-per-request pricing

DynamoDB Multi-Tenant Design Patterns

Pattern 1: Partition Key as Tenant ID

This DynamoDB table design uses the tenant ID as the partition key, ensuring that all data for a given tenant is stored together and can be efficiently queried. The item ID serves as the sort key, allowing you to retrieve specific items or perform range queries within a tenant's partition. This pattern provides natural tenant isolation and excellent query performance for single-tenant operations.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# Table design with tenant_id as partition key
table_schema = {
    'TableName': 'multi-tenant-data',
    'KeySchema': [
        {'AttributeName': 'tenant_id', 'KeyType': 'HASH'},  # Partition key
        {'AttributeName': 'item_id', 'KeyType': 'RANGE'}    # Sort key
    ],
    'AttributeDefinitions': [
        {'AttributeName': 'tenant_id', 'AttributeType': 'S'},
        {'AttributeName': 'item_id', 'AttributeType': 'S'}
    ]
}

# Query examples
import boto3

dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('multi-tenant-data')

def get_tenant_items(tenant_id):
    response = table.query(
        KeyConditionExpression='tenant_id = :tid',
        ExpressionAttributeValues={':tid': tenant_id}
    )
    return response['Items']

def get_specific_item(tenant_id, item_id):
    response = table.get_item(
        Key={'tenant_id': tenant_id, 'item_id': item_id}
    )
    return response.get('Item')

Advantages:

Perfect data distribution across partitions
Efficient tenant-specific queries
Automatic scaling per tenant
No noisy neighbor issues (each tenant on different partitions)

Considerations:

Cannot easily query across tenants
Hot partition risk for very large tenants
Need GSIs for non-tenant queries

Pattern 2: Composite Keys with Tenant Prefix

This DynamoDB pattern uses composite keys with tenant and entity type prefixes, enabling flexible queries within a tenant's data. By structuring the partition key as TENANT#{tenant_id} and the sort key as {entity_type}#{entity_id}, you can efficiently query all entities of a specific type for a tenant using the begins_with condition. This pattern supports multiple entity types in a single table while maintaining tenant isolation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# Use tenant prefix in sort key for flexible queries
def create_item(tenant_id, entity_type, entity_id, data):
    table.put_item(
        Item={
            'PK': f'TENANT#{tenant_id}',
            'SK': f'{entity_type}#{entity_id}',
            'data': data,
            'tenant_id': tenant_id,
            'entity_type': entity_type,
            'created_at': datetime.utcnow().isoformat()
        }
    )

# Query all users for a tenant
def get_tenant_users(tenant_id):
    response = table.query(
        KeyConditionExpression='PK = :pk AND begins_with(SK, :sk)',
        ExpressionAttributeValues={
            ':pk': f'TENANT#{tenant_id}',
            ':sk': 'USER#'
        }
    )
    return response['Items']

Pattern 3: Global Secondary Indexes for Cross-Tenant Queries

When you need to query data across tenants or by attributes other than tenant ID, Global Secondary Indexes (GSIs) provide the solution. This example creates a GSI with email as the partition key and tenant_id as the sort key, enabling efficient lookups by email address while still maintaining tenant context. This is particularly useful for admin operations, user authentication, or compliance queries that need to search across all tenants.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# GSI for querying by email across tenants (admin use case)
gsi_schema = {
    'IndexName': 'email-index',
    'KeySchema': [
        {'AttributeName': 'email', 'KeyType': 'HASH'},
        {'AttributeName': 'tenant_id', 'KeyType': 'RANGE'}
    ],
    'Projection': {'ProjectionType': 'ALL'}
}

# Query user by email
def find_user_by_email(email):
    response = table.query(
        IndexName='email-index',
        KeyConditionExpression='email = :email',
        ExpressionAttributeValues={':email': email}
    )
    return response['Items']

DynamoDB Tenant Isolation with IAM

DynamoDB supports fine-grained access control through IAM policies that can restrict access at the item level using condition keys. This allows you to enforce tenant isolation by ensuring users can only access items belonging to their tenant, even when using a shared table architecture.

Fine-Grained Access Control:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# Generate tenant-specific IAM policy
def generate_tenant_policy(tenant_id):
    policy = {
        "Version": "2012-10-17",
        "Statement": [{
            "Effect": "Allow",
            "Action": [
                "dynamodb:GetItem",
                "dynamodb:PutItem",
                "dynamodb:UpdateItem",
                "dynamodb:DeleteItem",
                "dynamodb:Query"
            ],
            "Resource": "arn:aws:dynamodb:region:account:table/multi-tenant-data",
            "Condition": {
                "ForAllValues:StringEquals": {
                    "dynamodb:LeadingKeys": [tenant_id]
                }
            }
        }]
    }
    return policy

# Use with AWS STS to generate temporary credentials
sts = boto3.client('sts')

def get_tenant_credentials(tenant_id):
    policy = generate_tenant_policy(tenant_id)
    
    response = sts.assume_role(
        RoleArn='arn:aws:iam::account:role/TenantRole',
        RoleSessionName=f'tenant-{tenant_id}',
        Policy=json.dumps(policy),
        DurationSeconds=3600
    )
    
    return response['Credentials']

Hybrid Architecture: DynamoDB + RDS

Use DynamoDB for:

Tenant metadata and configuration
Session management
Real-time activity feeds
High-throughput event logging
Caching layer

Use RDS for:

Complex relational data
ACID transactions
Complex joins and aggregations
Reporting and analytics

Implementation Example:

This hybrid approach uses DynamoDB to store tenant metadata and routing information for fast lookups, while tenant-specific transactional data resides in dedicated RDS databases. The pattern combines DynamoDB's low-latency key-value access with RDS's relational capabilities for complex queries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
class HybridDataStore:
    def __init__(self):
        self.dynamodb = boto3.resource('dynamodb')
        self.metadata_table = self.dynamodb.Table('tenant-metadata')
        self.rds_connection_pool = {}
    
    def get_tenant_config(self, tenant_id):
        # Fast metadata lookup from DynamoDB
        response = self.metadata_table.get_item(
            Key={'tenant_id': tenant_id}
        )
        return response.get('Item', {})
    
    def get_tenant_database(self, tenant_id):
        # Get RDS endpoint from DynamoDB metadata
        config = self.get_tenant_config(tenant_id)
        
        if tenant_id not in self.rds_connection_pool:
            self.rds_connection_pool[tenant_id] = psycopg2.connect(
                host=config['db_endpoint'],
                database=config['db_name'],
                user=config['db_user'],
                password=config['db_password']
            )
        
        return self.rds_connection_pool[tenant_id]
    
    def log_tenant_activity(self, tenant_id, activity_type, details):
        # High-throughput logging to DynamoDB
        self.dynamodb.Table('tenant-activity').put_item(
            Item={
                'tenant_id': tenant_id,
                'timestamp': int(time.time() * 1000),
                'activity_type': activity_type,
                'details': details
            }
        )

Aurora Serverless v2 Deep Dive

When to Choose Aurora Serverless v2

Aurora Serverless v2 is ideal for multi-tenant SaaS applications with:

Unpredictable or variable workloads per tenant
Development and test environments
Infrequently accessed applications
New applications with unknown scaling requirements

Capacity Units and Scaling

Understanding ACUs (Aurora Capacity Units):

1 ACU = 2 GB RAM + corresponding CPU and networking
Minimum: 0.5 ACU (1 GB RAM)
Maximum: 128 ACU (256 GB RAM)
Scales in increments of 0.5 ACU

Scaling Configuration:

Configure Aurora Serverless v2 scaling parameters to automatically adjust capacity based on workload demands. This example shows how to set minimum and maximum ACU limits, enable auto-pause for cost savings during idle periods, and securely manage database credentials using AWS Secrets Manager.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
import boto3
import json

rds = boto3.client('rds')
secretsmanager = boto3.client('secretsmanager')

def create_serverless_v2_cluster(tenant_id):
    # Generate secure credentials using Secrets Manager
    secret_name = f'tenant-{tenant_id}-cluster-credentials'
    
    secret_response = secretsmanager.create_secret(
        Name=secret_name,
        Description=f'Aurora Serverless v2 credentials for tenant {tenant_id}',
        GenerateSecretString={
            'SecretStringTemplate': json.dumps({'username': 'admin'}),
            'GenerateStringKey': 'password',
            'PasswordLength': 32,
            'ExcludeCharacters': '"@/\\',
            'RequireEachIncludedType': True
        },
        Tags=[
            {'Key': 'TenantId', 'Value': tenant_id},
            {'Key': 'Purpose', 'Value': 'Aurora-Credentials'}
        ]
    )
    
    # Retrieve the generated password
    secret_value = secretsmanager.get_secret_value(SecretId=secret_name)
    credentials = json.loads(secret_value['SecretString'])
    
    response = rds.create_db_cluster(
        DBClusterIdentifier=f'tenant-{tenant_id}-cluster',
        Engine='aurora-postgresql',
        EngineVersion='15.4',
        EngineMode='provisioned',  # Serverless v2 uses provisioned mode
        ServerlessV2ScalingConfiguration={
            'MinCapacity': 0.5,  # Minimum ACUs
            'MaxCapacity': 16,   # Maximum ACUs
        },
        MasterUsername=credentials['username'],
        MasterUserPassword=credentials['password'],
        DatabaseName='tenantdb',
        BackupRetentionPeriod=7,
        StorageEncrypted=True,  # Enable encryption at rest
        EnableCloudwatchLogsExports=['postgresql'],
        Tags=[
            {'Key': 'TenantId', 'Value': tenant_id},
            {'Key': 'Pattern', 'Value': 'serverless-v2'},
            {'Key': 'SecretArn', 'Value': secret_response['ARN']}
        ]
    )
    
    # Create serverless v2 instance
    rds.create_db_instance(
        DBInstanceIdentifier=f'tenant-{tenant_id}-instance',
        DBInstanceClass='db.serverless',
        Engine='aurora-postgresql',
        DBClusterIdentifier=f'tenant-{tenant_id}-cluster'
    )
    
    return {
        'cluster': response,
        'secret_arn': secret_response['ARN']
    }

Scaling Patterns and Best Practices

Workload-Based Scaling:

Monitor Aurora Serverless v2 capacity utilization over time to identify scaling patterns and optimize your ACU configuration. This analysis helps you right-size minimum and maximum ACU settings based on actual workload patterns, reducing costs while maintaining performance.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# Monitor and adjust scaling configuration
cloudwatch = boto3.client('cloudwatch')

def analyze_scaling_patterns(cluster_id, days=7):
    # Get ACU utilization metrics
    response = cloudwatch.get_metric_statistics(
        Namespace='AWS/RDS',
        MetricName='ServerlessDatabaseCapacity',
        Dimensions=[
            {'Name': 'DBClusterIdentifier', 'Value': cluster_id}
        ],
        StartTime=datetime.utcnow() - timedelta(days=days),
        EndTime=datetime.utcnow(),
        Period=3600,  # 1 hour
        Statistics=['Average', 'Maximum', 'Minimum']
    )
    
    datapoints = response['Datapoints']
    
    if not datapoints:
        return None
    
    avg_acu = sum(d['Average'] for d in datapoints) / len(datapoints)
    max_acu = max(d['Maximum'] for d in datapoints)
    min_acu = min(d['Minimum'] for d in datapoints)
    
    # Recommendations
    recommendations = []
    
    if max_acu < current_max * 0.7:
        recommendations.append(f"Consider reducing MaxCapacity to {int(max_acu * 1.2)}")
    
    if min_acu > current_min * 1.5:
        recommendations.append(f"Consider increasing MinCapacity to {int(min_acu * 0.8)}")
    
    return {
        'avg_acu': avg_acu,
        'max_acu': max_acu,
        'min_acu': min_acu,
        'recommendations': recommendations
    }

Conclusion

Choosing the right multi-tenant database pattern is a critical architectural decision that impacts your SaaS application's scalability, cost, security, and operational complexity. Here are the key takeaways:

Pattern Selection Guidelines

Choose Database-per-Tenant when:

Serving enterprise customers with strict compliance requirements
Data isolation is non-negotiable
Tenants have highly variable workloads
You have < 100 high-value tenants

Choose Schema-per-Tenant when:

You need balance between isolation and cost
Serving 10-500 tenants per database
Tenants need some customization
Compliance requires logical separation

Choose Shared Database when:

Optimizing for cost efficiency
Serving thousands of small tenants
Tenants have similar, predictable workloads
You need to scale to 10,000+ tenants

Best Practices Summary

Start Simple: Begin with shared database, evolve as needed
Plan for Growth: Design with scalability in mind from day one
Automate Everything: Tenant provisioning, backups, monitoring
Test Isolation: Implement comprehensive tenant isolation tests
Monitor Continuously: Track per-tenant metrics and costs
Secure by Default: Encryption, network isolation, audit logging
Document Decisions: Maintain clear documentation of your architecture
Plan for DR: Regular backup testing and disaster recovery drills

Did you find this guide helpful? Share your multi-tenant architecture experiences and questions in the comments below.

# databases # saas # startups

Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.

Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article

Builder Center

Multi-Tenant SaaS Database Design Patterns on AWS

John Adelana

Introduction

The Three Core Patterns

1. Database-per-Tenant (Silo Model)

2. Schema-per-Tenant (Bridge Model)

3. Shared Database with Tenant Identifier (Pool Model)

Pattern 1: Database-per-Tenant (Silo Model)

Overview

Architecture Characteristics

AWS Implementation Options

Advantages

Disadvantages

When to Use Database-per-Tenant

Real-World Example: Healthcare SaaS Platform

Pattern 2: Schema-per-Tenant (Bridge Model)

Overview

Architecture Characteristics

AWS Implementation Options

Advantages

Disadvantages

When to Use Schema-per-Tenant

Real-World Example: Project Management SaaS

Pattern 3: Shared Database with Tenant Identifier (Pool Model)

Overview

Architecture Characteristics

AWS Implementation Options

Advantages

Disadvantages

Critical Security Considerations

When to Use Shared Database with Tenant ID

Real-World Example: Marketing Automation Platform

Hybrid Approaches: Mixing Patterns

Tiered Architecture Example

Implementation Strategy

AWS Service Recommendations by Pattern

Database-per-Tenant

Schema-per-Tenant

Shared Database with Tenant ID

Sharding Strategies for Scale

Horizontal Sharding Approaches

Shard Management with AWS

Monitoring and Observability

Key Metrics to Track

Cost Optimization Strategies

Database-per-Tenant Cost Optimization

Schema-per-Tenant Cost Optimization

Shared Database Cost Optimization

Security Best Practices

Network Security

Encryption

Audit Logging

Decision Framework

Choosing Your Pattern: A Decision Tree

Evaluation Criteria Matrix

Common Pitfalls and How to Avoid Them

Pitfall 1: Inadequate Tenant Isolation Testing

Pitfall 2: Connection Pool Exhaustion

Pitfall 3: Noisy Neighbor Problems

Pitfall 4: Inefficient Schema Migrations

Pitfall 5: Inadequate Monitoring

Performance Optimization Techniques

Indexing Strategies

Query Optimization

Connection Pooling with RDS Proxy

Disaster Recovery and Business Continuity

Backup Strategies by Pattern

Disaster Recovery Architecture

RTO and RPO Targets

Compliance and Regulatory Considerations

Data Residency Requirements

GDPR Compliance

HIPAA Compliance

Future-Proofing Your Architecture

Planning for Scale

Evolution Path

Modernization Opportunities

NoSQL and DynamoDB Multi-Tenant Patterns

DynamoDB for Multi-Tenant Applications