Level: Intermediate
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.