Multitenancy Patterns in Community PostgreSQL

Let’s explore 6 Postgres database design patterns for an efficient and scalable multi-tenant database for use with a web application. Each pattern will be demonstrated as runnable SQL via psql on a local instance using a narrated and interactive style. Additionally, the code will be available on GitHub so that attendees can run it themselves and provide feedback. Because we’re using community Postgres and no third-party extensions, the design patterns run anywhere Postgres runs, from cloud providers to self-hosted instances.

The patterns we’ll look at are: scaling a single large instance, using composite primary keys, logging tenant DML, tracking tenant resource consumption, adding row level security (RLS), and using partitioned tables for multitenancy use cases.

These patterns will help teams design their own Multi-tenant databases so they can maximize cost efficiency by consolidating to fewer Postgres instances. As query volume and data sizes grow, teams may wonder whether these patterns will scale. Recommendations on the limitations of each pattern will be presented as well as alternative solutions, so that attendees gain a broader context on managing growth.

Transcript

1. Multitenancy Patterns in Community PostgreSQL bit.ly/pg-mten Multitenancy Patterns - PGConf NYC 2025 Slide 1 of 30
2. Inspiration for this talk: Operating lots of Postgres instances A dozen PostgreSQL instances serving different user segments A mix of multi-customer and single-customer databases Configuration inconsistencies: users, grants, schema objects, tables, indexes Over-provisioned instances had excessive spending, under-provisioned instances had performance problems What if there was a better way? 🤔 Multitenancy Patterns - PGConf NYC 2025 Slide 2 of 30
3. Multitenancy Patterns - PGConf NYC 2025 Slide 3 of 30
4. Multitenancy Patterns - PGConf NYC 2025 Slide 4 of 30
5. Postgres Received a PostgreSQL 17 Contributor Coin (2024)1 Multitenancy Patterns - PGConf NYC 2025 Slide 5 of 30
6. Better Design: Multi-Tenant Database Let's explore benefits and limitations of 6 patterns in multi-tenant database design Multitenancy Patterns - PGConf NYC 2025 Slide 6 of 30
7. Benefits and drawbacks without Multi-Tenancy Compute isolation Strong data isolation Possibly less code or schema design changes needed Increased costs from overprovisioned instances Complexity from managing a fleet like administration, upgrades, monitoring Multitenancy Patterns - PGConf NYC 2025 Slide 7 of 30
8. Opportunities with Multi-tenant DB consolidation Cost savings, greater resource efficiency Avoiding or deferring "fleet management" challenges Can achieve (weaker) forms of tenant data uniqueness and isolation Can distribute some computation work but ultimately limited by single instance Multitenancy Patterns - PGConf NYC 2025 Slide 8 of 30
9. Multitenancy Challenges Lacking native tenant primitives, need to build in more pieces using database or application-level features Can't achieve full compute isolation Can be limited by shared Postgres subsystems (Autovacuum, buffer cache) Requires some upfront architecture planning or costly schema/data migrations Multitenancy Patterns - PGConf NYC 2025 Slide 9 of 30
10. 💪 1—Single Big DB 🔑 2— Composite Primary Keys 📄 3—Tenant Data Logs ⚡ 4—Tenant Query Logs 🔒 5—Row Level Security 🍕 6— Partitioned Tables Multitenancy Patterns - PGConf NYC 2025 Slide 10 of 30 Starting up Learning Optimizing
11. 💪 1—Single Big DB: E-commerce Multitenant design Quad single: database, schema, user, all pgconf , on single instance ⛁ suppliers (Our "tenant") ⛁ customers ⛁ orders (FK supplier_id , FK customer_id ) Multitenancy Patterns - PGConf NYC 2025 Slide 11 of 30 Starting up Learning Optimizing
12. ┌─────────────────────────────────────────────────────────────────┐ │ │ │ Server instance, Postgres 18, hardware resources (CPU, mem) │ │ User: "postgres" superuser, regular user "pgconf" │ │ │ │ ┌────────────────────────────────────────────────────┐ │ │ │ │ │ │ │ Database "pgconf" │ │ │ │ │ │ │ │ ┌───────────────────────────────────────┐ │ │ │ │ │ │ │ │ │ │ │ Schema "pgconf" │ │ │ │ │ │ │ │ │ │ │ │ ┌────────────────┐ ┌──────────────┐ │ │ │ │ │ │ │ pgconf. | | pgconf. │ │ │ │ | | │ │ customers │ │ orders │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ │ └────────────────┘ └──────────────┘ │ │ │ │ │ │ │ │ │ │ │ └───────────────────────────────────────┘ │ │ │ │ │ │ │ └────────────────────────────────────────────────────┘ │ │ │ └─────────────────────────────────────────────────────────────────┘ Multitenancy Patterns - PGConf NYC 2025 Slide 12 of 30
13. Primary Keys Decision Point ⛁ Goldilocks PK data type: bigint 8 bytes. integer 4 bytes too small. 16 bytes UUID too big. ⛁ The suppliers primary key id is the tenant identifier ⛁ Add supplier_id to all tables, a form of denormalization Multitenancy Patterns - PGConf NYC 2025 Slide 13 of 30
14. Tenant Data Identification Identify tenant data using supplier_id foreign key, no join needed Uniformity in scripts, use generated column on suppliers table to generate supplier_id column Multicolumn indexes with (supplier_id, id) to likely offer good performance as data grows Easier row data movement, copy tenant rows to other environments: staging, demo Multitenancy Patterns - PGConf NYC 2025 Slide 14 of 30
15. Demos github.com / andyatkinson / presentations / pgconf2025 / README.md Docker Postgres 18 instance sh create_db.sh DEMO #1 Multitenancy Patterns - PGConf NYC 2025 Slide 15 of 30
16. 1—Single Big DB: Vertically scale as long as possible Google Cloud PostgreSQL: 96 vCPUs, 624 GB Microsoft Azure: 96 vCores, 672 GB AWS RDS db.r8g.48xlarge, 192 vCPUs, 1536 GB (1.5 TB) memory (210K annually on-demand, 140K 1-year reservation per Vantage.sh ) 4 Multitenancy Patterns - PGConf NYC 2025 Slide 16 of 30 Starting up Learning Optimizing
17. 🔑 2—Primary Key Alternative: Composite Primary Keys Improves ability to isolate or relocate data Active Record ORM in Ruby on Rails supports composite primary keys Downside: Longer key definitions CONSTRAINT fk_customer FOREIGN KEY (supplier_id, id) REFERENCES customers (supplier_id, id) DEMO #2 Multitenancy Patterns - PGConf NYC 2025 Slide 17 of 30 Starting up Learning Optimizing
18. 🔑 Primary Key Alternative: UUID Version 7 Postgres 18 generates UUID Version 7 values with uuidv7() function UUIDs avoid primary key conflicts when generating from multiple instances. We can also achieve that with CPKs and discrete tenant placement. 5 Multitenancy Patterns - PGConf NYC 2025 Slide 18 of 30 Starting up Learning Optimizing
19. 📄 3—Tenant Data Logs Report on Inserts, Updates, and Deletes from suppliers Use cases: Usage based billing, vertical scale planning, future split outs ⛁ Table supplier_data_changes to capture these events Use triggers and trigger functions to capture changes and metadata Store data using JSON columns, use JSON_TABLE() (Postgres 17 blog post ) 8 DEMO #3 Multitenancy Patterns - PGConf NYC 2025 Slide 19 of 30 Starting up Learning Optimizing
20. ⚡ 4—Tenant-scoped Query Activity Due to pg_stat_statements query normalization, we lose our supplier_id ⛁ Let's fix that with a supplier_query_logs table DEMO #4 Multitenancy Patterns - PGConf NYC 2025 Slide 20 of 30 Starting up Learning Optimizing
21. 🔒 5—Row Level Security For Suppliers How do we add more security so suppliers see only their data? Let's use Row Level Security to achieve that DEMO #5 Multitenancy Patterns - PGConf NYC 2025 Slide 21 of 30 Starting up Learning Optimizing
22. 🍕 6—Partitioned Tables ⛁ As the orders table grows large, it's more difficult to modify and performance worsens Let's use declaractive table partitioning to slice it up, maintaining good performance and making modifications easier Our partitioned table uses a CPK ( supplier_id , id ) 7 Multitenancy Patterns - PGConf NYC 2025 Slide 22 of 30 Starting up Learning Optimizing
23. 6—Partitioning and Multi-Tenancy ⛁ Let's create a orders_partitioned table, using LIST partitioning, with a partition per supplier Imagine we have < 1000 suppliers Partitioned tables can be "detached" ( DETACH CONCURRENTLY ), a less resource intensive alternative to deleting unneeded rows When suppliers leave, we'll detach their partition, archive its data, then drop it DEMO #6 Multitenancy Patterns - PGConf NYC 2025 Slide 23 of 30 Starting up Learning Optimizing
24. Warning #1: RLS Performance Familiarize yourself with RLS latency Dian Fay: Row level security pitfalls. Compare your query execution plans without policies (and their functions) to understand how much overhead is added. 2 Multitenancy Patterns - PGConf NYC 2025 Slide 24 of 30 Starting up Learning Optimizing
25. Warning #2: Trigger overhead performance Triggers are scalable to a point What is that point? Good up to 50K inserts/second? Trigger functions are one type of commit latency. Could mitigate with a partitioned table, minimal indexes and constraints Otherwise move to async approach e.g. logical replication, CDC etc. (beyond this scope) Multitenancy Patterns - PGConf NYC 2025 Slide 25 of 30 Starting up Learning Optimizing
26. Warnings #3: Partitioning challenges Requires a big row data migration vs. in-place change if starting from an unpartitioned table LIST partitioning may not work with thousands of tenants (See: 5.12.6. Best Practices for Declarative Partitioning ) May exceed single instance limits and move to a sharded DB solution (See: SaaS on Rails on PostgreSQL , App-level sharding, Citus) 6 3 Multitenancy Patterns - PGConf NYC 2025 Slide 26 of 30 Starting up Learning Optimizing
27. 📣 CFP Open Oct. 20 Multitenancy Patterns - PGConf NYC 2025 Slide 27 of 30
28. Let's Recap Using open source Postgres, without managing a fleet of instances, we can: Avoid inconsistencies in release versions, users, permissions, schemas Store data from many tenants maintaining uniqueness and portability Report on tenant read and write activity Use row level security to limit access Split up some types of work using tenant-specific partitions Multitenancy Patterns - PGConf NYC 2025 Slide 28 of 30
29. Thank you! github.com/andyatkinson/presentations High Performance PostgreSQL for Rails @andyatkinson.com Multitenancy Patterns - PGConf NYC 2025 Slide 29 of 30
30. 1. wiki.postgresql.org/wiki/Contributor_Gifts 2. di.nmfay.com/rls-performance 3. andyatkinson.com/blog/2024/07/13/SaaS-on-Rails-on- PostgreSQL-POSETTE-2024-andrew-atkinson 4. instances.vantage.sh/aws/rds/db.r8g.48xlarge? currency=USD 5. thenile.dev/blog/uuidv7 6. postgresql.org/docs/current/ddl-partitioning.html#DDL- PARTITIONING-DECLARATIVE-BEST-PRACTICES 7. postgresql.org/docs/current/ddl-partitioning.html 8. andyatkinson.com/postgresql-17-json-table-merge- returning-updatable-views Multitenancy Patterns - PGConf NYC 2025 Slide 30 of 30