Database Sharding Strategies: The Nuclear Option for Scaling High-Traffic Apps in 2020

There comes a terrifying moment in every System Architect's career when htop shows all cores at 100%, the disk I/O wait is climbing past the acceptable threshold of 10%, and the CFO refuses to sign off on yet another "Big Iron" server upgrade. You have optimized your indexes. You have tuned innodb_buffer_pool_size to utilize 80% of RAM. You have deployed Read Replicas until you ran out of private IPs. Yet, the Write Master is dying. The application is write-bound, and no amount of caching will fix it. Welcome to the world of Database Sharding.

Sharding is horizontal scaling for your storage layer. It is complex, dangerous, and requires a fundamental rewrite of how your application treats data. But in 2020, with data volumes exploding across the Nordic tech sector, it is often the only path forward for high-performance applications. Before you execute this architectural pivot, you must understand the strategies, the tools (like ProxySQL), and the underlying infrastructure requirements. A sharded database turns disk calls into network calls. If your hosting provider has "noisy neighbors" or poor internal routing, your latency will destroy the theoretical throughput gains. This is why we engineered CoolVDS with dedicated bandwidth allocations—because sharding on a budget VPS is a suicide mission.

The Architecture of Fragmentation

Sharding involves breaking your monolithic dataset into smaller chunks (shards) distributed across multiple servers. Each server acts as a "Shared Nothing" unit. The complexity lies in routing: how does the application know which server holds the data for User ID 45902?

1. Key-Based (Hash) Sharding

This is the most common algorithmic approach. You take a shard key (e.g., customer_id) and apply a hash function to determine the destination server. It ensures a uniform distribution of data, preventing "hotspots" where one server does all the work while others sit idle. However, the rigidity is painful. Resharding (adding a new node) requires rehashing and migrating massive amounts of data, usually resulting in downtime.

2. Range-Based Sharding

Data is divided based on ranges of the shard key. For example, IDs 1–100,000 go to db-shard-01, and 100,001–200,000 go to db-shard-02. This is intuitive and makes "data locality" easier to manage. If you are a Norwegian e-commerce platform, you might shard by order_date. The downside is uneven distribution. If your Black Friday sale (November) goes to a single shard, that specific CoolVDS instance will melt while the others are bored.

3. Directory-Based Sharding (Lookup Service)

You maintain a separate lookup table that maps keys to shards. This offers maximum flexibility—you can move a specific high-value customer to a dedicated NVMe node without moving everyone else. The trade-off is performance. Every query requires a lookup first, doubling the latency impact. This effectively demands that your lookup service is cached in Redis or Memcached and resides on a low-latency network.

Implementation: The 2020 Stack

Let's move away from theory. If you are running a high-load stack today, you are likely using MySQL 8.0 or PostgreSQL 12. Here is how we implement sharding in the real world using ProxySQL, a battle-hardened tool that has become the standard for SQL traffic management.

MySQL Sharding with ProxySQL

Instead of rewriting your PHP or Go application to handle sharding logic, we place ProxySQL between the app and the database nodes. ProxySQL analyzes the SQL traffic and routes it based on rules defined in its configuration. It acts as a transparent layer.

First, we define our backend servers (shards) in the ProxySQL admin interface:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_replication_lag)
VALUES (10, '10.0.0.5', 3306, 10); -- Shard 1
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_replication_lag)
VALUES (20, '10.0.0.6', 3306, 10); -- Shard 2

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

Next, we create sharding rules. Let's assume we are sharding by user_id using a simple modulo operation. While ProxySQL is powerful, complex hashing logic is often better handled by the app, but for simple splitting (e.g., Read/Write split or table-based sharding), we can use query rules. For true ID-based sharding, we often inject a comment in the SQL from the app side, like /* shard=1 */ SELECT..., which ProxySQL detects.

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, "/\* shard=1 \*/", 10, 1);

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, "/\* shard=2 \*/", 20, 1);

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

This configuration assumes your application is smart enough to tag queries. If you need transparent sharding without app changes, you might look into Vitess, which was graduated by the CNCF late last year (2019), but Vitess brings a level of operational complexity that requires a dedicated DevOps team.

PostgreSQL Native Partitioning

If you are on the PostgreSQL team, version 12 (released late 2019) significantly improved declarative partitioning. While partitioning is not strictly sharding (it's usually on the same host), it is the precursor to using Postgres-FDW (Foreign Data Wrappers) to move partitions to different physical servers.

-- Parent Table
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

-- Partitions
CREATE TABLE measurement_y2019 PARTITION OF measurement
    FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');

CREATE TABLE measurement_y2020 PARTITION OF measurement
    FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

-- Indexing (Critical for performance)
CREATE INDEX ON measurement_y2020 (logdate);

In a CoolVDS environment, you can mount different partitions on different block storage volumes. You could keep measurement_y2020 on high-performance NVMe storage for rapid ingestion and analysis, while moving measurement_y2019 to cheaper, standard SSD storage since it is rarely accessed. This is Tiered Storage Architecture, and it saves thousands of Kroner in hosting costs.

The Infrastructure Bottleneck: Latency

This is where most deployments fail. When you shard a database, you introduce network latency into your transaction path. A query that used to take 0.1ms inside a CPU register now takes 0.5ms or 1.0ms to traverse the network stack, hit the switch, and reach the shard. If you perform a "scatter-gather" query (requesting data from 10 shards to aggregate a report), that latency stacks up.

Pro Tip: Never shard across data centers unless you are doing it for Disaster Recovery. For active transaction processing, your shards must reside in the same physical facility. CoolVDS ensures that your private LAN traffic between instances never leaves the local switch fabric, maintaining sub-millisecond latency.

In Norway, data sovereignty is also a massive factor. With the recent focus on GDPR and the Schrems II ruling implications looming over the industry, ensuring your shards physically reside in Oslo or nearby Nordic facilities is a compliance necessity, not just a performance one. Using a US-based cloud provider often means your data traverses international borders, complicating legal compliance with Datatilsynet.

When NOT to Shard

I recently consulted for a logistics firm in Bergen. They were convinced they needed sharding because their database was 500GB. They were ready to rewrite their entire codebase. I stopped them. Sharding is the last resort.

Do not shard if:

• You haven't optimized your queries (look for full table scans).
• You haven't implemented caching (Redis/Varnish).
• You are purely read-heavy (use Read Replicas instead).
• You can simply upgrade to a CoolVDS instance with 32 vCPUs and NVMe storage.

Vertical scaling (scaling up) is always operationally cheaper than horizontal scaling (scaling out) until you hit the hardware ceiling. But when you do hit that ceiling—when you have millions of concurrent users and a single write master cannot keep up—sharding is the only way to survive.

If you are at that breaking point, you need a foundation that won't flinch. You need guaranteed CPU cycles, not shared credits. You need NVMe storage that handles high IOPS without throttling. Don't let slow infrastructure kill your meticulously architected database cluster. Deploy your test shards on CoolVDS today and experience the difference raw performance makes.