Efficient way for storing lists in PostgreSQL

From: Marco Tosato <marco(dot)jem(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: Efficient way for storing lists in PostgreSQL
Date: 2017-12-11 19:49:52
Message-ID: CALTrA_9+u4frAun6HR++qSQFKcMC=hmN68NbUTBV73nU+R5Ccg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello everyone!

I'm looking for an efficient way of storing lists in PostgreSQL, the lists
will be used as partof a job management sistem, at least the ID of the job
must be stored inside the list.

Each list stores jobs with common characteristics (i.e. same level of
priority).

The system should satisfy the folloqing requirements:

- lists gets updated frequently removing from the front and adding to the
end

- there are many clients constantly modifying the lists (some clients are
"job submitters" others are "workers") ....possibly hundred of clients

My first idea was to use a table to store each list as a row, the list
itself would the stored inside an array field. This setup would allow the
system to update lists locking only a single record instead of locking the
entire table when inserting or removing one job from a queue.

What do you thing about using a PG array to manage a queue? Is it efficient
or should I store the nodes of the queues as records inside a table?

Thanks a lot to everyone!

Marco

Browse pgsql-novice by date

  From Date Subject
Next Message Efraín Déctor 2017-12-11 21:15:41 Re: Question about Logical Replication
Previous Message David G. Johnston 2017-12-11 19:42:39 Re: Question about Logical Replication