This method uses a mathematical formula (a hash function) on the shard key to assign data. For example, the system might calculate User ID (mod 4) to assign a user to 1 of 4 servers.

While hash functions help distribute data consistently, they only ensure an even distribution if the shard key has high cardinality and low frequency skew. If you choose a shard key with a common value, such as a "Last Name" where "Smith" appears 1,000 times more than "Pyne," the hash function will send every "Smith" record to the same shard. This creates a "hot shard" despite the use of a mathematical formula.

Adding new servers is also difficult with this method because the formula changes, which often requires you to "reshard" or move your data across the new server cluster.

Data is assigned based on ranges of values. For instance, you might place User IDs 1–1,000 on Server A and 1,001–2,000 on Server B. This is very intuitive and great for queries that need to read a sequence of data (range queries). The downside is "hot spots"—if all your new users are assigned to Server B, that server will do all the work while Server A sits idle.

This strategy uses a lookup table (a directory) that tracks exactly which shard holds which data. It offers the most flexibility because you can move data between shards without changing a formula. However, this lookup table becomes a bottleneck; every query must check the directory first, adding latency. If the directory fails, the entire database becomes inaccessible.

Geo sharding assigns data to specific servers based on a user's physical location. For example, data for users in France is stored on servers in the EU, while US users are on servers in North America. This significantly reduces latency (speed) for users and helps companies meet data residency laws like GDPR.

Sometimes called functional partitioning, this involves splitting data by feature rather than by row. For example, you might place all "User Profile" tables on Server A and all "Photo Upload" tables on Server B. While this organizes data logically, it is functionally similar to a microservices data architecture and doesn't solve the problem if one specific feature (like Photos) grows too large for a single server.

Sharding is a specific type of horizontal partitioning where the data pieces are distributed across completely different servers. This solves both hardware capacity limits (storage) and write throughput bottlenecks, as different servers can process writes simultaneously.

• Manual sharding: In a manual sharding environment, the developer or database administrator is responsible for defining the shard keys, creating the infrastructure for each shard, and writing the logic to route queries. This gives you granular control over exactly where data lives. However, it requires significant maintenance. If one shard becomes too large, you must manually "reshard" the data, which involves moving millions of rows to new servers while trying to minimize application downtime.
• Automatic sharding: Automatic sharding, often found in NoSQL databases like Bigtable or distributed SQL databases like Spanner, handles the distribution of data and traffic automatically. The system monitors the size of data and the load on each server. If a specific shard becomes a "hot spot" or grows too large, the database engine automatically splits the shard and moves data to a less congested server. This reduces the operational burden on your team and helps ensure consistent performance as your application scales.

Partitioning involves splitting a large table into smaller, more manageable pieces (like splitting a log table by month) but keeping them on the same server instance. This solves storage problems by making it easier to archive or delete old data without affecting the rest of the table. However, it does not solve server problems. Because all partitions still reside on a single machine, they continue to share the same CPU and RAM, which means partitioning cannot help if the server hits its performance limits.

Replication is the process of copying the entire database to multiple servers. This can be a good choice for read availability; if one server fails, another can take over. However, it does not help with write scaling because every piece of data written must be copied to every replica, limiting the write speed to the capacity of a single machine.

Just as importantly, most replication models only allow for one "writer" (the primary node) at a time. If you attempt to allow multiple servers to accept writes simultaneously, you risk write conflicts, where two servers try to update the same record with different information. Resolving these conflicts is technically difficult and can lead to data loss or inconsistency if not handled by a sophisticated distributed system.

A distributed database, such as Spanner, provides the benefits of sharding without the manual overhead. These systems are designed to sit across a cluster of machines from the start. They automatically handle data distribution, rebalancing, and replication transparently. Some of these systems have multiple writers; and automatically handle write conflicts. This allows you to scale horizontally while maintaining the consistency of a traditional relational database.