Database Sharding: The Nuclear Option for Scaling (And How Not to Blow Up)

Let’s be brutally honest: if you are reading this because you think you need to shard your database, you probably don't. In my fifteen years managing systems across the Nordics, I’ve seen more startups kill their velocity by implementing sharding prematurely than I have seen fail due to monolithic limits.

Sharding is not a feature. It is an admission that your dataset has grown too large for a single physical node to handle. It introduces network latency, transaction complexity, and maintenance nightmares. However, when you hit the ceiling of vertical scaling—somewhere around the 64 vCPU / 512GB RAM mark—it becomes the only path forward. If your iostat is screaming and your write-master is locking up during peak Oslo business hours, it is time to perform surgery.

The "Scale Up" Reality Check

Before we start cutting tables apart, look at your infrastructure. Are you running on shared hosting or a budget VPS with "burstable" CPU? That is your bottleneck.

Pro Tip: Run iostat -mx 2 during peak load. If your %util on the disk is consistently hitting 90%+, your problem isn't the database architecture; it's the storage IOPS. Sharding won't fix bad hardware.

In 2021, NVMe storage is not a luxury; it is a requirement for serious databases. We benchmarked a standard SSD VPS against a CoolVDS NVMe KVM instance. The difference in transaction throughput for a standard sysbench OLTP test was 4x. Before you rewrite your application logic to support sharding, migrate to a high-frequency compute node with dedicated resources. It is cheaper to pay for a larger server than to pay three engineers to maintain a sharded cluster.

When You Actually Need to Shard

You need to shard when:

• Write Throughput: You are exceeding the IOPS limit of a single NVMe controller (rare, but happens).
• Storage Size: Your dataset exceeds the maximum disk size feasible for quick backups/restores (usually > 2TB).
• Geography/GDPR: You need to keep Norwegian user data in Norway (for Datatilsynet compliance) while serving German users from Frankfurt.

Strategy 1: Application-Level Sharding (The "Manual" Way)

This is the most common approach for PHP/Python shops. Your application code decides which database node to connect to based on a shard_key, usually the user_id or company_id.

The logic is simple but brutal:

The Trade-off: You lose ACID transactions across shards. You cannot JOIN a table on Node 1 with a table on Node 2 without expensive application-level aggregation. Also, re-balancing (moving users from Node 1 to Node 5) requires custom scripts and downtime.

Strategy 2: PostgreSQL Native Partitioning (The "Sane" Way)

If you are on Postgres 13 (current stable as of late 2021), you have access to robust declarative partitioning. While partitioning is technically splitting tables on one node, it is the precursor to sharding using Postgres_FDW (Foreign Data Wrappers) or Citus.

Here is how you set up a time-series partition for log data, which is a classic use case here in Norway for maritime IoT data logs:

-- Parent table
CREATE TABLE sensor_logs (
    id bigserial,
    sensor_id int,
    log_date date not null,
    payload jsonb
) PARTITION BY RANGE (log_date);

-- Partitions
CREATE TABLE sensor_logs_y2021m09 PARTITION OF sensor_logs
    FOR VALUES FROM ('2021-09-01') TO ('2021-10-01');

CREATE TABLE sensor_logs_y2021m10 PARTITION OF sensor_logs
    FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');

-- Indexing the partition key is mandatory for performance
CREATE INDEX ON sensor_logs (log_date);

By moving older partitions to cheaper storage (or archiving them), you keep your hot dataset small and RAM-resident. On CoolVDS, you can attach block storage volumes for cold data while keeping the active partition on the local NVMe.

Strategy 3: Middleware Routing (ProxySQL for MySQL)

For the MySQL ecosystem (including MariaDB), ProxySQL is the gold standard in 2021. It sits between your app and your DB nodes. It speaks the MySQL protocol, so your app thinks it's talking to a single server.

You can configure query rules to route traffic. For example, sending all writes to the master and reads to replicas, or sharding based on comments.

Configuration Snippet for ProxySQL Sharding:

-- Insert sharding rules into ProxySQL admin interface
-- Route user_id < 1000 to Hostgroup 10
INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, destination_hostgroup, apply)
VALUES (10, 1, 'app_user', '^SELECT.*WHERE user_id < 1000', 10, 1);

-- Route user_id >= 1000 to Hostgroup 20
INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, destination_hostgroup, apply)
VALUES (11, 1, 'app_user', '^SELECT.*WHERE user_id >= 1000', 20, 1);

-- Load to runtime
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

This allows you to add nodes transparently. However, ProxySQL adds a hop. If your network latency between the app server and the ProxySQL instance, or ProxySQL and the DB, is high, your app will feel sluggish.

The Latency Tax & The Infrastructure Factor

This is where infrastructure choice dictates architecture. In a distributed sharded setup, the network is the computer. A query might hit a router, then a shard, then return. Every millisecond of latency accumulates.

If you are hosting in Norway, you want your nodes communicating over a private, high-speed backplane. Public internet routing is too unpredictable.

Why CoolVDS Works Here:

• KVM Isolation: Unlike containers (LXC/OpenVZ), KVM provides a hard guarantee on RAM and CPU. In a sharded cluster, if one "noisy neighbor" slows down Shard #3, 25% of your users suffer. We don't oversubscribe CPU on our performance tiers for this exact reason.
• Internal Network: Our internal datacenter throughput allows for near-instant communication between app nodes and database shards.
• Compliance: With the Schrems II ruling shaking up the industry last year, hosting data on US-owned clouds is a legal minefield. Keeping your shards physically in Oslo simplifies your GDPR posture immediately.

Quick Implementation Checklist (2021 Edition)

Final Thoughts: Don't Be a Hero

Complexity is the enemy of reliability. Start with a single, powerful CoolVDS node with optimized config. Only shard when physics leaves you no choice. And when you do, ensure your underlying infrastructure—the virtualization and the network—is rock solid. A sharded database on unstable hardware is just a distributed disaster waiting to happen.

Ready to test your limits? Deploy a high-performance KVM instance in our Oslo datacenter in under 55 seconds. Configure your CoolVDS server here.