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
Advertisement
Answer
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.