Database Scaling: Vertical vs Horizontal

Introduction

As your user base grows and data accumulates, your database will eventually become a bottleneck. Database scaling is one of the most critical technical decisions any successful application faces. This guide explains the available options and how to choose the right approach for your situation.

Types of Scaling

Vertical Scaling

The simplest approach: upgrade the existing server specifications (CPU, RAM, faster SSD).

Advantages:

• No code changes required
• Fast to implement
• Simple to manage

Disadvantages:

• Has a physical upper limit
• May require downtime
• Expensive at high tiers

When to use it: Always the first option. On cloud.net.sa, you can upgrade VPS specifications within minutes.

Horizontal Scaling

Adding multiple servers that work together.

Advantages:

• Theoretically unlimited scale
• No single point of failure
• Suitable for very large volumes

Disadvantages:

• Requires code modifications
• More complex to manage
• Requires distributed systems expertise

Practical Scaling Options

1. Read Replicas

Adding read servers that receive a copy of the primary database.

-- MySQL Master-Slave Replication setup
-- On the primary server (Master)
CHANGE MASTER TO
 MASTER_HOST='replica-server-ip',
 MASTER_USER='replication_user',
 MASTER_PASSWORD='strong_password';

// In Laravel, route reads to the replica
// config/database.php
'mysql' => [
 'read' => [
 'host' => ['replica1-ip', 'replica2-ip'],
 ],
 'write' => [
 'host' => ['master-ip'],
 ],
 'driver' => 'mysql',
 // ...
],

Best for: Applications where 80%+ of operations are reads (displaying data, reports).

2. Connection Pooling

Pooling connections instead of opening a new connection per request.

# Install PgBouncer for PostgreSQL
apt install pgbouncer

# Or ProxySQL for MySQL
apt install proxysql

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 100

This alone solves the "Too many connections" problem in many cases.

3. Database Caching with Redis

Reducing database load by caching results of frequent queries.

# Install Redis
apt install redis-server
systemctl enable redis-server

// Laravel: use cache for repeated queries
$users = Cache::remember('active_users', 3600, function {
 return User::where('active', true)->get;
});

// For rarely-changing data
$settings = Cache::rememberForever('app_settings', function {
 return Setting::all->keyBy('key');
});

4. Database Sharding

Splitting data across multiple databases based on a specific criterion.

// Example: splitting by geographic region
function getConnection(string $region): Connection {
 return match($region) {
 'riyadh' => DB::connection('db_riyadh'),
 'jeddah' => DB::connection('db_jeddah'),
 default => DB::connection('db_main'),
 };
}

Warning: Sharding is extremely complex to implement and maintain. Only consider it after exhausting simpler options.

Comparison Table

Solution	Complexity	Cost	Impact
Vertical Scaling	Low	Medium	Immediate
Read Replicas	Medium	Medium	High for reads
Connection Pooling	Low	Low	High
Redis Caching	Low	Low	Very high
Sharding	Very high	High	For massive scale

Indicators That You Need Scaling

• Response time consistently > 200ms
• Database CPU consistently > 70%
• "Too many connections" errors in logs
• Frequent deadlocks
• Disk approaching 80% capacity

Diagnose Before Scaling

# Enable slow query log in MySQL
mysql -u root -p << 'SQL'
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SQL

# Analyze a slow query
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

Adding the right index may solve the entire problem:

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Practical Recommendation

The correct order for resolving database performance problems:

1. Add missing indexes — free and immediate
2. Enable Redis caching — reduces 80% of repeated queries
3. Upgrade server specifications — easy and fast with cloud.net.sa
4. Add Read Replica — if the application relies on heavy reads
5. Connection Pooling — if connection count is the bottleneck
6. Sharding — only at millions of users

Summary

Do not jump directly to complex solutions. In most cases, adding an index or enabling Redis solves the problem. Horizontal scaling and sharding are for very advanced stages. Start simple, measure the impact, then move to the next step.