July 2024 Summaries

Sharding a database can significantly improve backup and restore performance, making it easier to handle large databases. PlanetScale's platform uses Vitess as a proxy, sharding, and coordination layer between applications and MySQL instances. Backups are taken every 12 hours by default but can be scheduled or manually triggered. In a sharded architecture, each shard can complete backup steps in parallel, allowing for quick backups even for large databases. The power of parallelization also applies to full database restores, making them faster and more efficient.

Building data pipelines using Vitess involves leveraging its VStream API, which is part of VReplication and allows for various types of data replication and transformation. This feature is particularly useful in Change Data Capture (CDC) scenarios, where it helps maintain in-sync copies of data across different systems. Vitess' primitives make it easy to build data pipelines, with popular CDC tools like Debezium using the VStream API to capture changes and propagate them to other systems. The VStream client outputs changes that are being streamed from a VTGate in real-time, allowing users to maintain a Data Warehouse or Data Lake for analytics and reporting purposes.

The text discusses the state of MySQL online schema migrations in 2024, focusing on ALTER TABLE statements. It examines native MySQL options such as INPLACE and INSTANT, highlighting their limitations and risks. The article also explores external tools like Vitess for running non-blocking schema changes, emphasizing their advantages over native MySQL solutions. Partitioning is mentioned as a unique case that may require specific handling depending on the type of change. Overall, the text suggests that 3rd party online schema change tools are the best option for most use cases in the foreseeable future.

The Vitess query planner has been optimized to handle aggregation better after a recent bug report. The initial problem was with a query causing VTGate to fetch large amounts of data, sometimes leading to an Out Of Memory (OOM) error. The solution involved delaying the "ordering under aggregation" rewriter until the "split aggregation" phase, allowing for efficient tree rewriting and pushing down aggregation under the join. This optimization significantly improves query performance and resource utilization by efficiently planning queries and pushing operations closer to the data.

Large databases often contain very large tables that make scaling difficult. This article covers three techniques to handle such tables while keeping the database performant: vertical scaling, vertical sharding, and horizontal sharding. Vertical scaling involves increasing the capacity of the server running the keyspace by adding more compute resources or growing the underlying storage capacity. Vertical sharding moves large tables onto separate servers, allowing for separate scaling of storage and compute for different parts of the database. Horizontal sharding takes a single table and spreads its rows out across many separate servers based on a sharding strategy, providing increased write throughput, backup speed, failure isolation, and cost savings.

Sharding strategies can be categorized into three types: directory-based, range-based, and hash-based. Directory-based sharding involves mapping data to shards using a lookup table, which is useful for specific criteria but may result in uneven data distribution and an extra query step. Range-based sharding splits up the shard mapping based on a range of values, which can lead to overloaded shards but allows for easy resharding operations. Hash-based sharding distributes data evenly across shards using a hash function, minimizing human guesswork and offering easier resharding options. PlanetScale typically recommends hash-based sharding as the default option due to its simplicity and effectiveness in maintaining an even distribution of data.