I use PostgreSQL 12 to store my raw data and ElasticSearch as the datawarehouse for reports.
It happens that there are situations with
JOIN (eg, data that exists in one table but does not exist in another) ElasticSearch not attending me, having to make queries that return a large number of results directly in PostgreSQL.
Many years ago I worked with a
SELECT <column1, column2> FROM <table> WHERE <conditions>; totally without
OFFSET, this caused my database to have a high load, taking up a lot of memory to return the results and my web server couldn't handle the amount of data that was returned, crashing (memory limit overflow, for example).
To solve this, I started working with a paging system, bringing in a limited number of records and paging the results with LIMIT and a condition to display the records below the last record of the previous query.
-- 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;
In my opinion this method does not seem to be very good.
CURSORS it seems that it does something similar, but using only one search. And several sites say that it performs much better than
Is this kind of statement true or is the current way I work also good?
As I cannot perform an
EXPLAIN ANALYZE at each
FETCH of the
CURSOR, I have no way of actually analyzing whether it is faster and has better performance.