Database-level multitenancy

As explained in Just Use Postgres!

Database-level multitenancy gives each tenant a separate database within a database infrastructure.

Database-level multitenancy gives every tenant a dedicated database object. A brewery and a coffee chain can share one PostgreSQL instance while keeping their business data isolated. section 2.1.1

PostgreSQL multitenancy can be organized at several levels:

• Table-level: share tables and distinguish records with a tenant_id column. section 2.1.1
• Schema-level: give each tenant a dedicated schema inside one logical database. section 2.1.1
• Database-level: give each tenant its own database, with access controlled through roles and permissions. section 2.1.1

Create isolated tenant databases

Connect to PostgreSQL with psql, create one database per merchant, and verify the databases: `docker exec -it postgres psql -U postgres`; `CREATE DATABASE coffee_chain; CREATE DATABASE brewery;`; `SELECT datname FROM pg_database;`. Connect to a tenant database with `\\c coffee_chain`. section 2.1.1

Figure 2.1 A Postgres instance with two databases for different tenants

Visualizes one PostgreSQL instance hosting separate databases for two tenants. section 2.1.1

How Just Use Postgres! covers it

How deep it goes

Defines three multitenancy levels and demonstrates database creation and connection, while deferring broader administration details.

Examples

Uses brewery and coffee-chain tenants, Docker-hosted PostgreSQL, CREATE DATABASE, pg_database, and psql database switching.

What it assumes

Assumes basic PostgreSQL and Docker access, while introducing table-, schema-, and database-level multitenancy.

What you can do after

Build it from scratch by creating tenant databases in PostgreSQL and connecting to each database with psql.

How it connects

Connects tenant isolation with PostgreSQL databases, roles, permissions, schemas, tables, and distributed server capacity.

liveBookmanning.com

Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

• multitenancykind of · Multitenancy is an architecture in which multiple independent tenants share an application or underlying infrastructure while their data, resources, or access remain logically separated.
• schema-level multitenancyalternative to · Schema-level multitenancy gives each tenant a dedicated schema containing its own tables within one logical database.
• table-level multitenancyalternative to · Table-level multitenancy is a database design in which multiple tenants’ records share tables and are distinguished by tenant-specific columns and query constraints.

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.