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?