Multi-Tenant Database Architecture for Rental Platforms

Building a multi-tenant SaaS platform requires careful database design. For rental management systems handling thousands of transactions daily, isolation, performance, and scalability are critical. Here's our approach.

The Multi-Tenancy Challenge

When building infrastructure for multiple rental businesses, each client (tenant) needs:

• Data isolation: Client A cannot access Client B's inventory
• Performance isolation: One tenant's heavy queries don't slow down others
• Schema flexibility: Custom fields per tenant without breaking the schema
• Cost efficiency: Shared infrastructure to keep costs low

Our Architecture: Hybrid Approach

1. Shared Schema with Tenant ID (Primary Data)

For most data, we use a shared PostgreSQL database with tenant_id in every table:

CREATE TABLE inventory (
    id UUID PRIMARY KEY,
    tenant_id UUID NOT NULL,
    item_name VARCHAR(255),
    status VARCHAR(50),
    created_at TIMESTAMP,
    CONSTRAINT fk_tenant FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
);

CREATE INDEX idx_inventory_tenant ON inventory(tenant_id);
CREATE INDEX idx_inventory_status ON inventory(tenant_id, status);

Pros: Cost-effective, easy to maintain, simple backups
Cons: Requires strict query discipline (every query MUST filter by tenant_id)

2. Separate Databases (High-Value Tenants)

For enterprise clients with >1M transactions/month, we provision dedicated RDS instances:

• Complete data isolation
• Custom performance tuning per client
• Dedicated read replicas
• Independent scaling

3. DynamoDB for Real-Time Data

Real-time inventory status and session data uses DynamoDB with composite keys:

Partition Key: tenant_id#item_id
Sort Key: timestamp
Attributes: status, location, reserved_by, available_until

This gives us instant availability checks without hitting PostgreSQL.

Query Patterns & Performance

Availability Check (Most Common Query)

-- Bad: Full table scan
SELECT * FROM inventory WHERE status = 'available';

-- Good: Tenant-scoped with index
SELECT * FROM inventory
WHERE tenant_id = 'abc-123'
  AND status = 'available'
  AND location_id = 'warehouse-1';

-- Response time: 12ms (with index)

Cross-Tenant Analytics (Admin Only)

For platform-wide metrics, we use read replicas and materialized views:

CREATE MATERIALIZED VIEW tenant_metrics AS
SELECT
    tenant_id,
    COUNT(*) as total_items,
    SUM(CASE WHEN status = 'rented' THEN 1 ELSE 0 END) as active_rentals,
    AVG(daily_revenue) as avg_revenue
FROM inventory
GROUP BY tenant_id;

REFRESH MATERIALIZED VIEW CONCURRENTLY tenant_metrics;

Security & Row-Level Security

PostgreSQL's Row-Level Security (RLS) provides defense-in-depth:

ALTER TABLE inventory ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON inventory
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

-- Application sets tenant context:
SET LOCAL app.tenant_id = 'abc-123';

This prevents accidental data leaks even if application code misses a filter.

Scaling Considerations

Current Stats (as of Nov 2025)

• Active Tenants: 47
• Total Records: 2.8M inventory items
• Daily Queries: ~400K
• Database Size: 145GB
• Average Query Time: 18ms

When to Shard

We're planning to implement sharding when we hit:

• 100+ active tenants
• 500GB+ database size
• or 1M+ queries/day

Sharding strategy will be by tenant_id hash to ensure even distribution.

Backup & Disaster Recovery

• Automated Backups: RDS snapshots every 6 hours, retained 30 days
• Point-in-Time Recovery: 5-minute granularity
• Cross-Region Replication: us-west-2 as failover
• Tenant-Specific Backups: Daily export to S3 per enterprise tenant

Lessons Learned

1. Always filter by tenant_id: We built a custom PostgREST middleware that automatically injects tenant_id filters. Prevents 99% of potential data leaks.

2. Indexes matter more in multi-tenant: With millions of records across tenants, proper composite indexes (tenant_id first) are critical for performance.

3. Monitor per-tenant resource usage: We track query count, storage, and CPU time per tenant. This helps identify abusive patterns and inform pricing.

4. DynamoDB + PostgreSQL is powerful: Using DynamoDB for hot data (inventory status) and PostgreSQL for transactional data gives us the best of both worlds.

Next Steps

We're currently evaluating:

• Aurora Serverless v2 for better cost/performance ratio
• Vitess for transparent sharding
• Tenant-aware connection pooling with PgBouncer

Building multi-tenant rental infrastructure? Talk to our team about leveraging our platform.