Re: using a postgres table as a multi-writer multi-updater queue

Hello.

On 23.11.2015 11:41, Chris Withers wrote:
> Hi All,
>
> I wondered if any of you could recommend best practices for using a postgres
> table as a queue. Roughly speaking, 100-200 workers will vomit rows and rates of
> a few hundres per second into the table leaving the status as new and then as
> many workers as needed to keep up with the load will plough through the queue
> changing the status to something other than new.
>
> My naive implementation would be something along the lines of:
>
> CREATE TABLE event (
> ts timestamp,
> event char(40),
> status char(10),
> CONSTRAINT pkey PRIMARY KEY(ts, event)
> );
>
>
> ...with writers doing INSERT or COPY to get data into the table and readers
> doing something like:
>
> SELECT FOR UPDATE * FROM event WHERE status='new' LIMIT 1000;
>
> ...so, grabbing batches of 1,000, working on them and then setting their status.
>
> But, am I correct in thinking that SELECT FOR UPDATE will not prevent multiple
> workers selecting the same rows?
>
> Anyway, is this approach reasonable? If so, what tweaks/optimisations should I
> be looking to make?
>
> If it's totally wrong, how should I be looking to approach the problem?

I suggest an excellent read on this topic:

http://www.depesz.com/2013/08/30/pick-a-task-to-work-on/

Highly recommended if you haven't read it yet.

Also, if you aim on 9.5 (not released yet), it will introduce:

SELECT...
FOR UPDATE
SKIP LOCKED -- this is new

which supports exactly this use-case (i.e. to implement a job queue).

HTH,

Ladislav Lenart