Как оптимизировать PostgreSQL под высокую нагрузку: EXPLAIN ANALYZE, индексы, PgBouncer, Partitioning и Replication

Представим, что вы разрабатываете SaaS-приложение на React, FastAPI и PostgreSQL.

На старте продукт работает быстро.

В базе данных несколько тысяч пользователей, десятки тысяч проектов и небольшое количество транзакций.

Обычные SQL-запросы выполняются за миллисекунды.

Но спустя год ситуация меняется.

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

Появляются проблемы:

• Dashboard загружается по 5–10 секунд.

• API периодически возвращает Timeout.

• PostgreSQL использует почти всю доступную память.

• Количество подключений приближается к лимиту.

• Некоторые SQL-запросы выполняются значительно дольше обычного.

• Фоновые задачи создают дополнительную нагрузку.

• Во время массового обновления данных появляются блокировки.

Разработчики решают увеличить количество Backend Workers.

Но после этого ситуация становится ещё хуже.

Почему?

Потому что каждый Worker создаёт дополнительные подключения к PostgreSQL, а медленные запросы продолжают конкурировать за ресурсы.

Проблема не всегда решается увеличением CPU или RAM.

Часто необходима системная оптимизация PostgreSQL под высокую нагрузку.

В этой статье разберём, как диагностировать узкие места PostgreSQL, использовать EXPLAIN ANALYZE, проектировать индексы, оптимизировать SQL, настраивать PgBouncer, применять Partitioning и Read Replicas, управлять VACUUM и блокировками и масштабировать базу данных в Production.

Часть 1. Как работает производительность PostgreSQL

1. Что влияет на скорость PostgreSQL

PostgreSQL Performance Tuning начинается с понимания основных источников нагрузки.

Производительность зависит от:

• структуры SQL-запросов;

• индексов;

• количества строк;

• распределения данных;

• статистики Query Planner;

• доступной оперативной памяти;

• дисковой подсистемы;

• количества подключений;

• блокировок;

• длительности транзакций;

• частоты записи;

• конфигурации Autovacuum.

Важно не оптимизировать всё одновременно.

Сначала нужно определить реальное узкое место.

2. Почему база данных замедляется со временем

PostgreSQL Performance Degradation может происходить по нескольким причинам.

Например:

10,000 rows

|

v

Fast Query

|

v

1,000,000 rows

|

v

Slow Query

Но рост таблицы сам по себе не всегда приводит к пропорциональному увеличению времени выполнения.

Многое зависит от Query Plan, индексов и количества строк, которые действительно нужно прочитать.

3. Как выглядит типичная архитектура

PostgreSQL Production Architecture:

 React Frontend

|

v

FastAPI

|

v

SQLAlchemy

|

v

PgBouncer

|

v

PostgreSQL

|

+-----------+-----------+

| |

v v

WAL Files Autovacuum

|

v

Read Replica

Это пример архитектуры с отдельным пулером подключений и репликацией.

Не каждому приложению нужны все эти компоненты.

4. Какие метрики PostgreSQL важнее всего

PostgreSQL Monitoring Metrics:

Ни одна метрика не должна рассматриваться изолированно.

Например, высокий CPU может быть следствием неэффективного SQL-запроса, а не недостаточной мощности сервера.

Часть 2. Поиск медленных SQL-запросов

5. Что такое Slow Query

PostgreSQL Slow Query — запрос, время выполнения которого превышает допустимый для конкретного приложения уровень.

Например:

GET /api/projects

Expected: 100 ms

Actual: 3,500 ms

Порог медленного запроса зависит от бизнес-операции.

Для интерактивного поиска 500 мс могут быть слишком большой задержкой.

Для сложного аналитического отчёта несколько секунд могут быть допустимы.

6. Как включить Slow Query Logging

PostgreSQL Slow Query Logging можно настроить через параметр:

log_min_duration_statement = 500ms

PostgreSQL будет логировать запросы, длительность которых достигает указанного порога.

В Production нужно учитывать объём логов и возможность попадания чувствительных данных в SQL.

7. Что такое pg_stat_statements

pg_stat_statements — расширение PostgreSQL для сбора агрегированной статистики выполнения SQL-запросов.

Оно помогает определить:

• самые затратные запросы;

• частоту выполнения;

• среднюю задержку;

• суммарное время;

• объём операций чтения.

8. Как включить pg_stat_statements

Для использования расширения обычно требуется добавить его в конфигурацию PostgreSQL:

shared_preload_libraries = 'pg_stat_statements'

После изменения параметра необходим перезапуск PostgreSQL.

Затем в нужной базе данных:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

В управляемых PostgreSQL-сервисах порядок настройки может отличаться.

9. Как найти самые дорогие запросы

PostgreSQL Top Queries:

SELECT

query,

calls,

total_exec_time,

mean_exec_time,

rows

FROM pg_stat_statements

ORDER BY total_exec_time DESC

LIMIT 20;

total_exec_time помогает найти запросы, которые суммарно потребляют больше всего времени.

mean_exec_time помогает обнаружить медленные отдельные операции.

Однако среднее значение может скрывать большие колебания задержки.

10. Почему нужно смотреть на Total Time

Представим два запроса:

Запрос A медленнее при отдельном выполнении.

Но запрос B создаёт гораздо большую суммарную нагрузку.

Поэтому оптимизация PostgreSQL должна учитывать и частоту, и стоимость запросов.

Часть 3. EXPLAIN ANALYZE

11. Что такое EXPLAIN

PostgreSQL EXPLAIN показывает план выполнения SQL-запроса, выбранный Query Planner.

Например:

EXPLAIN

SELECT *

FROM projects

WHERE organization_id = 123;

PostgreSQL покажет, как планирует получить данные.

12. Что такое EXPLAIN ANALYZE

EXPLAIN ANALYZE PostgreSQL не только показывает план, но и выполняет запрос, измеряя реальные показатели.

Например:

EXPLAIN (ANALYZE, BUFFERS)

SELECT *

FROM projects

WHERE organization_id = 123;

Важно: EXPLAIN ANALYZE действительно выполняет запрос.

Для UPDATE, DELETE и INSERT это означает возможное изменение данных.

Поэтому такие проверки нужно проводить осторожно, желательно в тестовой среде или внутри транзакции с последующим ROLLBACK.

13. Что такое Sequential Scan

PostgreSQL Sequential Scan означает последовательное чтение таблицы.

Пример:

Seq Scan on projects

Это не обязательно плохо.

Для маленьких таблиц Sequential Scan может быть быстрее Index Scan.

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

14. Что такое Index Scan

PostgreSQL Index Scan использует индекс для поиска строк.

Например:

Index Scan using idx_projects_org

Это может значительно уменьшить количество прочитанных строк.

Но Index Scan не всегда быстрее Sequential Scan.

Если запрос возвращает большую часть таблицы, последовательное чтение может оказаться эффективнее.

15. Что такое Bitmap Index Scan

PostgreSQL Bitmap Index Scan позволяет сначала определить подходящие позиции строк через индекс, а затем получить данные из таблицы.

Типичный план:

Bitmap Heap Scan

|

v

Bitmap Index Scan

Такой подход часто полезен, когда запрос возвращает умеренное количество строк.

16. Что такое Actual Rows

EXPLAIN Actual Rows показывает количество строк, полученных на конкретном этапе выполнения.

Например:

Estimated Rows: 100

Actual Rows: 100000

Большое расхождение может означать, что Query Planner использует неточные оценки.

Возможные причины:

• устаревшая статистика;

• неравномерное распределение данных;

• корреляция столбцов;

• сложные условия фильтрации.

17. Что такое BUFFERS

EXPLAIN BUFFERS показывает операции с буферами PostgreSQL.

Например:

Buffers: shared hit=1000 read=500

shared hit указывает на блоки, найденные в Shared Buffers.

shared read отражает блоки, прочитанные в Shared Buffers из нижележащего слоя хранения.

Это не обязательно означает физическое чтение непосредственно с диска: данные могли находиться в кеше операционной системы.

18. Как анализировать Query Plan

PostgreSQL Query Plan Analysis:

Сначала смотрим:

1. Общее время выполнения.

2. Самые дорогие операции.

3. Actual Rows и Estimated Rows.

4. Количество прочитанных блоков.

5. Sort и Hash Operations.

6. Использование индексов.

7. Nested Loop, Hash Join и Merge Join.

Главная задача — найти операцию, которая создаёт основную нагрузку.

Часть 4. Индексы PostgreSQL

19. Что такое PostgreSQL Index

PostgreSQL Index — структура данных, ускоряющая поиск строк по определённым условиям.

Например:

CREATE INDEX idx_projects_org

ON projects (organization_id);

Теперь PostgreSQL может быстрее находить проекты конкретной организации.

20. Почему индексы не бесплатны

PostgreSQL Index Overhead:

Индексы требуют:

• дополнительного дискового пространства;

• обновления при изменении данных;

• дополнительной работы при INSERT;

• дополнительной работы при UPDATE;

• дополнительной работы при DELETE;

• обслуживания через VACUUM и другие механизмы.

Поэтому создавать индекс для каждого столбца не нужно.

21. Что такое B-Tree Index

PostgreSQL B-Tree Index — наиболее распространённый тип индекса.

Подходит для:

• равенства;

• диапазонов;

• сортировки;

• некоторых Prefix Conditions.

Пример:

CREATE INDEX idx_users_email

ON users (email);

22. Что такое Composite Index

PostgreSQL Composite Index — индекс по нескольким столбцам.

Например:

CREATE INDEX idx_projects_org_created

ON projects (

organization_id,

created_at DESC

);

Он полезен для запроса:

SELECT id, name, created_at

FROM projects

WHERE organization_id = 123

ORDER BY created_at DESC

LIMIT 20;

Порядок столбцов в индексе имеет значение.

23. Почему порядок колонок важен

Composite Index Column Order:

Индекс:

CREATE INDEX idx_example

ON projects (

organization_id,

status,

created_at DESC

);

Может хорошо подходить для:

SELECT *

FROM projects

WHERE organization_id = 123

AND status = 'active'

ORDER BY created_at DESC;

Но он не обязательно будет оптимален для запроса только по created_at.

Проектировать Composite Index нужно на основании реальных Query Patterns.

24. Что такое Partial Index

PostgreSQL Partial Index — индекс только для строк, удовлетворяющих условию.

Например:

CREATE INDEX idx_active_projects

ON projects (organization_id, created_at DESC)

WHERE deleted_at IS NULL;

Это полезно, если приложение часто запрашивает только неудалённые проекты.

Partial Index может быть меньше полного индекса.

25. Что такое Covering Index

PostgreSQL Covering Index позволяет включать дополнительные столбцы через INCLUDE.

Например:

CREATE INDEX idx_projects_covering

ON projects (organization_id, created_at DESC)

INCLUDE (name, status);

Это может помочь PostgreSQL выполнить Index-Only Scan.

Но Index-Only Scan также зависит от Visibility Map и состояния таблицы.

26. Что такое GIN Index

PostgreSQL GIN Index часто используется для:

• Full-Text Search;

• JSONB;

• массивов;

• некоторых расширений поиска.

Например:

CREATE INDEX idx_documents_search

ON documents

USING GIN (search_vector);

GIN Index не является универсальной заменой B-Tree.

27. Что такое BRIN Index

PostgreSQL BRIN Index может быть эффективен для очень больших таблиц, если значения столбца хорошо коррелируют с физическим расположением строк.

Например, для таблицы событий, куда записи преимущественно добавляются по времени:

CREATE INDEX idx_events_created_brin

ON events

USING BRIN (created_at);

BRIN обычно компактнее B-Tree.

Но его эффективность зависит от распределения данных.

28. Как создавать индекс без длительной блокировки записи

PostgreSQL CREATE INDEX CONCURRENTLY:

CREATE INDEX CONCURRENTLY idx_orders_created

ON orders (created_at);

Этот режим позволяет продолжать обычные операции записи во время построения индекса.

Однако операция может выполняться дольше и имеет ограничения.

Например, CREATE INDEX CONCURRENTLY нельзя запускать внутри обычного Transaction Block.

При сбое также может остаться невалидный индекс, требующий отдельной обработки.

Часть 5. Оптимизация SQL-запросов

29. Почему SELECT * может быть проблемой

PostgreSQL SELECT Optimization:

Неоптимальный запрос:

SELECT *

FROM projects

WHERE organization_id = 123;

Если таблица содержит большие JSONB-поля, описание и дополнительные данные, приложение может загружать намного больше информации, чем нужно.

Лучше выбирать необходимые столбцы:

SELECT

id,

name,

created_at

FROM projects

WHERE organization_id = 123;

Это уменьшает передачу данных и иногда позволяет использовать Index-Only Scan.

30. Что такое N+1 Query Problem

N+1 Query Problem SQLAlchemy:

Например:

1 query:

Load 100 projects

100 queries:

Load owner for each project

Итого:

101 SQL Queries

Это увеличивает нагрузку на PostgreSQL.

31. Как решить N+1 в SQLAlchemy

SQLAlchemy selectinload Optimization:

from sqlalchemy import select

from sqlalchemy.orm import selectinload

statement = (

select(Project)

.options(

selectinload(Project.owner)

)

.where(

Project.organization_id == organization_id

)

)

result = await session.execute(statement)

projects = result.scalars().all()

selectinload позволяет загружать связанные объекты дополнительными пакетными запросами вместо отдельного запроса для каждой записи.

32. JOIN vs Multiple Queries

PostgreSQL JOIN Optimization:

JOIN может уменьшить количество обращений к базе.

Но слишком большой JOIN способен создавать огромное промежуточное множество строк.

Например, объединение нескольких отношений One-to-Many может привести к Multiplicative Row Explosion.

Поэтому выбор между JOIN и несколькими запросами зависит от структуры данных.

33. Почему OFFSET становится медленным

PostgreSQL OFFSET Pagination Performance:

SELECT *

FROM events

ORDER BY created_at DESC

LIMIT 20

OFFSET 100000;

PostgreSQL может потребоваться пройти большое количество строк, прежде чем вернуть нужную страницу.

На больших объёмах данных это создаёт лишнюю работу.

34. Что такое Keyset Pagination

PostgreSQL Keyset Pagination:

SELECT

id,

created_at,

event_type

FROM events

WHERE (created_at, id)

< ('2026-10-01T12:00:00Z', 50000)

ORDER BY created_at DESC, id DESC

LIMIT 20;

Здесь предполагается, что id имеет совместимый с примером числовой тип.

Для UUID нужно использовать UUID-значение.

Соответствующий индекс:

CREATE INDEX idx_events_cursor

ON events (created_at DESC, id DESC);

Keyset Pagination особенно полезна для больших списков и бесконечной прокрутки.

35. Почему нужно избегать функций над индексируемыми столбцами

PostgreSQL Index Expression Optimization:

Например:

SELECT *

FROM users

WHERE LOWER(email) = 'user@example.com';

Обычный индекс по email не всегда поможет такому условию.

Можно создать Expression Index:

CREATE INDEX idx_users_email_lower

ON users (LOWER(email));

Или использовать другой подход к хранению и нормализации Email.

Часть 6. Query Planner и статистика

36. Что такое PostgreSQL Query Planner

PostgreSQL Query Planner выбирает план выполнения запроса на основании статистики и оценок стоимости.

Например, он решает:

• использовать индекс или Sequential Scan;

• выбрать Nested Loop или Hash Join;

• в каком порядке соединять таблицы;

• как выполнять сортировку.

37. Что делает ANALYZE

PostgreSQL ANALYZE обновляет статистику распределения данных.

ANALYZE projects;

Эта информация помогает Query Planner выбирать более подходящие планы.

Обычно статистику автоматически обновляет Autovacuum/Autoanalyze.

Но после массовых изменений может потребоваться дополнительный ANALYZE.

38. Что такое Extended Statistics

PostgreSQL Extended Statistics помогают Query Planner учитывать зависимости между столбцами.

Например:

CREATE STATISTICS stats_projects_org_status

(dependencies, ndistinct)

ON organization_id, status

FROM projects;

Затем:

ANALYZE projects;

Это может улучшить оценки для запросов с несколькими связанными условиями.

39. Почему Planner иногда ошибается

PostgreSQL Bad Query Plan может возникнуть из-за:

• устаревшей статистики;

• неравномерного распределения;

• неверных оценок количества строк;

• неподходящих индексов;

• сложных JOIN;

• Parameter-Sensitive Queries.

Поэтому оптимизацию лучше начинать с реального Execution Plan.

Часть 7. Connection Pooling

40. Почему подключения PostgreSQL дорогие

PostgreSQL Connection Management:

Каждое серверное подключение PostgreSQL связано с отдельным Backend Process.

Большое количество подключений потребляет ресурсы.

Например:

FastAPI Worker 1 -> 20 Connections

FastAPI Worker 2 -> 20 Connections

FastAPI Worker 3 -> 20 Connections

FastAPI Worker 4 -> 20 Connections

Total: 80 Connections

При горизонтальном масштабировании количество подключений может быстро расти.

41. Что такое Connection Pooling

PostgreSQL Connection Pooling — повторное использование подключений вместо постоянного создания новых.

Например:

FastAPI Requests

|

v

Connection Pool

|

v

PostgreSQL

Это уменьшает накладные расходы и помогает ограничивать конкуренцию за подключения.

42. Connection Pool в SQLAlchemy

SQLAlchemy Async Connection Pool:

from sqlalchemy.ext.asyncio import (

create_async_engine,

)

engine = create_async_engine(

DATABASE_URL,

pool_size=10,

max_overflow=5,

pool_timeout=30,

pool_pre_ping=True,

)

Параметры:

• pool_size

  — базовый размер пула;

• max_overflow

  — дополнительные подключения сверх базового размера;

• pool_timeout

  — максимальное время ожидания свободного подключения;

• pool_pre_ping

  — проверка пригодности подключения при выдаче из пула.

Значения нужно подбирать с учётом количества процессов приложения.

43. Как рассчитывать количество подключений

PostgreSQL Connection Pool Sizing:

Представим:

5 Application Instances

4 Workers per Instance

Pool Size: 10

Теоретически базовые пулы могут суммарно содержать:

[5 \times 4 \times 10 = 200]

подключений.

Если max_overflow=5, возможный максимум со стороны этих пулов:

[5 \times 4 \times (10+5) = 300]

Дополнительно могут существовать подключения от Workers, миграций, администраторов и других сервисов.

Поэтому нельзя выбирать Pool Size без учёта общей архитектуры.

Часть 8. PgBouncer

44. Что такое PgBouncer

PgBouncer — лёгкий Connection Pooler для PostgreSQL.

Он принимает клиентские подключения и управляет меньшим количеством серверных подключений.

FastAPI

|

v

PgBouncer

|

v

PostgreSQL

PgBouncer особенно полезен при большом количестве кратковременных транзакций.

45. Какие режимы PgBouncer существуют

PgBouncer Pooling Modes:

Transaction Pooling часто используется для веб-приложений.

Но он имеет ограничения совместимости с функциями, зависящими от состояния сессии.

46. Почему Transaction Pooling требует осторожности

PgBouncer Transaction Pooling Limitations:

Следует учитывать:

• Session-Level Settings;

• Temporary Tables;

• Session Advisory Locks;

• Prepared Statements;

• LISTEN/NOTIFY;

• особенности используемого драйвера.

Поддержка некоторых возможностей зависит от версии PgBouncer и конкретной конфигурации.

Например, работа с Protocol-Level Prepared Statements может поддерживаться при соответствующих настройках современных версий PgBouncer.

Но нельзя считать, что все Session-Level Features автоматически совместимы с Transaction Pooling.

47. PgBouncer и SQLAlchemy

PgBouncer SQLAlchemy Configuration:

При использовании Transaction Pooling иногда целесообразно отключать дополнительный пул SQLAlchemy:

from sqlalchemy.pool import NullPool

engine = create_async_engine(

DATABASE_URL,

poolclass=NullPool,

)

Это не универсальное требование.

В некоторых конфигурациях дополнительный клиентский пул может быть полезен.

Выбор зависит от модели нагрузки и настроек PgBouncer.

48. Когда PgBouncer не решит проблему

PgBouncer Performance Limitations:

PgBouncer не ускорит автоматически:

• неэффективные JOIN;

• отсутствие индексов;

• длительные SQL-запросы;

• блокировки;

• медленную дисковую подсистему.

Он помогает управлять подключениями, но не заменяет оптимизацию запросов.

Часть 9. MVCC и VACUUM

49. Что такое MVCC

PostgreSQL MVCC — Multi-Version Concurrency Control.

PostgreSQL использует версии строк, чтобы поддерживать конкурентную работу транзакций.

Например, при UPDATE старая версия строки может некоторое время сохраняться.

Это помогает обеспечивать согласованность чтения.

50. Что такое Dead Tuples

PostgreSQL Dead Tuples — устаревшие версии строк, которые больше не нужны активным транзакциям и могут быть очищены.

При большом количестве UPDATE и DELETE их число может увеличиваться.

Это влияет на размер таблиц и эффективность доступа.

51. Что такое VACUUM

PostgreSQL VACUUM освобождает пространство от ненужных версий строк для повторного использования и выполняет другие задачи обслуживания.

Пример:

VACUUM projects;

Обычный VACUUM обычно не возвращает освобождённое пространство операционной системе, но делает его доступным для повторного использования внутри таблицы.

52. Что такое Autovacuum

PostgreSQL Autovacuum — автоматический механизм обслуживания таблиц.

Он помогает:

• очищать Dead Tuples;

• обновлять статистику;

• предотвращать проблемы с Transaction ID Wraparound.

Отключать Autovacuum без серьёзных оснований опасно.

53. Что такое Table Bloat

PostgreSQL Table Bloat — избыточное использование дискового пространства таблицами или индексами.

Причины могут включать:

• интенсивные UPDATE;

• большое количество DELETE;

• длительные транзакции;

• недостаточную эффективность VACUUM.

Bloat может увеличивать объём чтения и ухудшать производительность.

54. VACUUM vs VACUUM FULL

VACUUM vs VACUUM FULL:

VACUUM FULL нельзя бездумно запускать на большой Production-таблице.

55. Почему Long-Running Transactions опасны

PostgreSQL Long Running Transactions могут препятствовать очистке устаревших версий строк.

Например:

Transaction A starts

|

v

Transaction stays open

|

v

Many UPDATE operations

|

v

Dead Tuples accumulate

Поэтому важно отслеживать длительные транзакции.

Часть 10. Блокировки PostgreSQL

56. Что такое PostgreSQL Locks

PostgreSQL Locks защищают согласованность данных при конкурентных операциях.

Например, две транзакции могут пытаться обновить одну строку.

Одна из них может ожидать завершения другой.

57. Как найти активные запросы

PostgreSQL pg_stat_activity:

SELECT

pid,

state,

wait_event_type,

wait_event,

query_start,

query

FROM pg_stat_activity

WHERE state <> 'idle';

Этот запрос помогает увидеть активные Backend Processes.

При этом доступность полного текста запросов зависит от прав пользователя.

58. Как найти блокирующие процессы

PostgreSQL Blocking Queries:

SELECT

blocked.pid AS blocked_pid,

blocked.query AS blocked_query,

blocking.pid AS blocking_pid,

blocking.query AS blocking_query

FROM pg_stat_activity AS blocked

JOIN pg_stat_activity AS blocking

ON blocking.pid = ANY(

pg_blocking_pids(blocked.pid)

);

Это помогает определить, какие процессы блокируют другие.

59. Что такое Deadlock

PostgreSQL Deadlock возникает, когда транзакции взаимно ожидают ресурсы друг друга.

Например:

Transaction A locks Row 1

Transaction B locks Row 2

A waits for Row 2

B waits for Row 1

PostgreSQL обнаруживает Deadlock и отменяет одну из транзакций.

60. Как уменьшить Deadlocks

PostgreSQL Deadlock Prevention:

• обновлять ресурсы в одинаковом порядке;

• сокращать длительность транзакций;

• создавать подходящие индексы;

• избегать ненужных блокировок;

• использовать Retry для безопасных операций;

• контролировать конкурентные обновления.

Важно, чтобы повторная попытка была идемпотентной.

Часть 11. Partitioning

61. Что такое PostgreSQL Partitioning

PostgreSQL Partitioning — разделение логической таблицы на несколько физических Partitions.

Например:

events

|

+-- events_2026_01

|

+-- events_2026_02

|

+-- events_2026_03

Это полезно для больших таблиц с естественным ключом разделения.

62. Когда Partitioning полезен

PostgreSQL Partitioning Use Cases:

• Event Logs;

• Audit Logs;

• Time-Series Data;

• крупные таблицы транзакций;

• данные с регулярным удалением старых периодов.

Но Partitioning не является универсальным ускорителем запросов.

Неправильно выбранный Partition Key может ухудшить ситуацию.

63. Как создать Partitioned Table

PostgreSQL Range Partitioning:

CREATE TABLE events (

id BIGINT NOT NULL,

organization_id UUID NOT NULL,

event_type TEXT NOT NULL,

created_at TIMESTAMPTZ NOT NULL

)

PARTITION BY RANGE (created_at);

Создадим Partition:

CREATE TABLE events_2026_10

PARTITION OF events

FOR VALUES FROM ('2026-10-01')

TO ('2026-11-01');

В Production нужно учитывать часовой пояс, границы периодов и создание будущих Partitions.

64. Что такое Partition Pruning

PostgreSQL Partition Pruning — исключение ненужных Partitions из плана выполнения.

Например:

SELECT *

FROM events

WHERE created_at >= '2026-10-01'

AND created_at < '2026-11-01';

PostgreSQL может обратиться только к соответствующим Partitions.

Это уменьшает объём работы.

65. Почему Partition Key важен

PostgreSQL Partition Key Selection:

Если таблица разделена по created_at, но запросы почти всегда фильтруют только organization_id, PostgreSQL может потребоваться обращаться ко многим Partitions.

Поэтому Partition Key должен соответствовать основным Query Patterns и требованиям обслуживания данных.

66. Ограничения Partitioning

PostgreSQL Partitioning Limitations:

Нужно учитывать:

• сложность управления Partitions;

• особенности уникальных ограничений;

• дополнительные индексы;

• изменение Query Plans;

• большое количество Partitions;

• миграции схемы.

Например, уникальное ограничение на Partitioned Table обычно должно включать все столбцы Partition Key.

Часть 12. Replication

67. Что такое PostgreSQL Replication

PostgreSQL Replication — механизм передачи изменений с одного PostgreSQL Server на другой.

Типовая архитектура:

 FastAPI

|

+--------+--------+

| |

v v

Primary Read Replica

|

v

WAL Stream

|

v

Replica

Primary обычно принимает операции записи.

Read Replica может обслуживать запросы чтения.

68. Что такое Streaming Replication

PostgreSQL Streaming Replication передаёт WAL-записи от Primary к Replica.

WAL — Write-Ahead Log.

Он содержит информацию, необходимую для восстановления изменений.

69. Что такое Read Replica

PostgreSQL Read Replica — сервер, который воспроизводит изменения Primary и может обслуживать запросы чтения.

Например:

Write Requests -> Primary

Read Requests -> Replica

Это помогает распределять нагрузку.

Но не каждый запрос следует автоматически направлять на Replica.

70. Что такое Replication Lag

PostgreSQL Replication Lag — отставание Replica от Primary.

Например:

Primary:

Subscription = Active

Replica:

Subscription = Pending

Через некоторое время Replica применит изменения.

Поэтому после критической записи может потребоваться читать данные непосредственно с Primary.

71. Как учитывать Read-After-Write Consistency

Read After Write Consistency PostgreSQL:

Представим:

1. User updates profile

2. Write goes to Primary

3. User refreshes page

4. Read goes to Replica

5. Old profile is returned

Возможные решения:

• временно направлять чтение на Primary;

• использовать механизмы ожидания репликации;

• проектировать UX с учётом задержки;

• выбирать подходящие гарантии согласованности.

72. Synchronous vs Asynchronous Replication

PostgreSQL Synchronous vs Asynchronous Replication:

Конкретные гарантии зависят от конфигурации PostgreSQL и уровня синхронного подтверждения.

73. Replication vs Backup

PostgreSQL Replication vs Backup:

Replication не заменяет Backup.

Если пользователь случайно удалит важные данные, удаление может распространиться на Replica.

Поэтому нужны:

• регулярные Backups;

• WAL Archiving;

• Point-in-Time Recovery;

• проверка восстановления.

Часть 13. PostgreSQL Configuration Tuning

74. Что такое shared_buffers

PostgreSQL shared_buffers — область памяти PostgreSQL для кеширования страниц данных.

Пример:

shared_buffers = 2GB

Оптимальное значение зависит от RAM, нагрузки и операционной системы.

Не стоит автоматически назначать максимально возможный размер.

75. Что такое work_mem

PostgreSQL work_mem — лимит памяти для определённых операций выполнения запросов, например Sort и Hash.

Пример:

work_mem = 16MB

Важно: этот лимит применяется к отдельным операциям, а не ко всему PostgreSQL Process.

Один сложный запрос может использовать несколько таких операций.

Поэтому слишком большое значение может привести к высокому потреблению памяти при конкурентной нагрузке.

76. Что такое maintenance_work_mem

PostgreSQL maintenance_work_mem используется для некоторых операций обслуживания.

Например:

• CREATE INDEX;

• VACUUM;

• ALTER TABLE в определённых сценариях.

Значение следует выбирать с учётом доступной памяти и числа параллельных операций обслуживания.

77. Что такое effective_cache_size

PostgreSQL effective_cache_size — оценка объёма памяти, доступной для кеширования данных PostgreSQL и операционной системой.

Этот параметр помогает Query Planner оценивать стоимость доступа к данным.

Он не резервирует память напрямую.

78. Почему нельзя копировать чужой postgresql.conf

PostgreSQL Configuration Best Practices:

Конфигурация зависит от:

• RAM;

• CPU;

• типа дисков;

• количества подключений;

• характера SQL-запросов;

• объёма данных;

• интенсивности записи.

Настройки, подходящие аналитической базе, могут быть плохими для OLTP-приложения.

Поэтому каждое изменение следует измерять.

Часть 14. Оптимизация PostgreSQL для SaaS

79. Multi-Tenant Database Performance

PostgreSQL Multi-Tenant Performance требует учитывать распределение данных между организациями.

Например:

Organization A:

100 projects

Organization B:

5,000,000 projects

Один Tenant может создавать непропорционально большую нагрузку.

Это называют эффектом Noisy Neighbor.

80. Индексы для Multi-Tenant SaaS

PostgreSQL Tenant Indexing:

CREATE INDEX idx_tasks_org_status_created

ON tasks (

organization_id,

status,

created_at DESC

);

Такой индекс может быть полезен для запросов:

SELECT

id,

title,

created_at

FROM tasks

WHERE organization_id = 123

AND status = 'open'

ORDER BY created_at DESC

LIMIT 50;

Здесь предполагается числовой тип organization_id.

Если в схеме используется UUID, необходимо передавать корректное UUID-значение.

81. Почему Tenant Isolation важнее оптимизации

PostgreSQL Tenant Isolation:

Даже самый быстрый запрос не должен возвращать данные чужой организации.

Поэтому:

SELECT *

FROM tasks

WHERE organization_id = :organization_id;

Фильтр Tenant должен формироваться на основании доверенного контекста авторизации.

Для дополнительной защиты можно использовать Row-Level Security.

82. Что такое Row-Level Security

PostgreSQL Row-Level Security (RLS) позволяет задавать политики доступа к строкам таблицы.

Например:

ALTER TABLE tasks

ENABLE ROW LEVEL SECURITY;

Но само включение RLS не создаёт полноценную безопасную конфигурацию.

Нужно определить политики, роли, особенности владельца таблицы и безопасную передачу Tenant Context.

83. Как избежать Noisy Neighbor

SaaS Noisy Neighbor Prevention:

• Rate Limiting;

• Query Timeouts;

• ограничения тяжёлых отчётов;

• отдельные очереди фоновых задач;

• Monitoring по Tenant;

• оптимизация индексов;

• Resource Isolation при необходимости.

Для очень крупных Enterprise Tenants иногда используют отдельные базы данных или выделенные ресурсы.

Часть 15. Высокая нагрузка и масштабирование

84. Vertical vs Horizontal Scaling PostgreSQL

Vertical vs Horizontal Scaling PostgreSQL:

Vertical Scaling — увеличение ресурсов одного сервера.

Например:

8 CPU -> 16 CPU

32 GB RAM -> 64 GB RAM

Horizontal Scaling — добавление серверов.

Например:

Primary

|

+-- Read Replica 1

|

+-- Read Replica 2

Но обычные Read Replicas не масштабируют запись так же, как чтение.

85. Когда нужны Read Replicas

PostgreSQL Read Scaling полезен, если:

• много тяжёлых запросов чтения;

• Primary перегружен чтением;

• есть аналитические запросы;

• допустима определённая задержка репликации.

Но перед добавлением Replica стоит проверить индексы и Query Plans.

86. Что такое Database Sharding

PostgreSQL Sharding — распределение данных между несколькими независимыми базами или узлами.

Например:

Tenant A -> Database 1

Tenant B -> Database 2

Tenant C -> Database 3

Sharding помогает распределять нагрузку и объём данных.

Но значительно усложняет:

• транзакции;

• миграции;

• JOIN между Shards;

• Backup;

• Routing;

• Rebalancing.

Поэтому Sharding обычно рассматривают после более простых способов оптимизации.

87. Когда PostgreSQL действительно нужен Sharding

When to Shard PostgreSQL:

Sharding может понадобиться, если:

• один Primary больше не справляется с записью;

• объём данных превышает разумные пределы одного узла;

• требуется изоляция крупных Tenants;

• есть естественный Shard Key;

• команда готова поддерживать распределённую инфраструктуру.

Но для многих SaaS достаточно хорошо оптимизированного PostgreSQL с PgBouncer и Read Replicas.

Часть 16. Load Testing PostgreSQL

88. Почему нужны нагрузочные тесты

PostgreSQL Load Testing позволяет оценивать поведение базы под ожидаемой нагрузкой.

Например:

100 concurrent users

500 concurrent users

1,000 concurrent users

Важно измерять не только Requests Per Second, но и:

• P95 Latency;

• Error Rate;

• Database Connections;

• CPU;

• Disk I/O;

• Lock Waits.

89. Что такое pgbench

PostgreSQL pgbench — инструмент нагрузочного тестирования PostgreSQL.

Пример:

pgbench -i -s 10 mydatabase

Затем:

pgbench -c 20 -j 4 -T 60 mydatabase

Параметры:

• -c

  — количество клиентов;

• -j

  — количество потоков;

• -T

  — длительность теста.

pgbench -i создаёт и заполняет тестовые таблицы.

Не следует запускать инициализацию бездумно в Production-базе.

90. Почему Synthetic Benchmark недостаточно

Realistic PostgreSQL Benchmark:

Синтетический тест полезен, но может не отражать реальные запросы SaaS.

Поэтому нужно дополнительно тестировать:

• Dashboard;

• Search;

• Pagination;

• Billing;

• Bulk Operations;

• Analytics;

• Background Jobs.

Реалистичные данные и распределение нагрузки важны не меньше, чем количество виртуальных пользователей.

Часть 17. Production-архитектура

91. Рекомендуемая PostgreSQL Architecture для SaaS

Production PostgreSQL Scaling Architecture:

 React Frontend

|

v

Load Balancer

|

+------------+------------+

| |

v v

FastAPI App 1 FastAPI App 2

| |

+------------+------------+

|

v

PgBouncer

|

v

PostgreSQL Primary

|

+------------+------------+

| |

v v

Read Replica 1 Read Replica 2

| |

+------------+------------+

|

v

Monitoring Stack

|

v

Prometheus + Grafana

В реальной архитектуре маршрутизация чтений на Replica обычно требует отдельной логики приложения, Proxy или подходящей инфраструктуры.

PgBouncer сам по себе не обязан автоматически распределять запросы между Primary и Replicas.

92. Как внедрять оптимизацию поэтапно

PostgreSQL Optimization Roadmap:

Этап 1. Observability

Добавить:

• pg_stat_statements;

• Slow Query Logging;

• PostgreSQL Metrics;

• Query Latency Dashboards.

Этап 2. SQL Optimization

Проверить:

• EXPLAIN ANALYZE;

• N+1 Queries;

• Pagination;

• JOIN;

• SELECT Columns.

Этап 3. Indexing

Добавить:

• Composite Indexes;

• Partial Indexes;

• Covering Indexes;

• GIN/BRIN при необходимости.

Этап 4. Connection Management

Настроить:

• SQLAlchemy Pool;

• PgBouncer;

• Connection Limits;

• Transaction Timeouts.

Этап 5. Database Maintenance

Проверить:

• Autovacuum;

• Dead Tuples;

• Table Bloat;

• Long-Running Transactions;

• Lock Contention.

Этап 6. Scaling

Рассмотреть:

• Vertical Scaling;

• Read Replicas;

• Partitioning;

• Sharding при необходимости.

Часть 18. Типичные ошибки

93. Ошибки оптимизации PostgreSQL

Первая ошибка — увеличивать CPU без анализа SQL-запросов.

Вторая — создавать индексы на все столбцы.

Третья — игнорировать EXPLAIN ANALYZE.

Четвёртая — не использовать pg_stat_statements.

Пятая — выполнять SELECT * без необходимости.

Шестая — допускать N+1 Queries.

Седьмая — использовать глубокую OFFSET Pagination.

Восьмая — создавать слишком большие Connection Pools.

Девятая — считать PgBouncer универсальным ускорителем.

Десятая — игнорировать ограничения Transaction Pooling.

Одиннадцатая — отключать Autovacuum.

Двенадцатая — запускать VACUUM FULL без планирования.

Тринадцатая — не отслеживать Long-Running Transactions.

Четырнадцатая — игнорировать Deadlocks.

Пятнадцатая — использовать Partitioning без подходящего Partition Key.

Шестнадцатая — считать Replication заменой Backup.

Семнадцатая — направлять критические Read-After-Write запросы на отстающую Replica.

Восемнадцатая — использовать Sharding слишком рано.

Девятнадцатая — не учитывать Tenant Isolation.

Двадцатая — копировать чужую PostgreSQL Configuration без тестирования.

Эти ошибки PostgreSQL Performance Tuning могут ухудшить производительность даже после увеличения ресурсов сервера.

Часть 19. Production Checklist

94. Чек-лист оптимизации PostgreSQL

PostgreSQL Performance Production Checklist:

Monitoring

• Включён pg_stat_statements.

• Настроен Slow Query Logging.

• Измеряется Query Latency.

• Отслеживаются Active Connections.

• Отслеживаются Lock Waits.

• Отслеживается Disk I/O.

• Мониторится Replication Lag.

SQL

• Проверены EXPLAIN ANALYZE Plans.

• Устранены критические N+1 Queries.

• Нет лишних SELECT *.

• Используется подходящая Pagination.

• Оптимизированы тяжёлые JOIN.

• Проверена статистика Query Planner.

Indexing

• Есть индексы для основных Query Patterns.

• Проверен порядок столбцов Composite Indexes.

• Используются Partial Indexes при необходимости.

• Нет большого количества бесполезных индексов.

• Индексы создаются безопасно для Production.

Connections

• Рассчитан общий Connection Budget.

• Настроен SQLAlchemy Pool.

• Рассмотрен PgBouncer.

• Учтены ограничения Transaction Pooling.

• Есть Connection Timeouts.

Maintenance

• Работает Autovacuum.

• Мониторятся Dead Tuples.

• Контролируется Table Bloat.

• Отслеживаются Long-Running Transactions.

• Есть стратегия обслуживания крупных таблиц.

Scaling

• Определена нагрузка на чтение и запись.

• Read Replicas используются осознанно.

• Учтена Replication Lag.

• Partitioning применяется по необходимости.

• Sharding не внедряется без оснований.

Reliability

• Есть Backups.

• Настроено восстановление.

• Проверяется Point-in-Time Recovery.

• Есть Monitoring и Alerts.

• Проведены Load Tests.

• Документированы процедуры Incident Response.

FAQ

Как ускорить PostgreSQL?

Начните с поиска дорогих запросов через pg_stat_statements и EXPLAIN ANALYZE. Затем оптимизируйте SQL, индексы и управление подключениями.

Что такое EXPLAIN ANALYZE?

Команда PostgreSQL, которая выполняет запрос и показывает фактический план выполнения с измеренными показателями.

Чем EXPLAIN отличается от EXPLAIN ANALYZE?

EXPLAIN показывает план без выполнения запроса, а EXPLAIN ANALYZE выполняет запрос и собирает фактическую статистику.

Что такое Sequential Scan?

Последовательное чтение строк таблицы.

Всегда ли Sequential Scan плохой?

Нет. Для маленьких таблиц или запросов, возвращающих большую часть строк, он может быть эффективнее индекса.

Что такое Composite Index?

Индекс по нескольким столбцам.

Что такое Partial Index?

Индекс, включающий только строки, соответствующие определённому условию.

Что такое Covering Index?

Индекс, содержащий дополнительные столбцы для возможного выполнения Index-Only Scan.

Что такое GIN Index?

Тип индекса, часто используемый для Full-Text Search, JSONB и массивов.

Что такое BRIN Index?

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

Что такое pg_stat_statements?

Расширение PostgreSQL для сбора статистики SQL-запросов.

Что такое N+1 Query Problem?

Проблема, при которой после одного основного запроса выполняется множество дополнительных запросов для связанных данных.

Как решить N+1 в SQLAlchemy?

Использовать подходящие стратегии загрузки отношений, например selectinload или joinedload.

Почему OFFSET медленный?

При больших смещениях PostgreSQL может потребоваться пройти большое количество строк до нужной страницы.

Что такое Keyset Pagination?

Пагинация на основании значений последней полученной строки вместо большого OFFSET.

Что такое PgBouncer?

Connection Pooler для PostgreSQL.

Зачем нужен Connection Pooling?

Чтобы повторно использовать подключения и контролировать количество соединений с базой данных.

Что такое Transaction Pooling?

Режим PgBouncer, при котором серверное подключение выделяется на время транзакции.

Что такое MVCC?

Механизм управления версиями строк для поддержки конкурентных транзакций.

Что такое VACUUM?

Операция обслуживания PostgreSQL, очищающая ненужные версии строк и выполняющая другие задачи.

Что такое Autovacuum?

Автоматический механизм обслуживания таблиц PostgreSQL.

Чем VACUUM отличается от VACUUM FULL?

VACUUM обычно освобождает пространство для повторного использования, а VACUUM FULL переписывает таблицу и может возвращать пространство файловой системе.

Что такое Table Bloat?

Избыточное использование пространства таблицами или индексами.

Что такое Deadlock?

Ситуация взаимного ожидания ресурсов несколькими транзакциями.

Что такое Partitioning?

Разделение логической таблицы на физические Partitions.

Когда нужен Partitioning?

Часто для больших таблиц событий, логов и данных с естественным разделением по времени.

Что такое Partition Pruning?

Исключение ненужных Partitions при выполнении запроса.

Что такое Read Replica?

Сервер PostgreSQL, воспроизводящий изменения Primary и способный обслуживать чтение.

Что такое Replication Lag?

Отставание Replica от Primary.

Заменяет ли Replication Backup?

Нет. Репликация не защищает от всех сценариев потери или ошибочного изменения данных.

Что такое Sharding?

Распределение данных между несколькими независимыми базами или узлами.

Нужен ли Sharding для SaaS?

Не всегда. Во многих проектах достаточно оптимизации SQL, индексов, PgBouncer и Read Replicas.

Что такое pgbench?

Инструмент нагрузочного тестирования PostgreSQL.

Какой PostgreSQL Index самый быстрый?

Универсально самого быстрого индекса нет. Выбор зависит от структуры запроса и данных.

Какую архитектуру PostgreSQL выбрать для FastAPI?

Для начала обычно достаточно одного PostgreSQL Primary, правильно настроенного SQLAlchemy Pool, индексов и Monitoring. Дополнительные компоненты следует внедрять по мере необходимости.

Заключение

PostgreSQL способен эффективно обслуживать крупные Full Stack приложения, но высокая производительность требует системного подхода.

Самая распространённая ошибка — пытаться решить проблемы базы данных исключительно увеличением серверных ресурсов.

На практике часто больше пользы приносят:

• оптимизация SQL-запросов;

• правильные индексы;

• устранение N+1 Queries;

• Keyset Pagination;

• настройка Connection Pooling;

• контроль Autovacuum;

• анализ блокировок;

• мониторинг реальной нагрузки.

EXPLAIN ANALYZE помогает понять, как PostgreSQL выполняет запросы.

pg_stat_statements показывает, какие SQL-операции создают основную нагрузку.

Composite и Partial Indexes позволяют ускорять типичные запросы SaaS.

PgBouncer помогает управлять большим количеством подключений.

Autovacuum поддерживает нормальное состояние таблиц.

Partitioning упрощает обслуживание некоторых больших наборов данных.

Read Replicas позволяют распределять нагрузку чтения.

Sharding может понадобиться при дальнейшем росте, но его следует рассматривать только после оценки более простых решений.

Главный принцип: PostgreSQL нужно оптимизировать на основании измерений, а не предположений.

Сначала найдите узкое место.

Затем сформулируйте гипотезу.

Проверьте её через EXPLAIN ANALYZE, Metrics и Load Testing.

Внесите изменение.

После этого сравните результаты.

Такой подход позволяет масштабировать SaaS-приложение постепенно, без преждевременного усложнения архитектуры.