Если вы пытаетесь оптимизировать производительность Вашего основанного на PostgreSQL приложения, Вы наверняка пользуетесь базовыми инструментами: EXPLAIN (BUFFERS, ANALYZE), pg_stat_statements, auto_explain, log_statement_min_duration, и т.д.

Возможно Вы смотрите в сторону конфликтов блокировок с помощью log_lock_waits, следите за поведением ваших контрольных точек и т.д.

Но задумывалились ли Вы о задержках в сети? Игроки знают о ней, но имеет ли это отношение к Вашему серверу с приложением?

Задержка влияет

Задержки типичных клиент/сервер кольцевых сетей может варьироваться от 0.01 мс (локальный) до ~0.5 мс в коммутируемой сети, 5 мс по WiFi, 20 мс при ADSL, 300 мс при межконтинентальной маршрутизации, и даже больше для таких вещей, как спутники и WWAN ссылки.

Тривиальный SELECT может занять порядка 0.1 мс на выполнение на стороне сервера. Тривиальный INSERT 0.5 мс.

Каждый раз, когда Ваше приложение выполняет запрос, ему приходится ждать ответа от сервера с успехом/ошибкой и возможно результатом, метаданными запроса и т.д. Это влечет, по крайней мере, задержку одного прохода по сети туда-обратно.

Когда работа идет преимущественно с маленькими, простыми запросами, сетевая задержка может быть значительной для времени их выполнения, если база данных и приложение находятся на разных серверах.

Большинство приложений, особенно ORMы, очень склонны к выполнению большого количества довольно простых запросов. К примеру, если Ваше Hibernate приложение получает сущность посредством лениво извлеченного отношения @OneToMany к 1000 дочерних объектов, оно вероятно планирует выполнить 1001 запросов благодаря n+1 проблеме выборки, если не больше. Это означает, что оно потратит тысячекратное время задержки Вашей сети на проход туда-обратно только на ожидание. Можно использовать left join fetch чтобы избежать этого… но тогда Вы передадите 1000 раз родительский элемент в JOIN и придется его дедуплицировать.

Аналогично, если Вы заполняете базу данных через ORM, Вы вероятно делаете сотни тысяч обычных INSERTов… и ждете после каждого отдельного из INSERTов подтверждения от сервера, что все в порядке.

Достаточно легко сфокусироваться на времени выполнения запроса и попытаться оптимизировать его, но есть большое количество вариантов того, что можно сделать с простейшим INSERT INTO… VALUES .... Удалите некоторые индексы и ограничения, убедитесь что он находится в транзакции, и все почти готово.

Но что насчет избавления от всех сетевых ожиданий? Даже в локальной сети они начинают расти после тысячи запросов.

COPY

Один из способов избежать задержки — использовать COPY. Для того, чтобы использовать поддержку PostgreSQl'кого COPY, Ваше приложение или драйвер должны производить CSV-подобный набор строк и транслировать их на сервер в непрерывной последовательности. Или серверу может быть предложено отправить Вашему приложению CSV-подобный поток.

В любом случае, приложение не может чередовать COPY с другими запросами, а также копирования-вставки должны быть загружены непосредственно в таблицу назначения. Общий подход заключается в том, чтобы делать COPY во временную таблицу, а уже непосредственно из нее делать INSERT INTO… SELECT ..., UPDATE… FROM ...., DELETE FROM… USING..., и т.д., используя копированные данные для изменения основных таблиц одной операцией.

Это удобно, если вы пишете свой собственный SQL напрямую, но фреймворки и ORMы не поддерживают этого, плюс он может только непосредственно заменить простую вставку. Вашему приложению, фреймворку или пользовательскому драйверу приходится иметь дело со специальным представлением, необходимым для COPY, искать необходимые для этого метаданные и т.д.

(Известные драйверы, которые действительно поддерживают COPY включают в себя: libpq, PgJDBC, psycopg2 и Pg gem… но вовсе не обязательно что фреймворки и ORMS, построенные на их основе тоже поддерживают COPY.)

PgJDBC — режим группирования

У PostgreSQL'кого JDBC драйвера имеется решение для этой проблемы. Оно основано на особенностях работы по группированию API JDBC драйвера: он посылает группу запросов к серверу, после чего ждет только одного подтверждения, что весь набор прошел успешно.

По крайней мере в теории. На самом же деле, некоторые проблемы реализации ограничивают это все до нескольких сотен запросов в пачке за раз в лучшем случае. Драйвер также может только выполнять запросы, которые возвращают результат в виде набора, если он может определить насколько большим будет результат со временем. Несмотря на эти ограничения, использование Statement.executeBatch() может предложить очень большой прирост производительности приложениям, которые выполняют такие задачи, как массовая загрузка данными удаленных экземпляров баз данных.

Поскольку это стандартный API, он может использоваться приложениями, которые работают на нескольких СУБД. Hibernate, например, может использовать JDBC группировку, хотя он не делает это по умолчанию.

libpq и группирование

Большинство (все?) других PostgreSQL драйверов не имеет поддержки группирования. PgJDBC использует протокол PostgreSQL полностью и независимо, в то время как большинство других драйверов используют C библиотеку libpq, которая поставляется как часть PostgreSQL.

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

PostgreSQL сервер поддерживает группирование просто отлично, и PgJDBC уже вовсю его использует. В связи с этим я написал поддержку группирования для libpq и предложил его в следующую версию PostgreSQL. Так как он только меняет клиент, в случае подтверждения, он все же будет ускорять процесс при подключении к старым серверам.

Я был бы очень заинтересован в обратной связи от авторов и продвинутых пользователей основанных на libpq пользовательских драйверов и разработчиков основанных на libpq приложений. Патч успешно применяется к PostgreSQL версии 9.6 beta 1, если Вы хотите его попробовать. Имеется подробная документация и всеобъемлющий пример программы.

Производительность

Думаю что сервер СУБД на основе RDS или Heroku Postgres будет хорошим примером того, где описанный функционал может быть полезен. В частности, обращение к ним не из их собственных сетей прекрасно показывает как может повредить задержка.

На ~320 мс сетевой задержке:

• 500 insert'ов без группирвоания: 167.0 сек
• 500 insert'ов с группированием: 1.2 сек

… что приблизительно в 120 раз быстрее.

Как правило, Вы не используете межконтинентальное соединение между сервером, на котором находится приложение и сервером, на котором развернута база данных, эти же серверы использованы для наглядности влияния задержки. Даже через Unix сокет на локальном хосте я увидел прирост производительности на 50% для 10000 insert'ов.

Группирование в существующих приложениях

К сожалению, невозможно автоматически включить использование группирования для существующих приложений. Они должны использовать немного другой интерфейс, где они посылают серии запросов и только потом запрашивают результат.

Должно быть достаточно легко адаптировать приложения, которые уже используют асинхронный интерфейс libpq, особенно если они используют неблокирующий режим и select()/poll()/epoll()/WaitForMultipleObjectsEx цикл. Приложения, которые используют синхронный libpq интерфейс потребуют большего количества изменений.

Группирование в других пользовательских драйверах

Аналогичным образом, пользовательским драйверам, фрэймворкам и ORM'ам обычно требуются интерфейс и внутренние изменения, чтобы разрешить использование группирования. Если они уже используют цикл событий и неблокирующий I/O, то их должно быть достаточно легко изменить.

Я был бы рад увидеть Python, Ruby и прочих пользователей имеющих возможность использовать этот функционал, так что мне интересно посмотреть, кто заинтересован. Представьте, что вы в состоянии сделать это:

import psycopg2
conn = psycopg2.connect(...)
cur = conn.cursor()

# this is just an idea, this code does not work with psycopg2:
futures = [ cur.async_execute(sql) for sql in my_queries ]
for future in futures:
    result = future.result  # waits if result not ready yet
    ... process the result ...
conn.commit()

Выполнение асинхронного группирования не должно быть сложным со стороны пользователя.

COPY наибыстрейший

Практичные клиенты по-прежнему предпочитают COPY. Вот некоторые результаты с моего ноутбука:

inserting 1000000 rows batched, unbatched and with COPY
batch insert elapsed:      23.715315s
sequential insert elapsed: 36.150162s
COPY elapsed:              1.743593s
Done.

Группирование работы порождает удивительно большой прирост производительности даже на локальном unix сокете… но COPY оставляет оба индивидуальных INSERT подхода далеко позади в пыли.

Используйте COPY.

Изображение

Изображение для этой статьи — это Goldfields Water Supply Scheme трубопровод из Mundaring Weir близ Перта в Западной Австралии до внутренних (пустынных) золотых приисков. Она подходит к этой статье, так как из-за продолжительности его строительства и под шквалом критики, его создатель и основной инициатор, C. Y. O’Connor, покончил с собой за 12 месяцев до сдачи трубопровода в эксплуатацию. Местные жители часто (ошибочно) говорят, что он умер после того, как трубопровод был открыт, но вода не текла — так много это времени заняло, что все считали проект трубопровода провальным. Но, недели спустя, вода пошла.