Выбор лучшей конфигурации таблицы для TimescaleDB

Описание задачи

Существует довольно распространенная задача для маркетплейсов - рассчитывать объем продаж для некоторых продуктов в течение 1 дня/7 дней/30 дней в режиме почти реального времени. TimescaleDB или любая другая база данных временных рядов идеально подходит для такой задачи. Давайте протестируем TimescaleDB, так как в моем текущем проекте уже используется PostgreSQL. Я буду использовать очень упрощенную модель из моего текущего проекта (рынок NFT). У нас есть тысячи коллекций для множества блокчейнов, и мы собираем каждую трансакцию листинга/предложения или продажи в таблице. Общее количество трансакций огромное: миллиарды строк. Идея о том, как мы можем сделать расчет быстрым, заключается в разделении нашей таблицы на временные сегменты и расчете чисел, используя их.

Подготовка

Это схема таблицы и некоторые индексы:

create table public.sale_changes
(
    time          timestampz not null,
    id            varchar(1024),
    collection_id varchar(512),
    blockchain    varchar(512),
    floor         numeric(78, 10),
    sales         bigint,
    volume        numeric(78, 10)
);
select create_hypertable('sale_changes', 'time', chunk_time_interval = > interval '1 day');
create unique index ON sale_changes(blockchain, id, time);
create index ON sale_changes(collection_id, time);
create * from add_dimension('sale_changes', by_hash('blockchain', 16));

Мы создали несколько дополнительных индексов:

• CREATE UNIQUE INDEX ON sale_changes(blockchain, id, time); -- это необходимо для согласованности данных
• CREATE INDEX ON sale_changes(collection_id, time); -- это для более быстрой агрегации по коллекциям

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

Я сделал резервную копию данных за текущий год и загрузил ее в мой локальный TimescaleDB:

• POLYGON: 4917155 строк
• ETHEREUM: 3032708 строк
• APTOS: 129760 строк
• CELO: 13692 строк
• ZKSYNC: 8832 строк

Итак, у нас есть локально 8102147 (~8 миллионов).

Давайте используем 2 запроса для тестирования производительности:

-- all query
select csc.*,
       cs.floor,
       cs.owners from (select collection_id,
       SUM(volume),
       SUM(sales)  AS total_sales,
       CASE
           WHEN first(floor, time) = 0 THEN 0
           ELSE ROUND(((last(floor, time) - first(floor, time)) / first(floor, time)) * 100, 2)
           END         AS floor_change
from sale_changes
where time >= NOW() - INTERVAL '$period'
group by collection_id
order by floor_change DESC
limit ?

-- Specific chain query:
select csc.*,
       cs.floor,
       cs.owners
from (select collection_id,
             SUM(volume),
             SUM(sales) AS total_sales,
             CASE
                 WHEN first(floor, time) = 0 THEN 0
          ELSE ROUND(((last (floor, time) - first (floor, time)) / first (floor, time)) * 100, 2)
          END AS floor_change
                           from $table
WHERE blockchain = ? and time >= NOW() - INTERVAL '$period'
group by collection_id
order by floor_change DESC
limit ?

Тестирование

Мы будем тестировать производительность, выполняя каждый запрос 10 раз и собирая метрики по времени выполнения. Моя локальная среда:

• MacBook M1 с 32 ГБ ОЗУ
• TimescaleDB 2.17.2-pg17 в Docker
• увеличенные настройки памяти для pg: shared_buffers=8GB, work_mem=8GB

Первая попытка

Результаты использования первого запроса:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	71	105	77.3	74.5	9.7
1 день	100	73	81	76.0	75.0	2.4
7 дней	100	161	192	168.1	166.0	8.5
30 дней	100	515	630	536.3	526.5	32.2

Результаты использования второго запроса с небольшим блокчейном CELO:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	13	101	23.7	15.0	25.8
1 день	100	13	99	23.4	14.5	25.2
7 дней	100	14	99	24.5	15.5	24.9
30 дней	100	16	104	26.1	17.5	26.0

Результаты использования второго запроса с крупным блокчейном ETHEREUM:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	27	114	37.8	29.5	25.5
1 день	100	27	112	37.0	28.0	25.0
7 дней	100	66	172	79.6	68.5	30.9
30 дней	100	206	382	251.6	220.0	61.9

Мы видим, что запросы для интервала в 30 секунд не такие быстрые, как ожидалось, за исключением блокчейна CELO. Это совершенно ожидаемо, поскольку базе данных необходимо обрабатывать больше строк. Давайте попробуем другую конфигурацию и посмотрим, сможем ли улучшить производительность.

Вторая попытка

Давайте включим сжатие. Мы будем использовать collection_id как сегмент сжатия, поскольку имеет смысл хранить эти значения вместе.

ALTER TABLE sale_changes SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'collection_id'
    );
SELECT compress_chunk(c) from show_chunks('sale_changes') c;

Результаты использования первого запроса:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	249	1091	355.9	261.5	247.0
1 день	100	237	286	255.4	251.0	15.4
7 дней	100	247	278	259.4	260.5	8.9
30 дней	100	349	412	370.2	358.5	21.0

Результаты использования второго запроса с небольшим блокчейном CELO:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	19	131	33.1	21.0	32.8
1 день	100	18	128	32.9	21.5	32.1
7 дней	100	21	129	33.3	22.0	31.9
30 дней	100	25	151	42.1	25.0	36.9

Результаты использования второго запроса с крупным блокчейном ETHEREUM:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	90	203	115.7	99.0	34.6
1 день	100	94	208	110.5	99.5	32.8
7 дней	100	95	219	111.1	98.5	36.3
30 дней	100	149	272	170.7	159.5	34.2

Сжатая таблица дала худшие результаты... Даже для запросов на малый интервал, однако для 30 дней в случае всех блокчейнов это улучшило результат. Полагаю, хранение чисел вместе по collection_id помогло здесь.

Третья попытка

Попробуем увеличить сегмент для нашей гипертаблицы, учитывая, что у нас достаточно ОЗУ. Следуя официальной рекомендации, хорошо, если сегмент может занимать 25% памяти. В настоящее время у меня выделено 24 ГБ ОЗУ для Docker.

select create_hypertable('sale_changes', 'time', chunk_time_interval => interval '7 day');
CREATE UNIQUE INDEX ON sale_changes(blockchain, id, time);
CREATE INDEX ON sale_changes(collection_id, time);
SELECT * FROM add_dimension('sale_changes', by_hash('blockchain', 16));

Результаты использования первого запроса:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	10	61	17.1	11.0	14.8
1 день	100	10	18	11.6	11.0	2.2
7 дней	100	87	119	91.5	88.0	9.3
30 дней	100	367	514	390.4	373.5	42.9

Результаты использования второго запроса с небольшим блокчейном CELO:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	2	14	5.0	4.5	3.2
1 день	100	2	12	5.0	4.0	2.7
7 дней	100	3	15	6.2	5.0	3.4
30 дней	100	6	15	7.6	7.0	2.6

Результаты использования второго запроса с крупным блокчейном ETHEREUM:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	3	14	5.9	5.0	2.8
1 день	100	5	13	6.0	5.0	2.4
7 дней	100	32	47	37.6	34.5	5.4
30 дней	100	129	140	133.3	132.0	3.2

Теперь у нас намного лучшие результаты! Мне не нравится только запрос всех цепочек за 30 дней. Запросы CELO/ETHEREUM действительно быстрые.

Четвертая попытка

Попробуем использовать сжатие для 7-дневного сегмента.

Результаты использования первого запроса:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	47	153	60.3	49.5	31.0
1 день	100	50	62	54.8	54.0	4.4
7 дней	100	62	73	67.1	67.0	3.6
30 дней	100	139	182	150.4	145.0	12.5

Результаты использования второго запроса с небольшим блокчейном CELO:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	18	39	22.6	20.0	6.1
1 день	100	20	36	25.1	23.0	5.7
7 дней	100	6	21	8.0	7.0	4.4
30 дней	100	7	21	9.4	8.0	4.0

Результаты использования второго запроса с крупным блокчейном ETHEREUM:

Период	Лимит	Мин. Время (мс)	Макс. Время (мс)	Средн. Время (мс)	Медиан. Время (мс)	Стд. Отклонение (мс)
1 час	100	21	34	23.8	23.0	3.5
1 день	100	23	59	29.0	24.5	10.5
7 дней	100	31	58	38.2	36.5	7.4
30 дней	100	59	74	64.5	63.5	4.2

Наконец, наши запросы стали очень быстрыми. Самый медленный по-прежнему все цепочки за 30 дней, однако медианное время <0.2s, так что я бы сказал, что это быстро для агрегации среди миллионов строк.

Графики

Заключение

Посмотрим на размеры таблиц с использованием запроса:

select 
    hypertable_name, 
    pg_size_pretty(hypertable_size(format('%I.%I', hypertable_schema, hypertable_name)::regclass))
from timescaledb_information.hypertables;

• 1дневная таблица сегментов: 2.4 ГБ
• 1дневная таблица сегментов с компрессией: 2.6 ГБ
• 7дневная таблица сегментов: 2.2 ГБ
• 7дневная таблица сегментов с компрессией: 0.3 ГБ

Это почти в 10 раз больше между первой таблицей и последней, это объясняет, почему таблица сегментов с 7дн с компрессией работает так быстро. Конфигурация с 7дн сегментом и компрессией - явный победитель.