Building a Software-as-a-Service (SaaS) platform introduces a core architectural question: How do you isolate client data? In PostgreSQL, developers typically choose between three partitioning models: Database-per-tenant, Schema-per-tenant, or Shared-Database with Tenant ID rows. This article details the trade-offs of each approach and provides indexing guidelines for high-concurrency Node.js Express APIs.
1. The Isolation Models
- Shared Database, Shared Schema: Every table contains a
tenant_idcolumn. It is the cheapest model to run but places the burden of security on your SQL query WHERE clauses. - Schema-per-tenant: Each tenant has their own isolated namespace within a single database. This strikes a balance between separation and server cost.
- Database-per-tenant: Total physical isolation. Highly secure, but makes migrations and cross-tenant analytics complex to coordinate.
2. Optimizing Queries in Shared Schemas
If you choose the shared database model (recommended for resource efficiency), you must index the tenant_id field alongside query parameters. A composite index is essential:
CREATE INDEX idx_users_tenant_email ON users(tenant_id, email);By defining the tenant_id as the leading column, PostgreSQL can filter out all other tenant records in a single B-Tree scan, ensuring that query times do not degrade as you sign up more accounts.
3. Express API Connection Pooling
With high numbers of concurrent tenants, connection limits become a bottleneck. In Node.js, always utilize the pg pool client with strict limits rather than spawning new clients on every API request:
const pool = new Pool({ max: 20, idleTimeoutMillis: 30000 });Pair this pool with an external proxy like PgBouncer in transaction pooling mode to scale client thresholds past thousands of active queries without hitting database memory exhaustion walls.