Scaling the Database

Most applications never outgrow a single database server, and the ones that do rarely need every tool at once. This page covers the three stages in the order you should reach for them, because each is meaningfully more expensive to operate than the last:

1. Connection pooling - stop paying for connection setup on every request. Almost always worth turning on.
2. Read replicas - take read load off the primary. Buys a lot of headroom, at the cost of reads that can be slightly stale.
3. Sharding with Vitess - split one logical database across many servers. Only when one primary genuinely cannot hold the write volume or the data.

If you are not sure which you need, you almost certainly want the first one and not yet the third.

Connection Pooling

Opening a database connection is expensive: a TCP handshake, a TLS handshake, and an authentication round trip before a single query runs. A pool keeps connections open and hands them out, so that cost is paid once instead of per request.

Configure it per connection in config/database.ts:

// config/database.ts
export default {
  default: env.DB_CONNECTION || 'mysql',

  connections: {
    mysql: {
      name: env.DB_DATABASE || 'stacks',
      host: env.DB_HOST || '127.0.0.1',
      port: env.DB_PORT || 3306,
      username: env.DB_USERNAME || 'root',
      password: env.DB_PASSWORD || '',

      pool: {
        max: 10,
        idleTimeoutMs: 30_000,
        acquireTimeoutMs: 10_000,
      },
    },
  },
}

Every knob is optional. Omitting the whole pool block leaves the driver on its own defaults, which is the right answer for an app that has never had to think about this.

Or set them from the environment:

DB_POOL_MAX=10
DB_POOL_IDLE_TIMEOUT_MS=30000
DB_POOL_ACQUIRE_TIMEOUT_MS=10000

Sizing the pool

The common mistake is setting max too high. Each connection consumes memory and a worker on the database server, and a pool larger than the server can serve just moves the queue from your app to the database, where it is harder to see. Start around 10 per application process and raise it only when you can show connection acquisition is the bottleneck.

Remember to multiply: eight app processes with max: 25 is 200 connections, which is already past the default max_connections on many MySQL installs.

Read Replicas

A read replica is a copy of your database that receives a stream of changes from the primary. Sending reads there frees the primary to do writes, which is usually the first real scaling win after pooling.

Declare replicas on the connection they replicate. Each entry inherits the port, credentials, and database name from the primary, so normally only host is needed:

// config/database.ts
connections: {
  mysql: {
    name: 'stacks',
    host: 'primary.internal',
    username: 'app',
    password: env.DB_PASSWORD,

    replicas: [
      { host: 'replica-a.internal' },
      { host: 'replica-b.internal' },
    ],
  },
},

reads: {
  autoRoute: false,
  strategy: 'round-robin',
},

Or from the environment:

DB_READ_HOSTS=replica-a.internal,replica-b.internal

Inheriting credentials is deliberate. Repeating a username and password on every replica entry is how host lists drift out of sync with a rotated password.

The hazard: replication is asynchronous

A row committed to the primary is not instantly on a replica. The lag is usually milliseconds, occasionally seconds under load. So this is a bug:

await User.create({ email })
const user = await User.where('email', email).first() // may be null

It fails intermittently, depends on load, and disappears when you attach a debugger. That is why Stacks does not route reads to replicas by default.

Explicit routing with db.read

The safe default is to opt in per query. Use db.read for the specific reads that genuinely tolerate stale data - dashboards, reports, analytics, search backfills:

import { db } from '@stacksjs/database'

// Goes to a replica when one is configured.
const stats = await db.read
  .selectFrom('orders')
  .select(['status'])
  .count()

// Goes to the primary, always.
const order = await db.selectFrom('orders').where('id', id).first()

With no replicas configured, db.read transparently falls back to the primary, so adding it to your code is safe before you have provisioned anything.

Automatic routing

If your application as a whole tolerates stale reads, you can route every read without asking:

reads: {
  autoRoute: true,
},

DB_READ_AUTO_ROUTE=true

Turning this on is a statement about your application, not a performance tweak. Two rules still protect you:

• Reads inside a transaction stay on the primary. A transaction must see its own uncommitted writes, and all of its statements must reach one connection.
• Reads after a write in the same request stay on the primary. Once a request has written anything, its later reads go to the primary, which covers the common read-your-writes case.

That second rule is scoped to a single request, so one request writing does not force every other request onto the primary. It is established at the request boundary; background jobs and one-shot scripts have no such boundary and should use db.read explicitly if they want a replica.

What is not covered: a read in a different request from the write. If user A saves a profile and user B loads it a few milliseconds later, B can see the old version. Decide whether that is acceptable before enabling this.

Distribution strategies

replicas: [
  { host: 'replica-small.internal', weight: 1 },
  { host: 'replica-large.internal', weight: 4 },
],

reads: { autoRoute: true, strategy: 'weighted' },

Sharding with Vitess

Vitess presents MySQL through vtgate, which speaks the MySQL wire protocol and routes each query to the right tablet or shard.

An unsharded keyspace is a practical starting point: it retains ordinary MySQL foreign keys, AUTO_INCREMENT, and single-shard transactions while putting the routing and operational layer in place. Split the keyspace only when one primary genuinely cannot hold the write volume or data.

Configuration

// config/database.ts
export default {
  default: 'vitess',

  connections: {
    vitess: {
      name: env.DB_DATABASE || 'stacks', // this is a KEYSPACE
      host: env.DB_HOST || '127.0.0.1',  // vtgate, not a MySQL server
      port: env.DB_PORT || 15306,        // vtgate's port, not 3306
      username: env.DB_USERNAME || 'root',
      password: env.DB_PASSWORD || '',
      sharded: env.DB_VITESS_SHARDED ?? false,
    },
  },
}

DB_CONNECTION=vitess
DB_HOST=vtgate.internal
DB_PORT=15306
DB_DATABASE=commerce
DB_VITESS_SHARDED=false

Two details differ from a plain MySQL connection and both matter:

• name is a keyspace, not a database. It is the unit Vitess shards and what the VSchema is written against.
• The port is 15306, vtgate's MySQL-protocol port. Connecting to 3306 on a Vitess cluster reaches an individual tablet's underlying MySQL and silently bypasses sharding altogether. That is worse than a connection error, because everything appears to work.

Set DB_VITESS_SHARDED=true only after the keyspace is split. Stacks then enables the stricter DDL audit and bun-query-builder generation profile described below.

What a sharded keyspace cannot do

A keyspace is split across shards that share nothing, and three consequences follow:

No foreign keys. Enforcing one would need a cross-shard read on every write. Referential integrity moves into your application. buddy doctor reports orphan rows.

No AUTO_INCREMENT. Every shard would independently hand out the same values and collide. This is not a syntax error, which makes it more dangerous than one: the DDL succeeds and the collisions appear later, under load, as duplicate-key failures on inserts that used to be fine. Use useUuid: true on your models, or back a table with a sequence in an unsharded keyspace.

No cross-shard ACID. A transaction touching two shards is best-effort or two-phase, not the single-node guarantee the rest of the framework assumes.

Stacks checks for the first two before running any migration:

./buddy migrate

❌ Error: The migration files in database/migrations use SQL features that vitess does not implement.

Nothing was migrated, so the database is unchanged.

89 use(s) of REFERENCES across 17 file(s), for example:
  0000000122-create-campaign_sends-table.sql:3  "campaign_id" INTEGER not null REFERENCES "campaigns"("id"),

Distributed engines cannot enforce a foreign key across shards, so referential
integrity has to move into the application. Regenerate the corpus for this
dialect - the generator emits the backing index without the constraint - and
rely on the model relationships plus `buddy doctor` (which reports orphan rows)
instead of database-level cascades.

This runs before the first statement, so a corpus that cannot work never half-applies. If you know your corpus is fine, STACKS_ALLOW_DDL_CONSTRAINT_VIOLATIONS=1 proceeds anyway.

Generating a VSchema

Vitess needs a VSchema: a document that says, for every table, which column decides the shard and how that column maps to one. Without it vtgate cannot route a query.

./buddy generate:vschema           # writes database/vschema.json
./buddy generate:vschema --dry-run # print it instead

Stacks derives it from your models, because the interesting part is a fact your models already state.

Sharding each table by its own id looks obviously correct and is quietly the worst option: users lands on the shard for users.id, posts on the shard for posts.id, and a join between them has to fan out to every shard and merge. The database still answers, so nothing looks broken. It just costs several times more than it should, and the cost grows as you add shards.

The fix is to co-locate: a child table shards by the key of its parent, so a user and all of their posts live on one shard and the join stays local. belongsTo already declares that relationship, so the generator reads it:

Root entities (sharded by their own id):
  users       ->  id (hash)
  categories  ->  id (hash)

Co-located with a parent (joins to that parent stay on one shard):
  orders      ->  customer_id (hash), with customers
  order_items ->  order_id (hash), with orders
  comments    ->  post_id (hash), with posts

Warnings:
  order_items: belongs to 2 parents; sharded by Order only, so joins through Product will scatter

The report is printed so you can challenge it. A generated topology you cannot see is one you cannot review.

Note that co-location is one level deep. If comments belongs to posts and posts belongs to users, comments co-locate with their post, not with their user. Making that transitive needs a lookup vindex you have to design, so the generator stops rather than inventing a topology that looks right and is not.

Overriding the derived choice

When the derived column is wrong, declare it on the model:

// app/Models/Order.ts
export default defineModel({
  name: 'Order',
  table: 'orders',

  traits: {
    useUuid: true, // sharded keyspaces cannot use AUTO_INCREMENT

    sharding: {
      column: 'tenant_id',
      vindex: 'hash',
    },
  },
})

Mark small, frequently-joined lookup tables as reference tables. Splitting them would make every join against them a scatter-gather, while duplicating a few hundred rows costs nothing:

traits: {
  sharding: { unsharded: true },
},

Schema changes

Vitess applies schema changes through its own online DDL machinery so they can roll out shard by shard without locking the keyspace. buddy migrate applies DDL over the connection, which is appropriate for an unsharded keyspace but not for a sharded one. For sharded keyspaces, generate the migrations and hand the SQL to Vitess:

./buddy generate:migrations
vtctldclient ApplySchema --sql-file database/migrations/<file>.sql <keyspace>

Managing the cluster from ts-cloud

If you deploy with ts-cloud, its dashboard manages most of this for you. Declare the cluster and, optionally, its control plane:

// cloud.config.ts
infrastructure: {
  appDatabase: {
    engine: 'vitess',
    name: 'commerce',           // keyspace
    host: 'vtgate.internal',
    port: 15306,
    username: 'app',
    password: env.DB_PASSWORD,

    vitess: {
      vtctldAddr: 'vtctld.internal:15999',
      cell: 'zone1',
      clientVersion: '21.0.0',
    },
  },
},

The dashboard then shows keyspaces, shards, and tablet health, and flags any shard with no serving primary. That failure is worth calling out: such a shard still answers reads and only fails writes, so it does not look like an outage.

Schema changes applied from the dashboard run as Vitess online DDL and are tracked with live progress, so a migration can be retried, cancelled, or completed from there.

Provisioning a cluster

ts-cloud can also install and run Vitess on the box:

// cloud.config.ts
infrastructure: {
  compute: {
    services: {
      vitess: {
        cell: 'zone1',
        keyspaces: [
          { name: 'commerce', sharded: true },
          { name: 'lookup' },
        ],
      },
    },
  },
},

That installs Vitess and etcd from the pantry registry, writes systemd units for etcd, vtctld, vttablet (beside a managed mysqld), and vtgate, registers the cell, creates the keyspaces, and waits for vtgate to actually serve before the deploy is allowed to continue.

For development, one process instead of five:

services: {
  vitess: { mode: 'combo' },
},

combo runs the whole stack inside vtcombo with an in-memory topology. There is nothing to bootstrap and it goes away cleanly, which makes it right for local work and CI. It is not durable and binds to loopback with no authentication, so it must never hold real data.

Related

• Database Package - connection configuration and the query builder
• Models - traits, including sharding
• Query Monitoring - finding the slow queries worth scaling for