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

Ответ 1

Курсоры являются разумным выбором для подкачки в небольших приложениях интрасети, которые работают с большими наборами данных, но вы должны быть готовы отбросить их после таймаута. Пользователи любят побродить, пойти на обед, отправиться в отпуск на две недели и т.д. И оставить свои приложения. Если это веб-приложение, там даже вопрос о том, что такое "работа" и как определить, находится ли пользователь по-прежнему.

Они не подходят для крупномасштабных приложений с высоким количеством клиентов и клиентов, которые приходят и уходят почти в случайном порядке, например, в веб-приложениях или веб-API. Я бы не рекомендовал использовать курсоры в вашем приложении, если у вас нет достаточно небольшого количества клиентов и очень высоких ставок запросов... в этом случае отправка небольших партий строк будет очень неэффективной, и вы должны подумать о том, чтобы разрешить запросы диапазонов и т.д. /p >

Курсоры имеют несколько затрат. Если курсором не является WITH HOLD, вы должны сохранить транзакцию открытой. Открытая транзакция может помешать autovacuum правильно выполнять свою работу, вызывая раздувание таблиц и другие проблемы. Если курсор объявлен WITH HOLD, и транзакция не открыта, вы должны оплатить стоимость материализации и хранения потенциально большого набора результатов - по крайней мере, я думаю, что как работают курсоры. Альтернатива так же плоха, что и транзакция неявно открыта до тех пор, пока курсор не будет уничтожен и не будет очищаться от строк.

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

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

В целом, несмотря на то, что он может быть весьма неэффективным, LIMIT и OFFSET могут быть лучшим решением. Часто бывает лучше искать первичный ключ, а не использовать OFFSET, хотя.

Кстати, вы смотрели документацию для курсоров в PL/pgSQL. Вы хотите нормальные курсоры уровня SQL для этого задания.

У курсоров требуется, чтобы соединение с базой данных оставалось открытым?

Да.

Выполняют ли курсоры внутри транзакции, блокируя ресурсы, пока они "закрыты"?

Да, если они не являются WITH HOLD, и в этом случае они потребляют другие ресурсы базы данных.

Есть ли еще какие-то "gotchas", о которых я не знаю?

Да, как объясняется выше.

Ответ 2

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

Вместо этого используйте LIMIT и OFFSET в своих запросах; см. LIMIT и OFFSET в документах Pg.

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

Создайте RESTful API, чтобы клиент мог вызвать "next_url" (также переданный в ответе), чтобы получить следующий набор строк.