CODEWITHSUNDEEP
asp.netdatabasepostgresqlpagination
NDeveloper
NDeveloper

Reputation: 1847

offset/limit performance optimization

I have a table with structure like :

I need to implement pagination. My simple query looks like :

SELECT Id, Post, etc FROM Posts ORDER BY CreationDate desc OFFSET x LIMIT 15

When there are few records (below 1 mln) performance is somewhat bearable, but when the table grows there is a noticeable difference.

Skipping the fact that there is good to configure DB settings like cache size, work memory, cost, shared mem, etc... What can be done to improve the performance and what are the best practices of pagination using Postgres. There is something similar asked here, but I am not sure if this can be applied in my case too.

Since my Id is auto incremented (so predictable) one of the other options I was thinking is to have something like this 

SELECT Id, Post...FROM Posts WHERE Id > x and Id < y

But this seems to complicate things, I have to get the count of records all the time and besides it is not guaranteed that I will always get 15 records(for example if one of the posts has been deleted and Ids are not in "straight" sequence anymore).

I was thinking about CURSOR too, but if I am not mistaken CURSOR will keep the connection open, which is not acceptable in my case.

Upvotes: 1

Views: 812

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324385

Pagination is hard; the RDBMS model isn't well suited to large numbers of short-lived queries with stateful scrolling. As you noted, resource use tends to be too high.

You have the options:

  • LIMIT and OFFSET
  • Using a cursor
  • Copying the results to a temporary table or into memcached or similar, then reading it from there
  • x > id and LIMIT

Of these, I prefer x > id with a LIMIT. Just remember the last ID you saw and ask for the next one. If you have a monotonously increasing sequence this will be simple, reliable, and for simple queries it'll be efficient.

Upvotes: 2

Related Questions