PostgreSQL LIMIT with OFFSET is not working beyond 100000

user_6187336 0
Viewed 1304

I am using postgres (9.6) docker for my project. After loading large volume of data, for example 234453, I need to cut data in chunks using limit and offset. But I have observed that my query is getting hanged beyond the limit of 100000. Like I can cut when I am giving limit 100000. But when I am using 120000, it is hanging.

Data loaded: 234453 rows Query: This is working: docker exec -it pg_1 psql -P pager postgres postgres -t -c "delete from schema1.table1 where col_id not in (select col_id from schema1.table1 limit 100000 offset 1)"

This is hanged: docker exec -it pg_1 psql -P pager postgres postgres -t -c "delete from schema1.table1 where col_id not in (select col_id from schema1.table1 limit 120000 offset 1)"

The next cut should be from 100001 to 230000, something like that.

Can you please let me know, how can I do that? Or whether I am doing anything wrong?

Thanks, Suvendu

offset postgresql pagination sql database
2 Answers

Offset Pagination is efficient only if the OFFSET value is relatively small.

Otherwise, even if you index the sorting and filtering criteria, the DB will have to scan the index and count the entries until it reaches the right OFFSET value. For very large values, it means the index is scanned almost entirely.

So, you are better off using Keyset Pagination.

user_10251180

I have tried with the row_number() and it is working for the large volumes of data also. Below is my query:

docker exec -it pg_1 psql -P pager postgres postgres -t -c "delete from schema1.table1 WHERE col_id NOT IN (SELECT x.col_id FROM (select col_id , ROW_NUMBER () OVER (ORDER BY col_id) from schema1.table1) x WHERE ROW_NUMBER BETWEEN 100001 AND 230000)"

user_61873360
Related