yet another dev: “Потоковая вставка данных в Postgres через денормализованные таблицы В…”

Потоковая вставка данных в Postgres через денормализованные таблицы

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

Подготовительная часть

Нам нужна промежуточная таблица. В Postgres удобно использовать временные таблицы, которые автоматически удаляются после коммита:

CREATE TEMP TABLE … ON COMMIT DROP;

Процесс вставки

1️⃣ Данные из источника, например из csv-файла, вставляются напрямую в SQL промежуточную таблицу с помощью COPY.

var sql = 
"""
copy stage_table
(resource, billing_date, cost)
from stdin (format binary)
""";

using var importer = conn.BeginBinaryImport(sql);

foreach (var d in DataRows)
{
  importer.StartRow();
  importer.Write(d.Resource, NpgsqlDbType.Text);
  importer.Write(d.BillingDate, NpgsqlDbType.Date);
  importer.Write(d.Cost, NpgsqlDbType.Integer);
}

2️⃣ Денормализованные данные из stage_table объединяются с данными в нормализованных таблицах при помощи INSERT … ON CONFLICT:

-- Группируем стоимость ресурсов по датам
WITH src AS (
  SELECT resource, billing_date, sum(cost) AS cost
  FROM stage_table
  GROUP BY resource, billing_date
),
-- Вставляем (обновляем) ресурсы
res_map AS (
  INSERT INTO resources(resource)
  SELECT DISTINCT resource
  FROM src
  ON CONFLICT (resource) DO UPDATE
    SET resource = EXCLUDED.resource
  RETURNING id, resource
)

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

3️⃣ Полученные ID используются для вставки данных в billing_data тем же способом через INSERT … ON CONFLICT:

INSERT INTO billing_data(resource_id, billing_date, cost)
-- вставляем id из пред. шага
SELECT m.id, s.billing_date, s.cost 
FROM src s
JOIN res_map m USING (resource)
ON CONFLICT (resource_id, billing_date) DO UPDATE
  SET cost = EXCLUDED.cost

Использовать “GROUP BY resource, billing_date” необязательно. В моём случае, было допустимо сгруппировать стоимость по дням, т.к. более детализированные данные не нужны. Если нужна детализация, то GROUP BY лучше убрать, тогда в billing_data попадут все исходные строки.

Бенчмарки

Как известно, индексы могут ускорить запросы, но также и замедлить вставку, ведь каждое изменение таблицы требует поддержания индекса в актуальном состоянии. Поэтому я сравнил насколько сильно индексы могут замедлить вставку. Проверял несколько сценариев:

- без индекса;
- create index idx_billing_data on billing_data(resource);
- create index idx_billing_data on billing_data(resource, billing_date);
- create index idx_billing_data on billing_data(resource, billing_date, cost);
- create index idx_billing_data on billing_data(resource, billing_date) include (cost).

Результаты на второй картинке. Исходный код и результаты тут.

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

Влияние индексов на SELECT и GROUP BY

В этом подходе используется SELECT и GROUP BY. Когда я реализовывал его в FinOps-дашборде, я отдельно исследовал, как индексы влияют на выполнение вот такого запроса:

select resource, billing_date, sum(cost)
from stage_table
group by resource, billing_date

Я перепробовал разные варианты индексов, чтобы добиться максимальной скорости. Как думаете, с каким индексом на stage_table такой запрос отработает быстрее всего? Варианты оставляю в опросе 👇 О результатах расскажу на следующей неделе — сейчас как раз обрабатываю результаты бенчмарков.