WITH s AS (
SELECT pg_column_size(instance)::bigint AS sz
FROM top_product_storage.product_instance_aud
where rev > 6232000000 and rev < 6249000000
)
SELECT
count(*) AS rows,
pg_size_pretty(min(sz)) AS min_size,
pg_size_pretty(percentile_cont(0.25) WITHIN GROUP (ORDER BY sz)::bigint) AS p25,
pg_size_pretty(percentile_cont(0.50) WITHIN GROUP (ORDER BY sz)::bigint) AS median,
pg_size_pretty(percentile_cont(0.75) WITHIN GROUP (ORDER BY sz)::bigint) AS p75,
pg_size_pretty(percentile_cont(0.90) WITHIN GROUP (ORDER BY sz)::bigint) AS p90,
pg_size_pretty(percentile_cont(0.95) WITHIN GROUP (ORDER BY sz)::bigint) AS p95,
pg_size_pretty(percentile_cont(0.99) WITHIN GROUP (ORDER BY sz)::bigint) AS p99,
pg_size_pretty(max(sz)) AS max_size,
pg_size_pretty(avg(sz)::bigint) AS avg_size,
pg_size_pretty(sum(sz)) AS total_size
FROM s;
WITH s AS ( SELECT pg_column_size(instance)::bigint AS sz FROM top_product_storage.product_instance_aud where rev > 6232000000 and rev < 6249000000 ) SELECT count(*) AS rows, pg_size_pretty(min(sz)) AS min_size, pg_size_pretty(percentile_cont(0.25) WITHIN GROUP (ORDER BY sz)::bigint) AS p25, pg_size_pretty(percentile_cont(0.50) WITHIN GROUP (ORDER BY sz)::bigint) AS median, pg_size_pretty(percentile_cont(0.75) WITHIN GROUP (ORDER BY sz)::bigint) AS p75, pg_size_pretty(percentile_cont(0.90) WITHIN GROUP (ORDER BY sz)::bigint) AS p90, pg_size_pretty(percentile_cont(0.95) WITHIN GROUP (ORDER BY sz)::bigint) AS p95, pg_size_pretty(percentile_cont(0.99) WITHIN GROUP (ORDER BY sz)::bigint) AS p99, pg_size_pretty(max(sz)) AS max_size, pg_size_pretty(avg(sz)::bigint) AS avg_size, pg_size_pretty(sum(sz)) AS total_size FROM s;
При этом на один продукт в аудит таблицах хранится по медиане 4 версии, а по p99 22 версии, p95 12 (приблизительные данные за 24 часа). Соответственно столько патчей на один продукт в среднем и летит.
WITH lens AS ( SELECT octet_length(instance::text)::bigint AS len — реальный «сырой» размер JSON, bigint FROM top_product_storage.product_instance_aud_6232000000_6233000000 limit 20 ) SELECT pg_size_pretty( (percentile_cont(0.50) WITHIN GROUP (ORDER BY len))::bigint ) AS median_raw, pg_size_pretty( (percentile_cont(0.90) WITHIN GROUP (ORDER BY len))::bigint ) AS p90_raw, pg_size_pretty( avg(len)::bigint ) AS avg_raw FROM lens;
“2664 bytes” “3676 bytes” “4875 bytes” Ориентировочные RAW размеры (без сжатия) jsonb колонки (выборка меньше и в другом окне) p50 - 1996 bytes p90 - 4780 bytes p99 - 32 kB
Альтернативные результаты product_task (одна полная строка в бд) min 178 b p25 197 b median 209b p75 226b p90 242b p99 266b max_size 325b avg 213b
520 мб чистого веса продукт тасок в сутки
participant_role (одна полная строка в бд) min 226b p25 364 median 396 p75 438 p90 482 p99 519 max 555b avg 404
при обычной динамике за 24 часа сформируется примерно 1,6 млн версий объёмом ≈ 640 мб
realty_info
min 237 bytes p25 324 bytes median 324 bytes p75 340 bytes p90 340 bytes p95 348 bytes p99 380 bytes max 554 bytes avg 328 bytes
при обычной динамике формируется 3 мб в сутки
product car min size 219 bytes p25 321 bytes median 321 bytes p75 514 bytes p90 544 bytes p95 545 bytes p99 553 bytes max 882 bytes avg 372 bytes
генерирует ≈ 7,7 тыс. версий и ~2,8 МБ в сутки
participant_role (одна полная строка в бд)
min 226 B
median 396 B
p90 482 B
p99 519 B
max 555 B
avg 404 B
product_task
min 178 B
median 209 B
p90 242 B
p99 266 B
max_size 325 B
avg 213 B
realty_info
min 237 B
median 324 B
p90 340 B
p99 380 B
max_size 554 B
avg 328 B
product car
min 219 B
median 321 bytes
p90 544 bytes
p99 553 bytes
max 882 bytes
avg 372 bytes
итого альтернативных сущностей 520 + 640 + 3 + 3 = 1166
Итого 104 гб за 90 дней