Multi-tenant architecture with PostgreSQL in SaaS applications

Modern SaaS applications often need to serve multiple customers using the same infrastructure. This model is known as multi-tenant architecture - where different organizations (tenants) share the same system, but with their data fully isolated. In this article, I explain how to structure this pattern using PostgreSQL, focusing on simplicity, scalability, and security.

What is a multi-tenant architecture

In a multi-tenant application, multiple customers use the same application and database, but each customer has its own isolated data. The system must ensure that users from one company never gain access to another company's information. This model is widely used in SaaS products such as management platforms, CRMs, marketplaces, and enterprise systems.

Main multi-tenancy strategies

There are three common approaches to implementing multi-tenancy in databases:

Database per tenant: each customer has a separate database.
Schema per tenant: each customer has its own schema inside the same database.
Shared database with tenant_id: all data lives in the same database and tables, but each record has a tenant identifier.

In most modern SaaS products, the most common approach is the third one: shared database with tenant_id. It offers the best balance between scalability, operational simplicity, and infrastructure cost.

Database modeling

The basic principle is simple: almost all business-related tables should include a tenant_id column. This field identifies which organization that data belongs to.

CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  total NUMERIC NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

This pattern guarantees that all data is linked to a specific tenant. From that point on, every query must always filter by tenant_id.

SELECT * FROM orders
WHERE tenant_id = 'tenant-uuid';

Applying isolation in the backend

In the application backend, tenant_id is usually derived from the authenticated user. This means every query executed by the system should automatically apply this filter.

const orders = await db.orders.findMany({
  where: {
    tenantId: session.tenantId
  }
});

This pattern prevents users from accessing data from other organizations and keeps the system consistent.

Row Level Security in PostgreSQL

An additional security layer can be implemented using PostgreSQL's Row Level Security (RLS). With RLS, the database itself guarantees that only records from the correct tenant can be accessed.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy
ON orders
USING (tenant_id = current_setting('app.current_tenant')::uuid);

With this approach, even if a query is executed without the correct filter in the backend, the database still protects access to the data.

Scalability in multi-tenant systems

A tenant_id-based architecture also makes scaling easier. Common strategies include:

Indexing by tenant_id to improve query performance.
Table partitioning based on tenant_id.
Moving larger tenants to dedicated databases as the platform grows.
Using tenant-scoped caches to reduce database load.

Real-world applications in SaaS products

This pattern is used across many types of SaaS products, such as business management platforms, scheduling systems, marketplaces, and CRMs. In projects I have recently built, the multi-tenant architecture enabled support for multiple clients while maintaining data isolation and reducing operational costs.

Conclusion

Multi-tenant architectures are the foundation of many modern SaaS products. Using PostgreSQL and a consistent tenant_id-based design, it is possible to build scalable, secure systems that are relatively simple to maintain. When combined with solid backend practices and database-level security, this approach becomes a robust solution for multi-customer platforms.

Vinicius Aguiar