Data and mail
Sharded databases
stackd shards MySQL with Vitess and PostgreSQL with Citus, spreading a
database's data over several groups of servers, with backups and restores,
growing and shrinking, and failover per shard. A sharded database has the
topology sharded; its engine picks the system. Linking apps, routers and
placement work as in Managed databases.
Shape
| MySQL (Vitess) | PostgreSQL (Citus) | |
|---|---|---|
| A shard is | a key range (-80, 80-) of one keyspace |
a worker group holding some of every distributed table's 32 shards |
| Created with | 2, 4, 8 or 16 shards | 1 to 4 workers, plus the coordinator |
| Members per shard | 1 (no failover) or 3 | 1 or 3, the coordinator too |
| Control plane | 1 or 3 control members | runs in the coordinator's members |
| Apps connect to | mysql-<db>:6446 |
postgres-<db>:5432 (read/write), 5433 (read-only) |
| Growing | split every shard in two | add a worker, then rebalance |
You pick 1 to 9 servers and stackd lays the members out, each shard's members on different servers, spread evenly. A server can hold one member of each shard. Memory is checked against everything a server would run: about 640 MiB per Vitess tablet, 192 MiB per control member, 450 MiB per Citus member.
Vitess and Citus publish amd64 builds only, so the agent builds their images from source on each server, on any architecture.
MySQL with Vitess
- Links get
_URL(mysql://…@mysql-<db>:6446/<keyspace>) and_RO_URL(…/<keyspace>@replica, reading from replicas); both use port- Every linked app shares the keyspace.
- The VSchema decides how tables are sharded. Apps can change it with
ALTER VSCHEMA(so migrations can shard their own tables), and it can be viewed and edited in the panel or withGET/PUT /api/databases/{id}/vschema. AUTO_INCREMENTids are unique and increasing across shards: stackd gives each sharded table with anAUTO_INCREMENTcolumn a Vitess sequence and points the VSchema at it, including tables created later. Ids aren't dense: a restart or failover can skip a block of them.- Transactions spanning shards are atomic when shards have replicas (two-phase commit). They cost a few more round trips than single-shard ones. A read in the middle of one can still see one shard's part. With one tablet per shard, they commit shard by shard, and a crash between two shards' commits leaves one side committed.
- Failover is per shard. A shard whose primary's server froze failed over in about 20s, other shards unaffected. Queries are held for up to 10s during a primary change or a split's cutover instead of failing.
- Switchover takes about 1.5s.
- Adding a tablet to a shard copies the shard from its primary (about
10s for a small one). A shard with replicas keeps at least 2 tablets,
else 1. Sequence tablets are added, removed and drained with
shard: "sequence"; control members withshard: "control"(e.g.POST /members). Replacing control members doesn't restart the routers, so app connections stay up.
Splitting and merging
POST /scale-out splits shards in two and POST /scale-in merges pairs,
one shard (or pair) at a time. During each step the data is copied to the
new shards, kept in step, and traffic switches over once they've caught
up. Expect a write or two to fail at a cutover (VT13001); no rows are
lost. Merging stops at two shards; shards split and merge evenly, as
powers of two from 2 to 64, and are uneven midway.
The servers need room for one shard's (or pair's) new tablets beside the old ones while a step runs; the API checks before starting. A step that fails before copying is abandoned, and the shards stay as they were, serving.
Backups and restores
A backup covers every shard at one moment. Writes pause for about a second while it starts; a long transaction holding a lock delays that. If the pause can't be taken safely, the backup fails rather than being recorded as consistent.
A restore creates a new database with the backup's shards, data, sequences
and VSchema. Pass shards (any power of two) to restore into a different
number of shards.
External access
A vtgate on every server, with TLS required and a certificate from the
database's CA, the same users as apps, and the same allowlist, CA download
and admin user as MySQL clusters. --ssl-mode=VERIFY_IDENTITY works.
PostgreSQL with Citus
- Apps get a role and a schema they own in the one Citus database,
with their
search_pathset to it (_SCHEMAin the environment). Tables stay on the coordinator until the app callscreate_distributed_tableorcreate_reference_table. - Citus's usage reports are off
(
citus.enable_statistics_collection = off). - Formation takes about 2.5 minutes for 3 groups of 3.
- Failover is per group. With the coordinator's and two workers' primaries on a frozen server, writes failed for about 33s.
- Switchover takes about 3s.
- Adding a worker (
POST /scale-out) puts a new group on the least loaded servers (or the ones given) and rebalances shards onto it, one at a time, without failing writes.POST /rebalancestarts a rebalance again by hand. - Removing a worker (
POST /scale-in,{"worker": n}for a given one, else the last; at least one stays) moves its shards to the others, then removes it. Workers keep their numbers, so removing worker 2 of 3 leaves 1 and 3, and the next one added is 4. - Adding or removing a member works like PostgreSQL ha.
- External access works as for PostgreSQL ha, through the coordinator.
- Backups cover every group at one moment; writes pause for about a second. A restore can go into any number of workers, with each table distributed as it was.
Draining a server
Draining moves each sharded member to a server with no member of that shard, switching the primary over first. A server with every shard's members, like each of three servers holding three-member shards, needs a fourth server to drain onto.
Limitations
- Vitess transactions across shards aren't atomic without replicas, so a crash can leave one half committed.
- Backups pause writes for about a second.
- Resharding needs room for one shard's new tablets beside the old ones while a step runs.
- Sequence ids aren't dense.
- etcd in databases created before its TLS has no authentication until it's turned on, as for PostgreSQL ha (see Managed databases). Members and routers restart one at a time; a shard without replicas is unavailable while its member restarts.
- Citus replicates asynchronously within each group.