StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm gonna make a kind of odd sounding suggestion. Add an auto-inc big int to the source table, and create a set of 10 indexes with modulo division. Here's a simple test case example:</p> <pre><code>create table queue (id bigserial, input text); create index q0 on queue (id) where id%10=0; create index q1 on queue (id) where id%10=1; create index q2 on queue (id) where id%10=2; create index q3 on queue (id) where id%10=3; create index q4 on queue (id) where id%10=4; create index q5 on queue (id) where id%10=5; create index q6 on queue (id) where id%10=6; create index q7 on queue (id) where id%10=7; create index q8 on queue (id) where id%10=8; create index q9 on queue (id) where id%10=9; insert into queue select generate_series(1,50000),'this'; </code></pre> <p>What we've done here is to create a set of indexes that index 1/10th of the table. Next, we'll select a chunk of one of those ranges to work on:</p> <pre><code>begin; select * from queue where id%10=0 limit 100 for update; id | input ------+------- 10 | this 20 | this 30 | this -- do work here -- commit; </code></pre> <p>Now the interesting part. If you have > 10 workers with this setup, you just cycle them through the numbers and any more than 10 will just wait when the above select for update runs. But any other number (1 through 9) will still work.</p> <pre><code>begin; select * from queue where id%10=1 limit 100 for update; id | input -----+------- 1 | this 11 | this 21 | this 31 | this -- do work here commit; </code></pre> <p>This way all the work is divided into 10 buckets. Want more buckets? Change the number after the % and increase the number of indexes to match.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload