Database Design for SaaS

Introduction

A poorly designed database is the most common cause of SaaS application slowdowns. In relational models, indexing mistakes, bad relationships, and lack of scaling plans cause APIs to slow down as customer data grows.

Background

SaaS databases must support two conflicting requirements: fast transactional updates (OLTP) and fast analytical reports (OLAP). Relational databases like PostgreSQL are excellent for transactional workloads, but they require careful schema design to scale efficiently as tenant numbers grow.

Implementation

We designed our relational schema using composite indexes to speed up queries across tenants.

Challenges

As the table sizes grew beyond 100 million rows, performance issues surfaced:

• Table Bloat: Frequently updated tables (like sessions or logs) caused autovacuum queues to freeze database operations.
• Slow Joins: Joining large tenant tables caused nested-loop queries that took seconds to execute.

Solutions

We solved this by establishing database best practices:

1. Table Partitioning: Dividing tables into logical blocks based on the tenant_id to speed up scans.
2. Read Replicas: Routing write traffic to the primary database and analytical read queries to read replicas.

Results

Implementing table partitioning and composite indexing reduced query times on large tenant tables from 850ms to 4ms, while read replicas offloaded 70% of database load from the primary instance.

Conclusion

Scaling a relational database requires structure and discipline. By partitioning tables early, using composite indexing on tenant keys, and isolating read traffic, you can scale Postgres to handle hundreds of millions of records.