Partage et réplication de bases de données

Partage et réplication de bases de données

1) Pourquoi est-ce nécessaire

• Bande passante (croissance horizontale write QPS).
• Disponibilité (failover rapide, pas de point de panne unique).
• Localisation des données (multi-région, faible latence).
• Isolation des voisins bruyants (hot tenants/hot keys).

2) Termes de base et modèles de cohérence

Primary/Leader ↔ Replica/Follower : enregistrement sur le leader, lecture sur les répliques.
Réplication synchrone : confirmation de transaction après écriture sur N nœuds (RPO faible, latence supérieure).
Asynchrone : le leader fixe le commit et envoie le journal plus tard (RPO> 0, latence faible).
Quorum (Raft/Paxos) : écriture sur la plupart des nœuds ; un seul logue, un leader automatique.
Read-after-write : lecture garantie de ses enregistrements (voir § 5).

Le CAP dans le commerce est lu comme suit : en cas de problèmes de réseau, vous choisissez la cohérence (CP) ou la disponibilité (AP) pour les opérations critiques, souvent en combinant les niveaux sur différents chemins.

3) Réplication : options et pratiques

3. 1 Physique et logique

Physique (WAL/redo/binlog) : plus proche du journal en bloc, simple et rapide ; limité à la topologie/version homogène.
Logique : flux DML/DDL au niveau des lignes/tables ; permet des répliques partielles, des migrations entre versions, CDC pour DWH/streaming.

3. 2 Configuration et gestion

Contrôler lag (temps/octets/LSN).
Limitez le feedback hot-standby et les longues requêtes sur les répliques (afin de ne pas arrêter VACUUM/Clining).
Pour MySQL - GTID et Orchestrator ; для PostgreSQL — Patroni/replication slots, synchronous_standby_names.

sql
-- на лидере
ALTER SYSTEM SET synchronous_commit = 'on';
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (standby_a, standby_b)';
SELECT pg_reload_conf();

sql
SET GLOBAL enforce_gtid_consistency = ON;
SET GLOBAL gtid_mode = ON; -- перезапуск
CHANGE MASTER TO MASTER_AUTO_POSITION=1;

3. 3 Topologies

1→N (leader → répliques) + cascades (réplique fusionne plus loin).
Multi-primary (active-active) - éviter dans OLTP sans gestion de conflit stricte.
Quorum-cluster (Raft) - CockroachDB/Yugabyte/PG-Raft-compléments.

4) Read/Write Split et routage

Écrivez toujours en leader ; Lisez les répliques, mais prenez en compte lag.

1. Session stick....: après un enregistrement réussi, le client lit avec le leader pendant « Δ T ».

2. Gate LSN/GTID : le client dit « je ne veux pas vieillir LSN = X », le routeur envoie à la réplique dont LSN ≥ X.

3. Stale-ok : une partie des requêtes permet des données obsolètes (répertoires/bandes).

Outils : PgBouncer/Pg....-II (Postgres), ProxySQL/MaxScale (MySQL), Vitess (routage de chardons).

Exemple de gate LSN (idée) : Conservez 'pg _ current _ wal _ lsn ()' dans le heder/cookie HTTP et demandez au routeur une réplique avec 'pg _ last _ wal _ replay _ lsn () ≥ LSN'.

5) Stratégies de Sharding

5. 1 Sélection de clé

• Hash par 'tenant _ id '/' user _ id' - uniformément, mais prive les range-scans.
• Range dans le temps/ID - excellent pour time-series/archive, mais risque hot-shard.
• Consistent hashing - simplifie l'ajout/suppression de chardons.
• Directory/lookup-table - flexible (n'importe quel algorithme), mais une autre table/cache.

5. 2 Modèles

Shared-nothing : chaque shard est une base de données/cluster distincte, l'application connaît le routage.
Middleware-sharding : Vitess (MySQL), Citus (Postgres), le niveau Proxy cache la topologie.
Fédération : division des domaines de données par service (catalogue, payments, auth).

5. 3 clés composites

Utilisez l'espace clé : '{tenant} : {entity} : {id}' et gardez-le dans l'application et le cache. Для Postgres — hash partitioning + LIST/RANGE subpartition.

sql
CREATE TABLE orders (
tenant_id int,
id     bigint,
created_at timestamptz,
...,
PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);

CREATE TABLE orders_t0 PARTITION OF orders FOR VALUES WITH (MODULUS 16, REMAINDER 0);
--... t1..t15

6) Génération d'identifiants

Évitez les incréments auto monotones « chauds » sur le charding.
Utilisez un ID de type snowflake 64 bits (time + region + shard + seq) ou un ULID/KSUID (monotonie et distribution).
Для Postgres — sequence per shard; pour MySQL - auto_increment_increment/offset (différents offsets sur les leaders des chards).

7) Transplantation et migration en ligne

Principes clés : double écriture (dual-write), idempotence, double routage temporaire.

1. Ajoutez un nouveau shard/cluster.

2. Activez la double lecture (vérification de cohérence).

3. Activer la dual-write (dans les deux boules), enregistrer les écarts.

4. Exécutez backfill des données historiques (batchi, réplication logique/CDC).

5. Changer la « source de vérité » en une nouvelle boule ; laissez la synchronisation « queue ».

6. Éteignez l'ancien.

Outils : Vitess Resharding, Citus move shards, pg_logical/pgoutput, Debezium (CDC), gh-ost/pt-online-schema-change (DDL sans verrous).

8) Multi-région et géo-distribution

Leader-follower per region : lecture locale, écriture - via un leader mondial (modèle simple, mais RTT cross-region).
Multi-leader : enregistrement dans les deux régions - besoin de conflit-merjing (timestamp/version/CRDT).
True distributed SQL (Raft) : CockroachDB/Yugabyte - les données sont « collées » à la région, les requêtes vont au quorum local.

• Argent/commandes - CP (quorum/leader), catalogues/bandes - AP (cache, eventual).
• Planifiez toujours le write fencing (clés uniques/versioning) avec un éventuel split-brain.

9) Cohérence dans la pratique

Read-your-writes : un leader ou une réplique qui a « rattrapé » LSN/GTID.
Monotonic reads : « pas plus vieux » que le dernier LSN lu.
Write-conflict control: `SELECT... FOR UPDATE ', version (' xmin '/' rowversion '), UPSERT avec vérification de version.
Idempotence : clés d'idempotence sur les paiements/événements.

10) Observabilité, SLO et alertes

Réplique lag : temps (s), distance LSN (bytes), seconds_behind_master (MySQL).
Reculs/conflits forcés, erreurs de réplication.
p95/p99 latency по route (read leader vs replica, write).
Throughput: TPS/locks/row-contended tables.
Bloat/VACUUM (PG), InnoDB buffer pool hit ratio (MySQL).
Dashboards : chargements per-shard, chardons « chauds », distribution de clés.

11) Backups, PITR et DR

Backup complet + WAL/binlog pour PITR (point-in-time recovery).
Stockez dans une autre région/nuage, faites régulièrement des tests de restauration.
Pour les chardons, une « coupe » cohérente (coordination du temps/LSN) ou une idempotence applicative lors de la récupération.
Les RPO/RTO sont prescrits et testés pour les jours de jeu.

bash pg_basebackup -D /backups/base -X stream -C -S slot_replica_1 архивация WAL через archive_command или pgBackRest/Barman

12) Sécurité et accès

Segmentation par VPC/ACL, mTLS par proxy.
Rôles/subventions selon le principe des droits minimaux ; utilisateurs individuels par shard/rôle.
Vérification DDL/DCL, limites pour les demandes « lourdes » sur les répliques.
Cryptage at rest (KMS) et en transit (TLS).
« Bouton Panique » : global 'READ ONLY' pendant l'incident/enquête.

13) Outils et briques

PostgreSQL: Patroni (HA), PgBouncer (pooling/RO-routing), repmgr, pgBackRest/Barman (бэкап), Citus (шардинг), pglogical/Logical Replication, pgbadger/pg_stat_statements.
MySQL : Orchestrator (topologie/auto-failover), ProxySQL/MaxScale (routage), Percona XtraBackup (backap), Group Replication/InnoDB Cluster, Vitess (sharding/resharding).
Distributed SQL : CockroachDB, YugabyteDB (quorum, partage/géolocalisation intégré).
CDC : Debezium + Kafka/Pulsar pour événements/ETL.

14) Anti-modèles

Single-primary sans auto-failover et sans tests DR.
« Magic » read-split sans compter lag → erreurs fantômes/bugs suspects.
« Charding » : complication prématurée au lieu de skale/index/cache vertical.
La plage chaude (time-range) sans time-bucket/hash-salt → une seule boule fond.
L'opération globale est 2PC sur des dizaines de chardes en OLTP - des queues élevées p99 et des blocages fréquents.
L'absence de dual-write/dual-read lors des migrations → la perte/dissynchron.
DDL en vente sans outils en ligne et sans fiches de compatibilité.

15) Chèque de mise en œuvre (0-60 jours)

0-15 jours

Définir la base de données SLO, RPO/RTO.
Activer la réplication, la surveillance lag, les backups de base + PITR.
Entrez un routeur (PgBouncer/ProxySQL) et une stratégie read-after-write.

16-30 jours

Choisissez une stratégie de partage, décrivez les clés et les schémas.
Préparer les outils de transplantation (Vitess/Citus/CDC).
Annuaire des services/tables marqués « read-stale-ok » vs « strict ».

31-60 jours

Exécutez pilot-shard, dual-read et backfill.
Game-day : échec du leader, récupération à partir du PITR, changement de région.
Automatiser les rapports sur les clés chaudes et les inégalités.

16) Métriques de maturité

Replica lag p95 <cible (par exemple 500 ms) pour les lectures critiques.
Tests DR réussis ≥ 1/trimestre (restore ≤ RTO, perte ≤ RPO).
Répartition de la charge par chardons : déséquilibre <20 % par QPS/stockage.
Proportion de requêtes avec strict-consistency, correctement routé, = 100 %.
Zero-data-loss dans les incidents nécessitant des garanties CP (argent/commandes).
DDL/migration en ligne sans interruption de service, avec indicateurs de compatibilité.

17) Exemples de recettes

sql
-- вычисляйте bucket = hash(user_id) % 16, храните (bucket, created_at)
PARTITION BY LIST (bucket) SUBPARTITION BY RANGE (created_at)

python lsn = db.leader_query("SELECT pg_current_wal_lsn()")
ctx.sticky_until = now()+5s ctx.min_lsn = lsn в роутере чтений: выбираем реплику с last_lsn >= ctx.min_lsn, иначе лидер

json
{
"tables": {
"orders": { "column_vindexes": [{ "column": "tenant_id", "name": "hash" }] }
}
}

18) Conclusion

Le sharding et la réplication sont non seulement une technique, mais aussi des processus : routage avec cohérence, discipline des migrations (dual-write/read, backfill), tests DR réguliers et observabilité des lag/hot chards. Commencez par un simple leader→replica + read-after-write, puis ajoutez le sharding là où le profil de charge le nécessite vraiment. Utilisez des plates-formes prêtes à l'emploi (Vitess/Citus/Distributed SQL) et gardez les données critiques de l'entreprise en mode CP - de sorte que la base ne sera plus un goulot de bouteille et deviendra la base prévisible et élastique de la plate-forme.