PGCon2017 - 20180510

PGCon 2017
The PostgreSQL Conference

Lukas Fittl
Ozgun Erdogan
Day Talks - Day 1 - 2017-05-25
Room DMS 1110
Start time 11:00
Duration 00:45
ID 1030
Event type Lecture
Track Scaling Out
Language used for presentation English

Designing your Multi-tenant Database for Scale with Postgres

As SaaS applications become more commonplace, there's a growing demand for scaling databases that power these applications. In this talk, we'll review three design patterns that are commonly used for scaling multi-tenant databases and each approach's trade-offs.

We'll then focus on one design pattern that optimizes on the scaling dimension, give an example architecture from the industry, and describe key properties of PostgreSQL that make suitable as a multi-tenant database.

If you’re building a multi-tenant application, you probably already have the notion of tenancy built in your data model. Typically, most information relates to tenants / customers / accounts and your database tables capture this natural relation. With smaller amounts of data, it’s easy to throw more hardware at the problem and scale up your database. As these tables grow however, you need to think about ways to scale your multi-tenant database across dozens or hundreds of machines.

In this talk, we're first going to talk about motivations behind scaling your SaaS (multi-tenant) database and several heuristics we found helpful on deciding when to scale out. We'll then describe three design patterns that are common in scaling SaaS databases: (1) Create one database per tenant, (2) Create one schema per tenant, and (3) Have all tenants share the same table(s).

Next, we'll highlight the tradeoffs involved with each design pattern and focus on one pattern that scales to hundreds of thousands of tenants. We'll also share an example architecture from the industry that describes this pattern in more detail.

Last, we'll talk about key PostgreSQL properties, such as semi-structured data types, that make building multi-tenant applications easy. We'll also mention Citus as a method to scale out your multi-tenant database. We'll conclude by answering frequently asked questions on multi-tenant databases and Q&A.