PostgreSQL Multi-Tenant Database Design for Healthcare SaaS

PostgreSQL is the most capable open-source database in the world, and it is the reason OpenMyPro can serve 150K+ users on a single Supabase instance without breaking a sweat. Here is how we designed our multi-tenant database for healthcare, from schema structure to indexing strategy to HIPAA-compliant access patterns.

Schema Design Philosophy

Our database follows a domain-driven schema design. Instead of dumping everything into the public schema, we organize tables by business domain. The providers schema contains provider profiles, specialties, availability, and pricing. The patients schema holds patient profiles, preferences, and health conditions. The bookings schema manages appointments, payments, and reviews. The public schema contains shared reference data like location hierarchies and specialty categories.

This separation provides several benefits. First, it makes the codebase navigable — finding the table for provider availability means looking in the providers schema, not scanning through 50 tables in public. Second, it enables schema-level permissions as an additional security layer beyond RLS. Third, it makes database migrations less risky because changes are scoped to a specific domain.

Table Design Patterns

Every table follows consistent patterns. A UUID primary key generated by gen_random_uuid(). Created_at and updated_at timestamps with default NOW() and trigger-based auto-update. A soft-delete pattern using deleted_at timestamps instead of hard deletes — critical in healthcare where data retention requirements mean we cannot permanently delete records.

Foreign keys reference parent tables explicitly with ON DELETE RESTRICT to prevent cascading deletions of related healthcare records. We never use ON DELETE CASCADE for tables containing patient data. If a provider account is deactivated, their appointment records must be preserved for patient access and regulatory compliance.

Indexing Strategy

Our indexing strategy follows a simple rule: index every column that appears in a WHERE clause, JOIN condition, or ORDER BY expression. This includes foreign key columns (PostgreSQL does not auto-index these), status columns used for filtering, and timestamp columns used for sorting.

We use partial indexes heavily for performance. A common query is "show all confirmed upcoming appointments for this provider." Instead of scanning the entire bookings table and filtering, we have a partial index on bookings where status = 'confirmed' AND appointment_date > NOW(). This index is tiny compared to a full table index and makes the dashboard query sub-millisecond.

Composite indexes serve multi-column lookups. Provider search queries filter by specialty, location, and availability simultaneously. A composite index on (specialty_id, city_id, is_available) serves this query perfectly without requiring three separate index scans.

Query Optimization

We profile every database query using EXPLAIN ANALYZE before it reaches production. The rule is simple: every user-facing query must execute in under 50ms under production load. Queries that exceed this threshold get optimized through better indexes, query restructuring, or materialized views.

Materialized views serve our analytics dashboards. Provider revenue reports, patient booking trends, and marketplace health metrics are computed and cached in materialized views that refresh on a schedule. This prevents expensive aggregation queries from competing with real-time transactional queries.

Connection Management

Supabase's PgBouncer sits between our application and PostgreSQL, pooling database connections. Without pooling, each serverless function invocation would open a new database connection — and PostgreSQL has a hard limit of around 500 connections depending on configuration. PgBouncer maintains a pool of 20-50 connections and allocates them on demand, supporting thousands of concurrent serverless function invocations.

We use transaction mode pooling for most queries, which releases the connection back to the pool after each transaction completes. For operations that require session-level state (like our batch import process), we use session mode on a separate pooler endpoint.

HIPAA Data Patterns

Healthcare data requires specific patterns. Patient names, health conditions, and appointment notes are encrypted at the application level before storage. The database stores ciphertext that is meaningless without the encryption key managed by our application. Even database administrators cannot read patient health information directly from the database.

Audit logging captures every read and write to PHI-containing tables. A trigger on each table inserts a record into the audit_log table with the operation type, user ID, timestamp, table name, record ID, and old/new values for updates. This audit trail is append-only — no UPDATE or DELETE operations are allowed on the audit_log table — ensuring tamper-proof compliance records.

Scaling Lessons

PostgreSQL has handled our growth from 0 to 150K+ users without a single performance crisis. The key is proactive optimization: add indexes before you need them, use connection pooling from day one, and monitor query performance continuously. Supabase's built-in monitoring shows slow queries, table sizes, and index usage — everything needed to stay ahead of scaling challenges.