Multi-tenancy is a crucial concept in modern database design, especially for applications that serve multiple clients or organizations—known as tenants—from a single platform. In a multi-tenant system, you design your database so that each tenant's data is kept separate, secure, and efficiently accessible. There are three common approaches to implementing multi-tenancy: shared, isolated, and hybrid.

With the shared approach, all tenants' data resides in the same set of tables, but every row is tagged with a tenant_id or similar identifier. This method is space-efficient and simplifies schema updates, but it requires careful attention to data isolation and security. The isolated approach gives each tenant their own set of tables or even a separate database, which maximizes isolation and security but can make maintenance and scaling more challenging. The hybrid model combines elements of both, perhaps sharing some tables while isolating others, to balance flexibility, security, and operational complexity.

-- Shared multi-tenant orders table schema
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    tenant_id INT NOT NULL, -- identifies the tenant
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20),
    extra_info JSONB
);

-- Index to optimize queries by tenant
CREATE INDEX idx_orders_tenant_id ON orders(tenant_id);

When designing for multi-tenancy, you must consider three critical factors: data isolation, security, and query optimization. Data isolation ensures that one tenant cannot access another's data, even though the data may be stored together in shared tables. This is typically enforced using a tenant_id column and strict query filtering. Security is paramount—every query and application function must respect tenant boundaries, and you may use database roles or application-level checks to enforce this. Query optimization is also important because filtering by tenant_id can impact performance as data grows; indexing the tenant_id column is a common strategy to keep queries fast and efficient.

In a shared multi-tenant database, queries must always include the tenant identifier to ensure only the correct tenant's data is accessed. This pattern also applies to updates and deletes, not just selects. Application logic and database constraints must work together to prevent accidental data leaks between tenants.

-- Select all orders for a specific tenant (tenant_id = 42)
SELECT *
FROM orders
WHERE tenant_id = 42;

-- Insert a new order for a tenant (tenant_id = 42)
INSERT INTO orders (tenant_id, customer_id, product_id, quantity, order_date, status, extra_info)
VALUES (42, 5, 2, 3, '2024-06-01', 'pending', '{"gift_wrap": false}');

-- Update an order, ensuring only the correct tenant's data is affected
UPDATE orders
SET status = 'shipped'
WHERE order_id = 100 AND tenant_id = 42;

-- Delete an order belonging to a specific tenant
DELETE FROM orders
WHERE order_id = 101 AND tenant_id = 42;