Database Sharding: Surviving the Write-Heavy Monolith in Post-Schrems II Europe

It’s 3:00 AM. Your phone buzzes. The primary database node is pegged at 100% CPU. IOWait is through the roof. You’ve already upgraded the RAM to the maximum the server supports. You’ve optimized every slow query in the slow_query_log. There is nothing left to tune.

Welcome to the wall. Vertical scaling is dead.

If you are running a high-traffic platform in 2020, relying on a single write master is a ticking time bomb. With the recent CJEU ruling (Schrems II) effectively killing the Privacy Shield, moving your data to a massive US cloud provider isn't just a latency issue anymore—it’s a compliance nightmare. You need a strategy that scales horizontally and stays within the legal boundaries of the EEA, preferably right here in Norway.

The Monolith's Last Stand

Most developers try to delay sharding. I get it. It adds complexity. You lose ACID transactions across nodes. JOINs become expensive or impossible. But when you are pushing 50,000 writes per second, a single NVMe drive, even enterprise-grade ones like we use at CoolVDS, will eventually choke.

Before you shard, you usually try read-replicas. That solves the read problem. But it doesn't solve the write problem. All writes must go to the master. To fix this, we break the database into smaller, autonomous pieces called shards.

The Strategy: Application-Level vs. Middleware

You have two main paths in 2020:

1. Application Logic: Your code knows that User IDs 1-1000000 live on db-shard-01 and 1000001-2000000 live on db-shard-02.
2. Middleware (Proxy): Tools like ProxySQL or Vitess intercept queries and route them.

For a clean architecture on bare-metal or KVM VPS, I prefer ProxySQL for MySQL environments. It allows you to keep the application code relatively dumb while the infrastructure handles the complexity.

Implementation: Configuring ProxySQL for Sharding

Let's assume we are sharding based on user_id using a modulo operation. We have 2 shards. If user_id % 2 == 0, it goes to Shard A. Otherwise, Shard B.

First, we configure the hostgroups in ProxySQL Admin:

-- Define the backend servers (Your CoolVDS instances)
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES 
(10, '10.0.0.5', 3306), -- Shard 0 (Even)
(20, '10.0.0.6', 3306); -- Shard 1 (Odd)

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Next, we need sharding rules. This is where the magic happens. We intercept queries containing user_id and route them.

-- Route even User IDs to HG 10
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, "user_id=[02468]$", 10, 1);

-- Route odd User IDs to HG 20
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, "user_id=[13579]$", 20, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Note: This regex is a simplified example. In production, you'd likely use a specific hashing algorithm or a lookup service, but this illustrates the logic.

The Hardware Reality: Why IOPS Matter

Sharding multiplies your hardware requirements. Instead of one beefy server, you now manage five or ten. This is where the "Noisy Neighbor" effect kills projects.

If you deploy your shards on a budget shared hosting platform, and another tenant decides to mine crypto or run a backup script, your shard's latency spikes. In a sharded environment, the slowest shard defines your global tail latency.

Pro Tip: Always disable swap on database nodes. If your database starts swapping, it's already dead. Configure vm.swappiness = 0 in /etc/sysctl.conf immediately after provisioning.

This is why we standardized on KVM virtualization at CoolVDS. Unlike OpenVZ or LXC containers often sold as "VPS" by budget providers, KVM provides rigid resource isolation. When you provision a VPS Norway instance with us, the NVMe IOPS you see are yours. We don't oversell storage I/O.

Optimizing the Shard Config (my.cnf)

Since each shard handles a smaller dataset, you can tune the configuration differently than a monolith. Here is a snippet from a my.cnf optimized for a write-heavy shard node running MySQL 8.0:

[mysqld]
# NETWORK & CONNECTION
bind-address            = 0.0.0.0
max_connections         = 1000

# INNODB SPECIFICS
# Set this to 70-80% of total RAM
innodb_buffer_pool_size = 12G 

# Crucial for write-heavy shards. 
# 1 = ACID compliant (Safe).
# 0 or 2 = Faster, but risk of data loss on power failure.
# On stable infrastructure like CoolVDS, 1 is recommended for financial data.
innodb_flush_log_at_trx_commit = 1

# STORAGE I/O
# Optimized for NVMe drives
innodb_io_capacity      = 2000
innodb_io_capacity_max  = 4000
innodb_flush_method     = O_DIRECT
innodb_log_file_size    = 1G

Data Migration Code: Moving to Shards

You have an existing massive table. How do you move it without downtime? You don't. You minimize downtime. Here is a Python script concept using pymysql to migrate data in batches based on our modulo logic.

import pymysql

# Connections
source_conn = pymysql.connect(host='old_master', user='root', password='secure_password')
shard_even = pymysql.connect(host='10.0.0.5', user='root', password='secure_password')
shard_odd = pymysql.connect(host='10.0.0.6', user='root', password='secure_password')

def migrate_batch(start_id, end_id):
    with source_conn.cursor() as cursor:
        sql = "SELECT * FROM users WHERE id BETWEEN %s AND %s"
        cursor.execute(sql, (start_id, end_id))
        rows = cursor.fetchall()
        
        for row in rows:
            user_id = row['id']
            target_shard = shard_even if user_id % 2 == 0 else shard_odd
            
            with target_shard.cursor() as t_cursor:
                # Construct insert dynamically based on columns
                placeholders = ', '.join(['%s'] * len(row))
                cols = ', '.join(row.keys())
                insert_sql = f"INSERT INTO users ({cols}) VALUES ({placeholders})"
                t_cursor.execute(insert_sql, list(row.values()))
            target_shard.commit()

# Run in small batches to avoid locking the source table for too long
migrate_batch(1, 1000)

The Norwegian Context: Latency and Sovereignty

Latency is physics. If your users are in Oslo, Bergen, or Stockholm, routing queries to a shard in Frankfurt adds 20-30ms of round-trip time. In a sharded app doing multiple lookups, that adds up to seconds of delay.

By hosting your shards on managed hosting or self-managed VPS infrastructure in Oslo, you cut that latency to under 5ms. Furthermore, with the Datatilsynet (Norwegian Data Protection Authority) keeping a close eye on GDPR compliance post-2020, keeping data within Norwegian borders is the safest bet for the pragmatic CTO.

Comparison: Vertical vs Horizontal Scaling Costs

Conclusion

Sharding isn't a silver bullet. It introduces operational overhead. But when you need raw write throughput and strict data sovereignty, it is the only path forward. Don't build your complex architecture on flaky foundations.

You need low latency. You need predictable I/O. You need ddos protection that doesn't scrub your legitimate traffic.

Start your architecture right. Spin up a VPS Norway instance on CoolVDS today and see what dedicated NVMe performance actually feels like.