vitess

# Vitess

Vitess is a MySQL-compatible, cloud-native database system originally built at YouTube to scale MySQL. PlanetScale runs Vitess as a managed service. Core capabilities:

- **Horizontal sharding**: Built-in sharding transparent to the application — no sharding logic in app code.
- **Connection pooling**: VTTablet multiplexes client connections to MySQL, scaling concurrent connections far beyond native MySQL limits.
- **High availability**: Automatic primary failure detection and repair. Resharding and data migrations with near-zero downtime.
- **Query rewriting and caching**: VTGate rewrites and optimizes queries before routing to shards.
- **Schema management**: Apply schema changes across all shards consistently, in the background, without disrupting workloads.
- **Materialized views and messaging**: Cross-shard materialized views and publish/subscribe messaging via VStream.

## Key concepts

| Concept        | What it is                                                                                    |
| -------------- | --------------------------------------------------------------------------------------------- |
| **Keyspace**   | Logical database mapping to one or more shards. Analogous to a MySQL schema.                  |
| **Shard**      | A horizontal partition of a keyspace, each backed by a separate MySQL instance.               |
| **VSchema**    | Configuration defining how tables map to shards, vindex (sharding) keys, and routing rules.   |
| **Vindex**     | Sharding function mapping column values to shards (`hash`, `unicode_loose_xxhash`, `lookup`). |
| **VTGate**     | Stateless proxy that plans and routes queries to the correct shard(s).                        |
| **Online DDL** | Non-blocking schema migrations. On PlanetScale, use deploy requests for production changes.   |

## PlanetScale specifics

- **Branching**: Git-like database branches for development; deploy requests for production schema changes.
- **Connections**: MySQL protocol, port `3306` (direct) or `443` (serverless). SSL always required.

## SQL compatibility

Vitess supports nearly all MySQL syntax — most applications work without query changes. Standard DML, DDL, joins, subqueries, CTEs (including recursive CTEs as of v21+), window functions, and common built-in functions all work as expected.

Known limitations:

- **Stored procedures / triggers / events**: Not supported through VTGate.
- **`LOCK TABLES` / `GET_LOCK`**: Not supported through VTGate.
- **`SELECT ... FOR UPDATE`**: Works within a single shard; cross-shard locking is not atomic.
- **Cross-shard joins**: Supported but expensive (scatter-gather). Filter by vindex column for single-shard routing.
- **Correlated subqueries**: May fail or perform poorly cross-shard. Rewrite as joins when possible.
- **IDs**: Use **Vitess Sequences** (a global counter in an unsharded keyspace) or app-generated IDs (UUIDs, snowflake) to avoid collisions on sharded tables.
- **Aggregations on sharded tables**: `GROUP BY`/`ORDER BY`/`LIMIT` merge in VTGate memory. Large result sets can be slow.
- **Foreign keys**: Limited support. Prefer application-level referential integrity on sharded keyspaces.

## References

| Topic          | Reference                                                                                                                                     | Use for                                                              |
| -------------- | --------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------- |
| VSchema        | [references/vschema.md](https://raw.githubusercontent.com/planetscale/database-skills/main/skills/vitess/references/vschema.md)               | VSchema design, vindexes, sequences, sharding strategies             |
| Schema Changes | [references/schema-changes.md](https://raw.githubusercontent.com/planetscale/database-skills/main/skills/vitess/references/schema-changes.md) | Online DDL, managed migrations, ddl strategies, migration lifecycle  |
| VReplication   | [references/vreplication.md](https://raw.githubusercontent.com/planetscale/database-skills/main/skills/vitess/references/vreplication.md)     | MoveTables, Reshard, Materialize, VDiff, VStream                     |
| Architecture   | [references/architecture.md](https://raw.githubusercontent.com/planetscale/database-skills/main/skills/vitess/references/architecture.md)     | VTGate, VTTablet, Topology Service, VTOrc, component interactions    |
| Query Serving  | [references/query-serving.md](https://raw.githubusercontent.com/planetscale/database-skills/main/skills/vitess/references/query-serving.md)   | Query routing, MySQL compatibility, cross-shard performance, EXPLAIN |