Как предоставить клиенту API 1 000 000 результатов базы данных?

Следуя моему предыдущему вопросу:

Использование "Курсоры" для подкачки в PostgreSQL

Каков хороший способ предоставить клиенту API 1 000 000 результатов базы данных?

В настоящее время мы используем PostgreSQL. Несколько предложенных методов:

Пейджинг с использованием Cursors
Пейджинг с использованием случайных чисел (добавьте "GREATER THAN ORDER BY" к каждому запросу)
Пейджинг с использованием LIMIT и OFFSET (ломается для очень больших наборов данных)
Сохраните информацию в файл и дайте клиенту загрузить ее.
Итерация по результатам, затем отправьте данные на клиентский сервер
Возвращает только ключи клиенту, а затем позволяет клиенту запрашивать объекты из облачных файлов, таких как Amazon S3 (все еще может потребоваться подкачка только для получения имен файлов).

Что я не думал об этом глупо и просто лучше, чем любой из этих вариантов?

Ответ 1

В таблице есть первичный ключ. Используйте его.

Вместо LIMIT и OFFSET сделайте свой пейджинг с фильтром на первичном ключе. Вы намекнули на это со своим комментарием:

Пейджинг с использованием случайных чисел (добавьте "БОЛЬШЕ, ЧЕМ ЗАКАЗАТЬ", чтобы каждый запрос)

но нет ничего случайного о том, как вы должны это делать.

SELECT * FROM big_table WHERE id > $1 ORDER BY id ASC LIMIT $2

Разрешить клиенту указывать оба параметра, последний идентификатор, который он видел, и количество записей для извлечения. В вашем API должен быть либо заполнитель, либо дополнительный параметр, либо альтернативный вызов для "выборки первых n идентификаторов", где он исключает предложение WHERE из запроса, но это тривиально.

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

Этот подход отличается от подхода LIMIT и OFFSET одним ключевым способом: одновременная модификация. Если вы INSERT в таблице с ключом ниже ключа, который уже видел какой-либо клиент, этот подход вообще не изменит его результаты, тогда как подход OFFSET повторит строку. Точно так же, если вы DELETE строку с более низким, чем уже видели ID, результаты этого подхода не изменятся, тогда как OFFSET пропустит невидимую строку. Однако нет никакой разницы только для таблиц с добавлением только с сгенерированными ключами.

Если вы заранее знаете, что клиенту понадобится весь набор результатов, наиболее эффективная вещь - просто отправить им весь результирующий набор без этого пейджингового бизнеса. Это, где я буду использовать курсор. Прочитайте строки из БД и отправьте их клиенту так быстро, как клиент их примет. Этот API должен будет устанавливать ограничения на то, насколько медленным был клиент, чтобы избежать чрезмерной нагрузки на бэкэнд; для медленного клиента я бы, вероятно, переключился на пейджинг (как описано выше) или спулал весь вывод курсора во временный файл и закрыл соединение с БД.

Важные оговорки:

Требуется, чтобы индекс UNIQUE/UNIQUE или PRIMARY KEY был надежным
Различные режимы одновременной модификации для ограничения/смещения, см. выше

Ответ 2

Попросите API принять смещение для начала и количество возвращаемых записей. Это своего рода пейджинг, где клиент может определить, сколько записей будет возвращено в одном запросе страницы. API также должен возвращать общее количество записей, доступных для запроса, чтобы клиент знал, сколько "страниц", или, возможно, оно может получить, когда оно заработало последние записи, когда количество возвращенных записей равно нулю или меньше, чем число запрошенные записи. Вы можете контролировать это в своем запросе PostgresSQL, используя предложение OFFSET (которое записывается для начала извлечения) и предложение LIMIT (количество возвращаемых записей) в инструкции SELECT.