NevaDWH

Добро пожаловать на форум NevaDWH!

NevaDWH — платформа в активной разработке. Мы строим среду, где IT-стартапы и команды смогут публиковать свои проекты, делиться прогрессом и находить аудиторию.

Сейчас форум на ранней стадии: функциональность и контент будут появляться постепенно. Это нормально — вы видите «живую» версию платформы, а не финальный продукт.

Что планируется публикация стартапов и pet-проектов в сфере IT; обсуждение архитектуры данных, инструментов и кейсов NevaDWH; новости о релизах, экспериментах и возможностях для участников. Как участвовать задавайте вопросы в комментариях к этой теме; делитесь идеями в разделе General Discussion; технические статьи по БД и DWH — в Technical / База данных; для обратной связи по платформе используйте Comments & Feedback.

Спасибо, что заглянули. Будем рады вашим вопросам и предложениям.

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