Read replicas на собеседовании системного аналитика

Зачем read replicas

Primary handles writes. Replicas — reads.

Reads dominate (95% queries) → distribute load.

Scale read capacity. Take pressure off primary.

Replication lag

Replicas behind primary — milliseconds, sometimes seconds.

Time T: write to primary.
Time T+50ms: replica catches up.

Stale reads. If user reads replica между T и T+50ms — sees old data.

Lag growing. Replica overloaded или network slow. Monitor pg_stat_replication.

Read-your-writes

User writes — immediately wants read it.

Problem. Write goes к primary. Read goes к replica → maybe stale → user sees old value.

Solutions:

• Sticky session. Recently-written user reads from primary для N seconds.
• Causality tracking. Note timestamp write, ensure replica caught up.
• Read from primary для that user temporarily.

Routing strategies

Application-level. App sends reads / writes к different connection pools.

read_db = create_engine("postgres://replica:5432/...")
write_db = create_engine("postgres://primary:5432/...")

Proxy. Connection pooler (pgbouncer / pgcat) routes.

Tools. PgBouncer + Patroni для primary failover.

Application logic часто complex. ORMs (SQLAlchemy, Django) поддерживают routing decorators.

Связанные темы

• Database sharding для DE
• WAL и репликация для DE
• Connection pooling для DE
• CAP теорема для SA
• Подготовка к собесу системного аналитика

FAQ

Это официальная информация?

Нет. Статья основана на documentation Postgres / MySQL replication.

Тренируйте системный анализ — откройте тренажёр с 1500+ вопросами для собесов.