Always On и read scale-out: SQL Server vs PostgreSQL

Admin

Горизонтальное масштабирование чтения: 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 — напишите.