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

From: Ladislav Lenart <lenartlad(at)volny(dot)cz>
To: Chris Withers <chris(at)simplistix(dot)co(dot)uk>, pgsql-general(at)postgresql(dot)org
Subject: Re: using a postgres table as a multi-writer multi-updater queue
Date: 2015-11-23 12:12:27
Message-ID: 565302AB.20405@volny.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2015-11-23 13:46:53 Re: current_query='IDLE" in pg_stat_activity
Previous Message Benedikt Grundmann 2015-11-23 11:12:25 Problems with pg_upgrade after change of unix user running db.