Parallel queries on CTE for writing operations in PostgreSQL
<p>From <a href="https://www.postgresql.org/docs/9.6/static/release-9-6.html#AEN130398" rel="nofollow noreferrer">PostgreSQL 9.6 Release Notes</a>:</p>

<blockquote>
  <p>Only strictly read-only queries where the driving table is accessed via a sequential scan can be parallelized.</p>
</blockquote>

<p>My question is: If a CTE (<code>WITH</code> clause) contains only read operations, but its results is used to feed a writing operation, like an insert or update, is it also disallowed to parallelize sequential scans?</p>

<p>I mean, as CTE is much like <em>a temporary table which only exists for currently executing query</em>, can I suppose that its inner query can take advantage of the brand new parallel seq-scan of PostgreSQL 9.6? Or, otherwise, is it treated as a <em>using</em> subquery and cannot perform parallel scan?</p>

<p>For example, consider this query:</p>

<pre><code>WITH foobarbaz AS (
  SELECT foo FROM bar
  WHERE some_expensive_function(baz)
)
DELETE FROM bar
USING foobarbaz
WHERE bar.foo = foobarbaz.foo
;
</code></pre>

<p>Is that foobarbaz calculation expected to be able to be parallelized or is it disallowed because of the delete sentence?</p>

<p>If it isn't allowed, I thought that can replace the CTE by a <em>CREATE TEMPORARY TABLE</em> statement. But I think I will fall into the same issue as <em>CREATE TABLE</em> is a write operation. Am I wrong?</p>

<p>Lastly, a last chance I could try is to perform it as a pure read operation and use its result as input for insert and / or update operations. Outside of a transaction it should work. But the question is: If the read operation and the insert/update are between a <code>begin</code> and <code>commit</code> sentences, it not will be allowed anyway? I understand they are two completely different operations, but in the same transaction and Postgres.</p>

<p>To be clear, my concern is that I have an awful mass of hard-to-read and hard-to-redesign SQL queries that involves multiple sequential scans with low-performance function calls and which performs complex changes over two tables. The whole process runs in a single transaction because, if not, the mess in case of failure would be totally unrecoverable.</p>

<p>My hope is to able to parallelize some sequential scans to take advantage of the 8 cpu cores of the machine to be able to complete the process sooner.</p>

<p>Please, don't answer that I need to fully redesign that mess: I know and I'm working on it. But it is a great project and we need to continue working meantime.</p>

<p>Anyway, any suggestion will be thankful.</p>

<p><strong>EDIT:</strong></p>

<p>I add a brief report of what I could discover up to now:</p>

<ul>
<li><p>As @a_horse_with_no_name says in his comment (thanks), CTE and the rest of the query is a single DML statement and, if it has a write operation, even outside of the CTE, then the CTE cannot be parallelized (I also tested it).</p></li>
<li><p>Also I found this <a href="https://wiki.postgresql.org/wiki/Parallel_Query" rel="nofollow noreferrer">wiki page</a> with more concise information about parallel scans than what I found in the release notes link.</p></li>
<li><p>An interesting point I could check thanks to that wiki page is that I need to declare the involved functions as <em>parallel safe</em>. I did it and worked (in a test without writings).</p></li>
<li><p>Another interesting point is what @a_horse_with_no_name says in his second comment: Using DbLink to perform a pure read-only query. But, investigating a bit about that, I seen that <a href="https://www.postgresql.org/docs/9.6/static/postgres-fdw.html" rel="nofollow noreferrer">postgres_fdw</a>, which is explicitly mentioned in the wiki as non supporting parallel scans, <a href="https://www.postgresql.org/docs/9.6/static/dblink.html" rel="nofollow noreferrer">provides roughly the same functionality using a more modern and standards-compliant infrastructure</a>.</p>

<ul>
<li>And, on the other hand, even if it would worked, I were end up getting data from outside the transaction which, in some cases would be acceptable for me but, I think, not as good idea as general solution.</li>
</ul></li>
<li><p>Finally, I checked that is possible to perform a parallel-scan in a read-only query <strong>inside</strong> a transaction, even if it later performs write operations (no exception is triggered and I could commit).</p></li>
</ul>

<p>...in summary, I think that my best bet (if not the only one) would be to refactor the script in a way that it reads the data to memory before to later perform the write operations in the same transaction.</p>

<p>It will increase I/O overhead but, attending the latencies I manage it will be even better.</p>