Comparison
PartitioningvsSharding
Partitioning
you split the giant table into monthly chunks so the query touches one of them and dropping old data is a DDL statement instead of a delete.
Splitting one logical table into physical pieces by a key, usually range or hash, inside a single database. The wins are partition pruning at query time and cheap bulk deletion by dropping a partition. It only helps queries that filter on the partition key; everything else now scans every partition.
Full entry →Sharding
one database stopped being enough, so you split the rows across several of them and now every query has to know which one to ask.
Splitting data across independent database instances so writes and storage scale past one machine. It is the point at which cross-shard joins, distributed transactions and global uniqueness stop being free. Resharding a live system is one of the hardest routine operations in backend engineering, which is why the shard key deserves more thought than the schema.
Full entry →