Горизонтальное масштабирование чтения: SQL Server Always On и аналоги в PostgreSQL
Аннотация. Разбор Always On Availability Groups (AG) в Microsoft SQL Server как способа повысить отказоустойчивость и разгрузить чтение, с оценкой типичного прироста производительности, требований к лицензированию и параллелями с экосистемой PostgreSQL (streaming/logical replication, Patroni, pgpool).
1. Что именно масштабируем
«Горизонтальное масштабирование БД» часто смешивают с двумя разными задачами:
| Задача | Always On AG | PostgreSQL |
|---|---|---|
| Высокая доступность (HA) — быстрый failover | Да, основной сценарий | Да (Patroni, repmgr, встроенный failover) |
| Масштабирование чтения (read scale-out) | Да, readable secondary + read-only routing | Да, hot standby / read replicas |
| Масштабирование записи (write scale-out) | Нет* | Частично (шардинг: Citus, pg_shard, приложение) |
* Always On AG — это одна первичная writable-копия данных. Вторичные реплики асинхронно/синхронно повторяют журнал; запись не распределяется между узлами.
Вывод: AG и postgres-replica решают HA + read scale-out, но не заменяют шардирование для тяжёлой записи.
2. SQL Server: Always On Availability Groups
2.1. Архитектура
- Primary replica — принимает INSERT/UPDATE/DELETE.
- Secondary replica(s) — получают поток из transaction log (redo).
- Readable secondary — secondary, на которой разрешены SELECT с
ApplicationIntent=ReadOnlyили маршрутизацией read-only routing. - Windows Server Failover Cluster (WSFC) или с SQL Server 2017+ Linux + Pacemaker/Corosync — координация failover.
- Listener (AG Listener) — виртуальное имя + IP, клиент подключается к listener, маршрутизатор отправляет чтение на secondary.
┌─────────────────┐
Запись ────────► │ Primary replica │
└────────┬────────┘
│ log stream
┌──────────────┼──────────────┐
▼ ▼ ▼
Secondary-1 Secondary-2 Secondary-3
(readable) (readable) (DR only)
▲
Чтение ─────┘ (read-only routing / read intent)
2.2. Режимы синхронизации и влияние на чтение
| Режим | Failover RPO | Задержка чтения на secondary | Нагрузка на primary |
|---|---|---|---|
| Synchronous commit | ~0 (при кворуме) | Минимальная | Выше latency записи |
| Asynchronous commit | секунды | Возможен replica lag | Меньше влияние на запись |
На readable secondary возможны:
- Read-your-writes не гарантируется при async — пользователь может не увидеть только что записанные данные.
- Blocking redo — тяжёлый long-running SELECT на secondary может задерживать redo (зависит от версии и настроек
ALLOW_CONNECTIONS,READ_ONLY_ROUTING).
2.3. Оценка прироста производительности чтения
Точный процент без профиля нагрузки не существует; ориентиры для OLAP/отчётов/read-heavy (70–95 % SELECT):
| Конфигурация | Ожидаемый эффект | Комментарий |
|---|---|---|
| 1 primary + 1 readable secondary | +30–80 % суммарной пропускной способности чтения | При равном железе и если чтение упиралось в CPU/IO primary |
| 1 primary + 2–3 readable secondary | +50–150 % (не линейно) | Убывающая отдача: накладные расходы redo, lag, неидеальное распределение запросов |
| Тяжёлая запись + sync AG | Прирост чтения есть, запись может замедлиться на 5–25 % | Цена синхронной репликации |
Когда эффект мал:
- узкое место — диск primary (redo queue растёт быстрее, чем secondary успевает);
- запросы требуют самых свежих данных (нужен primary);
- много мелких random read — secondary не снимает блокировки на primary.
Практическая рекомендация: закладывать +1 readable secondary ≈ +40–60 % capacity SELECT при типичном DWH/BI, если secondary на сопоставимом железе и async replication.
2.4. Лицензирование SQL Server (Always On)
Актуально на семейство SQL Server 2019/2022; детали — в документации Microsoft и pricing guide.
| Возможность | Standard Edition | Enterprise Edition |
|---|---|---|
| Basic Availability Group | Да: 1 AG, 1 БД, 1 secondary, без read scale-out | — |
| Always On AG (полноценный) | Ограничено: обычно 2 реплики (1 primary + 1 secondary) | До 9 реплик (1 primary + 8 secondary) |
| Несколько readable secondary | Нет / сильно ограничено | Да |
| Read-only routing (автоматическое направление SELECT) | Ограничено | Полная поддержка |
| Distributed AG (между кластерами) | Нет | Enterprise |
| Columnstore / advanced BI на secondary | Ограничения | Полная поддержка |
Что покупать для типичного prod read scale-out:
- Enterprise Edition на каждый узел кластера (core-based licensing) или Server+CAL — если нужны несколько readable secondary, read-only routing, >2 реплик, enterprise-функции.
- Standard Edition — если достаточно HA + одна secondary без полноценного read scale-out (Basic AG / минимальный AG).
- Windows Server Datacenter — если используется Storage Replica / несколько VM с общим SAN (зависит от топологии).
- Software Assurance — для migration rights / Azure hybrid benefit (опционально).
Rough cost logic (качественно):
- HA «дешевле»: Standard + Basic AG.
- BI/DWH с разгрузкой отчётов на 2–3 реплики: почти всегда Enterprise на всех SQL-узлах.
3. PostgreSQL: как добиться того же
3.1. Streaming replication (физическая реплика)
Аналог async/sync secondary в AG:
Primary (read/write) ──WAL stream──► Standby (hot standby, read-only)
pg_hba.conf+primary_conninfoна standby.hot_standby = on— SELECT на replica.- Lag —
pg_stat_replication.replay_lag(PG 13+).
Прирост чтения: сопоставим с AG — +30–80 % на одну replica при read-heavy, если приложение умеет разделять read/write.
3.2. Logical replication
Публикация таблиц → подписчик. Полезно для:
- частичной репликации (не вся БД);
- upgrade major version;
- несколько подписчиков с разным набором данных.
Минус: больше overhead, не заменяет полный HA-failover «из коробки» без доп. оркестрации.
3.3. Оркестрация HA (аналог WSFC + AG)
| Инструмент | Роль |
|---|---|
| Patroni + etcd/Consul | Автовыбор primary, failover |
| repmgr | Управление replication + switchover |
| pg_auto_failover | Citus Data / Azure-стиль managed failover |
3.4. Маршрутизация чтения (аналог read-only routing)
PostgreSQL не имеет AG Listener в ядре. Варианты:
- Два connection string в приложении —
DATABASE_URL_WRITE,DATABASE_URL_READ. - PgBouncer — два pool'а (primary/replica).
- pgpool-II — load balancing + read/write split (
load_balance_mode,master_slave_mode). - HAProxy + healthcheck — TCP на primary:5432 / replica:5432.
- Managed cloud — AWS RDS/Aurora read replicas, Azure Flexible Server read replicas (встроенный endpoint).
3.5. Лицензирование PostgreSQL
| Компонент | Лицензия |
|---|---|
| PostgreSQL | Open Source (PostgreSQL License, BSD-like) |
| Patroni, pgpool, PgBouncer | Open Source |
| EnterpriseDB / Postgres Pro | Коммерческая поддержка + доп. функции (опционально) |
| Citus (write scale-out) | Open core / managed |
Итого: за сам движок платите железо/облако + поддержка, а не per-core SQL Server Enterprise.
4. Сравнительная таблица
| Критерий | SQL Server Always On AG | PostgreSQL |
|---|---|---|
| HA failover | Встроено + WSFC/Pacemaker | Patroni / repmgr / cloud |
| Read replica | Readable secondary | Hot standby / logical replica |
| Авто-маршрутизация SELECT | Read-only routing (Enterprise) | pgpool / app-level / proxy |
| Синхронная реplica | SYNCHRONOUS_COMMIT |
synchronous_standby_names |
| Lag monitoring | DMVs, sys.dm_hadr_* |
pg_stat_replication |
| Стоимость read scale-out | Enterprise ($$$) | Infra + engineering |
| Write scale-out | Шардирование вне AG | Citus / app sharding |
| Типичный прирост чтения (+1 replica) | +40–60 % SELECT* | +40–60 % SELECT* |
* Оценка при read-heavy и равном железе; не гарантия SLA.
5. Пример топологии для NevaDWH-подобного DWH
SQL Server:
AG «DWH-RO»
Primary: ETL + админ-запись
Secondary-1 (readable): Power BI / отчёты
Secondary-2 (async, DR): аварийный failover
Listener: dwh-sql.neva.loc
Connection string отчётов: ApplicationIntent=ReadOnly;MultiSubnetFailover=True
PostgreSQL:
Primary (Patroni leader): ETL + запись
Replica-1: BI read pool (PgBouncer, pool_mode=transaction)
Replica-2: DR
HAProxy: dwh-pg-write:5432 / dwh-pg-read:5433
6. Риски и ограничения
SQL Server AG:
- лицензионная стоимость Enterprise для полноценного read scale-out;
- redo queue при пиках записи;
- tempdb и agent jobs на secondary — ограничения.
PostgreSQL:
- split read/write — ответственность приложения или pgpool;
- failover → promotion replica (кратковременный downtime без Patroni);
- logical replication — не все DDL реплицируются автоматически.
7. Заключение
Always On Availability Groups — зрелое решение HA + масштабирование чтения в экосистеме Microsoft, но полноценный read scale-out с несколькими readable secondary и read-only routing — территория Enterprise Edition. Ожидайте ~40–60 % дополнительной ёмкости SELECT на каждую сопоставимую readable-реплику при OLAP-нагрузке, не линейно.
PostgreSQL даёт сопоставимую архитектуру (streaming replication + hot standby) без лицензий на ядро, но требует явной схемы маршрутизации (PgBouncer/pgpool/два URL) и оркестрации HA (Patroni).
Для NevaDWH, где уже есть поддержка MS SQL и PostgreSQL, разумная стратегия:
- prod SQL Server + тяжёлые корпоративные BI → AG + Enterprise, если бюджет позволяет;
- dev/test, стартапы, cloud-native → PostgreSQL replica + Patroni + PgBouncer read pool.
Обсуждение конфигураций под ваши SLA и sizing — в комментариях. Если нужен разбор конкретной версии SQL Server (2019 vs 2022) или Patroni-манифест для k3s — напишите.