Масштабирование PostgreSQL

Зачем

Масштабирование баз данных — ключевой процесс в обеспечении производительности и надёжности системы при увеличении объёма данных и количества пользователей. Интересно посмотреть, какие из общих подходов применимы для PostgreSQL.

Вертикальное масштабирование Горизонтальное масштабирование Партиционирование Шардирование Репликация Гибридные подходы

Вертикальное масштабирование (Scale-Up)

Увеличение мощности сервера, путем добавления более мощных процессоров, большего объема оперативной памяти или ускоренных накопителей, очевидно, хорошо работает для PostgreSQL, как и для большинства других СУБД.
Если для вертикального масштабирования не используется просто более дорогой облачный инстанс, с предустановленными настройками, то после апдейта нужно не забыть обновить postgresql.conf. Для быстрого подбора начального оптимального конфигурационного файла можно воспользоваться утилитой pgtune.

Для моего Mac получились вот такие рекомендации: 
      
        # 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 $$;

Партиционирование (Partioning)

Партиционирование — это процесс разделения одной таблицы базы данных на меньшие, логически разделенные таблицы, называемые партициями. Эти партиции все еще управляются в рамках одной и той же базы данных. Партиционирование часто полностью прозрачно для приложений, которые взаимодействуют с базой данных.
В 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-транзакций. Тем не менее, существуют сторонние решения. Прежде чем использовать их, внимательно изучите реализацию и ограничения. Возможно, будет отсутствовать OLTP-консистентность между шардами, или некоторые запросы потребуют лишних сетевых запросов. Рассмотрим некоторые способы реализации.
Ручной шардинг В этом случае данные разбиваются вручную на несколько баз данных и управление ими происходит вручную. Это требует самостоятельного управления данными и запросами между различными шардами. Но предоставляет максимум гибкости и прозрачности в разработке.
Postgres-XL - расширение PostgreSQL, которое добавляет горизонтальное масштабирование и распределение данных на несколько узлов.
Citus - расширение для PostgreSQL, которое позволяет масштабироваться на несколько узлов. Citus автоматически распределяет данные и запросы между шардами.
pg_shard - расширение, разработанное командой Citus, которое предоставляет простой механизм Sharding для PostgreSQL. Позволяет создавать и управлять шардами, а также автоматически маршрутизировать запросы к правильным нодам.

Для нашего примера возьмем 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 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 поддерживает два вида репликации: логическую и физическую. Оба вида репликации основаны на использовании журнала 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
Особенностью физической репликации является то, что она применяется целиком к реплике, при этом отсутствует возможность кастомизации. Скорость репликации выше, и такие реплики гарантируют конечную согласованность данных.
Логическая репликация предоставляет гораздо больше опций в обмен на риск допустить серьезные ошибки. Настроим логическую репликацию для нашей таблицы 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
В нашем сетапе нет никаких препятствий для работы с таблицей, которая подписана на изменения. Можно модифицировать данные любым образом, но важно помнить, что порция изменений с мастера применяется к существующему набору данных. Результаты модификаций могут быть непредсказуемыми, так как это может привести к несогласованности данных, вплоть до невозможности полного применения изменений с мастера.
Тем не менее, логическая репликация может быть удобным инструментом для синхронизации таблиц, которые являются общими для нескольких инсталляций PostgreSQL с одним узлом распространения данных, например для общих настроек, системных учетных записей и аналогичных задач.

Гибридные подходы

В книге "The Art of Scalability: Scalable Web Architecture, Processes, and Organizations for the Modern Enterprise" упоминается концепция "куб масштабирования". Этот подход представляет собой модель, которая помогает рассматривать масштабируемость в нескольких измерениях. Куб состоит из трех осей масштабирования:
X-axis scaling (Горизонтальное масштабирование): Дублирование приложения или сервиса, где каждая копия обслуживает ту же функциональность.
Y-axis scaling (Вертикальное масштабирование): Разделение функциональности приложения или сервиса на отдельные слои или компоненты и масштабирование каждого слоя.
Z-axis scaling (Шардинг): Разделение данных на части и размещение каждой части на отдельных серверах или узлах.
Использование всех трех осей позволяет эффективно масштабировать приложения и сервисы, обеспечивая высокую производительность, отказоустойчивость и масштабируемость.

Попробуем применить каждый из перечисленных подходов к масштабированию PostgreSQL:
Вертикальное масштабирование: Этот подход соответствует оси Y куба масштабирования. При вертикальном масштабировании увеличивается мощность отдельного сервера путем увеличения его ресурсов. Это подходит для улучшения производительности конкретного компонента системы.
Партиционирование: Партиционирование соответствует оси Z куба масштабирования. Позволяет увеличить масштабируемость базы данных и обеспечить более эффективное управление большими объемами данных.
Шардирование: Также соответствует оси Z куба масштабирования. Шардирование является одним из способов реализации партиционирования, где данные разбиваются на отдельные шарды, и хранятся на разных серверах. Каждый шард обслуживает только часть данных, что позволяет обрабатывать большой объем информации.
Репликация: Репликация может быть рассмотрена как гибридный подход, так как она включает создание копий данных на нескольких серверах (оси X и Y). Это обеспечивает отказоустойчивость и увеличивает доступность данных, а также может улучшить производительность приложения.

Гибридные подходы сочетают в себе различные методы масштабирования для достижения оптимальной производительности и отказоустойчивости. Например, можно комбинировать горизонтальное и вертикальное масштабирование, партиционирование с репликацией или использовать шардирование вместе с вертикальным масштабированием. Каждый гибридный подход будет зависеть от конкретных потребностей и ограничений системы.