Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v89JS-00DXvX-QC for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Oct 2025 03:33:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v89JQ-003jTA-Bt for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Oct 2025 03:33:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v89JQ-003jT1-0z for pgsql-hackers@lists.postgresql.org; Mon, 13 Oct 2025 03:33:28 +0000 Received: from mail-pj1-x1035.google.com ([2607:f8b0:4864:20::1035]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v89JO-001rBp-1Q for pgsql-hackers@lists.postgresql.org; Mon, 13 Oct 2025 03:33:28 +0000 Received: by mail-pj1-x1035.google.com with SMTP id 98e67ed59e1d1-330b4739538so3585456a91.3 for ; Sun, 12 Oct 2025 20:33:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1760326402; x=1760931202; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=O7AvFqi17INgZh5nlVIYsF7fe7ruhFm+28fkaJlFx0Y=; b=ANIoGdifaxNBAxhc6OWMKZIPElD0Bq0xOcAIuD+yPVFuX3YvY+Pa2sh8RWIQBpbiBe Pg8coeQe170jHzkbf08LPawsGGjx3RuRR5QfGCYq6YJLJgg1mc2ha5G69dhbbeb/OUj4 fsCkb20xyilZmCZw3V/WZvLQNp19I7MpxCFxRBc1e52qTPnEP6qusYfsXx6+lyAd+JgC J5HXtC6sys9vI4Qht1K69UV7qqVRK7M7GeTo6IA9FYMplWLCGjHHRYZDB6XvjNMTNO3k jCKaNX3t0UnNY9EeO6yqSMkdXT4Xc60tdZ2sDwA3omN3H4606E0ECIldpb6j+sf4nz76 6+hw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1760326402; x=1760931202; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=O7AvFqi17INgZh5nlVIYsF7fe7ruhFm+28fkaJlFx0Y=; b=kEEVhLUB/sGkP6pBsWJkuyKwYSE2CUCLz+kIvdt1VRQDVYWs5D27wWzWqRQgK7cOGG We0gXc3ZXB42qdULtEim7QCoYUmvMmzzCATMarh1pn0bSCR9JUr4c4DF38FYKY1uo3jo xscXtuNBRkce5hsoSecV4AI0Lw+ymcXvtDZdii4E4+wTrmz8A/bgMQBg2Rn4rwQ4dKi+ fVWs48+0GweOamvHUpW0jT9qwEoQdtRieYEJ9ufcbxf9rbJ+0JIAYOxJxPSxroFWtiBx uO/gaZSToYpwxcnLGKQK399foloI+8DdgkzTB2qrLgb13wkCm0G/aE1Iuif4MBNl/MWX TQjQ== X-Forwarded-Encrypted: i=1; AJvYcCUGPlyw3kgkoe4ZvLx3bhVWG8r7dMUWIgdqJbvB8Vzbvua2Yoz15cHQnAiluYWj5rkEovsJO1ni9W2HPgqE@lists.postgresql.org X-Gm-Message-State: AOJu0YwBq3DcZuvaJxVwHCduQ1iILq2wDC2YGGF299eOYUqyZz4aTum6 0W3QvupOGI0OEYWR2lUKAgrjmLYu/0MD9J0/Trx/YLfXSku1X2gx7XsH X-Gm-Gg: ASbGncvodzkkSk1+VABHD4s4x0xa7sGvYB8IhJJ1xuN9NgIfQYg4ffcVIfFy0vuAnsH GgM6zO+pdcXcv9UXH8oMoW/8UOA1tR38RBdcm0gPOKdVXo2tcD+Myby5gKjzhJHmnIFJHvQwb+N xn2Ku8mP0XhSLkv44D6tS4srx6z7azWWutUI1EPiL/ywW4wKLZPBh5UMBedvGTqnWVnUqi3i6tQ SVD7PBJXE+ncCPZxPUaAzj5lKlWJwwms6p+JsvRc1KoFUoGwAPnlgCFRbnOTBUYdqoeE6YjCgj3 cz1GU9CpFqIhbbPzZsSVgW2c3zYx1PfWYGroDfuOAr4nyEKA8Da66NUv+ORLRQx1Y/785/7Y/Zb jEIkcR9jTRNXiab3ua0d4QcXEmXvCx+FBUvf8vIelZmAj9aAPIBZQm249J1zzDLQ= X-Google-Smtp-Source: AGHT+IFyf6qE2OU+aZCb0ix9t5ziW8wbVCt9HJNGPrpGRLXEJmOGCCxsRZdKKD5oaYRL3AedF2ifjw== X-Received: by 2002:a17:90b:388e:b0:33b:6c1f:4d25 with SMTP id 98e67ed59e1d1-33b6c1f5256mr10515968a91.16.1760326402365; Sun, 12 Oct 2025 20:33:22 -0700 (PDT) Received: from smtpclient.apple ([170.178.170.211]) by smtp.gmail.com with ESMTPSA id 98e67ed59e1d1-33b5288eae7sm10322453a91.0.2025.10.12.20.33.19 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 12 Oct 2025 20:33:21 -0700 (PDT) From: Chao Li Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_467F0D5E-788A-4981-8631-2337758A5845" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward Date: Mon, 13 Oct 2025 11:33:06 +0800 In-Reply-To: <3686.1760232320@sss.pgh.pa.us> Cc: Dimitrios Apostolou , Nathan Bossart , Thomas Munro , pgsql-hackers@lists.postgresql.org To: Tom Lane References: <2edb7a57-b225-3b23-a680-62ba90658fec@gmx.net> <1282067.1744651649@sss.pgh.pa.us> <5ed51587-5a24-4d79-e36e-927620d0e3a2@gmx.net> <72d2c81b-a0fe-3d99-9dd4-0d771e6e673e@gmx.net> <1po8os49-r63o-2923-p37n-12698o1qn7p0@tzk.arg> <3515357.1760128017@sss.pgh.pa.us> <3698234.1760149336@sss.pgh.pa.us> <3686.1760232320@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_467F0D5E-788A-4981-8631-2337758A5845 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Oct 12, 2025, at 09:25, Tom Lane wrote: >=20 > While playing around with the test cases for pg_dump compression, > I was startled to discover that the performance of compress_lz4's > "stream API" code is absolutely abysmal. Here is a simple test > case to demonstrate, using the regression database as test data: >=20 > $ pg_dump -Fd --compress=3Dlz4 -f rlz4.dir regression > $ time pg_restore -f /dev/null rlz4.dir >=20 > real 0m0.023s > user 0m0.017s > sys 0m0.006s >=20 > So far so good, but now let's compress the toc.dat file: >=20 > $ lz4 -f -m --rm rlz4.dir/toc.dat > $ time pg_restore -f /dev/null rlz4.dir >=20 > real 0m1.335s > user 0m1.326s > sys 0m0.008s >=20 > Considering that lz4 prides itself on fast decompression speed, > that is not a sane result. Decompressing the file only requires > a couple ms on my machine: >=20 > $ time lz4cat rlz4.dir/toc.dat.lz4 >/dev/null >=20 > real 0m0.002s > user 0m0.000s > sys 0m0.002s >=20 > So on this example, pg_restore is something more than 600x slower > to read the TOC data than it ought to be. >=20 I tested the patch on my MacBook M4 (Sequoia 15.6.1), compiled with -O2 = optimization: - For LZ4 performance improvement: Without the fix (latest master): ``` chaol@ChaodeMacBook-Air tmp % pg_dump -Fd --compress=3Dlz4 -f rlz4.dir = regression chaol@ChaodeMacBook-Air tmp % time pg_restore -f /dev/null rlz4.dir pg_restore -f /dev/null rlz4.dir 0.03s user 0.03s system 11% cpu 0.463 = total chaol@ChaodeMacBook-Air tmp % lz4 -f -m --rm rlz4.dir/toc.dat # It took 1.59s to restore from compressed data, much slower than from = uncompressed data. chaol@ChaodeMacBook-Air tmp % time pg_restore -f /dev/null rlz4.dir pg_restore -f /dev/null rlz4.dir 1.59s user 0.02s system 97% cpu 1.653 = total ``` With the fix: ``` chaol@ChaodeMacBook-Air tmp % pg_dump -Fd --compress=3Dlz4 -f rlz4.dir = regression chaol@ChaodeMacBook-Air tmp % time pg_restore -f /dev/null rlz4.dir pg_restore -f /dev/null rlz4.dir 0.02s user 0.03s system 16% cpu 0.305 = total chaol@ChaodeMacBook-Air tmp % lz4 -f -m --rm rlz4.dir/toc.dat # Much faster !!! chaol@ChaodeMacBook-Air tmp % time pg_restore -f /dev/null rlz4.dir pg_restore -f /dev/null rlz4.dir 0.02s user 0.02s system 93% cpu 0.043 = total ``` - For Gzip zero read bug: Without the fix: ``` chaol@ChaodeMacBook-Air tmp % pg_dump -Fd --compress=3Dgzip -f rgzip.dir = regression chaol@ChaodeMacBook-Air tmp % gzip rgzip.dir/toc.dat # Failed chaol@ChaodeMacBook-Air tmp % pg_restore -f /dev/null rgzip.dir pg_restore: error: could not read from input file: ``` With the fix: ``` chaol@ChaodeMacBook-Air tmp % pg_dump -Fd --compress=3Dgzip -f rgzip.dir = regression chaol@ChaodeMacBook-Air tmp % gzip rgzip.dir/toc.dat # Ran successfully=20 chaol@ChaodeMacBook-Air tmp % pg_restore -f /dev/null rgzip.dir ``` I also reviewed the code change, basically LGTM. Just a couple of = trivial comments: 1 - 0001 In WriteDataToArchiveLZ4() ``` + required =3D LZ4F_compressBound(chunk, &state->prefs); + if (required > state->buflen - state->bufdata) + { + cs->writeF(AH, state->buffer, state->bufdata); + state->bufdata =3D 0; + } ``` And in EndCompressorLZ4() ``` + required =3D LZ4F_compressBound(0, &state->prefs); + if (required > state->buflen - state->bufdata) + { + cs->writeF(AH, state->buffer, state->bufdata); + state->bufdata =3D 0; + } ``` These two code pieces are similar, only difference is the first = parameter passed to LZ4F_compressBound(). I think we can create an inline function for it. But these are just 5 = lines, I don=E2=80=99t have a strong option on that. Same thing for the other code pieces in LZ4Stream_write() and = LZ4Stream_close(). 2 - 0003 ``` /* * LZ4 equivalent to feof() or gzeof(). Return true iff there is no - * decompressed output in the overflow buffer and the end of the = backing file ``` This doesn=E2=80=99t belong to the current patch. But =E2=80=9Ciff=E2=80=9D= seems a typo of =E2=80=9Cif=E2=80=9D. You may fix it as you are = touching this piece of code. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ --Apple-Mail=_467F0D5E-788A-4981-8631-2337758A5845 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Oct 12, 2025, at 09:25, Tom Lane = <tgl@sss.pgh.pa.us> wrote:

While playing around with the = test cases for pg_dump compression,
I was startled to discover that = the performance of compress_lz4's
"stream API" code is absolutely = abysmal.  Here is a simple test
case to demonstrate, using the = regression database as test data:

$ pg_dump -Fd --compress=3Dlz4 = -f rlz4.dir regression
$ time pg_restore -f /dev/null = rlz4.dir

real    0m0.023s
user =    0m0.017s
sys =     0m0.006s

So far so good, but now let's = compress the toc.dat file:

$ lz4 -f -m --rm rlz4.dir/toc.dat
$ = time pg_restore -f /dev/null rlz4.dir

real =    0m1.335s
user    0m1.326s
sys =     0m0.008s

Considering that lz4 prides = itself on fast decompression speed,
that is not a sane result. =  Decompressing the file only requires
a couple ms on my = machine:

$ time lz4cat rlz4.dir/toc.dat.lz4 = >/dev/null

real    0m0.002s
user =    0m0.000s
sys =     0m0.002s

So on this example, pg_restore = is something more than 600x slower
to read the TOC data than it ought = to be.


I tested the = patch on my MacBook M4 (Sequoia 15.6.1), compiled with -O2 = optimization:

- For LZ4 performance = improvement:

Without the fix (latest = master):
```
chaol@ChaodeMacBook-Air tmp % pg_dump = -Fd --compress=3Dlz4 -f rlz4.dir = regression

chaol@ChaodeMacBook-Air tmp % = time pg_restore -f /dev/null rlz4.dir
pg_restore -f /dev/null = rlz4.dir  0.03s user 0.03s system 11% cpu 0.463 = total

chaol@ChaodeMacBook-Air tmp % lz4 -f -m = --rm rlz4.dir/toc.dat

# It took 1.59s to = restore from compressed data, much slower than from uncompressed = data.
chaol@ChaodeMacBook-Air tmp % time pg_restore -f = /dev/null rlz4.dir
pg_restore -f /dev/null rlz4.dir =  1.59s user 0.02s system 97% cpu 1.653 = total
```

With the = fix:
```
chaol@ChaodeMacBook-Air tmp % pg_dump = -Fd --compress=3Dlz4 -f rlz4.dir = regression

chaol@ChaodeMacBook-Air tmp % time = pg_restore -f /dev/null rlz4.dir
pg_restore -f /dev/null = rlz4.dir  0.02s user 0.03s system 16% cpu 0.305 = total

chaol@ChaodeMacBook-Air tmp % lz4 -f -m = --rm rlz4.dir/toc.dat

# Much faster = !!!
chaol@ChaodeMacBook-Air tmp % time pg_restore -f /dev/null = rlz4.dir
pg_restore -f /dev/null rlz4.dir  0.02s user = 0.02s system 93% cpu 0.043 = total
```

- For Gzip zero read = bug:

Without the = fix:
```
chaol@ChaodeMacBook-Air tmp % pg_dump = -Fd --compress=3Dgzip -f rgzip.dir = regression
chaol@ChaodeMacBook-Air tmp % gzip = rgzip.dir/toc.dat

# = Failed
chaol@ChaodeMacBook-Air tmp % pg_restore -f /dev/null = rgzip.dir
pg_restore: error: could not read from input = file:
```

With the = fix:
```
chaol@ChaodeMacBook-Air tmp % pg_dump = -Fd --compress=3Dgzip -f rgzip.dir = regression
chaol@ChaodeMacBook-Air tmp % gzip = rgzip.dir/toc.dat

# Ran = successfully 
chaol@ChaodeMacBook-Air tmp % pg_restore -f = /dev/null rgzip.dir
```

I also = reviewed the code change, basically LGTM. Just a couple of trivial = comments:

1 - = 0001
In WriteDataToArchiveLZ4()

``= `
+ required =3D = LZ4F_compressBound(chunk, &state->prefs);
+ = if (required > state->buflen - = state->bufdata)
+ {
+ = cs->writeF(AH, state->buffer, = state->bufdata);
+ state->bufdata = =3D 0;
+ = }
```

And = in EndCompressorLZ4()
```
+ required = =3D LZ4F_compressBound(0, &state->prefs);
+ if = (required > state->buflen - state->bufdata)
+ = {
+ cs->writeF(AH, = state->buffer, state->bufdata);
+ = state->bufdata =3D 0;
+ = }
```

These two code = pieces are similar, only difference is the first parameter passed to = LZ4F_compressBound().

I think we can create an = inline function for it. But these are just 5 lines, I don=E2=80=99t have = a strong option on that.

Same thing for the = other code pieces in LZ4Stream_write() and = LZ4Stream_close().

2 - = 0003
```
 /*
  * LZ4 = equivalent to feof() or gzeof().  Return true iff there is = no
- * decompressed output in the overflow buffer and the end = of the backing file
```

This = doesn=E2=80=99t belong to the current patch. But =E2=80=9Ciff=E2=80=9D = seems a typo of =E2=80=9Cif=E2=80=9D. You may fix it as you are touching = this piece of code.

Best regards,
--
Chao Li (Evan)
HighGo Software = Co., Ltd.
https://www.highgo.com/




= --Apple-Mail=_467F0D5E-788A-4981-8631-2337758A5845--