Database Read Replicas with Laravel: Optimizing Query Performance

Introduction

As your application grows, the database often becomes the first bottleneck. Read Replicas are a solution for distributing read queries to database copies, allowing the primary database to focus on write operations.

Why Read Replicas?

Without Replicas:
┌─────────────────┐
│    Primary DB   │  ← ALL reads + writes
│    (Overload)   │
└─────────────────┘

With Replicas:
┌─────────────────┐
│    Primary DB   │  ← writes only
└────────┬────────┘
         │ replication
    ┌────┴────┬────────┐
    ▼         ▼        ▼
┌───────┐ ┌───────┐ ┌───────┐
│Replica│ │Replica│ │Replica│  ← reads distributed
│   1   │ │   2   │ │   3   │
└───────┘ └───────┘ └───────┘

Benefits

Increased Read Throughput: Distribute reads across multiple replicas
Reduced Primary Load: Primary only handles writes
High Availability: Replicas can be promoted to primary
Geographic Distribution: Replicas closer to users
Analytics Isolation: Run heavy reports on replica

MySQL Replication Configuration

AWS RDS Setup

# terraform/rds.tf
resource "aws_db_instance" "primary" {
  identifier           = "laravel-primary"
  engine               = "mysql"
  engine_version       = "8.0"
  instance_class       = "db.r6g.large"
  allocated_storage    = 100
  storage_type         = "gp3"
  
  db_name              = "laravel"
  username             = "admin"
  password             = var.db_password
  
  vpc_security_group_ids = [aws_security_group.db.id]
  db_subnet_group_name   = aws_db_subnet_group.main.name
  
  backup_retention_period = 7
  backup_window           = "03:00-04:00"
  maintenance_window      = "Mon:04:00-Mon:05:00"
  
  multi_az               = true
  storage_encrypted      = true
  
  parameter_group_name = aws_db_parameter_group.mysql.name
  
  tags = {
    Name = "laravel-primary"
  }
}

resource "aws_db_instance" "replica_1" {
  identifier           = "laravel-replica-1"
  replicate_source_db  = aws_db_instance.primary.identifier
  instance_class       = "db.r6g.large"
  
  vpc_security_group_ids = [aws_security_group.db.id]
  
  storage_encrypted    = true
  skip_final_snapshot  = true
  
  tags = {
    Name = "laravel-replica-1"
  }
}

resource "aws_db_instance" "replica_2" {
  identifier           = "laravel-replica-2"
  replicate_source_db  = aws_db_instance.primary.identifier
  instance_class       = "db.r6g.large"
  
  vpc_security_group_ids = [aws_security_group.db.id]
  
  storage_encrypted    = true
  skip_final_snapshot  = true
  
  tags = {
    Name = "laravel-replica-2"
  }
}

Laravel Configuration

// config/database.php
'mysql' => [
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', 'replica-1.example.com'),
            env('DB_READ_HOST_2', 'replica-2.example.com'),
        ],
    ],
    'write' => [
        'host' => env('DB_WRITE_HOST', 'primary.example.com'),
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'database' => env('DB_DATABASE', 'laravel'),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        PDO::ATTR_PERSISTENT => false,
    ]) : [],
],

Environment Variables

# Primary (Write)
DB_WRITE_HOST=laravel-primary.xxxxx.us-east-1.rds.amazonaws.com

# Replicas (Read)
DB_READ_HOST_1=laravel-replica-1.xxxxx.us-east-1.rds.amazonaws.com
DB_READ_HOST_2=laravel-replica-2.xxxxx.us-east-1.rds.amazonaws.com

DB_DATABASE=laravel
DB_USERNAME=admin
DB_PASSWORD=your-secure-password

PostgreSQL Read Replicas

Configuration

// config/database.php
'pgsql' => [
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', 'replica-1.example.com'),
            env('DB_READ_HOST_2', 'replica-2.example.com'),
        ],
        'port' => env('DB_READ_PORT', '5432'),
    ],
    'write' => [
        'host' => env('DB_WRITE_HOST', 'primary.example.com'),
        'port' => env('DB_WRITE_PORT', '5432'),
    ],
    'sticky' => true,
    'driver' => 'pgsql',
    'database' => env('DB_DATABASE', 'laravel'),
    'username' => env('DB_USERNAME', 'postgres'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    'search_path' => 'public',
    'sslmode' => 'require',
],

PgBouncer for Connection Pooling

; /etc/pgbouncer/pgbouncer.ini
[databases]
; Primary for writes
laravel_write = host=primary.example.com port=5432 dbname=laravel

; Replicas for reads (load balanced)
laravel_read = host=replica-1.example.com,replica-2.example.com port=5432 dbname=laravel load_balance_hosts=round-robin

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Connection pooling settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 25
reserve_pool_timeout = 5

Understanding Sticky Sessions

The Replication Lag Problem

// Without sticky sessions - potential issue
$user = User::create(['name' => 'John']);  // Write to primary

// Immediately query replica - data might not be synced
$user = User::find($user->id);  // Read from replica - might be NULL!

Sticky Sessions Solution

// config/database.php
'sticky' => true,  // Enable sticky sessions

// With sticky = true
$user = User::create(['name' => 'John']);  // Write to primary
// Laravel automatically uses primary for subsequent reads in this request
$user = User::find($user->id);  // Read from PRIMARY (safe!)

How Sticky Sessions Work

// Inside Laravel DatabaseManager
public function select($query, $bindings = [], $useReadPdo = true)
{
    // If sticky enabled and there was a write in this request
    if ($this->recordsModified && $this->getConfig('sticky')) {
        $useReadPdo = false;  // Force use write connection
    }
    
    return $this->run($query, $bindings, function ($query, $bindings) use ($useReadPdo) {
        $pdo = $useReadPdo ? $this->getReadPdo() : $this->getPdo();
        // ...
    });
}

Query Routing Strategies

1. Automatic Routing (Default)

// Laravel automatically routes queries
User::all();           // → Read replica
User::create([...]);   // → Write primary
User::find(1);         // → Read replica (or primary if sticky)

2. Force Write Connection

// When you need the latest data
$user = User::onWriteConnection()->find($userId);

// Or with query builder
$users = DB::connection('mysql')
    ->useWritePdo()
    ->table('users')
    ->where('active', true)
    ->get();

3. Explicit Connection Selection

// app/Services/ReportService.php
namespace App\Services;

use Illuminate\Support\Facades\DB;

class ReportService
{
    public function generateHeavyReport(): array
    {
        // Force read from replica for heavy queries
        return DB::connection('mysql')
            ->useReadPdo()
            ->table('orders')
            ->join('order_items', 'orders.id', '=', 'order_items.order_id')
            ->join('products', 'order_items.product_id', '=', 'products.id')
            ->selectRaw('
                DATE(orders.created_at) as date,
                COUNT(DISTINCT orders.id) as order_count,
                SUM(order_items.quantity * order_items.price) as revenue
            ')
            ->where('orders.created_at', '>=', now()->subDays(30))
            ->groupBy('date')
            ->get()
            ->toArray();
    }
}

4. Custom Read/Write Logic

// app/Database/SmartConnection.php
namespace App\Database;

use Illuminate\Database\MySqlConnection;

class SmartConnection extends MySqlConnection
{
    protected array $writeOperations = [
        'insert', 'update', 'delete', 'truncate',
        'create', 'alter', 'drop', 'lock', 'unlock'
    ];
    
    public function select($query, $bindings = [], $useReadPdo = true)
    {
        // Force write for specific queries
        if ($this->shouldForceWrite($query)) {
            $useReadPdo = false;
        }
        
        return parent::select($query, $bindings, $useReadPdo);
    }
    
    protected function shouldForceWrite(string $query): bool
    {
        // Force write connection for SELECT ... FOR UPDATE
        if (str_contains(strtoupper($query), 'FOR UPDATE')) {
            return true;
        }
        
        // Force write for queries with hint
        if (str_contains($query, '/*force_primary*/')) {
            return true;
        }
        
        return false;
    }
}

Handling Replication Lag

1. Monitoring Lag

// app/Services/ReplicationMonitor.php
namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;

class ReplicationMonitor
{
    public function checkLag(): array
    {
        $results = [];
        
        // Check MySQL replication lag
        $replicas = config('database.connections.mysql.read.host');
        
        foreach ($replicas as $index => $host) {
            try {
                $lag = DB::connection('mysql_replica_' . $index)
                    ->selectOne("SHOW SLAVE STATUS");
                
                $results[$host] = [
                    'seconds_behind_master' => $lag->Seconds_Behind_Master ?? null,
                    'slave_io_running' => $lag->Slave_IO_Running ?? 'Unknown',
                    'slave_sql_running' => $lag->Slave_SQL_Running ?? 'Unknown',
                ];
            } catch (\Exception $e) {
                $results[$host] = [
                    'error' => $e->getMessage(),
                ];
            }
        }
        
        return $results;
    }
    
    public function isLagAcceptable(int $maxLagSeconds = 5): bool
    {
        $lag = $this->checkLag();
        
        foreach ($lag as $host => $status) {
            if (isset($status['seconds_behind_master']) 
                && $status['seconds_behind_master'] > $maxLagSeconds) {
                return false;
            }
        }
        
        return true;
    }
}

2. Lag-Aware Queries

// app/Traits/LagAwareQueries.php
namespace App\Traits;

use Illuminate\Support\Facades\Cache;

trait LagAwareQueries
{
    protected function queryWithLagCheck(callable $query, int $maxLagSeconds = 2)
    {
        $lag = Cache::remember('db_replication_lag', 10, function () {
            return app(ReplicationMonitor::class)->checkLag();
        });
        
        // If lag is too high, force primary
        foreach ($lag as $status) {
            if (($status['seconds_behind_master'] ?? 0) > $maxLagSeconds) {
                return $this->onWriteConnection()->tap($query);
            }
        }
        
        return $query();
    }
}

// Usage
class UserRepository
{
    use LagAwareQueries;
    
    public function findCriticalUser(int $id)
    {
        return $this->queryWithLagCheck(function () use ($id) {
            return User::find($id);
        }, maxLagSeconds: 1);
    }
}

3. Write-then-Read Pattern

// app/Services/OrderService.php
namespace App\Services;

use App\Models\Order;
use Illuminate\Support\Facades\DB;

class OrderService
{
    public function createOrder(array $data): Order
    {
        return DB::transaction(function () use ($data) {
            // Create order
            $order = Order::create($data);
            
            // Load relationships within transaction
            // Ensures read from primary
            $order->load(['items', 'customer', 'shipping']);
            
            return $order;
        });
    }
    
    public function updateAndFetch(int $orderId, array $data): Order
    {
        DB::transaction(function () use ($orderId, $data) {
            Order::where('id', $orderId)->update($data);
        });
        
        // Force read from primary after update
        return Order::onWriteConnection()
            ->with(['items', 'customer'])
            ->findOrFail($orderId);
    }
}

Failover Handling

1. Health Check

// app/Services/DatabaseHealthCheck.php
namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class DatabaseHealthCheck
{
    public function checkConnections(): array
    {
        $status = [];
        
        // Check primary
        $status['primary'] = $this->checkConnection('mysql', 'write');
        
        // Check replicas
        $replicas = config('database.connections.mysql.read.host');
        foreach ($replicas as $index => $host) {
            $status["replica_{$index}"] = $this->checkConnection('mysql', 'read', $index);
        }
        
        return $status;
    }
    
    private function checkConnection(string $connection, string $type, ?int $index = null): array
    {
        try {
            $start = microtime(true);
            
            $pdo = $type === 'write' 
                ? DB::connection($connection)->getPdo()
                : DB::connection($connection)->getReadPdo();
            
            $pdo->query('SELECT 1');
            
            return [
                'status' => 'healthy',
                'latency_ms' => round((microtime(true) - $start) * 1000, 2),
            ];
        } catch (\Exception $e) {
            Log::error("Database connection failed", [
                'connection' => $connection,
                'type' => $type,
                'index' => $index,
                'error' => $e->getMessage(),
            ]);
            
            return [
                'status' => 'unhealthy',
                'error' => $e->getMessage(),
            ];
        }
    }
}

2. Circuit Breaker Pattern

// app/Database/CircuitBreaker.php
namespace App\Database;

use Illuminate\Support\Facades\Cache;

class CircuitBreaker
{
    private const STATE_CLOSED = 'closed';
    private const STATE_OPEN = 'open';
    private const STATE_HALF_OPEN = 'half_open';
    
    public function __construct(
        private string $service,
        private int $failureThreshold = 5,
        private int $recoveryTime = 30
    ) {}
    
    public function execute(callable $operation)
    {
        $state = $this->getState();
        
        if ($state === self::STATE_OPEN) {
            if ($this->shouldAttemptRecovery()) {
                $this->setState(self::STATE_HALF_OPEN);
            } else {
                throw new CircuitBreakerOpenException("Circuit breaker is open for {$this->service}");
            }
        }
        
        try {
            $result = $operation();
            $this->recordSuccess();
            return $result;
        } catch (\Exception $e) {
            $this->recordFailure();
            throw $e;
        }
    }
    
    private function getState(): string
    {
        return Cache::get("circuit_breaker:{$this->service}:state", self::STATE_CLOSED);
    }
    
    private function setState(string $state): void
    {
        Cache::put("circuit_breaker:{$this->service}:state", $state, 3600);
    }
    
    private function recordSuccess(): void
    {
        $state = $this->getState();
        
        if ($state === self::STATE_HALF_OPEN) {
            $this->setState(self::STATE_CLOSED);
            Cache::forget("circuit_breaker:{$this->service}:failures");
        }
    }
    
    private function recordFailure(): void
    {
        $failures = Cache::increment("circuit_breaker:{$this->service}:failures");
        
        if ($failures >= $this->failureThreshold) {
            $this->setState(self::STATE_OPEN);
            Cache::put("circuit_breaker:{$this->service}:opened_at", now()->timestamp, 3600);
        }
    }
    
    private function shouldAttemptRecovery(): bool
    {
        $openedAt = Cache::get("circuit_breaker:{$this->service}:opened_at", 0);
        return (now()->timestamp - $openedAt) >= $this->recoveryTime;
    }
}

Best Practices

1. Query Hints

// app/Models/User.php
class User extends Model
{
    public function scopeForcePrimary($query)
    {
        return $query->useWritePdo();
    }
    
    public function scopeForceReplica($query)
    {
        return $query->useReadPdo();
    }
}

// Usage
$user = User::forcePrimary()->find($id);
$users = User::forceReplica()->active()->get();

2. Monitoring Queries

// app/Providers/AppServiceProvider.php
public function boot(): void
{
    DB::listen(function ($query) {
        $connection = $query->connectionName;
        $time = $query->time;
        
        // Log slow queries
        if ($time > 1000) {
            Log::warning('Slow query detected', [
                'sql' => $query->sql,
                'bindings' => $query->bindings,
                'time' => $time,
                'connection' => $connection,
            ]);
        }
    });
}

Conclusion

Read Replicas help:

Scale read capacity without affecting writes
Improve performance by distributing queries
Increase availability with failover options
Isolate workloads (analytics vs production)

Implementation Checklist

Configure read/write connections
Enable sticky sessions
Setup replication lag monitoring
Implement health checks
Configure connection pooling
Test failover scenarios
Setup alerting for lag > threshold