Date: Wed, 2 Apr 2025 19:51:45 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Adrian Klaver <adrian.klaver@aklaver.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Performance issues during pg_restore -j with big partitioned
 table
In-Reply-To: <7be2dcc6-3ba4-4e3f-a154-8d13d816aa9b@aklaver.com>
Message-ID: <87a9886e-13a7-8c55-d626-edb7b6761677@gmx.net>
References: <84379bfb-bbda-84e6-cacc-e863ba9d6c37@gmx.net> <7d3fc27a-1229-4739-8d15-f4005dfa2435@aklaver.com> <7be2dcc6-3ba4-4e3f-a154-8d13d816aa9b@aklaver.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:C14pHml7KQA=;YSsv4qW/kLDUQee7fOfvk9mmA3F
 14iIWW1RaGuu0L9qUc2thgk5zQAvVfev8fQrnL3IRQnaOJ6ico81+3ON51+HwvfYR0TbQ4v1O
 jGILV6dSYj9i6qpfdLYI9Y2uSNTx2xz4Wza/OY9reoL8YQ+od5JX2pURyLak+7VMHIsqqhvgC
 aj6soQEwxlw/Pe9i0q5rufov25bY/7CmRnoU8XrnD/YXvGboHDrr48qHXzAPig83esrUQFucg
 jucoGjHmy+1sxYrUTAZAbhFBlPSTIu5Zysdx3oJ32/NaE+WG4l+0Z4UED77LlZnwXqXiM8uXn
 43jQyI1+ZsPyL1MnT4p8YNcZe8GbFoWap7Tpx2bGK3JLJBXCCcqVs7zg7ggvX2wWZ7XVGppbd
 uxfLnFiQeDkTzOi2iTVtb6UPMVFnQyZq0ATpTXU0Cz7xDLp+MZK8UQSgHnfl/I/sGc+P89RV/
 S90qzpgd571oQRSNKvRAqxrOl+3soLco6/JjIMeTmJtrTaGfoQfeJKw5QuMxbXoHJ2FOdd8TB
 OnSHc9HKkneRLLiC1CMm4T13bas9MvsXJWXHm5HimZcuSBVbHckX/+GHNHmuua1NGUG6HriLJ
 tRhwBWil8u2x2xw+Tjep1CEm4jbndQEK1rWVzvVg8nNWyIRF+PxyEyAmuWi0Wj0N8fwE4NyDQ
 x904s+Cg97AGmpkUdIt7AHGHrS4Q/mYDZKhPxzkRhwEJchFOfgS/roD9ZCO87zVVVsNZe/Ioh
 aCrNevfwHwUgyahc/be3dwHfCFv0g/xXPViMOP17T2gZQWtEEGvDwLB6sqZnjnjWXLHgPzJkd
 uUjFNV1tjP4D8Lk0QLHHC+951j9RNWrxzl70Wp11c8pXlXDoiRPy+nK19H/EgJr96E9GlK7xI
 brdsyJOrJk1SRxLcFWujVmqT5y09VzjF3iTo4px7i17ceezdrTmKimEhCdr+Ib6be8EwpzDhZ
 TdcuAWpYorI8f9LnDmgXSf+nGhlQmphPWIwWzkJ+i3ttQOcwlUVO1gvOz5CCnjuqW2tRlzkW5
 YjjFFFnK6YeXzJEymXr3UbKovj7f4SyNZSzEoBUPAcFbzyY3ij3mwEQe6ThTv4QU7m6ycrqUU
 Hop3e8ygbTstRWq1ToMP/8Ikh3axijDd+W/ZPAvyC9HRayq6sRlGc2w+7TWn+OoiiLh+o6Idr
 1AhC9JlJ2uNs6YmGM/Kp8+6fg3nH2ZvrK1+XR2SoLt8ssSVgSZmmavP7a0lDCisg4DiROPJlg
 m1mAkl1FWI3x62mXbdIbRzHWIu2P8YG2if/xIG1CpxEhqWTqcQHhHg3PcO4O1E/rPyzdatq6q
 qtVZf1+1JvxVi4eLIYjyNL1eXfDLSI/lTVKiTvKdVUPrG9Dziuq/phQl2nXhKGWAYskgSpy+/
 rIaddGjhju255po4kQCZsuNGm4l7qT8L8OzStBzdU1t1i5Y8IkD5cF8oRaKCloJlW2h7QY/wW
 Uk8KQjQ==
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/87a9886e-13a7-8c55-d626-edb7b6761677%40gmx.net>
Precedence: bulk

On Wed, 2 Apr 2025, Adrian Klaver wrote:
>
>
> On 4/2/25 10:39 AM, Adrian Klaver wrote:
>>
>
>>  --clean will drop the object entirely not TRUNCATE.
>>
>>  I'm guessing that this is being done by you per:
>>
>>  https://www.postgresql.org/message-id/53760c70-4a87-a453-9e02-57abc9cb=
2e54%40gmx.net
>>
>>  "After each failed attempt, I need to issue a TRUNCATE table1,table2,.=
..
>>  before I try again. "
>
> Oops, forgot to engage brain.
>
> From pg_backup_archiver.c:
>
> * In parallel restore, if we created the table earlier in
> * this run (so that we know it is empty) and we are not
> * restoring a load-via-partition-root data item then we
> * wrap the COPY in a transaction and precede it with a
> * TRUNCATE.  If wal_level is set to minimal this prevents
> * WAL-logging the COPY.  This obtains a speedup similar
> * to that from using single_txn mode in non-parallel
> * restores.

This makes sense. It creates the table earlier, and then truncates it just
before copying data into it. I wonder if this really circumvents the WAL
since I don't have --single-transaction  (incompatible to -j).


Dimitris