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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
