Return random, non-duplicate rows from a table - PostgreSQL

Question

I have an interesting problem where I am looking for some recommendations. I have a table "image" that contains many thousands of rows. I want to be able to return a selection of random strings with a limit of 50 at a time.

Client side, I have the original GetImages () method, which will initially return 50 "random" images (if there are so many). when the user scrolls them and reaches a certain amount (approximately 40+), another function is activated - GetMoreImages ().

The problem is that I'm not sure how to get more images without risking returning the same results as well.

For example, if there are only 60 images, I would like the call to GetMoreImages () to return only the remaining 10 images.

I feel like I should also mention that my Id table does not touch, as I use the Instagram approach ( http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram ) which leaves me with a potentially large gaps between each line identifier.

One of the ways I could try is to pass all the image IDs that I already have, but that would be inconvenient if the user scrolls through thousands of images.

I suppose another way could be to store a cached “random” set of values ​​on the application server for each user, but I also don't like the idea of ​​this.

- , , .

Answer 1

, :

select *
from images
order by random()
limit 50;

100% , . , . setseed(). , :

with t as (
      select setseed(1)
     )
select *
from images cross join t
order by random()
limit 50;

:

with t as (
      select setseed(1)
     ) 
select *
from images cross join t
order by random()
limit 50;

, random() . , :

with t as (
      select setseed(1)
     ),
     i as (
      select i.*, random() as rand
      from images i cross join t
     )
select *
from i
order by i.rand
limit 50;

, . limit 10 offset 50 ..

, , , , .

EDIT:

- . , .

, , . , , 8,191 131,071 - ( ). :

select i.*
from images i
order by mod(i.id * 8191 + 1, 131071)
limit 50 offset xxx;

"+1" . "", , id , . order by, , .

Answer 2

, , , .

?

, / -, -.

.

, , , - , , VALUES, , NOT IN . , , , ..

Gordon - , , , - " ", " ". , - , , .

, , . , . :

• CREATE TABLE AS SELECT ..., . . , . . , . .

• WITH HOLD . , . , , . .

• , , Gordon. , , , , / . " = 50 && seed = 1231" .

• . HTTP , ( cookie, URL ..) . , , , . NOT IN left-anti-join VALUES / .

. . , .

HTTP - , , , . , UNLOGGED, (sessionid, imageid), (requestid, imageid), . , .

, .

ORDER BY random() . .

, PostgreSQL , . , .

, .

. , , :

• , , OFFSET

• CREATE UNLOGGED TABLE AS SELECT ... . , , .

• ...

, ?

, ?

• . .
• (), .
• . .

. , , , , . , WITH HOLD, , HTTP. . , . , , " " , .. .

, UNLOGGED . , , HTTP - , . .

. . . , - .

Answer 3

. :

with r as (
    select distinct
        ceil(
            random() *
            (select max(image_id) from image)
        )::int as image_id
    from generate_series(1, 200)
    limit 100
)
select image_id
from image inner join r using (image_id)
limit 50

, 50 , , 50. "" . 2 5.

, , ( ) 100, . , . .

, . , , seem_images session_id ( user_id ) image_id GetImages. GetMoreImages.

with r as (
    select distinct
        ceil(
            random() *
            (select max(image_id) from image)
        )::int as image_id
    from generate_series(1, 200)
    limit 100
), t as (
    select image_id, image
    from image inner join r using (image_id)
    where not exists (
        select 1
        from seem_image
        where image_id = image.image_id and session_id = 1
    )
    limit 50
), i as (
    insert into seem_image (image_id, session_id)
    select image_id, 1
    from t
)
select * from t;

. 3 image . 100 200 . seem_image , .

image ( ) seem_image tables

create table image (
    image_id integer primary key,
    image bytea
);
insert into image (image_id, image)
select image_id, image
from
    generate_series(1, 5000000) g (image_id)
    cross join
    (values (decode(rpad('', 1024 * 100, 'F'), 'hex'))) i (image)
where mod (image_id, 5) not in (0, 1)
;
analyze image;

create table seem_image (
    session_id integer,
    image_id integer,
    primary key (image_id, session_id)
);