0

sorry if this is a really basic question.

I am thinking about creating a simple DB that stores a large number of pre-generated key and value pair. The key operations that I'll want to perform on this DB is to retrieve any record (that has not yet been retrieved), then either delete it or mark it as read so that I don't retrieve it again.

I considered the use of a relational DB such as Postgres but I can't think of an efficient way to quickly retrieve a single record. I believe if I were to use a limit it will still end up scanning through a large number of records.

I am not too familiar with NoSQL, and was wondering if this will be a better choice for my usecase? If so, would appreciate getting any information/resources to read up on how I can perform an efficient read & delete on any record using NoSQL.

Thanks in advance!

kito
  • 41
  • 7

1 Answers1

0

First, I would suggest having a primary key on the table, so it would look something like:

create table keyvalue (
    keyvalueid int generated always as identity,
    key text,
    valuetext,
    dateused timestamp
);

Then, create an index:

create index idx_keyvalue_dateused on keyvalue(keyvalueid)
    where dateused is null;

Then you can do an update and fetch the values using:

with u as (
      update keyvalue kv2
          set dateused = now()
          from (select kv.*
                from keyvalue kv
                where dateused is null
                limit 1
               ) kv2
          where kv2.keyvaluid = kv.keyvalueid
          returning *
     )
select *
from u;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hello thanks for the reply! Do correct me if I'm wrong, but I'm thinking that the part with limit 1 will still require the query to scan through the table (although its faster using the index) and retrieve all records that has dateused = null, before applying limit 1? As such, I thought it would be inefficient and am trying to look for a way to minimise retrieve a huge number of unused records each time I wish to retrieve a record. – kito Sep 04 '21 at 22:43
  • @Kito . . . The part with the `limit 1` should use the index and return the first value that is found. It is not really a full index scan, because it is only looking for one row and the index is filtered so the only rows in the index are those that match the query conditions. – Gordon Linoff Sep 05 '21 at 00:25
  • 1
    thanks for the clarification! you're right and after some further googling i found some resources for future references: https://stackoverflow.com/questions/455476/does-adding-limit-1-to-mysql-queries-make-them-faster-when-you-know-there-will/39751914 and https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html . Appreciate the help :) – kito Sep 05 '21 at 03:28