Репликация и шардирование

Зачем распределять данные

Одна база данных имеет пределы: максимальное количество соединений, ограниченная пропускная способность записи, конечный объем хранения. Репликация и шардирование решают эти ограничения разными способами.

Репликация

Master-Slave (Primary-Replica)

Один узел принимает записи (master), остальные получают копии данных (slaves/replicas).

<?php

declare(strict_types=1);

/**
 * Master-Slave replication pattern
 *
 * Write path: Client -> Master -> (async replication) -> Replicas
 * Read path:  Client -> Any Replica (or Master)
 *
 * Pros:
 * - Read scalability: add more replicas
 * - High availability: promote replica on master failure
 * - Read/write separation
 *
 * Cons:
 * - Replication lag (replicas may be slightly behind)
 * - Single write point (master is bottleneck for writes)
 * - Failover complexity
 */
final class MasterSlaveConnectionPool
{
    private \PDO $master;

    /** @var array<\PDO> */
    private array $replicas;

    private int $replicaIndex = 0;

    public function __construct(
        string $masterDsn,
        array $replicaDsns,
        string $user,
        string $password,
    ) {
        $options = [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
        ];

        $this->master = new \PDO($masterDsn, $user, $password, $options);

        $this->replicas = array_map(
            fn(string $dsn) => new \PDO($dsn, $user, $password, $options),
            $replicaDsns,
        );
    }

    public function getWriter(): \PDO
    {
        return $this->master;
    }

    /**
     * Round-robin across replicas.
     * Falls back to master if no replicas are available.
     */
    public function getReader(): \PDO
    {
        if (empty($this->replicas)) {
            return $this->master;
        }

        $replica = $this->replicas[$this->replicaIndex];
        $this->replicaIndex = ($this->replicaIndex + 1) % count($this->replicas);

        return $replica;
    }

    /**
     * For read-your-writes consistency:
     * After a write, read from master for a short window.
     */
    public function getReaderForUser(string $userId, \Redis $cache): \PDO
    {
        $lastWrite = $cache->get("last_write:$userId");

        if ($lastWrite !== false && (time() - (int) $lastWrite) < 5) {
            // User just wrote something: read from master
            // to ensure they see their own writes
            return $this->master;
        }

        return $this->getReader();
    }
}

Master-Master (Multi-Leader)

Несколько узлов принимают записи. Каждый реплицирует изменения другим.

<?php

declare(strict_types=1);

/**
 * Master-Master replication
 *
 * Both nodes accept writes and replicate to each other.
 *
 * Pros:
 * - Write availability (both nodes can write)
 * - Geographic distribution (write locally)
 *
 * Cons:
 * - Conflict resolution is complex
 * - Not suitable for all use cases
 * - Data divergence risk
 *
 * Conflict resolution strategies:
 * 1. Last-writer-wins (timestamp)
 * 2. Application-level merge
 * 3. Custom conflict handlers
 */

// Conflict resolution: Last-Writer-Wins with versioning
final class ConflictAwareRepository
{
    public function __construct(
        private readonly \PDO $localDb,
    ) {}

    /**
     * Update with optimistic concurrency control.
     * Uses version column to detect conflicts.
     */
    public function update(string $id, array $data, int $expectedVersion): bool
    {
        $data['version'] = $expectedVersion + 1;
        $data['updated_at'] = (new \DateTimeImmutable())->format('Y-m-d H:i:s.u');

        $setClauses = [];
        $params = ['id' => $id, 'expected_version' => $expectedVersion];

        foreach ($data as $column => $value) {
            $setClauses[] = "$column = :$column";
            $params[$column] = $value;
        }

        $sql = sprintf(
            'UPDATE documents SET %s WHERE id = :id AND version = :expected_version',
            implode(', ', $setClauses),
        );

        $stmt = $this->localDb->prepare($sql);
        $stmt->execute($params);

        if ($stmt->rowCount() === 0) {
            // Version mismatch: someone else updated this record
            // Application must handle the conflict
            return false;
        }

        return true;
    }

    /**
     * Merge conflict resolution for concurrent updates.
     * Strategy: field-level merge (like Git for data).
     */
    public function mergeConflict(
        array $baseVersion,
        array $localVersion,
        array $remoteVersion,
    ): array {
        $merged = $baseVersion;

        foreach (array_keys($baseVersion) as $field) {
            $localChanged = $localVersion[$field] !== $baseVersion[$field];
            $remoteChanged = $remoteVersion[$field] !== $baseVersion[$field];

            if ($localChanged && !$remoteChanged) {
                // Only local changed: take local
                $merged[$field] = $localVersion[$field];
            } elseif (!$localChanged && $remoteChanged) {
                // Only remote changed: take remote
                $merged[$field] = $remoteVersion[$field];
            } elseif ($localChanged && $remoteChanged) {
                // Both changed: need conflict resolution
                if ($localVersion[$field] === $remoteVersion[$field]) {
                    // Same change: no conflict
                    $merged[$field] = $localVersion[$field];
                } else {
                    // True conflict: take the latest by timestamp
                    $merged[$field] = $this->resolveByTimestamp(
                        $localVersion,
                        $remoteVersion,
                        $field,
                    );
                }
            }
        }

        return $merged;
    }

    private function resolveByTimestamp(array $local, array $remote, string $field): mixed
    {
        return $local['updated_at'] > $remote['updated_at']
            ? $local[$field]
            : $remote[$field];
    }
}

Шардирование

Шардирование по хэшу ключа

Данные распределяются по шардам на основе хэша ключа. Обеспечивает равномерное распределение.

<?php

declare(strict_types=1);

/**
 * Hash-based sharding: distribute data by hash of the key
 *
 * Pros:
 * - Even data distribution
 * - Simple routing logic
 *
 * Cons:
 * - Range queries require querying all shards
 * - Adding/removing shards requires data redistribution
 */
final class HashShardRouter
{
    /** @var array<\PDO> */
    private array $shards;

    public function __construct(array $shardConnections)
    {
        $this->shards = $shardConnections;
    }

    /**
     * Determine which shard holds data for a given key.
     */
    public function getShard(string $key): \PDO
    {
        $hash = crc32($key);
        $shardIndex = abs($hash) % count($this->shards);

        return $this->shards[$shardIndex];
    }

    /**
     * Execute a query on the correct shard.
     */
    public function queryByShard(string $shardKey, string $sql, array $params = []): array
    {
        $shard = $this->getShard($shardKey);
        $stmt = $shard->prepare($sql);
        $stmt->execute($params);

        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * Execute a query across ALL shards (scatter-gather).
     * Needed for queries that can't be routed to a single shard.
     * EXPENSIVE: avoid when possible.
     */
    public function queryAllShards(string $sql, array $params = []): array
    {
        $allResults = [];

        foreach ($this->shards as $shard) {
            $stmt = $shard->prepare($sql);
            $stmt->execute($params);
            $results = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            $allResults = array_merge($allResults, $results);
        }

        return $allResults;
    }
}

// Sharded user repository
final class ShardedUserRepository
{
    public function __construct(
        private readonly HashShardRouter $router,
    ) {}

    public function findById(string $userId): ?array
    {
        // Shard key = userId: always routes to the same shard
        $results = $this->router->queryByShard(
            $userId,
            'SELECT * FROM users WHERE id = :id',
            ['id' => $userId],
        );

        return $results[0] ?? null;
    }

    public function findByEmail(string $email): ?array
    {
        // Email is NOT the shard key!
        // Must query all shards (scatter-gather)
        $results = $this->router->queryAllShards(
            'SELECT * FROM users WHERE email = :email',
            ['email' => $email],
        );

        return $results[0] ?? null;
    }

    public function save(array $user): void
    {
        $shard = $this->router->getShard($user['id']);

        $shard->prepare(
            'INSERT INTO users (id, name, email, created_at)
             VALUES (:id, :name, :email, NOW())
             ON CONFLICT (id) DO UPDATE SET
                name = EXCLUDED.name,
                email = EXCLUDED.email'
        )->execute([
            'id' => $user['id'],
            'name' => $user['name'],
            'email' => $user['email'],
        ]);
    }
}

Шардирование по диапазону

Данные распределяются по шардам на основе диапазона ключей.

<?php

declare(strict_types=1);

/**
 * Range-based sharding: distribute by key range
 *
 * Pros:
 * - Efficient range queries (single shard scan)
 * - Easy to understand data placement
 *
 * Cons:
 * - Hotspots: popular ranges get more traffic
 * - Uneven data distribution
 * - Manual rebalancing needed
 */
final class RangeShardRouter
{
    /** @var array<array{min: string, max: string, shard: \PDO}> */
    private array $ranges;

    public function __construct(array $rangeConfig)
    {
        // Sort by range for binary search
        usort($rangeConfig, fn($a, $b) => strcmp($a['min'], $b['min']));
        $this->ranges = $rangeConfig;
    }

    public function getShard(string $key): \PDO
    {
        foreach ($this->ranges as $range) {
            if ($key >= $range['min'] && $key < $range['max']) {
                return $range['shard'];
            }
        }

        // Last range catches everything above
        return end($this->ranges)['shard'];
    }

    /**
     * Range query: may need to query multiple shards.
     * But fewer shards than scatter-gather.
     */
    public function queryRange(string $fromKey, string $toKey, string $sql, array $params): array
    {
        $results = [];

        foreach ($this->ranges as $range) {
            // Check if this shard's range overlaps with the query range
            if ($range['max'] > $fromKey && $range['min'] < $toKey) {
                $stmt = $range['shard']->prepare($sql);
                $stmt->execute(array_merge($params, [
                    'from_key' => max($fromKey, $range['min']),
                    'to_key' => min($toKey, $range['max']),
                ]));
                $results = array_merge($results, $stmt->fetchAll(\PDO::FETCH_ASSOC));
            }
        }

        return $results;
    }
}

// Example: time-based sharding for events
// Shard 1: 2024-01 to 2024-06
// Shard 2: 2024-07 to 2024-12
// Shard 3: 2025-01 to 2025-06

// Range queries like "events from March to April 2024"
// only need to query Shard 1

Выбор ключа шардирования

Выбор ключа -- критически важное решение. Неправильный ключ приведет к hot spots и неравномерной нагрузке.

<?php

declare(strict_types=1);

/**
 * Shard key selection guidelines
 */

// GOOD shard keys:
// - user_id: evenly distributed, most queries include it
// - order_id: unique per order, good for order service
// - tenant_id: natural isolation for multi-tenant systems

// BAD shard keys:
// - created_at: all new writes go to the latest shard (hot spot)
// - country: uneven distribution (US shard overloaded)
// - status: very few values, poor distribution

// COMPOUND shard keys for time-series data:
// Combine entity ID with time bucket to distribute writes
final class TimeSeriesShardKey
{
    /**
     * Generate a shard key that distributes time-series data evenly.
     * Combines entity ID (for distribution) with time bucket (for locality).
     */
    public static function generate(string $entityId, \DateTimeImmutable $timestamp): string
    {
        // Time bucket: hourly
        $bucket = $timestamp->format('YmdH');

        // Combine: entity determines shard, time provides locality
        return sprintf('%s:%s', $entityId, $bucket);
    }
}

Сравнение подходов

Когда шардировать

Сигналы

• Размер БД превышает возможности одного сервера
• Write throughput упирается в лимит master-узла
• Специфические таблицы стали слишком большими

Альтернативы перед шардированием

Шардирование -- это сложно. Попробуйте сначала:

1. Оптимизация запросов -- EXPLAIN ANALYZE
2. Индексы -- правильные составные индексы
3. Вертикальное масштабирование -- более мощный сервер
4. Read replicas -- для read-heavy нагрузок
5. Кэширование -- Redis/Memcached
6. Архивация -- перенос старых данных

Выводы

Репликация и шардирование -- это мощные инструменты, но они вносят значительную сложность. Репликация подходит для масштабирования чтения и повышения доступности. Шардирование -- для масштабирования записи и хранения. Выбирайте стратегию исходя из конкретных потребностей и не шардируйте преждевременно.