<story><title>Choose Postgres queue technology</title><url>https://adriano.fyi/posts/2023-09-24-choose-postgres-queue-technology/</url></story><parent_chain><item><author>matsemann</author><text>I&amp;#x27;ve done even simpler without locks (as no transaction logic), where I select a row, and then try to update a field about it being taken. If 1 row is affected, it&amp;#x27;s mine. If 0, someone else did it before me and I select a new row.&lt;p&gt;I&amp;#x27;ve used this for tasks at big organizations without issue. No need for any special deployments or new infra. Just spin up a few worker threads in your app. Perhaps a thread to reset abandoned tasks. But in three years this never actually happened, as everything was contained in try&amp;#x2F;catch that would add it back to the queue, and our java app was damn stable.</text></item><item><author>aduffy</author><text>For several projects I’ve opted for the even dumber approach, that works out of the box with every ORM&amp;#x2F;Query DSL framework in every language: using a normal table with SELECT FOR UPDATE SKIP LOCKED&lt;p&gt;&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.pgcasts.com&amp;#x2F;episodes&amp;#x2F;the-skip-locked-feature-in-postgres-9-5&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.pgcasts.com&amp;#x2F;episodes&amp;#x2F;the-skip-locked-feature-in-...&lt;/a&gt;&lt;p&gt;It’s not “web scale” but it easily extends to several thousand background jobs in my experience</text></item></parent_chain><comment><author>refibrillator</author><text>PSA: This is a read-modify-write pattern, thus it is not safe under concurrency unless a transaction isolation level of SERIALIZABLE is specified, or &lt;i&gt;some&lt;/i&gt; locking mechanism is used (select for update etc).</text></comment>
<story><title>Choose Postgres queue technology</title><url>https://adriano.fyi/posts/2023-09-24-choose-postgres-queue-technology/</url></story><parent_chain><item><author>matsemann</author><text>I&amp;#x27;ve done even simpler without locks (as no transaction logic), where I select a row, and then try to update a field about it being taken. If 1 row is affected, it&amp;#x27;s mine. If 0, someone else did it before me and I select a new row.&lt;p&gt;I&amp;#x27;ve used this for tasks at big organizations without issue. No need for any special deployments or new infra. Just spin up a few worker threads in your app. Perhaps a thread to reset abandoned tasks. But in three years this never actually happened, as everything was contained in try&amp;#x2F;catch that would add it back to the queue, and our java app was damn stable.</text></item><item><author>aduffy</author><text>For several projects I’ve opted for the even dumber approach, that works out of the box with every ORM&amp;#x2F;Query DSL framework in every language: using a normal table with SELECT FOR UPDATE SKIP LOCKED&lt;p&gt;&lt;a href=&quot;https:&amp;#x2F;&amp;#x2F;www.pgcasts.com&amp;#x2F;episodes&amp;#x2F;the-skip-locked-feature-in-postgres-9-5&quot; rel=&quot;nofollow noreferrer&quot;&gt;https:&amp;#x2F;&amp;#x2F;www.pgcasts.com&amp;#x2F;episodes&amp;#x2F;the-skip-locked-feature-in-...&lt;/a&gt;&lt;p&gt;It’s not “web scale” but it easily extends to several thousand background jobs in my experience</text></item></parent_chain><comment><author>valzam</author><text>The reason why you want to use skip locked is so that Postgres can automatically skip rows that are being concurrently accessed for updating the &amp;quot;status&amp;quot;. You are right, if you update a &amp;quot;status&amp;quot; field you don&amp;#x27;t really need to worry about advisory locks and skipping rows that are locked but it still helps with performance if you have a decent amount of concurrent consumers polling the table.</text></comment>