Storing last N samples in a table coming from multiple clients I want to store some last N (say fixed 50) samples in a table indexed by uuid. They're coming from multiple clients. The requirements of which last N are weak (the use case is to show some recent samples for debugging a stream of messages). But I want them to be relatively current, so my clients will update them often. So ideally I want to keep replacing the same rows.

My current plan is to have a schema like such:

CREATE TABLE samples (
  id     uuid     NOT NULL,
  index  integer  NOT NULL,
  text   text     NOT NULL,

  PRIMARY KEY(id, index)
);


The clients will then pick a random N between 0 and 50 and do upserts like such:

INSERT INTO samples
  (
    id,
    index,
    text
  )
VALUES
  (
    'f85c6f06-ae0b-48de-8c36-bfc86208495c',
    0,
    'sample 0'
  ),
  (
    'f85c6f06-ae0b-48de-8c36-bfc86208495c',
    2,
    'sample 2'
  )
ON CONFLICT (id, index)
DO UPDATE SET text = EXCLUDED.text;


The downside is that I'll be replacing them at random, which is probably going to work out but not guaranteed to. It also means multiple clients will trample on each other.

Is there a better way to do this? Some sort of round-robin style replace would be ideal, but I'm not sure how to get that effect. Is there a different strategy all together I could use here?
My idea is to use new table to store next index to update for each user:

CREATE TABLE next_sample_index (
  id     uuid     NOT NULL,
  index  integer  NOT NULL,

  PRIMARY KEY(id)
);


Just fill it with 0s for each user. I would use it as a kind of serial like this:

UPDATE
    next_sample_index
SET
    index = index + 1
WHERE
    id = XX
RETURNING
    index;


You will get next index and increase it at the same time.

Then you calculate modulus index % 50 so it acts as a round-robin queue.

To insert new sample:

DELETE FROM
    samples
WHERE
    id = XX AND
    index = --index-just-calculated--;


This DELETE command will do nothing if no such an index existed before.

INSERT INTO
    samples(
        id,
        index,
        text
    ) VALUES (
        XX,
        --index-just-calculated--,
        'f85c6f06-ae0b-48de-8c36-bfc86208495c'
    );


You just need to make sure that next_sample_index table is properly initialized or make a function to get next index that checks IF NOT FOUND condition (and makes INSERT if necessary).

XX in the examples is user id.

Hope this helps.