PostgreSQL Database Design for SaaS Applications

PostgreSQL is the database of choice for modern SaaS. Proper schema design impacts performance, security, and scalability.

Multi-Tenant Schema Design

-- Shared schema with tenant isolation
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  plan VARCHAR(50) DEFAULT 'free',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_projects_org ON projects(org_id);

Row-Level Security

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY org_isolation ON projects
  USING (org_id = current_setting('app.current_org_id')::UUID);

Connection Pooling with Prisma

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL + '?connection_limit=20&pool_timeout=10',
    },
  },
})

Migration Strategies

Use prisma migrate for schema changes. For zero-downtime deployments, use expand-and-contract pattern.

The Bottom Line

The Database Manager from BreafIO provides database management patterns. The SaaS Starter Kit uses PostgreSQL with Prisma. The API Boilerplate includes database integration. The Analytics Dashboard demonstrates complex queries, and the Admin Dashboard Pro uses row-level security for multi-tenancy.