As data volumes grow, distributing them across multiple database nodes becomes necessary. Sharding is a horizontal partitioning strategy that splits data across multiple database instances, with each shard holding a subset of total data. A shard key, such as user_id modulo the number of shards, determines where each record lives. Sharding enables horizontal scalability and better performance, but cross-shard queries become expensive, joins across shards are difficult, and rebalancing shards is complex.
Replication copies data from a primary database server to one or more replicas, improving read scalability, high availability, and disaster recovery. In synchronous replication, the primary waits for replica acknowledgment before confirming a write, providing stronger consistency. Asynchronous replication lets the primary proceed without waiting, giving better performance at the cost of eventual consistency. Semi-synchronous replication waits for at least one replica. Replication topologies include single-leader setups, multi-leader configurations (useful across datacenters but requiring conflict resolution), and leaderless designs where any node can accept reads or writes as long as a quorum is met. Common conflict resolution strategies include last-write-wins, vector clocks, and CRDTs.
Partitioning divides a large table into smaller pieces for easier management. Horizontal partitioning splits rows across partitions, such as orders divided by date range, while vertical partitioning splits columns, separating frequently accessed fields from rarely used ones. Common partitioning strategies include range partitioning by value ranges, hash partitioning by hash of a key, and list partitioning by explicit value lists.
Within a database, indexes speed up data retrieval at the cost of additional storage and slower writes. Most indexes use B-tree or B+ tree data structures, and indexes can be primary (on the primary key), secondary (on non-primary columns), composite (on multiple columns), or covering (containing all columns needed by a query). Choosing between SQL and NoSQL databases also shapes system design: SQL databases like PostgreSQL and MySQL are relational, use fixed schemas, offer ACID compliance, and scale vertically; NoSQL databases like MongoDB, Cassandra, and DynamoDB offer flexible schemas, horizontal scalability, and typically eventual consistency. Finally, connection pooling maintains a pool of reusable database connections, avoiding the overhead of establishing new connections for every request. Pools start with a minimum number of connections, grow up to a maximum under load, and are commonly implemented by tools like HikariCP, pgBouncer, and SQLAlchemy.