Multi-Tenant Architecture: A Database or Schema Per Tenant? A DB-Native Approach to Migration Management

When designing a multi-tenant architecture, your choice of strategy should reflect factors like tenant isolation, scalability, operational complexity, security, and cost. Here's a quick (and admittedly oversimplified) mapping of tradeoffs:

• Highest isolation & control → One database per tenant

• Balance of isolation & manageability → One schema per tenant

• Simplicity & scalability → Shared schema with partitioned tables

If you decide that a separate database or schema per tenant is the right fit, you’ll need a way to manage the operational overhead of maintaining and migrating objects across many databases.

While tools like Flyway and Liquibase exist and are widely used, I approached this from a database-native perspective. I wanted more control — particularly around how tables are created (including partitioning support) and how objects like stored procedures are handled.

So I built a proof of concept: a database schema management solution that runs and is administered entirely within the database itself. It’s still a work in progress and currently uses PostgreSQL’s dblink extension, with authentication handled in stored procedures (which will need to be reworked for production use). But even in its current state, there’s quite a lot working already.

Enjoy the deep dive!

Script Overview

The following scripts (link to GitHub below) demonstrate a proof of concept for managing a multi-tenant PostgreSQL environment where each tenant has their own database or their own schema. Please read the comments within the scripts for additional guidance — especially around database connection changes.

Script 1: Initialize Admin Schema

This script should be run while connected to the default PostgreSQL database (commonly called postgres).

• Creates a schema called admmgt, which acts as the control plane for managing tenant databases or schemas.

• Creates all required objects (tables, stored procedures, etc.) within the admmgt schema.

Script 2: Demonstration

There are two demo scripts. Use the version that maps the approach you would like to try (Database or Schema per tenant). This script simulates a basic use case of the management framework:

Link to scripts: https://github.com/steveoliai/DB_Admin