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

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

В прошлый раз я советовал использовать временные таблицы командой CREATE TEMP TABLE. У них есть удобный функционал автоматического удаления таблицы ON COMMIT DROP.

В процессе экспериментов и чтения документации Postgres я обнаружил другой тип таблиц: UNLOGGED. Они, как и TEMP таблицы, не используют Write-Ahead Log (WAL). То есть нет накладных расходов из-за записи в лог, но также нет и возможности восстановить данные в случае сбоя. Но, поскольку стейдж-таблица будет удалена сразу после завершения обработки данных, нам это и не нужно.

Я решил сравнить производительность трёх видов таблиц: обычные, UNLOGGED и TEMP. Я ожидал, что UNLOGGED и TEMP таблицы будут примерно одинаковыми по скорости и при этом быстрее обычных таблиц. Как оказалось, всё не так просто.

1️⃣ Если таблицы без индекса, то да, UNLOGGED и TEMP быстрее обычных таблиц (диаграмма 1) и показывают плюс-минус одинаковые результаты.

2️⃣ Ситуация меняется, если у таблиц есть индекс. Я создал индекс billing_data(resource, billing_date) include(cost). При относительно большом объёме данных (в моём случае больше 100 000 – 200 000 строк), TEMP таблицы становятся медленнее UNLOGGED таблиц и даже обычных таблиц (диаграмма 2).

Причина этого в буфере, который Postgres выделяет для временных таблиц. Размер буфера задаётся параметром temp_buffers и по умолчанию равен 8 Мб. При вставке в таблицу с индексом Postgres также должен поддерживать сам индекс в актуальном состоянии. В какой-то момент все данные перестают помещаться в буфер и Postgres начинает использовать диск. Это значительно влияет на скорость доступа к данным.

3️⃣ Чтобы проверить гипотезу, я сделал ещё один бенчмарк: вставка 1 млн строк в таблицы с индексом и разными значениями temp_buffer. Результаты (диаграмма 3) её подтверждают: с увеличением размера буфера, скорость записи в TEMP таблицы становится практически такой же, как у UNLOGGED таблиц.

Промежуточные выводы

1. Вставка в TEMP и UNLOGGED таблицы быстрее, чем в обычные таблицы.

2. Изменяя размер буфера TEMP таблиц можно влиять на скорость вставки данных.

Про индексы, UNLOGGED таблицы и их влияние на SELECT будет в следующем посте. Там тоже много чего интересного.