Зачем
Масштабирование баз данных - ключевой процесс для обеспечения производительности и надёжности системы при росте объёма данных и количества пользователей. Рассмотрим, какие подходы применимы для PostgreSQL.
- рекомендуется, встроено в PostgreSQL | - требует сторонних решений
Вертикальное масштабирование (Scale-Up)
Увеличение мощности сервера, путем добавления более мощных процессоров, большего объема оперативной памяти или ускоренных накопителей, очевидно, хорошо работает для PostgreSQL, как и для большинства других СУБД.
Если для вертикального масштабирования не используется просто более дорогой облачный инстанс с предустановленными настройками, то после обновления оборудования нужно не забыть обновить postgresql.conf.
Для быстрого подбора начального оптимального конфигурационного файла можно воспользоваться утилитой pgtune.
# DB Version: 16
# OS Type: mac
# DB Type: web
# Total Memory (RAM): 16 GB
# CPUs num: 12
# Connections num: 144
# Data Storage: ssd
max_connections = 144
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
work_mem = 7281kB
huge_pages = off
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 12
max_parallel_workers_per_gather = 4
max_parallel_workers = 12
max_parallel_maintenance_workers = 4
Горизонтальное масштабирование (Scale-Out)
Добавление большего количества узлов в систему для управления увеличивающейся нагрузкой. Вариантов и комбинаций достаточно много, попробуем рассмотреть самые популярные. Тестовый стенд соберем на основе docker-compose. Тестировать будем на таблице новостей, ниже приведен базовый пример создания и заполнения тестовыми данными.
CREATE TABLE news (
id serial PRIMARY KEY,
title VARCHAR(255),
type VARCHAR(50),
content TEXT,
publish_date DATE
);
DO $$
DECLARE
types text[] := ARRAY['Local', 'International', 'Economic', 'Sports', 'Entertainment'];
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO news (title, content, publish_date, type)
VALUES (
'Title ' || i,
'Content for news ' || i,
'2000-01-01'::DATE + (i || ' days')::INTERVAL,
types[1 + (random() * 4)::int] -- Randomly selecting news type
);
END LOOP;
END $$;
Партиционирование (Partitioning)
Партиционирование - это процесс разделения одной таблицы базы данных на меньшие, логически разделенные таблицы, называемые партициями.
Эти партиции все еще управляются в рамках одной и той же базы данных. Партиционирование часто полностью прозрачно для приложений, которые взаимодействуют с базой данных.
В PostgreSQL существует два подхода к партиционированию таблиц: декларативное партиционирование и партиционирование на основе наследования.
Декларативное партиционирование позволяет автоматически управлять партициями на основе определенных правил, заданных при создании таблицы. Это упрощает процесс управления партициями, так как база данных сама обрабатывает многие аспекты, такие как вставка, удаление и выборка данных в соответствующую партицию.
Для теста нам будет достаточно 1 инстанса БД, опишем его в docker-compose.yml
version: '3.1'
services:
db:
image: postgres:16
environment:
POSTGRES_PASSWORD: example
POSTGRES_DB: mydatabase
ports:
- "5432:5432"
запустим docker-compose up и законнектимся docker-compose exec - it db psql - U postgresВ PostgreSQL поддерживаются три основных типа партиционирования: диапазонное, списковое и хэш-партиционирование. Диапазонное партиционирование подходит для случаев, когда данные можно логически разделить на интервалы. Например, в нашем случае можно разделить наши новости по годам на основе
publish_date.
Списковое партиционирование эффективно, когда у вас есть четко определенный список значений, например, для нашего примера это будет type.
Хэш-партиционирование подходит для равномерного распределения данных, когда нет явных диапазонов или списков для разделения, т.е. мы можем использовать просто ID.Для нашего примера предположим, что оптимально будет разделить новости по годам. Сгенерируем таблицу и партиции.
CREATE TABLE news (
id serial,
title VARCHAR(255),
type VARCHAR(50),
content TEXT,
publish_date DATE,
PRIMARY KEY (id, publish_date)
) PARTITION BY RANGE (publish_date);
CREATE TABLE news_2000 PARTITION OF news FOR VALUES FROM ('2000-01-01') TO ('2001-01-01');
CREATE TABLE news_2001 PARTITION OF news FOR VALUES FROM ('2001-01-01') TO ('2002-01-01');
CREATE TABLE news_2002 PARTITION OF news FOR VALUES FROM ('2002-01-01') TO ('2003-01-01');
CREATE TABLE news_2003 PARTITION OF news FOR VALUES FROM ('2003-01-01') TO ('2004-01-01');
CREATE TABLE news_2004 PARTITION OF news FOR VALUES FROM ('2004-01-01') TO ('2005-01-01');
заполним таблицу тестовыми данными из базового скрипта. Проверим, что получилось после вставки:
postgres=# SELECT count(*) FROM news_2000;
count
-------
365
(1 row)
postgres=# SELECT count(*) FROM news_2001;
count
-------
365
(1 row)
postgres=# SELECT count(*) FROM news_2002;
count
-------
270
(1 row)
postgres=# SELECT count(*) FROM news_2003;
count
-------
0
(1 row)
Как и предполагалось, новости успешно распределены по соответствующим партициям.
В зависимости от требований бизнеса, мы можем эффективно управлять удалением устаревших новостей по годам, что позволяет оптимизировать объем хранения данных и реализовывать другие операции.
Шардирование (Sharding)
Шардирование(горизонтальное партиционирование), - это метод распределения данных по нескольким машинам или экземплярам базы данных, чтобы каждая машина управляла только частью данных.
Шардинг требует большей координации между серверами, так как данные физически разделены.
По состоянию на начало 2024 года ванильный PostgreSQL не поддерживает шардирование из коробки.
Отсутствие встроенного механизма шардинга обусловлено фокусом на поддержку ACID-транзакций. Тем не менее, существуют сторонние решения. Прежде чем использовать их, внимательно изучите реализацию и ограничения. Возможно, будут отсутствовать полноценные распределённые транзакции между шардами, или некоторые запросы потребуют дополнительных сетевых обращений. Рассмотрим некоторые способы реализации.
Ручной шардинг В этом случае данные разбиваются вручную на несколько баз данных и управление ими происходит вручную. Это требует самостоятельного управления данными и запросами между различными шардами. Но предоставляет максимум гибкости и прозрачности в разработке.
Citus - расширение для PostgreSQL, которое позволяет масштабироваться на несколько узлов. Citus автоматически распределяет данные и запросы между шардами. Является наиболее зрелым и активно поддерживаемым решением.
Postgres-XL - расширение PostgreSQL для горизонтального масштабирования. Проект практически не поддерживается с 2021 года, не рекомендуется для новых проектов.
Для нашего примера возьмем citus. Раздел When to use Citus намекает на правильное использование: analytics, multi-tenant, Time series data, Geospatial. Т.е. шарды должны быть относительно независимы в контексте OLTP, поскольку распределенные транзакции будут использовать механизм двухфазной фиксации (2PC), что может замедлить процесс.
Переделаем наш композ для работы с citus docker-compose.yml
version: "3"
services:
master:
container_name: "${COMPOSE_PROJECT_NAME:-citus}_master"
image: "citusdata/citus:12.1.3"
ports: ["${COORDINATOR_EXTERNAL_PORT:-5432}:5432"]
labels: ["com.citusdata.role=Master"]
environment: &AUTH
POSTGRES_USER: "${POSTGRES_USER:-postgres}"
POSTGRES_PASSWORD: "${POSTGRES_PASSWORD}"
PGUSER: "${POSTGRES_USER:-postgres}"
PGPASSWORD: "${POSTGRES_PASSWORD}"
POSTGRES_HOST_AUTH_METHOD: "${POSTGRES_HOST_AUTH_METHOD:-trust}"
worker:
image: "citusdata/citus:12.1.3"
labels: ["com.citusdata.role=Worker"]
depends_on: [manager]
environment: *AUTH
command: "/wait-for-manager.sh"
volumes:
- healthcheck-volume:/healthcheck
manager:
container_name: "${COMPOSE_PROJECT_NAME:-citus}_manager"
image: "citusdata/membership-manager:0.3.0"
volumes:
- "${DOCKER_SOCK:-/var/run/docker.sock}:/var/run/docker.sock"
- healthcheck-volume:/healthcheck
depends_on: [master]
environment: *AUTH
volumes:
healthcheck-volume:
соберем сетап docker-compose up и законнектимся на мастер ноду docker-compose exec - it master psql - U postgres Можем по необходимости заскейлить ноды, например до двух docker-compose up --scale worker=2
CREATE EXTENSION IF NOT EXISTS citus;
-- Доступные ноды
postgres=# SELECT master_get_active_worker_nodes();
master_get_active_worker_nodes
--------------------------------
(pg_scaling-worker-2,5432)
(pg_scaling-worker-1,5432)
(2 rows)
CREATE TABLE news (
id serial,
title VARCHAR(255),
type VARCHAR(50),
content TEXT,
publish_date DATE,
PRIMARY KEY (id, publish_date)
);
-- Распределяем данные по диапазону дат
SELECT create_distributed_table('news', 'publish_date');
-- Заполним тестовыми данными
DO $$
DECLARE
types text[] := ARRAY['Local', 'International', 'Economic', 'Sports', 'Entertainment'];
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO news (title, content, publish_date, type)
VALUES (
'Title ' || i,
'Content for news ' || i,
'2000-01-01'::DATE + (i || ' days')::INTERVAL,
types[1 + (random() * 4)::int] -- Randomly selecting news type
);
END LOOP;
END $$;
проверим что у нас получилось
-- наша распределенная таблица
SELECT * FROM pg_dist_partition;
logicalrelid | partmethod | partkey | colocationid | repmodel | autoconverted
--------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------+--------------+----------+---------------
news | h | {VAR :varno 1 :varattno 5 :vartype 1082 :vartypmod -1 :varcollid 0 :varnullingrels (b) :varlevelsup 0 :varnosyn 1 :varattnosyn 5 :location -1} | 1 | s | f
(1 row)
-- данные загружаются со всех нод
SELECT COUNT(*) FROM news;
count
-------
1000
(1 row)
-- теперь попробуем посмотреть как данные физически распределяются по нодам
-- pg_dist_shard: Содержит информацию о шардах данных и их распределении по узлам.
SELECT * FROM pg_dist_shard;
logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue
--------------+---------+--------------+---------------+---------------
news | 102008 | t | -2147483648 | -2013265921
news | 102009 | t | -2013265920 | -1879048193
news | 102010 | t | -1879048192 | -1744830465
news | 102011 | t | -1744830464 | -1610612737
...
news | 102035 | t | 1476395008 | 1610612735
news | 102036 | t | 1610612736 | 1744830463
news | 102037 | t | 1744830464 | 1879048191
news | 102038 | t | 1879048192 | 2013265919
news | 102039 | t | 2013265920 | 2147483647
(32 rows)
-- pg_dist_shard_placement: Предоставляет информацию о размещении шардов на узлах кластера.
shardid | shardstate | shardlength | nodename | nodeport | placementid
---------+------------+-------------+---------------------+----------+-------------
102008 | 1 | 0 | pg_scaling-worker-1 | 5432 | 1
102010 | 1 | 0 | pg_scaling-worker-1 | 5432 | 3
...
102035 | 1 | 0 | pg_scaling-worker-2 | 5432 | 28
102037 | 1 | 0 | pg_scaling-worker-2 | 5432 | 30
102039 | 1 | 0 | pg_scaling-worker-2 | 5432 | 32
(32 rows)
Таким образом, наша таблица распределена между двумя физическими узлами, но для приложения база данных остаётся единой.
Репликация (Replication)
Репликация - это процесс копирования данных с одного сервера PostgreSQL (мастер) на другие серверы (реплики). Это позволяет распределять читающую нагрузку, обеспечивать отказоустойчивость и увеличивать доступность данных.
PostgreSQL поддерживает два вида репликации: логическую и физическую. Оба вида репликации основаны на использовании журнала WAL(Write-Ahead Logging).
В физической репликации изменения данных записываются в WAL на мастер-сервере и затем реплицируются на реплики бинарным способом, используя эти журналы.
В логической репликации изменения данных также записываются в WAL на мастер-сервере, но в отличие от физической репликации, они интерпретируются как логические изменения.
На этот раз docker-compose.yml будет включать два PostgreSQL.
version: '3.1'
services:
db_master:
image: postgres:16
environment:
POSTGRES_PASSWORD: example
POSTGRES_DB: mydatabase
ports:
- "5432:5432"
db_replica:
image: postgres:16
environment:
POSTGRES_PASSWORD: example
POSTGRES_DB: mydatabase
depends_on:
- db_master
Физическая репликация копирует весь кластер PostgreSQL побайтово, без возможности выбрать отдельные таблицы или базы данных. Она быстрее логической и гарантирует полную согласованность данных между мастером и репликой.
Логическая репликация предоставляет гораздо больше опций в обмен на риск допустить серьезные ошибки. Настроим логическую репликацию для нашей таблицы
news.
Соберем сетап
docker-compose up и законнектимся на мастер ноду docker-compose exec - it db_master psql - U postgres. Установим способ репликации в logical ALTER SYSTEM SET wal_level TO 'logical';.
Аналогичную операцию проведем на реплике:
docker-compose exec - it db_replica psql - U postgres, обновляем ALTER SYSTEM SET wal_level TO 'logical';.
Перезапускаем сетап
docker-compose restart.
Еще раз законнектимся docker-compose exec - it db_master psql - U postgres и проверим, что настройки применились: SHOW wal_level; должен возвращать logical.
-- создади таблицу на обоих инстансах
-- docker-compose exec - it db_master psql - U postgres
-- docker-compose exec - it db_replica psql - U postgres
CREATE TABLE news (
id serial PRIMARY KEY,
title VARCHAR(255),
type VARCHAR(50),
content TEXT,
publish_date DATE
);
-- Логическая репликация работает по принципу PUB/SUB (Publisher/Subscriber).
-- Мастер публикует изменения, подписчик их применяет.
-- создадим Publisher на db_master
CREATE PUBLICATION mypub FOR TABLE news;
-- создадим Publisher на db_replica
CREATE SUBSCRIPTION mysub CONNECTION 'host=db_master user=postgres password=example' PUBLICATION mypub;
-- Заполним тестовыми данными на db_master
DO $$
DECLARE
types text[] := ARRAY['Local', 'International', 'Economic', 'Sports', 'Entertainment'];
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO news (title, content, publish_date, type)
VALUES (
'Title ' || i,
'Content for news ' || i,
'2000-01-01'::DATE + (i || ' days')::INTERVAL,
types[1 + (random() * 4)::int] -- Randomly selecting news type
);
END LOOP;
END $$;
-- проверим результат на db_master
SELECT COUNT(*) FROM news;
count
-------
1000
(1 row)
-- проверим результат на db_replica
SELECT COUNT(*) FROM news;
count
-------
1000
(1 row)
Логическая репликация без привлечения дополнительных расширений, например pglogical, не обеспечивает обновления схемы, эта ответственность остается на пользователе.
-- Изменим таблицу на db_master
ALTER TABLE news ADD COLUMN deleted_at DATE;
UPDATE news SET deleted_at = '2000-01-02';
-- Сделаем запрос на db_replica
-- колонка отсутствует
SELECT * FROM news;
id | title | type | content | publish_date
----+-------+------+---------+--------------
...
-- в логе ошибка:
ERROR: logical replication target relation "public.news" is missing replicated column: "deleted_at"
-- Изменим таблицу на db_replica
ALTER TABLE news ADD COLUMN deleted_at DATE;
-- Изменния успешно подтянулись на db_replica
SELECT * FROM news;
postgres=# SELECT * FROM news LIMIT 2;
id | title | type | content | publish_date | deleted_at
----+---------+---------------+--------------------+--------------+------------
1 | Title 1 | International | Content for news 1 | 2000-01-02 | 2000-01-02
2 | Title 2 | Sports | Content for news 2 | 2000-01-03 | 2000-01-02
-- Обновим news на db_replica
UPDATE news SET deleted_at = '2010-01-02';
-- Теперь у нас разные news на db_replica и db_master!
-- обновим news на db_master
UPDATE news SET deleted_at = NULL;
-- Изменения news применились и на db_replica, полностью перетерев предыдущий UPDATE
В нашем сетапе нет никаких препятствий для работы с таблицей, которая подписана на изменения. Можно модифицировать данные любым образом, но важно помнить, что порция изменений с мастера применяется к существующему набору данных. Результаты модификаций могут быть непредсказуемыми, так как это может привести к несогласованности данных, вплоть до невозможности полного применения изменений с мастера.
Пулинг соединений (Connection Pooling)
Каждое соединение с PostgreSQL создаёт отдельный процесс на сервере, что потребляет значительные ресурсы (около 10MB RAM на соединение). При большом количестве клиентов это становится узким местом. Пулеры соединений решают эту проблему, переиспользуя ограниченный набор соединений к базе данных.
PgBouncer - легковесный пулер соединений, де-факто стандарт для PostgreSQL. Поддерживает три режима:
- Session pooling - соединение закрепляется за клиентом на всю сессию
- Transaction pooling - соединение возвращается в пул после каждой транзакции (рекомендуется)
- Statement pooling - соединение возвращается после каждого запроса (ограниченная совместимость)
Пример минимальной конфигурации PgBouncer (
pgbouncer.ini):
[databases]
mydatabase = host=localhost port=5432 dbname=mydatabase
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction ; рекомендуемый режим
default_pool_size = 20 ; соединений к БД на пул
max_client_conn = 1000 ; максимум клиентских соединений
min_pool_size = 5 ; поддерживать минимум 5 соединений
Приложение подключается к PgBouncer (порт 6432) вместо PostgreSQL напрямую. PgBouncer переиспользует 20 реальных соединений к БД для обслуживания до 1000 клиентов.
SET) не будут работать предсказуемо между запросами.
Гибридные подходы
В книге "The Art of Scalability: Scalable Web Architecture, Processes, and Organizations for the Modern Enterprise" упоминается концепция "куб масштабирования". Этот подход представляет собой модель, которая помогает рассматривать масштабируемость в нескольких измерениях.
Куб состоит из трех осей масштабирования:
X-axis scaling (Клонирование): Горизонтальное дублирование - создание идентичных копий приложения или сервиса за балансировщиком нагрузки.
Y-axis scaling (Функциональная декомпозиция): Разделение приложения на отдельные сервисы по функциональности (микросервисная архитектура). Каждый сервис отвечает за свою часть бизнес-логики.
Z-axis scaling (Шардинг данных): Разделение данных на части по определённому ключу и размещение каждой части на отдельных серверах.
Использование всех трех осей позволяет эффективно масштабировать приложения и сервисы, обеспечивая высокую производительность, отказоустойчивость и масштабируемость.
Попробуем применить каждый из перечисленных подходов к масштабированию PostgreSQL:
Вертикальное масштабирование: Увеличение мощности отдельного сервера (CPU, RAM, SSD). Это базовый подход, который не относится напрямую к кубу масштабирования, но является отправной точкой.
Репликация: Соответствует оси X куба масштабирования. Создание идентичных копий базы данных для распределения читающей нагрузки и обеспечения отказоустойчивости.
Партиционирование: Локальное разделение данных в рамках одного сервера. Упрощает управление большими таблицами, но не является масштабированием в терминах куба.
Шардирование: Соответствует оси Z куба масштабирования. Данные разбиваются по ключу и распределяются между несколькими серверами. Каждый шард обслуживает только часть данных.
Гибридные подходы сочетают в себе различные методы масштабирования для достижения оптимальной производительности и отказоустойчивости. Например, можно комбинировать горизонтальное и вертикальное масштабирование, партиционирование с репликацией или использовать шардирование вместе с вертикальным масштабированием. Каждый гибридный подход будет зависеть от конкретных потребностей и ограничений системы.
Резюме
Типичный путь масштабирования PostgreSQL:
- Оптимизация запросов - индексы, EXPLAIN ANALYZE, pg_stat_statements
- Вертикальное масштабирование - увеличение ресурсов сервера, настройка postgresql.conf
- Connection pooling - PgBouncer для эффективного использования соединений
- Реплики для чтения - физическая репликация для распределения читающей нагрузки
- Партиционирование - для больших таблиц с историческими данными
- Шардирование - когда один сервер не справляется с записью (Citus)
Большинство приложений не доходят до шардирования - комбинации оптимизации, вертикального масштабирования и реплик достаточно для обработки значительных нагрузок.