maintaining a reference to a fetched row

From: Brian Karlak <zenkat(at)metaweb(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: maintaining a reference to a fetched row
Date: 2009-11-03 20:30:15
Message-ID: 22AC581D-50D1-4863-9FDE-35EDDB171269@metaweb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello All --

I have a simple queuing application written on top of postgres which
I'm trying to squeeze some more performance out of.

The setup is relatively simple: there is a central queue table in
postgres. Worker daemons do a bounded, ordered, limited SELECT to
grab a row, which they lock by setting a value in the queue.status
column. When the task is complete, results are written back to the
row. The system is designed to allow multiple concurrent daemons to
access a queue. At any one time, we expect 1-5M active items on the
queue.

Now this design is never going to win any performance awards against a
true queuing system like Active/Rabbit/Zero MQ, but it's tolerably
fast for our applications. Fetch/mark times are about 1ms,
independent of the number of items on the queue. This is acceptable
considering that our tasks take ~50ms to run.

However, the writing of results back to the row takes ~5ms, which is
slower than I'd like. It seems that this is because I need to to do
an index scan on the queue table to find the row I just fetched.

My question is this: is there some way that I can keep a cursor /
pointer / reference / whatever to the row I fetched originally, so
that I don't have to search for it again when I'm ready to write
results?

Thanks in advance for any pointers you can provide.

Brian

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeremy Harris 2009-11-03 20:42:56 Re: Free memory usage Sol10, 8.2.9
Previous Message Subbiah Stalin-XCGF84 2009-11-03 19:16:26 Free memory usage Sol10, 8.2.9