Take a look at these links. It should give you a clue to avoid LIMIT / OFFSET.
https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/
http://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way
Regards,
Walter
On Sep 29, 2016 19:19, "Guyren Howe" <guyren@xxxxxxxxx> wrote:
On Sep 29, 2016, at 16:14 , Colin Morelli <colin.morelli@xxxxxxxxx> wrote:Well then...just like that you made me feel like a total idiot! Hah.I guess that would work fine. I just need to encode some additional information in the pagination links that the API returns (a pagination "marker" would be a combination of created_at and uuid).I know this question is virtually impossible to answer without more information, but based on your gut - do you think it would make sense to define a compound index across (created_at, uuid), or do you think just an index on created_at is enough, if we can assume that most records (>80%) won't have collisions on their created_at timestamp?Quite likely just the created_at will be sufficient, although I like to add other fields to the index since you're going to have it anyway, if you have occasion to often filter the list on some other field.But you don't need the UUID field because even if there is collision, it will be a small number of records.On Thu, Sep 29, 2016 at 7:09 PM Guyren Howe <guyren@xxxxxxxxx> wrote:On Sep 29, 2016, at 16:03 , Colin Morelli <colin.morelli@xxxxxxxxx> wrote:
>
> Hey list,
>
> I'm using UUID primary keys in my application. I need to add pagination, and am trying to avoid OFFSET/LIMIT. I do have a created_at timestamp that I could use, but it's possible for multiple records to be created at the same timestamp (postgres offers millisecond precision here, I believe?)
>
> Is there an efficient way to filter based on the time component of UUID v1s? WHERE id > 'some-uuid' doesn't seem to work, as it looks like it's just performing a lexicographic sort on the hex representation of the UUID. Or, alternatively, does anyone have other suggestions on paginating large data sets?
Why not just sort on (created_at, uuid) (ie us the UUID just to force a complete ordering)?
