SQL Academy · Leçon

Répliques de lecture et routage des connexions

Dirigez le trafic en lecture seule vers des répliques avec PgBouncer ou un pilote intelligent, en acceptant le compromis lié au retard des données.

Répliques de lecture et routage des connexions est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Pourquoi utiliser des réplicas de lecture ?

Les charges de travail comportant beaucoup de lectures peuvent décharger les SELECT sur des réplicas :

Le serveur primaire gère les écritures
Les réplicas gèrent la plupart des lectures
Mise à l’échelle horizontale des lectures sans partitionnement horizontal

Limite de la cohérence éventuelle

Les réplicas prennent du retard sur le serveur primaire. Un utilisateur qui vient de passer une commande peut ne pas encore la voir sur le réplica. Vous pouvez :

Acheminer les lectures qui suivent les écritures vers le serveur primaire
Attendre que le réplica rattrape son retard
Mettre en cache dans l’application l’état obtenu après l’écriture

Stratégies de routage

Routage au niveau de l’application — le code de l’application choisit le serveur primaire ou le réplica
Routage au niveau du pilote — JDBC, pgjdbc et pgxpool gèrent un regroupement de connexions conscient du primaire et des réplicas
Routage au niveau du mandataire — pgpool, pgbouncer ou HAProxy détecte les lectures et les écritures

Routage de l'application

Deux regroupements de connexions, un par rôle :

const primary = new Pool({ host: 'primary', ... });
const replica = new Pool({ host: 'replica', ... });

// reads go to replica:
await replica.query('SELECT ...');

// writes (and read-your-writes) go to primary:
await primary.query('INSERT ...');

PgPool-II

Serveur mandataire qui analyse le SQL et achemine les SELECT vers les réplicas et les écritures vers primary. Prend en charge l'équilibrage de charge et le regroupement des connexions :

# pgpool.conf
backend_hostname0 = 'primary'
backend_hostname1 = 'replica1'
load_balance_mode = on

HAProxy avec vérifications par pg_isready

HAProxy ne comprend pas le SQL, mais peut effectuer le routage par port — une interface frontale pour les écritures (vers primary), une autre pour les lectures (vers les réplicas avec des vérifications d'état) :

frontend writes
  bind *:5432
  default_backend primary_pool

frontend reads
  bind *:5433
  default_backend replicas_pool
  balance roundrobin

Points de terminaison AWS RDS

RDS fournit un point de terminaison unique pour l'écriture et un autre pour la lecture, qui répartit la charge entre tous les réplicas — la solution la plus simple dans le cloud.

Prise en compte du retard de réplication

Suivez le retard et évitez d'acheminer les requêtes vers des réplicas qui ont trop de retard :

-- On primary:
SELECT client_addr, replay_lag FROM pg_stat_replication;

-- HAProxy / pgpool can use a custom health check that fails if replay_lag > threshold.

Schéma de session persistante

Pour « relire ses propres écritures » : affectez la session de l'utilisateur à primary pendant N secondes après une écriture. Vous pouvez aussi marquer le LSN de l'écriture et attendre la relecture par le replica avant de répondre.

Attente fondée sur le LSN

Après une écriture, capturez le LSN ; avant de lire sur le replica, attendez que celui-ci ait rejoué les données jusqu'à ce LSN :

-- After write on primary:
SELECT pg_current_wal_lsn();        -- save this

-- Before read on replica:
SELECT pg_last_wal_replay_lsn() >= $saved_lsn;
-- spin or wait until true

Mise en cache comme réplique

Redis / Memcache sert parfois de « réplique » pour mettre les lectures à l'échelle — c'est bien plus rapide qu'une autre instance PostgreSQL pour les clés très consultées.

Quand NE PAS utiliser de réplicas

Si votre charge de travail est principalement constituée d'écritures, les réplicas ajoutent surtout un coût sans accélérer le traitement. Si les lectures sont déjà rapides sur primary, vous n'en avez peut-être pas besoin.

Reprise après sinistre et mise à l'échelle des lectures

Les réplicas de basculement HA répondent à un besoin différent de celui des réplicas utilisés pour mettre les lectures à l'échelle. Vous disposez souvent des deux.

Récapitulatif

Les réplicas de lecture permettent de mettre les lectures à l'échelle, mais entraînent un retard.

Routage par l'application, le pilote ou le serveur mandataire
Retard de réplication = cohérence éventuelle
Affectation persistante à primary ou attente du LSN pour relire ses propres écritures
Surveillez le retard et revenez à primary s'il devient trop important

Vérification rapide

Un utilisateur publie un commentaire, puis actualise immédiatement la page. La page lit les données depuis un replica et n'affiche aucun commentaire. Quel est le problème et donnez une solution ?

Questions Fréquemment Posées

La leçon « Répliques de lecture et routage des connexions » est-elle gratuite ?

Oui — le texte complet de « Répliques de lecture et routage des connexions » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Répliques de lecture et routage des connexions » ?

Dirigez le trafic en lecture seule vers des répliques avec PgBouncer ou un pilote intelligent, en acceptant le compromis lié au retard des données. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Répliques de lecture et routage des connexions » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

Réplication en continu et WAL
Réplication logique pour le partitionnement horizontal
Basculement et élection du leader (Patroni, Stolon)
Répliques de lecture et routage des connexions

← Retour à SQL Academy