Building a Multi-Tenant API: Shared-Schema PostgreSQL

I’m currently building a SaaS platform for community builders, and early in my project ideation and research phase I hit one of the classic backend questions:

On one hand, I wanted a system with enough resilience to handle bursty, event-driven traffic without burning money on unnecessary infrastructure. On the other hand, I wanted it simple enough that I would not lose weeks managing complex database clusters before the product needed them.

This article walks through the constraints, load model, tenancy options, and the reasoning that pushed me toward a shared-schema PostgreSQL architecture.

App Constraints

• Scale target: under 1,000 tenants, with average community sizes around 500 members, or roughly 500k total users across the platform.
• Traffic pattern: extremely bursty. Baseline steady-state traffic is low, but push notifications for event RSVPs and live polls create sharp, localized spikes.

Modeling Burst Load

To stress test the backend, I modeled a worst-case surge around an event RSVP launch:

• Peak spike: 450 simultaneous users, assuming 90% active concurrency during a notification drop for a single community.
• Throughput: about 90 requests per second to the RSVP endpoint during a tight 5-second window.

Under the hood, my FastAPI backend interfaces with Nettu Scheduler, an open-source scheduling service.

Why wrap an external scheduling API with my own FastAPI layer? Developer velocity. Nettu is written in Rust, and I did not want to spend time learning Rust internals right now just to customize scheduler endpoints.

Each RSVP request follows this path:

1. Client
2. FastAPI Wrapper
3. Nettu Scheduler
4. Postgres DB

This execution path requires:

• 2 internal network hops
• 1 database read to check seat or slot availability
• 1 database write to commit the RSVP reservation

At this volume, connection starvation is the primary bottleneck rather than raw database disk throughput. To prevent FastAPI async workers from exhausting open database connections during a drop, PgBouncer sits in front of Postgres to pool and multiplex worker connections down to a fixed pool.

Why Shared-Schema PostgreSQL?

I evaluated three standard multi-tenancy models: DB-per-tenant, schema-per-tenant, and shared schema. I chose shared schema on PostgreSQL for three primary reasons.

Engine-Level Row-Level Security

One of the biggest risks with a shared table is accidental data leakage. This can happen when a query forgets to include a tenant filter, such as WHERE tenant_id = x.

Postgres solves this natively at the engine level with Row-Level Security. By setting a session variable on connection checkout, such as SET LOCAL app.current_tenant = 'tenant_123', Postgres can enforce tenant boundaries on every query automatically, even if the application layer forgets the tenant clause.

Schema Flexibility via JSONB

Different tenants need custom user metadata fields. Instead of building a complex EAV schema, PostgreSQL’s JSONB column support allows tenants to store arbitrary metadata.

Using JSONB with a Generalized Inverted Index keeps lookups fast because searching for a key can use index traversal instead of scanning every record.

Simple Usage Analytics

Keeping tenants in shared tables means I can monitor tenant usage metrics directly with simple SQL queries. That avoids the immediate overhead of setting up ETL pipelines to Snowflake or BigQuery during early-stage growth.

Real-World Trade-offs and Mitigations

No architecture is without trade-offs. Here is how I plan to handle the downsides of a shared schema:

Conclusion

Shared PostgreSQL tables will be this project’s initial backend implementation because they fit the current product shape:

• Native support for secure multi-tenancy via Row-Level Security
• Flexible tenant-specific metadata through JSONB
• Simple tenant usage analytics without an early analytics warehouse
• Lower operational overhead while the platform is still validating demand