Диск кончается не в тот день, когда ты на него посмотрел: размер базы, WAL, запас под перепаковку и память под соединения растут с разной скоростью. Планирование ёмкости — это таблица, в которую раз в сутки пишутся эти числа (что мониторить), регрессия по ней и формула, которая переводит тренд в гигабайты на год вперёд с порогом, за которым нужно решение: партиционировать, архивировать, добавлять узел.
Прогноз диска на 12 месяцев: два сценария роста и порог решения
Что здесь измерено, а что задано параметром
База 200 ГБ, выросшая до 1,1 ТБ за год, на этой машине невозможна, и числа таких баз здесь не приводятся. Измерено другое: сбор каждого показателя в таблицу истории и вывод тренда. «Сутки» имитируют 14 шагов нагрузки, и каждый шаг — снимок. Шаг d вставляет 150000 + 10000·d строк в таблицу events, а затем 8 секунд четыре клиента pgbench читают строки по первичному ключу. Растущий суточный объём задан в нагрузке нарочно, чтобы тренд было что находить.
Всё, чего в опыте нет, названо параметром примера: том 100 ГБ и порог 80 %, хранение WAL 7 суток, 200 соединений, два узла сортировки в запросе. Подставь свои. Абсолютные гигабайты модели относятся к этой учебной базе, а не к твоей: переносится форма формул.
docker run -d --name dba-capacity-planning --memory 2g -e POSTGRES_PASSWORD=pg postgres:18
docker exec -it dba-capacity-planning psql -U postgresТаблица истории и сборщик
Таблица events с первичным ключом и индексом по account, плюс таблица истории: по строке на «сутки».
SELECT version();
CREATE EXTENSION pg_buffercache;
CREATE TABLE events (id bigserial PRIMARY KEY, account int NOT NULL, payload text NOT NULL);
CREATE INDEX events_account_idx ON events (account);
CREATE TABLE capacity_history (
day int PRIMARY KEY, taken_at timestamptz NOT NULL DEFAULT now(),
db_bytes bigint, wal_bytes bigint, wal_fpi bigint, wal_lsn pg_lsn,
io_hits bigint, io_reads bigint, cached_bytes bigint, hot_bytes bigint);Сборщик — один INSERT ... SELECT (файл collect.sql, переменная :day задаётся из psql -v day=N):
INSERT INTO capacity_history
SELECT :day, now(),
pg_database_size(current_database()),
(SELECT wal_bytes::bigint FROM pg_stat_wal),
(SELECT wal_fpi FROM pg_stat_wal),
pg_current_wal_lsn(),
(SELECT sum(hits) FROM pg_stat_io WHERE object = 'relation'),
(SELECT sum(reads) FROM pg_stat_io WHERE object = 'relation'),
(SELECT count(*) * current_setting('block_size')::int FROM pg_buffercache
WHERE reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())),
(SELECT count(*) * current_setting('block_size')::int FROM pg_buffercache
WHERE usagecount >= 3
AND reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database()));Четыре источника: размер, WAL, попадания в кеш и содержимое shared_buffers. Каждый нужно читать с оговоркой. pg_database_size (тег REL_18_6, calculate_database_size) считает только каталог базы:
/* Shared storage in pg_global is not counted */
/* Include pg_default storage */
snprintf(pathname, sizeof(pathname), "base/%u", dbOid);
totalsize = db_dir_size(pathname);pg_wal в эту сумму не входит. Счётчики pg_stat_wal накапливаются с начала статистики, поэтому пишется накопленное значение, а суточный объём получается разностью. Источник счётчика — XLogInsertRecord: каждая вставленная запись прибавляет свою длину, а полностраничные образы считаются отдельно:
/* Report WAL traffic to the instrumentation. */
if (inserted)
{
pgWalUsage.wal_bytes += rechdr->xl_tot_len;
pgWalUsage.wal_records++;
pgWalUsage.wal_fpi += num_fpi;
/* Required for the flush of pending stats WAL data */
pgstat_report_fixed = true;
}Цикл «суток» (скрипты grow.sql и read.sql лежат в контейнере: первый — INSERT INTO events (account, payload) SELECT (random() * 100000)::int, repeat(md5(random()::text), 3) FROM generate_series(1, :n), второй — SELECT payload FROM events WHERE id = :id с \set id random(1, :maxid)):
P="docker exec -i dba-capacity-planning psql -U postgres -v ON_ERROR_STOP=1 -Atq"
$P -f /tmp/collect.sql -v day=0
for d in $(seq 1 14); do
$P -v n=$((150000 + 10000 * d)) -f /tmp/grow.sql
max=$($P -c "select max(id) from events")
docker exec dba-capacity-planning pgbench -U postgres -n -c 4 -j 2 -T 8 -D maxid=$max -f /tmp/read.sql postgres >/dev/null
$P -f /tmp/collect.sql -v day=$d
doneИстория за 14 шагов
Накопленные значения неудобны, поэтому поверх таблицы лежит представление с разностями: прирост размера и WAL за шаг, hit_pct как доля попаданий среди обращений к буферам за шаг.
CREATE VIEW capacity_daily AS
SELECT day, db_bytes,
db_bytes - lag(db_bytes) OVER w AS db_delta,
wal_bytes - lag(wal_bytes) OVER w AS wal_delta,
pg_wal_lsn_diff(wal_lsn, lag(wal_lsn) OVER w)::bigint AS lsn_delta,
wal_fpi - lag(wal_fpi) OVER w AS fpi_delta,
round(100.0 * (io_hits - lag(io_hits) OVER w)
/ ((io_hits - lag(io_hits) OVER w) + (io_reads - lag(io_reads) OVER w)), 2) AS hit_pct,
cached_bytes, hot_bytes
FROM capacity_history WINDOW w AS (ORDER BY day);
SELECT day, pg_size_pretty(db_bytes) AS db, pg_size_pretty(db_delta) AS grew,
pg_size_pretty(wal_delta) AS wal, pg_size_pretty(lsn_delta) AS lsn, fpi_delta AS fpi,
hit_pct, pg_size_pretty(cached_bytes) AS cached, pg_size_pretty(hot_bytes) AS hot
FROM capacity_daily ORDER BY day; day | db | grew | wal | lsn | fpi | hit_pct | cached | hot
-----+---------+-------+--------+--------+------+---------+---------+---------
0 | 7782 kB | | | | | | 2488 kB | 1384 kB
1 | 37 MB | 29 MB | 47 MB | 48 MB | 1 | 100.00 | 32 MB | 31 MB
2 | 64 MB | 28 MB | 49 MB | 50 MB | 1 | 100.00 | 59 MB | 59 MB
3 | 95 MB | 31 MB | 53 MB | 54 MB | 32 | 100.00 | 90 MB | 90 MB
4 | 126 MB | 30 MB | 55 MB | 56 MB | 0 | 100.00 | 121 MB | 120 MB
5 | 158 MB | 32 MB | 58 MB | 59 MB | 15 | 97.76 | 128 MB | 54 MB
6 | 193 MB | 36 MB | 62 MB | 63 MB | 1 | 93.90 | 128 MB | 32 MB
7 | 231 MB | 38 MB | 65 MB | 66 MB | 1 | 91.25 | 128 MB | 31 MB
8 | 268 MB | 37 MB | 66 MB | 67 MB | 1 | 89.52 | 128 MB | 34 MB
9 | 307 MB | 38 MB | 142 MB | 144 MB | 9782 | 88.86 | 128 MB | 34 MB
10 | 346 MB | 40 MB | 72 MB | 73 MB | 8 | 86.54 | 128 MB | 30 MB
11 | 389 MB | 43 MB | 76 MB | 78 MB | 10 | 86.07 | 128 MB | 22 MB
12 | 435 MB | 46 MB | 79 MB | 81 MB | 1 | 85.22 | 128 MB | 15 MB
13 | 484 MB | 48 MB | 82 MB | 84 MB | 2 | 83.62 | 128 MB | 10 MB
14 | 532 MB | 48 MB | 84 MB | 86 MB | 1 | 83.38 | 128 MB | 7528 kBЧто в таблице видно.
- Размер и WAL растут вместе. WAL за шаг в 1,6–1,8 раза больше прироста базы (кроме дня 9). Два источника,
wal_bytesизpg_stat_walи разностьpg_current_wal_lsn(), расходятся на 1–2 МБ за шаг: для планирования годится любой. - День 9 — выброс, а не тренд. WAL 142 МБ при росте базы 38 МБ, и 9782 полностраничных образа против единиц в соседних днях. Начало последнего checkpoint (
redo_lsn) лежит между снимками дней 8 и 9, поэтому первая запись каждой страницы после него положила её целиком (полностраничные записи, checkpoint).
SELECT checkpoint_time, redo_lsn FROM pg_control_checkpoint();
SELECT day, taken_at, wal_lsn FROM capacity_history WHERE day IN (8, 9, 10); checkpoint_time | redo_lsn
------------------------+------------
2026-10-28 11:57:41+00 | 0/22362F38
day | taken_at | wal_lsn
-----+-------------------------------+------------
8 | 2026-10-28 11:57:40.151987+00 | 0/1E775600
9 | 2026-10-28 11:57:50.512039+00 | 0/2771F068
10 | 2026-10-28 11:58:00.54779+00 | 0/2C00ACF8Запрос выполнен сразу после цикла: 0/22362F38 лежит между 0/1E775600 и 0/2771F068.
- Рабочее множество упёрлось в
shared_buffers. Пока база меньше 128 МБ,cachedравен ей почти целиком, аhit_pct— 100,00. С дня 5 (158 МБ)cachedстоит на 128 МБ, аhit_pctпадает: 97,76, затем 93,90, на дне 8 (268 МБ, вдвое большеshared_buffers) — 89,52. Объём кеша потолка не показывает, потолок показывает hit ratio. «Горячая» часть (usagecount≥ 3) сжалась со 120 до 7,5 МБ: случайные обращения размазались по растущей таблице, и счётчик использования не копится (clock sweep).
Тренд регрессией
Прирост за сутки не постоянен: он сам растёт. Его тренд — линейная регрессия по шагам, WAL на байт роста — медиана, чтобы выброс дня 9 не сдвигал оценку.
CREATE VIEW capacity_trend AS
SELECT regr_slope(db_delta, day) AS inc_slope,
regr_intercept(db_delta, day) AS inc_icpt,
regr_r2(db_delta, day) AS inc_r2,
(SELECT avg(db_delta) FROM capacity_daily WHERE day > 7) AS inc_last7,
percentile_cont(0.5) WITHIN GROUP (ORDER BY wal_delta::float8 / db_delta) AS wal_per_byte,
(SELECT db_bytes FROM capacity_daily WHERE day = 14) AS size_now
FROM capacity_daily WHERE day >= 1;
SELECT pg_size_pretty(inc_slope::bigint) AS inc_slope_per_day, round(inc_r2::numeric, 3) AS inc_r2,
pg_size_pretty(inc_last7::bigint) AS inc_last7, round(wal_per_byte::numeric, 2) AS wal_per_db_byte
FROM capacity_trend; inc_slope_per_day | inc_r2 | inc_last7 | wal_per_db_byte
-------------------+--------+-----------+-----------------
1674 kB | 0.962 | 43 MB | 1.76Суточный прирост растёт на 1674 КБ за шаг (R² 0,962), последние семь шагов дали в среднем 43 МБ в сутки, на каждый байт роста базы приходится 1,76 байта WAL. Это и есть исходные данные модели.
Запас на перепаковку и память
Диск должен вмещать не только данные. Перепаковка таблицы (VACUUM FULL и pg_repack) держит рядом старую и новую копии. Пик размера каталога базы во время VACUUM FULL events замерен опросом du раз в 0,2 с (скрипт идёт внутри контейнера и сам ждёт фоновый VACUUM FULL):
D=$(psql -U postgres -Atc "select oid from pg_database where datname='postgres'")
psql -U postgres -Atq -c "VACUUM FULL events" &
P=$!; peak=0
while kill -0 $P 2>/dev/null; do
s=$(du -sb /var/lib/postgresql/18/docker/base/$D | cut -f1); [ "$s" -gt "$peak" ] && peak=$s; sleep 0.2
done
echo "peak_bytes=$peak"peak_bytes=1084839615База перед этим занимала 557 569 727 байт, пик составил 1,95 её размера. Доля самой большой таблицы в базе в опыте pg_total_relation_size('events') / pg_database_size(...) = 0,985, потому что таблица одна. Эта доля f и есть запас: у боевой базы подставь свою. Отдельно pg_wal: в конце опыта du -sb показал для него 1 073 741 824 байта, для base — 562 859 917 байт. Здесь каталог WAL совпал с max_wal_size (1024 МБ в pg_settings) и в pg_database_size не входит.
Память складывается из shared_buffers, частных страниц каждого соединения, work_mem на каждый узел сортировки или хеша и maintenance_work_mem на каждого autovacuum-воркера. Частная часть соединения замерена в /proc/<pid>/status (RssAnon) у 20 бэкендов под pgbench -c 20, а потребность узла — Sort Method в EXPLAIN:
backends=20 sum_RssAnon_kB=65772 avg_kB=3288EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT id FROM events WHERE id <= 300000 ORDER BY payload;
SET work_mem = '64MB'; -- и тот же запрос ещё раз Sort Method: external merge Disk: 33800kB
Sort Method: quicksort Memory: 47848kBСортировка 300 000 строк просит 47,8 МБ: при work_mem 4 МБ она ушла на диск (33,8 МБ), при 64 МБ уложилась в память. Верхняя граница по формуле при параметрах примера (200 соединений, два узла по 64 МБ, три воркера по 64 МБ):
SELECT pg_size_pretty(((128 + 200 * (3288 / 1024.0 + 2 * 64) + 3 * 64) * 1024^2)::bigint) AS total_mem; total_mem
-----------
26 GBОсновная часть — соединения × узлы × work_mem (25 ГБ из 26), а не shared_buffers. Это граница худшего случая, а не прогноз; сокращает её пулер соединений.
Модель на 12 месяцев
Нужное место = данные × (1 + f) + хранение WAL × w × суточный прирост. Здесь f = 0,985 (запас на перепаковку), w = 1,76 (WAL на байт роста), хранение — 7 суток (параметр). Два сценария суточного прироста g(d) на шаге d после 14-го: A — постоянный, равный среднему последних семи шагов (inc_last7); B — растущий по регрессии, g(d) = inc_icpt + inc_slope·(14 + d), размер S_B(d) = size_now + d·(inc_icpt + inc_slope·14) + inc_slope·d·(d+1)/2. Месяц модели — 30 шагов; порог — 80 % тома в 100 ГБ (параметры примера).
CREATE VIEW capacity_model AS
WITH p AS (
SELECT 7 AS wal_days, 0.8 * 100 * 1024^3 AS limit_bytes,
pg_total_relation_size('events')::float8 / pg_database_size(current_database()) AS f
)
SELECT d, limit_bytes,
size_a * (1 + f) + wal_days * wal_per_byte * g_a AS need_a,
size_b * (1 + f) + wal_days * wal_per_byte * g_b AS need_b,
size_a, size_b
FROM (SELECT d, f, wal_days, wal_per_byte, limit_bytes,
size_now + inc_last7 * d AS size_a, inc_last7 AS g_a,
size_now + d * (inc_icpt + inc_slope * 14) + inc_slope * d * (d + 1) / 2 AS size_b,
inc_icpt + inc_slope * (14 + d) AS g_b
FROM generate_series(0, 360) AS d, capacity_trend, p) s;
SELECT d / 30 AS month,
round((size_a / 1024^3)::numeric, 1) AS a_data_gb, round((need_a / 1024^3)::numeric, 1) AS a_need_gb,
round((size_b / 1024^3)::numeric, 1) AS b_data_gb, round((need_b / 1024^3)::numeric, 1) AS b_need_gb,
round((limit_bytes / 1024^3)::numeric, 1) AS limit_gb
FROM capacity_model WHERE d % 30 = 0 ORDER BY d;
SELECT min(d) AS first_step, round(min(d) / 30.0, 1) AS month FROM capacity_model WHERE need_b > limit_bytes; month | a_data_gb | a_need_gb | b_data_gb | b_need_gb | limit_gb
-------+-----------+-----------+-----------+-----------+----------
0 | 0.5 | 1.5 | 0.5 | 1.6 | 80.0
1 | 1.8 | 4.0 | 2.7 | 6.5 | 80.0
2 | 3.0 | 6.5 | 6.3 | 14.2 | 80.0
3 | 4.3 | 9.0 | 11.3 | 24.7 | 80.0
4 | 5.6 | 11.5 | 17.7 | 38.2 | 80.0
5 | 6.8 | 14.0 | 25.6 | 54.4 | 80.0
6 | 8.1 | 16.5 | 35.0 | 73.6 | 80.0
7 | 9.3 | 19.0 | 45.8 | 95.5 | 80.0
8 | 10.6 | 21.5 | 58.0 | 120.4 | 80.0
9 | 11.8 | 24.0 | 71.6 | 148.1 | 80.0
10 | 13.1 | 26.5 | 86.7 | 178.6 | 80.0
11 | 14.4 | 29.0 | 103.2 | 212.0 | 80.0
12 | 15.6 | 31.5 | 121.2 | 248.2 | 80.0
first_step | month
------------+-------
190 | 6.3Те же 14 снимков дают 31,5 ГБ в сценарии A и 248,2 ГБ в B. B превышает порог в 80 ГБ впервые на 190-м шаге (месяц 6,3), A порога за год не достигает. Разница между сценариями — это вопрос не к модели, а к тренду: если inc_r2 по свежим данным высокий, а наклон положителен, верен B.
Что из этого следует
- Точка решения выбирается по порогу, а не по концу диска. Порог в 80 % тома даёт время на партиционирование: в опыте до него 190 шагов, и это время расходуется на миграцию.
- Смотри суточный прирост, а не размер. Размер — сумма прироста: одни и те же 14 снимков дают 31,5 или 248,2 ГБ в зависимости от того, постоянен ли прирост. Ускоряется ли рост, показывают регрессия по приросту и её R².
- WAL, запас на перепаковку и память — часть расчёта.
pg_database_sizeне видитpg_wal, перепаковка поднимает пик каталога до 1,95 размера базы, а худший случай памяти растёт линейно с числом соединений. - Упор в
shared_buffersвиден по hit ratio, а не по кешу. Когдаcached_bytesстал равенshared_buffers, следующий сигнал — падениеhit_pct; порог «ниже 90 %» в опыте пройден на дне 8 (параметр примера).
Чек-лист
- Заведи таблицу истории и собирай
pg_database_size,pg_stat_wal,pg_stat_ioиpg_buffercacheраз в сутки; храни накопленные значения, разности считай представлением. - Тренд считай регрессией по суточному приросту (
regr_slope,regr_r2), WAL на байт роста — медианой, чтобы выбросы после checkpoint не сдвигали оценку. - Прогноз строй минимум в двух сценариях (прирост постоянный и растущий), в нужном месте учитывай запас на перепаковку и WAL.
- Порог решения задай заранее (доля тома, hit ratio) и запиши, какое действие он запускает.
- Убери контейнер вместе с данными:
docker rm -f -v dba-capacity-planning.