Eu uso o PostgreSQL 12 para armazenar meus dados brutos e o ElasticSearch como datawarehouse para relatórios.
Acontece que existem situações com JOIN(por exemplo, dados que existem em uma tabela mas não existem em outra) o ElasticSearch não me atendendo, tendo que fazer consultas que retornam uma grande quantidade de resultados diretamente no PostgreSQL.
Muitos anos atrás eu trabalhava com um SELECT <column1, column2> FROM <table> WHERE <conditions>;totalmente sem LIMITou OFFSET, isso fazia com que meu banco de dados ficasse com uma carga alta, ocupando muita memória para retornar os resultados e meu servidor web não dava conta da quantidade de dados que era retornado, travando (memória estouro de limite, por exemplo).
Para resolver isso, comecei a trabalhar com um sistema de paginação, trazendo um número limitado de registros e paginando os resultados com LIMIT e uma condição para exibir os registros abaixo do último registro da consulta anterior.
Exemplo:
-- 1st Query
SELECT <column1, column2> FROM <table> WHERE <conditions> LIMIT 512;
-- 2nd Query
SELECT <column1, column2> FROM <table> WHERE <conditions> AND id < last_id_query_1 LIMIT 512;
-- 3rd Query
SELECT <column1, column2> FROM <table> WHERE <conditions> AND id < last_id_query_2 LIMIT 512;
Na minha opinião este método não parece ser muito bom. Lendo sobre CURSORSisso parece que ele faz algo parecido, mas usando apenas uma busca. E vários sites dizem que ele tem um desempenho muito melhor do que o LIMIT/OFFSET.
Esse tipo de afirmação é verdadeira ou a forma como trabalho atualmente também é boa?
Como não consigo realizar um EXPLAIN ANALYZEem cada um FETCHdos CURSOR, não tenho como realmente analisar se ele é mais rápido e tem melhor desempenho.
O que você está fazendo atualmente é paginação de conjunto de chaves (exceto que você não mostra o ORDER BY necessário para fazê-lo funcionar corretamente - estou assumindo que está realmente lá). Com muitas consultas e com o índice apropriado, isso pode funcionar muito bem. Você menciona algumas preocupações sobre isso, mas sem detalhes sobre essas preocupações, é difícil saber quão válidas e/ou endereçáveis elas são.
A paginação do cursor e do conjunto de chaves deve ser muito mais eficiente do que OFFSET/LIIMT quando há um grande número de páginas, mas observe que no seu exemplo não há OFFSET. Há apenas um LIMIT e um conjunto de chaves para onde começar. Em outras palavras, OFFSET/LIMIT pode ser ruim, mas não é isso que você está fazendo.
O bom da paginação do conjunto de chaves sobre a paginação do cursor do lado do servidor é que o estado é totalmente e eficientemente gerenciado pelo cliente. Se o cliente não voltar para a próxima página por duas semanas (ou nunca), isso não é uma preocupação para o servidor. No entanto, com um cursor do lado do servidor, você precisa de algum mecanismo para manter a conexão do banco de dados aberta entre as visitas à página e reanexá-la. Você também precisa de algum mecanismo para declarar um cursor abandonado, para que não acumule e consuma uma quantidade ilimitada de recursos.
auto_explain ainda gravará planos para cursores, desde que seja fechado de forma limpa, seja por um CLOSE explícito ou por COMMIT.