Database Sharding Architectures: When Vertical Scaling Fails

There is a specific moment in every Systems Architect's career that I call "The Wall." It usually happens at 3:00 AM on a Tuesday. Your primary PostgreSQL node has hit 98% CPU utilization, the iowait is screaming because your NVMe drives can't flush dirty pages fast enough, and the write-ahead log (WAL) is lagging by gigabytes. You throw more RAM at it. You upgrade the CPU cores. It buys you two weeks.

Vertical scaling (scaling up) is a trap. It works until the hardware physics—or your CFO—says no. If you are operating a high-traffic SaaS targeting the Nordic market, relying on a single massive database node is not just a bottleneck; it is a single point of failure that no amount of caching can fix forever.

This is where sharding comes in. It is not a silver bullet. In fact, it breaks ACID compliance guarantees, makes joins a nightmare, and complicates your deployment pipeline. But when you are pushing 50,000 TPS (Transactions Per Second), it is the only path forward. Here is how we architect sharded systems that actually survive production.

The Geometry of Data: Why Sharding Matters in 2025

Sharding is horizontal scaling. Instead of one massive instance, you split your data across multiple nodes (shards) based on a specific key (e.g., user_id, region_id, or tenant_id). This reduces the index size on individual nodes, keeping B-Trees shallow and fast.

The Norwegian Context: GDPR and Latency

For those of us hosting in Norway, sharding offers a unique compliance advantage. Under GDPR and the scrutiny of Datatilsynet, data residency is critical. A geo-sharding strategy allows you to pin Norwegian user data to physical nodes located in Oslo (like CoolVDS NVMe instances), while non-EEA data can live elsewhere. This architectural segregation simplifies audit trails significantly.

Strategy 1: Application-Level Sharding (The "Do It Yourself" Approach)

This is the most flexible but code-heavy approach. Your application logic determines which database server to query. It requires zero special database software, just smart routing logic.

The Implementation:

You define a map or a hashing algorithm in your backend.

def get_db_shard(user_id):
    # Simple modulo sharding for 4 shards
    shard_index = user_id % 4
    
    shards = {
        0: "db-shard-01.oslo.coolvds.net",
        1: "db-shard-02.oslo.coolvds.net",
        2: "db-shard-03.oslo.coolvds.net",
        3: "db-shard-04.oslo.coolvds.net"
    }
    
    return connect(host=shards[shard_index], user="app_user")

This works well until you need to add a 5th shard. Then you have to rebalance everything. To avoid this, successful teams in 2025 use Consistent Hashing or look-up tables (LUTs) stored in a high-speed key-value store like Redis.

Strategy 2: Middleware Routing (ProxySQL / Vitess)

If you don't want to pollute your application code with routing logic, you place a proxy in front of your database cluster. For MySQL workloads, ProxySQL is the industry standard. It understands the SQL protocol and can route queries based on regex or query comments.

Here is a snippet of a ProxySQL configuration block for splitting reads and writes, a precursor to full sharding:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES
(1, 1, '^SELECT.*FOR UPDATE$', 10, 1), -- Send locking reads to Primary (HG 10)
(2, 1, '^SELECT', 20, 1);              -- Send standard reads to Replicas (HG 20)

For true sharding, tools like Vitess (originally built by YouTube) abstract the sharding entirely. The application thinks it talks to one massive MySQL DB, but Vitess routes it to thousands of nodes. Note: Vitess adds latency. If your servers are not on a low-latency network (like the internal mesh we use at CoolVDS), the extra hops will kill your Time-To-Byte.

Strategy 3: Native Partitioning (PostgreSQL 17)

PostgreSQL has made massive strides in declarative partitioning. By 2025, Postgres 17 allows for very efficient partition pruning. While partitioning is strictly local to one instance, combining it with Foreign Data Wrappers (postgres_fdw) allows you to create a sharded cluster natively.

Example: Creating a Time-Series Shard Structure

-- Parent table
CREATE TABLE sensor_logs (
    log_id UUID DEFAULT gen_random_uuid(),
    device_id INT NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL,
    payload JSONB
) PARTITION BY RANGE (recorded_at);

-- Partitions (Shards)
CREATE TABLE sensor_logs_2025_06 PARTITION OF sensor_logs
    FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');

CREATE TABLE sensor_logs_2025_07 PARTITION OF sensor_logs
    FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');

-- Indexing allows the query planner to skip scanning irrelevant partitions
CREATE INDEX ON sensor_logs (recorded_at);

When you query WHERE recorded_at > '2025-07-15', Postgres 17 instantly ignores the June partition. This reduces I/O dramatically.

Pro Tip: Never shard without a strategy for resharding. Data is rarely distributed evenly. You will have "hot shards" where your most active users live. Monitor your disk_io_util and buffer_pool_wait_free metrics religiously. If one shard is consistently 20% hotter than others, you need to migrate keys.

The Hardware Reality: IOPS is the Bottleneck

You can have the best sharding algorithm in the world, but if the underlying storage is spinning rust or network-throttled block storage, you will fail. Distributed databases are "chatty." They require constant synchronization.

At CoolVDS, we see clients migrate from hyperscalers specifically for this reason. Hyperscalers often cap IOPS unless you pay exorbitant fees for "Provisioned IOPS."

Comparison: Hosting for Sharded DBs

Configuring the Node: The `my.cnf` Essentials

Whether you run 4 shards or 40, the individual node configuration must be tight. For a standard MySQL 8.4 LTS instance running on a 32GB RAM CoolVDS instance, do not rely on defaults.

[mysqld]
# 70-80% of RAM for InnoDB Buffer Pool
innodb_buffer_pool_size = 24G

# Critical for SSD/NVMe performance
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_flush_neighbors = 0

# Durability settings (ACID)
# Set to 2 if you can tolerate 1 second of data loss for write speed
innodb_flush_log_at_trx_commit = 1 

# Connection handling
max_connections = 1000
thread_cache_size = 100

Conclusion: Don't Shard Until You Must

Sharding introduces operational overhead. You lose foreign keys across shards. You lose complex transactions. You need a dedicated team just to manage the topology.

However, if you are hitting the limits of vertical scaling, there is no alternative. Start by splitting your Reads from your Writes. Then, look into functional partitioning (splitting User tables from Analytics tables). Finally, implement horizontal sharding.

And when you do, ensure your infrastructure isn't the weak link. A distributed database on high-latency network storage is a disaster waiting to happen. Test your sharding logic on CoolVDS NVMe instances. The low latency within our Norwegian datacenter provides the stability your cluster needs to stay in sync.

Ready to benchmark your sharded architecture? Spin up a high-performance NVMe instance in Oslo in under 60 seconds.