Re: Filtering by UUID

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(at)gmail(dot)com> wrote:

>
> On Sep 29, 2016, at 16:14 , Colin Morelli <colin(dot)morelli(at)gmail(dot)com> 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(at)gmail(dot)com> wrote:
>
>> On Sep 29, 2016, at 16:03 , Colin Morelli <colin(dot)morelli(at)gmail(dot)com>
>> 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)?
>>
>>
>