# Horizontal scaling Packstub Tenancy can load-balance new tenant databases across a pool of database servers. Each pool member is an ordinary named connection in `config/database.php` pointing at a different server; when a tenant signs up, the plugin picks a member using a placement strategy and persists the choice on the tenant. From that moment, provisioning, runtime connections, migrations, and deletion all happen on that tenant's own server — and adding capacity to your SaaS is one connection definition plus one line of config. This guide describes the pool under the default **dedicated** [database strategy](https://packstub.dev/docs/filament-tenancy/database-strategies), where members are database *servers* and each tenant's database is created on one of them. Under the **shared** strategy the same pool, strategies, and tooling place tenants across pre-provisioned shared *shard* databases instead — see [Sharded shared](https://packstub.dev/docs/filament-tenancy/database-strategies#sharded-shared-many-tenants-per-database-many-databases) for the differences. ## Why horizontal scaling Every one-server multi-database SaaS hits the same ceiling. Databases-per-tenant is a great isolation model, but a single database server has finite connections, finite IOPS, finite disk, and — on managed platforms — a hard cap on the number of databases it will comfortably host. When tenant 300 signs up, your only options on one server are a bigger machine (vertical scaling, with a ceiling of its own) or a migration project. The database pool removes that ceiling. It decides *placement*: each new tenant's database is created on one of several servers, chosen by strategy (fewest tenants by default). The pool never routes requests at runtime — stancl/tenancy already resolves every tenant's server from a single persisted attribute — so there is no proxy, no extra hop, and no shared point of failure between tenant databases. ``` new tenant signups | v +----------------------+ | DatabasePool | | (placement only) | +----+------------+----+ | | persists tenancy_db_connection per tenant | | v v +---------------+ +---------------+ +---------------+ | Server A | | Server B | ... | Server C | | tenant_pool_1 | | tenant_pool_2 | | tenant_pool_3 | | 100 DBs | | 100 DBs | | (just added: | +---------------+ +---------------+ | fills first) | +---------------+ ``` Need more capacity? Provision another server, add its connection to `config/database.php`, append the name to the pool, deploy. The default `least-tenants` strategy immediately favors the empty server until the pool evens out. ## How it works The entire mechanism rests on one attribute that stancl/tenancy already understands: the per-tenant `tenancy_db_connection`, stored in the tenant's `data` column. It names the *template connection* — the `config/database.php` entry whose host, port, and credentials describe the tenant's server. Everything that touches a tenant database resolves the server from it: - **Provisioning** — the queued `CreateDatabase` and `MigrateDatabase` jobs connect to the tenant's server to create and migrate its database. - **Runtime** — stancl's `DatabaseTenancyBootstrapper` builds the live `tenant` connection from the template, so every query inside tenant context hits the right server. - **Maintenance** — `tenants:migrate` and friends iterate tenants and follow each tenant's own connection. - **Deletion** — `DeleteDatabase` drops the database on the tenant's server, and only there. The pool's job is to *write* that attribute once. Assignment runs from the tenant model's `creating` hook, registered by the plugin's service provider — so every creation path is covered: the Filament onboarding page, `Tenant::create()` in Tinker or a seeder, your test suite, and stancl's pending-tenant pool alike. Because the hook runs before the `INSERT`, the connection is committed together with the tenant row and is already visible when the `TenantCreated` provisioning pipeline — dispatched only after the surrounding transaction commits — reaches a worker. Two kinds of tenants are deliberately skipped: - Tenants that already carry an explicit `tenancy_db_connection` — a pin always wins (see [Pin a tenant to a server](#pin-a-tenant-to-a-server)). - Tenants created with stancl's `tenancy_create_database => false` flag — under the dedicated strategy they have no database to place. (Under the [shared strategy](https://packstub.dev/docs/filament-tenancy/database-strategies) these are exactly the tenants the pool places — onto shard databases rather than servers.) Placement is **sticky**. The attribute is written once, at creation, and nothing in the plugin ever rewrites it: strategies only affect *future* tenants, failed provisioning retries re-run on the originally assigned server, and pins are never rebalanced. A tenant's data never silently moves. ## Set up a pool ### 1. Define one connection per server Each pool member is a normal Laravel connection pointing at a tenant database server: host, port, admin credentials that can `CREATE DATABASE`, and an **existing maintenance database** on that server (provisioning connects to it *before* the tenant's database exists). On PostgreSQL the built-in `postgres` database is the natural choice. A complete two-server PostgreSQL example for `config/database.php`: ```php 'connections' => [ // ... 'tenant_pool_1' => [ 'driver' => 'pgsql', 'host' => env('TENANT_POOL_1_HOST', '10.0.1.10'), 'port' => env('TENANT_POOL_1_PORT', 5432), // An existing database on this server. Provisioning connects to it // to run CREATE DATABASE — it never holds tenant data. 'database' => env('TENANT_POOL_1_DATABASE', 'postgres'), 'username' => env('TENANT_POOL_1_USERNAME', 'tenant_admin'), 'password' => env('TENANT_POOL_1_PASSWORD'), 'charset' => 'utf8', 'prefix' => '', 'search_path' => 'public', 'sslmode' => 'prefer', ], 'tenant_pool_2' => [ 'driver' => 'pgsql', 'host' => env('TENANT_POOL_2_HOST', '10.0.1.11'), 'port' => env('TENANT_POOL_2_PORT', 5432), 'database' => env('TENANT_POOL_2_DATABASE', 'postgres'), 'username' => env('TENANT_POOL_2_USERNAME', 'tenant_admin'), 'password' => env('TENANT_POOL_2_PASSWORD'), 'charset' => 'utf8', 'prefix' => '', 'search_path' => 'public', 'sslmode' => 'prefer', ], ], ``` ```dotenv TENANT_POOL_1_HOST=10.0.1.10 TENANT_POOL_1_USERNAME=tenant_admin TENANT_POOL_1_PASSWORD=secret TENANT_POOL_2_HOST=10.0.1.11 TENANT_POOL_2_USERNAME=tenant_admin TENANT_POOL_2_PASSWORD=secret ``` The MySQL variant: ```php 'tenant_pool_1' => [ 'driver' => 'mysql', 'host' => env('TENANT_POOL_1_HOST', '10.0.1.10'), 'port' => env('TENANT_POOL_1_PORT', 3306), // An existing schema the admin user can connect to before any // tenant database exists on this server. 'database' => env('TENANT_POOL_1_DATABASE', 'tenant_admin'), 'username' => env('TENANT_POOL_1_USERNAME', 'tenant_admin'), 'password' => env('TENANT_POOL_1_PASSWORD'), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, ], ``` Two rules for pool connections: - **No database URLs.** Template connections must use individual `host`/`port`/`database` fields — stancl's bootstrapper rejects URL-based configs because the URL silently overrides the per-tenant database name. Boot validation refuses URL-configured members. - **Valid database names for your driver.** Tenant database names are built from `tenancy.database.prefix` + tenant key + `tenancy.database.suffix`. Make sure the suffix suits your driver — a `.sqlite` suffix left over from local testing produces invalid PostgreSQL and MySQL database names. ### 2. Enable the pool In `config/packstub-tenancy.php`: ```php 'database_pool' => [ 'enabled' => true, 'connections' => ['tenant_pool_1', 'tenant_pool_2'], 'strategy' => 'least-tenants', 'weights' => [], ], ``` Or fluently on the plugin — the whole `database_pool` block has a fluent mirror: ```php use Packstub\Tenancy\TenancyPlugin; $panel->plugin( TenancyPlugin::make() ->databasePool(['tenant_pool_1', 'tenant_pool_2']) ); ``` With a strategy and weights: ```php TenancyPlugin::make() ->databasePool( ['tenant_pool_1', 'tenant_pool_2', 'tenant_pool_3'], strategy: 'weighted', weights: ['tenant_pool_3' => 2], ); ``` Passing an empty array disables the pool. Fluent overrides are written back into the `packstub-tenancy.database_pool` config when the panel boots, so the creation hook, the `tenants:pool` command, and the pool widget all observe the same pool the panel was configured with. ### 3. Let boot validation check your work A misconfigured pool member would otherwise fail *asynchronously*, inside the queued provisioning pipeline — a far worse place to learn about a typo. So the pool validates eagerly at boot and throws a descriptive `RuntimeException` if: - the pool is enabled but `connections` is empty; - a listed connection is not defined in `config/database.php`; - a member uses a name reserved by stancl tenancy (`tenant`, or the configured `tenant_host_connection` name); - a member is configured through a database URL; - a member's driver has no tenant database manager registered in `tenancy.database.managers`; - the strategy is not one of `least-tenants`, `round-robin`, `weighted`; - a weight references a connection that is not a pool member, or is not a positive number. For the reachability and privilege side — can each server actually be reached, and can its role actually `CREATE DATABASE` — run `tenants:pool --check` (see [Inspect the pool](#inspect-the-pool)). ## Placement strategies ### `least-tenants` (default) Picks the member currently hosting the fewest tenants. Counts come straight from the central `tenants` table (`data->tenancy_db_connection`), so the strategy needs no bookkeeping table and **self-heals**: deleting tenants lowers a member's count, and a freshly added server — with zero tenants — is favored for every new signup until the pool evens out. This is the strategy you want unless you have a specific reason not to. Two simultaneous signups may read the same counts and land on the same member. That is a benign, self-correcting skew of one tenant, which is why the pool takes no lock. ### `round-robin` Rotates through members in configured order. The cursor is *derived* from the total pooled-tenant count (`total % member count`) rather than stored, so rotation needs no extra state — but deleting tenants shifts the cursor, so distribution can drift after churn. Use `least-tenants` if exact evenness after deletions matters. ### `weighted` Picks the member with the lowest tenants-per-weight ratio, so a server with weight `2` converges on twice the tenants of a weight-`1` server. Members without an explicit weight count as weight `1`. Use it when your servers are not the same size: ```php 'database_pool' => [ 'enabled' => true, 'connections' => ['tenant_pool_1', 'tenant_pool_2', 'tenant_pool_3'], 'strategy' => 'weighted', // tenant_pool_3 is a bigger machine: it converges on 2x the tenants // of each weight-1 member. 'weights' => ['tenant_pool_3' => 2], ], ``` ## Add a server to a running pool This is the payoff: once the pool is running, scaling out is a provisioning task plus **one line of config**. ### 1. Provision the database server Stand up PostgreSQL or MySQL on a new machine (or a new managed instance), reachable from your web servers *and* your queue workers. Allow their IPs in the firewall / `pg_hba.conf`. ### 2. Create the admin role The role provisioning connects as must be able to create (and, for offboarding, drop) databases. PostgreSQL — the `CREATEDB` role attribute is exactly what `tenants:pool --check` probes for: ```sql CREATE ROLE tenant_admin WITH LOGIN CREATEDB PASSWORD 'choose-a-strong-password'; ``` MySQL — a global `CREATE` grant is what `tenants:pool --check` looks for; `DROP` covers offboarding, and a wildcard grant covers day-to-day queries inside every tenant database (adjust `tenant%` to your `tenancy.database.prefix`): ```sql CREATE USER 'tenant_admin'@'%' IDENTIFIED BY 'choose-a-strong-password'; GRANT CREATE, DROP ON *.* TO 'tenant_admin'@'%'; GRANT ALL PRIVILEGES ON `tenant%`.* TO 'tenant_admin'@'%'; ``` ### 3. Add the connection and enroll it Add `tenant_pool_3` to `config/database.php` (same shape as the existing members) and its env vars, then append one name to the pool: ```php 'connections' => ['tenant_pool_1', 'tenant_pool_2', 'tenant_pool_3'], ``` ### 4. Deploy — to web servers AND queue workers Provisioning runs on the queue. A worker that does not know the `tenant_pool_3` connection cannot create databases on it, so the new connection definition and env vars must reach **every host**: web servers and queue workers both. Deploy, rebuild the config cache if you use one, and restart workers so they load it: ```bash php artisan config:cache php artisan queue:restart ``` ### 5. Verify and watch it fill ```bash php artisan tenants:pool --check ``` With `least-tenants`, the next signup lands on the empty server — `Next placement: tenant_pool_3` — and keeps doing so until the pool balances. Watch it happen in the [`DatabasePoolOverview` widget](#inspect-the-pool) or by re-running `tenants:pool`. That is the whole runbook. No proxy reconfiguration, no rebalancing job, no downtime: existing tenants stay exactly where they are. ## Inspect the pool ### `tenants:pool` Shows how tenants are distributed across the pool and flags configuration problems: ```bash php artisan tenants:pool ``` ``` +--------------------+--------+---------+--------+-------+ | Connection | Driver | Tenants | Weight | Share | +--------------------+--------+---------+--------+-------+ | tenant_pool_1 | pgsql | 104 | 1 | 51% | | tenant_pool_2 | pgsql | 98 | 1 | 48% | | tenant_pool_3 | pgsql | 2 | 1 | 1% | | (template default) | — | 37 | — | — | +--------------------+--------+---------+--------+-------+ Strategy: least-tenants Next placement: tenant_pool_3 ``` The `(template default)` row appears when some tenants have no pool assignment — typically tenants created before the pool was enabled. They follow stancl's `template_tenant_connection` (or the central connection) and are not counted in pooled shares. If any tenant points at a connection that no longer exists in `config/database.php`, the command prints an error block and exits non-zero (see [Operations](#operations)). The command also reports stranded tenants when the pool is disabled, so it is safe in any setup. ### `tenants:pool --check` Connects to every pool member and verifies it can actually create tenant databases — reachability *and* privileges, per driver: ```bash php artisan tenants:pool --check ``` ``` ✓ tenant_pool_1 ......................... reachable, role can CREATE DATABASE ✓ tenant_pool_2 ......................... reachable, role can CREATE DATABASE ✗ tenant_pool_3 reachable, but the role lacks CREATEDB — provisioning will fail on this server ``` On PostgreSQL the probe checks the connected role for `CREATEDB` (or superuser); on MySQL/MariaDB it queries `information_schema.USER_PRIVILEGES` for a global `CREATE` grant (parsing `SHOW GRANTS` would false-positive on compound privileges like `CREATE VIEW`); on SQLite it verifies the tenant database directory is writable. A member that connects but lacks the privilege would otherwise fail asynchronously inside the queued pipeline — this check surfaces that before the first signup does. The exit code is non-zero on any failure, which makes the command a natural CI or cron health check. SQL Server has no privilege probe yet: `sqlsrv` members are reported with a yellow `?` as *unknown* rather than claimed healthy, and don't affect the exit code — verify the login's `CREATE DATABASE` (or `dbcreator`) permission manually. ### The `DatabasePoolOverview` widget A Filament stats widget with one stat per pool member: tenant count, driver and host, and a highlight on the member the next tenant will land on. Register it in your central admin panel: ```php use Packstub\Tenancy\Filament\Widgets\DatabasePoolOverview; $panel->widgets([ DatabasePoolOverview::class, ]); ``` The widget hides itself while the pool is disabled, so it is safe to register unconditionally. ## Pin a tenant to a server Some tenants must live on a specific server — a data-residency requirement, an enterprise contract, a dedicated machine. Create the tenant with an explicit `tenancy_db_connection` and the pool steps aside: ```php use Packstub\Tenancy\Models\Tenant; Tenant::create([ 'name' => 'Contoso GmbH', 'slug' => 'contoso', // Any connection defined in config/database.php — it does not // have to be a pool member. 'tenancy_db_connection' => 'tenant_eu_dedicated', ]); ``` Pins are permanent by design: the assignment hook skips any tenant that already carries a connection, and no strategy, scale-out, or retry ever rebalances an assigned tenant. The same applies in reverse to database-less tenants — under the dedicated strategy, anything created with stancl's `tenancy_create_database => false` flag is skipped entirely, since there is no database to place. ## Operations ### Queue workers must share the connection config Provisioning (`CreateDatabase` → `MigrateDatabase` → optional seeding → ready) runs on the queue, and runtime jobs that enter tenant context resolve servers the same way the web tier does. Every queue worker therefore needs the **same `database.connections` entries and env vars** as your web servers. Treat "add a pool member" as a config deploy to the whole fleet, always followed by `php artisan queue:restart`. ### If a pool member disappears Removing or renaming a connection in `config/database.php` **strands** every tenant whose persisted `tenancy_db_connection` points at it. Stranded tenants cannot boot — the bootstrapper would read a null connection config. `tenants:pool` flags them loudly and exits non-zero: ``` ERROR 2 tenant(s) point at connections missing from config/database.php — they cannot boot: acme -> tenant_pool_2 globex -> tenant_pool_2 WARN Restore the connection definition, or repoint the tenants at an existing pool member. ``` Recovery is usually trivial: **restore the connection definition under its original name**. The tenants' databases are untouched on the server — only the name lookup was broken. Repointing tenants at a different member is only correct after you have actually moved their data there (below). ### Moving a tenant between servers There is no automatic tenant mover — be aware of that going in. Moving a tenant is a manual dump/restore plus one attribute update: 1. Pick a maintenance window for that tenant (writes during the copy are lost). 2. Dump the tenant's database from its current server and restore it onto the target, for example: ```bash pg_dump --host 10.0.1.10 --username tenant_admin --dbname tenant_acme \ | psql --host 10.0.1.12 --username tenant_admin --dbname tenant_acme ``` (Create the empty database on the target first: `createdb --host 10.0.1.12 --username tenant_admin tenant_acme`.) 3. Update the persisted attribute: ```php $tenant = Tenant::where('slug', 'acme')->firstOrFail(); $tenant->setInternal('db_connection', 'tenant_pool_3'); $tenant->save(); ``` 4. Verify the tenant boots and its data is present, then drop the old database on the source server yourself — nothing does it for you. ### Backups Back up **every pool member** — each server holds real tenant data. Back up the central database too: it holds the tenant → server map (`tenancy_db_connection`), and a restore is only coherent when the map and the servers agree. Include new members in the backup rotation as part of the [add-a-server runbook](#add-a-server-to-a-running-pool). ### Monitoring - Run `tenants:pool --check` on a schedule and alert on a non-zero exit code — it catches unreachable members, revoked privileges, and stranded tenants in one shot. - Watch per-server fundamentals (disk, connections, IOPS) with your regular database monitoring; the pool spreads tenants but does not observe server load. - Keep the `DatabasePoolOverview` widget on your central admin dashboard for an at-a-glance distribution view. ## Testing your pool The package ships two levels of pool tests you can run and crib from: - **Real multi-server E2E** — `composer test:multi-server` boots **three independent PostgreSQL server instances** (separate data directories, ports, and postmasters, via `tests/bin/multi-server-pg.sh`), then runs `tests/E2E/MultiServerPostgresTest.php` against them: tenants distributing across two real servers, runtime connections hitting the right server with fully isolated data, a third server joining mid-test and receiving the next tenant, deletion dropping the database from the right server only, and `--check` confirming `CREATE DATABASE` privileges. Without the wrapper script the suite skips itself, so a plain `composer test` stays dependency-free. - **SQLite journey test** — `tests/Feature/HorizontalScalingE2ETest.php` runs the whole story on the driver every CI box has: signup → pool placement → async provisioning → isolated tenant database → add a server → recover a failed pooled tenant on its originally assigned member → offboard. The journey test is the template for your own suite: define throwaway pool connections and the `packstub-tenancy.database_pool` config in `setUp()`, create tenants through your real signup path, then assert on `$tenant->getInternal('db_connection')` and `app(DatabasePool::class)->distribution()`. ## FAQ **Can I mix database drivers in one pool?** Mechanically, yes — validation only requires each member's driver to have a tenant database manager registered in `tenancy.database.managers`, and every provisioning step follows the member's own driver. Practically, your tenant migrations then have to be portable across all drivers in the pool, so most teams standardize on one. **Do SQLite pools make sense?** For testing the pool's behavior, yes — the package's own journey test runs on SQLite, and `tenants:pool --check` probes the tenant database directory for writability. For actual scaling, no: all SQLite tenant databases are files under one local path, so SQLite members never map to separate servers. **Can I change the strategy later?** Yes, at any time. Strategy only affects *future* placements; existing assignments are never touched. Switching an uneven pool to `least-tenants` gradually rebalances it through new signups alone. **What happens to existing tenants when I enable the pool?** Nothing. Assignment happens only at creation, so pre-pool tenants keep a `null` connection and continue to follow stancl's `template_tenant_connection` (or the central connection). They appear as the `(template default)` row in `tenants:pool`. If you want them on pool members, move them explicitly (see [Moving a tenant between servers](#moving-a-tenant-between-servers)). **Does round-robin stay perfectly even?** Not after churn. The rotation cursor is derived from the total pooled-tenant count rather than stored, so deleting tenants shifts it and distribution can drift. `least-tenants` is the strategy that actively converges on evenness. **Can the central database share a server with a pool member?** Yes. Pool members are just connections; nothing stops `tenant_pool_1` from pointing at the same physical server that hosts your central database — a common starting topology before the first scale-out. Give the pool member its own connection entry (with the admin credentials) rather than reusing the central connection, and remember the names `tenant` and the configured `tenant_host_connection` are reserved by stancl and rejected at boot. **Two tenants signed up at the same instant and landed on the same server — is that a bug?** No. Placement reads live counts without locking, so simultaneous creations can briefly skew a member by one tenant. `least-tenants` corrects it on the very next signup. **Does provisioning retry respect the original placement?** Yes. Assignment happens at creation, before provisioning can fail, so `php artisan tenants:retry-provisioning {slug}` re-runs the pipeline on the tenant's originally assigned server — it never rebalances. Fix the server, then retry. ## Next steps - New to the package? Start with the [installation guide](https://packstub.dev/docs/filament-tenancy/installation). - Provisioning fails on a new member? `tenants:pool --check` first, then `tenants:retry-provisioning {slug}` once the server is healthy.