Community Open Hours: Multi-tenant Apps With Distributed PostgreSQL

Summary: Multi-tenant apps with distributed PostgreSQL (Yugabyte / Postgres)

What the session is about

• The hosts discuss how to build multi-tenant SaaS applications with distributed PostgreSQL-compatible systems (specifically Yugabyte / “distributed SQL with Postgres-like semantics”).
• The guiding theme is to balance cost/consolidation with data isolation for security and performance.
• They outline common multi-tenancy architectures, then demonstrate:
  1. Single database, shared table approach
  2. Dedicated schema per tenant
  3. Dedicated database per tenant
  4. Distributed placement (distributed Yugabyte) for region isolation and scalability

Early framing: why multi-tenancy matters

• Multi-tenancy is increasingly common because cloud/SaaS providers must serve many customers without provisioning separate infrastructure per customer.
• Example: a Shopify-like SaaS platform where thousands of merchants are tenants, requiring tenant data isolation while sharing the platform efficiently.
• Key constraints:
  • Security isolation: tenants must not see or modify other tenants’ data
  • Performance isolation: avoid “noisy neighbor” effects
  • Latency & regional requirements: e.g., users in Europe connect from Europe
  • Compliance (GDPR): store transactions closer to required geographies

---

“News” segment: open source vs business model

• The discussion touches on distributed SQL vendors shifting licensing models (e.g., mention of CockroachDB shifting licenses).
• Emphasis:
  • Open source can still be a viable long-term business model when designed intentionally.
  • Yugabyte is presented as fully open source for the core engine, with enterprise products for premium features (e.g., security / replication / multicluster).
• Upcoming Yugabyte updates were teased for September, along with summits/events.

---

Typical multi-tenant solutions for Postgres-compatible DBs

1) Shared table (single table, tenant_id column)

• One database/server instance.
• Tenants share:
  • the same schema
  • the same table structure
  • rows distinguished via a tenant identifier (e.g., tenant_id)
• Pros:
  • simplest to implement
  • easier schema-level upgrades “in bulk”
  • supports partitioning strategies (including “parent table” logic with partitions)
• Cons / risks:
  • application migrations can be harder if tenants can’t all upgrade together
  • risk of noisy tenant performance impacting others
  • increased operational complexity if relying heavily on large partitions/indexes on a massive shared table

2) Dedicated schema per tenant

• One database instance, but each tenant gets its own schema.
• Example pattern:
  • each tenant’s tables live in their schema
  • the app sets search_path to route queries
• Pros:
  • better structural isolation than shared tables
  • permissions can be easier (roles tied to schema)
  • safer online/rolling upgrades (tenant-specific schema changes)
• Cons / tradeoffs:
  • physical backup at the DB level still backs up the whole database
  • cross-tenant queries (joins across tenants) are harder and not typical

3) Dedicated database per tenant

• Multiple logical databases under a single Postgres cluster/instance.
• Each tenant gets:
  • its own database object
  • roles mapped to that database’s permissions
• Pros:
  • stronger data isolation
  • clearer security boundaries (tenant owner role restricted to its own DB)
  • easier “rolling upgrade” per tenant (migrate one tenant DB at a time)
  • cleaner conceptual per-tenant backup/restore strategy (still cluster-managed)
• Cons / tradeoffs:
  • application can’t easily query both tenants’ tables in one session without multiple connections
  • increased connection-string/credential management (one DB per tenant)

Demoed with psql commands:

• Create databases for “tenant A” and “tenant B”
• Create tenant-specific roles
• Grant ownership/privileges so each role can connect only to its tenant database
• Demonstrate that using the wrong role/password cannot access the other tenant’s relations

---

Distributed Yugabyte demo: tenant placement across regions/nodes

Key mechanism: table spaces + placement by region

• When tenants grow and require stronger scalability/availability, the architecture moves to distributed placement.
• Yugabyte uses table spaces with replica placement rules mapped to regions (e.g., “region-one”, “region-three”).
• They assign:
  • tenant A’s default table space → region placement rule
  • tenant B’s default table space → different region placement rule
• Replication factor is set to keep tenants resilient to failures within a region (e.g., RF=3 across nodes in that region).

Performance/security verification

• They demonstrate that:
  • tenants’ tables and tablets (distributed storage units) are placed only in the assigned region
  • raft leaders/followers reside on servers in that region
• They also cover operational considerations:
  • application connections must ensure they land in the “right region”
  • “smart drivers” can route connections automatically for region consistency
  • they inspect physical storage impact (e.g., capturing/grabbing on-disk data for validation when encryption isn’t enabled)

Avoiding cross-region traces

• They intentionally discuss that statements/logs may include data, so they avoid connecting from other regions to prevent traces/memory artifacts/log statements appearing where they shouldn’t.

---

Extending placement to partitioning scenarios

• They show that the same table space mapping concept can be used with partitioning:
  • within a shared logical table/tenant-id partitioning scheme, partitions for specific tenant sets can be mapped to different region/table spaces.

---

Overall comparison: distributed vs sharding-style approaches

• The hosts contrast distributed Yugabyte-style architecture with sharding strategies:
  • With sharding/monolithic DBs, you may need more infrastructure per shard (replicas/standbys), and reshaping/shifting tenants between regions can be complex.
  • With distributed SQL, you can isolate tenants by placement rules (table spaces) and often keep operations more transparent.
• They highlight that Yugabyte supports:
  • isolation + scalability across nodes/regions
  • “global database” querying patterns when appropriate (without always requiring cross-database joins)

---

Main speakers / sources

• Frank (primary presenter for architecture discussion and demos)
• KIC (mentioned as providing perspective on open source business model/licensing; “KIC’s view / his take”)