Database Sharding Strategies: Survival Guide for High-Traffic Norwegian Apps

There is a specific moment in every Systems Architect's career that triggers a cold sweat. It's not a DDoS attack. It's not a botched deployment. It is the moment you realize your primary database write-master has hit 95% CPU utilization, your NVMe queues are saturating, and you have no more vertical headroom. You can't just throw more RAM at the problem anymore.

Welcome to the world of sharding. It is complex, it introduces maintenance overhead, and if you do it wrong, you will lose data. But for high-scale applications serving the Nordic market, it is often the inevitable next step.

The Monolith Limit: When `ALTER TABLE` Becomes a Nightmare

In 2020, we saw a massive surge in e-commerce traffic across Norway. I worked with a client in Oslo whose single MySQL 8.0 instance was handling sessions, orders, and inventory. As their Black Friday traffic hit, the iowait skyrocketed. The server didn't crash, but latency for users in Bergen went from 20ms to 2.5 seconds. That kills conversion rates.

Vertical scaling (upgrading to a bigger VPS) works until it doesn't. You eventually hit the hardware wall. Sharding—splitting your data horizontally across multiple nodes—is the answer. But before you slice your data, you need the right infrastructure. Sharding increases network chatter. If your nodes are on cheap, oversold cloud instances with high "steal time," your distributed queries will crawl.

Strategy 1: Key-Based Sharding (Hash Sharding)

This is the most common approach for SaaS platforms. You take a specific key (like `user_id` or `tenant_uuid`), hash it, and use the modulo operator to determine which database shard the data lives on.

The Logic:

def get_shard_id(user_id, total_shards):
    # Simple modulo sharding
    return user_id % total_shards

# Example: User 105 with 4 shards
# 105 % 4 = 1 -> Direct traffic to DB_Shard_1

This distributes load evenly. However, adding shards later requires rebalancing data, which is a massive pain. If you start with 4 shards on CoolVDS instances, plan to jump to 8 or 16. Powers of two make rehashing significantly easier.

Strategy 2: Directory-Based Sharding

Here, you maintain a lookup table (a "directory") that maps an ID to a specific shard. This service sits in front of your database cluster. It's flexible—you can move a heavy user to their own dedicated NVMe VPS without moving everyone else—but the lookup service becomes a single point of failure.

Pro Tip: Never host your lookup database on the same physical disk as your shards. If the I/O saturates on the shard, your lookup fails, and your entire app goes dark. On CoolVDS, we recommend using separate KVM instances for the Directory Service to ensure isolation.

The Technology Stack: What Works in 2021

Since the Schrems II ruling last year, moving data outside the EEA is a legal minefield. Hosting your shards on US-owned clouds introduces GDPR headaches. Using a Norwegian provider ensures data sovereignty. But software-wise, what should you use?

ProxySQL for MySQL Sharding

Don't implement sharding logic deeply in your application code if you can avoid it. Use middleware. ProxySQL is the battle-standard in 2021 for this. It speaks the MySQL protocol and routes queries based on rules.

Here is a configuration snippet for routing traffic based on sharding keys using ProxySQL:

-- Define the backend shards (hostgroups)
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, '10.0.0.1', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, '10.0.0.2', 3306);

-- Route queries based on user_id comments or hints
-- App sends: SELECT * FROM orders /* sharding_key=50 */
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, 'sharding_key=([0-9]+)', 10); -- Simplified logic for demo

PostgreSQL Partitioning

If you are on Postgres 12 or 13, declarative partitioning is robust. While it's technically partitioning (often on one node), you can use Foreign Data Wrappers (postgres_fdw) to push those partitions to different servers, effectively achieving sharding.

-- Create the parent table
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

-- Create partitions on different tablespaces (mapped to different disks/mounts)
CREATE TABLE measurement_y2021m01 PARTITION OF measurement
    FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');

Infrastructure Matters: The I/O Bottleneck

Sharding solves CPU limits, but it intensifies I/O requirements. Every shard needs fast disk access. In Norway, latency to the NIX (Norwegian Internet Exchange) is low, but disk latency is where battles are lost.

You must monitor your disk latency. Here is a quick `iostat` check you should run during peak load:

# Check extended stats, update every 2 seconds
iostat -x 2 

# Look at the 'await' column. 
# If await > 5ms on an NVMe drive, you are saturating the queue.

Many VPS providers cap your IOPS silently. This is fatal for sharded databases. Because CoolVDS is built on KVM with direct NVMe pass-through performance characteristics, you get the raw throughput your database engine expects. We don't use container-based virtualization for high-performance plans because the kernel overhead creates jitter.

Handling Schema Changes

How do you run `ALTER TABLE` across 16 shards without downtime? You can't just run it sequentially. In early 2021, the tool of choice is gh-ost (GitHub's Online Schema Transitions).

gh-ost \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=1000 \
  --chunk-size=1000 \
  --throttle-control-replicas="10.0.0.5:3306" \
  --alter="ADD COLUMN loyalty_points INT DEFAULT 0" \
  --database="shop_prod" \
  --table="users" \
  --execute

This tool creates a ghost table, syncs data, and swaps it atomically. It is safer than the native MySQL online DDL for massive tables.

The Latency Trap

If you split your database, your application might need to query multiple shards to assemble a view (Aggregation). This is dangerous. If you have 4 shards and your app needs data from all 4 to render the dashboard, your page load time is determined by the slowest shard.

The Solution: Application-side parallelism. Do not execute these queries serially in PHP or Python.

import concurrent.futures

def fetch_shard_data(shard_connection):
    return shard_connection.execute("SELECT sum(total) FROM orders")

# Bad: Serial execution
# total = sum([fetch_shard_data(conn) for conn in shards])

# Good: Parallel execution
with concurrent.futures.ThreadPoolExecutor(max_workers=4) as executor:
    futures = [executor.submit(fetch_shard_data, conn) for conn in shards]
    results = [f.result() for f in futures]

Conclusion: Complexity Requires Stability

Sharding is not a silver bullet. It is a complexity multiplier. It requires strict discipline in code deployment and robust infrastructure. In the post-Schrems II era, keeping this architecture within Norwegian borders isn't just about performance; it's about compliance.

When you are ready to split your monolith, you need a foundation that doesn't flinch under load. You need consistent I/O, predictable latency, and KVM isolation.

Don't let storage bottlenecks throttle your growth. Deploy your database shards on CoolVDS high-performance NVMe instances today and keep your latency in the green.