Re: Add mention of execution time memory for enable_partitionwise_* GUCs

2024-07-23 Thread Dimitrios Apostolou
Thank you for the patch improving the docs, I think it's a clear improvement from before. On Thu, 18 Jul 2024, David Rowley wrote: I considered writing about work_mem, but felt I wanted to keep it as brief as possible and just have some words that might make someone think twice. The details in

Re: [PATCH v1] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-04-01 Thread Dimitrios Apostolou
On Sat, 29 Mar 2025, Dimitrios Apostolou wrote: P.S. What is the recommended way to test a change, besides a generic make check? And how do I run selectively only the pg_dump/restore tests, in order to speed up my development routine? I have tested it with: make -C src/bin/pg_dump check

Re: [PATCH v1] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-04-01 Thread Dimitrios Apostolou
andom reads is a possible speed. In that worst case it would be better to seek() forward for lengths of over 1MB. On 1 April 2025 22:04:00 CEST, Nathan Bossart wrote: >On Tue, Apr 01, 2025 at 09:33:32PM +0200, Dimitrios Apostolou wrote: >> It didn't break any test, but I also don&#

[PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-04-14 Thread Dimitrios Apostolou
On Fri, 4 Apr 2025, Dimitrios Apostolou wrote: I just managed to run pgindent, here is v2 with the comment style fixed. Any feedback on this one-liner? Or is the lack of feedback a clue that I have been missing something important in my patch submission? :-) Should I CC people that are

[WIP] Implement "pg_restore --data-only --clean" as a way to skip WAL

2025-04-14 Thread Dimitrios Apostolou
when TRUNCATing the empty /referenced/ table doesn't make sense. What do you think? Thank you in advance, Dimitris From ee28b0b9c1d3b78f318f259f4907785db98f31e6 Mon Sep 17 00:00:00 2001 From: Dimitrios Apostolou Date: Sat, 12 Apr 2025 01:59:45 +0200 Subject: [PATCH v1] pg_restore --clean

Re: Fundamental scheduling bug in parallel restore of partitioned tables

2025-04-15 Thread Dimitrios Apostolou
On 15 April 2025 18:18:41 CEST, Tom Lane wrote: >Dimitrios Apostolou writes: >> I only got the "ERROR: deadlock detected" message once, with pg_restore >> compiled from master branch. > >Oh, hmm ... do you recall just when on the master branch? I'm

Re: Fundamental scheduling bug in parallel restore of partitioned tables

2025-04-15 Thread Dimitrios Apostolou
On Mon, 14 Apr 2025, Tom Lane wrote: I wrote: Here's a draft patch for this. It seems to fix the problem in light testing. I realized that the "repro" I had for this isn't testing the same thing that Dimitrios is seeing; what it is exposing looks more like a bug or at least a behavioral chan

Re: [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-04-04 Thread Dimitrios Apostolou
I just managed to run pgindent, here is v2 with the comment style fixed.From 74258e15a3ecca5abe981aa64dfff434c4b27969 Mon Sep 17 00:00:00 2001 From: Dimitrios Apostolou Date: Sat, 29 Mar 2025 01:16:07 +0100 Subject: [PATCH v2 1/2] parallel pg_restore: avoid disk seeks when jumping short

[PATCH] parallel pg_restore: move offset-building phase to before forking

2025-04-04 Thread Dimitrios Apostolou
children and starting assigning jobs to them. What do you think? Thanks, Dimitris From 8aaf72bb31fcb55ed57893e64b5cbb14e23d14c7 Mon Sep 17 00:00:00 2001 From: Dimitrios Apostolou Date: Sat, 29 Mar 2025 01:16:19 +0100 Subject: [PATCH v1] parallel pg_restore: move offset-building phase to before forking

[PATCH v1] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-03-28 Thread Dimitrios Apostolou
much appreciated. Regards, Dimitris P.S. What is the recommended way to test a change, besides a generic make check? And how do I run selectively only the pg_dump/restore tests, in order to speed up my development routine? From 5a43ce169a8b03a866728f2af8117871203f395a Mon Sep 17 00:00:00 2001 Fro

fallocate() causes btrfs to never compress postgresql files

2025-04-02 Thread Dimitrios Apostolou
Hi, this is just a heads-up about files being generated by PostgreSQL 17 not being compressed by Btrfs, even when mounted with the force-compress mount option. I have this occuring aggressively when restoring a database via pg_restore. I think this is caused mdzeroextend() calling FileFallocate()

Re: [PING] fallocate() causes btrfs to never compress postgresql files

2025-06-02 Thread Dimitrios Apostolou
On Tue, 3 Jun 2025, Thomas Munro wrote: On Mon, Jun 2, 2025 at 10:14 PM Dimitrios Apostolou wrote: On Sun, 1 Jun 2025, Thomas Munro wrote: Or for a completely different approach: I wonder if ftruncate() would be more efficient on COW systems anyway. The minimum thing we need is for the file

Re: [PING] fallocate() causes btrfs to never compress postgresql files

2025-06-02 Thread Dimitrios Apostolou
On Sun, 1 Jun 2025, Thomas Munro wrote: Or for a completely different approach: I wonder if ftruncate() would be more efficient on COW systems anyway. The minimum thing we need is for the file system to remember the new size, 'cause, erm, we don't. All the rest is probably a waste of cycles, si

Re: [PING] fallocate() causes btrfs to never compress postgresql files

2025-05-29 Thread Dimitrios Apostolou
On Wed, 28 May 2025, Tomas Vondra wrote: Isn't guaranteeing success of a write a general issue with compressed filesystem? Why is posix_fallocate() any special in this regard? Shouldn't the filesystem be defensive and assume the data is not compressible? Or maybe just return EOPNOTSUPP when in d

Re: [PATCH v2] parallel pg_restore: move offset-building phase to before forking

2025-06-15 Thread Dimitrios Apostolou
Rebased and attached v3, which fixes compilation on mingw32. On Tue, 10 Jun 2025, Dimitrios Apostolou wrote: Rebased and attached new patch. Should I add it to July's commitfest? On Fri, 4 Apr 2025, Dimitrios Apostolou wrote: Hello list, based on the delays I experienced in pg_re

Re: [WIP PATCH v3] Implement "pg_restore --data-only --clean" as a way to skip WAL

2025-06-15 Thread Dimitrios Apostolou
Attaching files now... On Mon, 16 Jun 2025, Dimitrios Apostolou wrote: Attaching v3 of the patch, together with a new test file that tests previously untested flags of pg_restore. Added to July's commitfest: https://commitfest.postgresql.org/patch/5821/ On Thu, 12 Jun 2025, Dimi

[WIP PATCH v3] Implement "pg_restore --data-only --clean" as a way to skip WAL

2025-06-15 Thread Dimitrios Apostolou
Attaching v3 of the patch, together with a new test file that tests previously untested flags of pg_restore. Added to July's commitfest: https://commitfest.postgresql.org/patch/5821/ On Thu, 12 Jun 2025, Dimitrios Apostolou wrote: I wonder about the following in pg_restore.c. Right n

--enable-{debug,cassert} should also activate --enable-depend

2025-06-17 Thread Dimitrios Apostolou
Hello list, I just spent 2 miserable days tracking down a very weird issue. I ended up stepping through the code in the debugger. As it turned out, the very same moment of execution, one datastructure (struct _restoreOptions defined in pg_backup.h) contained different fields on one data frame

Re: --enable-{debug,cassert} should also activate --enable-depend

2025-06-17 Thread Dimitrios Apostolou
On Tue, 17 Jun 2025, Aleksander Alekseev wrote: Hi Dimitris, I just spent 2 miserable days tracking down a very weird issue. I ended up stepping through the code in the debugger. As it turned out, the very same moment of execution, one datastructure (struct _restoreOptions defined in pg_bac

[WIP PATCH v2] Implement "pg_restore --data-only --clean" as a way to skip WAL

2025-06-09 Thread Dimitrios Apostolou
On Mon, 14 Apr 2025, Dimitrios Apostolou wrote: Hello list, I implemented --clean support for --data-only, in order to avoid logging to the WAL while populating the database. The attached patch issues a TRUNCATE before COPY on each worker process, and provides a significant speed advantage

[PATCH v2] parallel pg_restore: move offset-building phase to before forking

2025-06-09 Thread Dimitrios Apostolou
Rebased and attached new patch. Should I add it to July's commitfest? On Fri, 4 Apr 2025, Dimitrios Apostolou wrote: Hello list, based on the delays I experienced in pg_restore, as described at: https://www.postgresql.org/message-id/flat/6bd16bdb-aa5e-0512-739d-b84100596...@gmx.n

Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-06-09 Thread Dimitrios Apostolou
On Mon, 14 Apr 2025, Tom Lane wrote: You should add your patch to the July commitfest [1] to make sure we don't lose track of it. I rebased the patch (attached) and created an entry in the commitfest: https://commitfest.postgresql.org/patch/5809/ Thanks! Dimitris

Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-06-09 Thread Dimitrios Apostolou
Attached now... On Mon, 9 Jun 2025, Dimitrios Apostolou wrote: On Mon, 14 Apr 2025, Tom Lane wrote: You should add your patch to the July commitfest [1] to make sure we don't lose track of it. I rebased the patch (attached) and created an entry in the commitfest:

Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-06-14 Thread Dimitrios Apostolou
On Fri, 13 Jun 2025, Nathan Bossart wrote: On Fri, Jun 13, 2025 at 01:00:26AM +0200, Dimitrios Apostolou wrote: By the way, I might have set the threshold to 1MB in my program, but lowering it won't show a difference in my test case, since the lseek()s I was noticing before the patch

Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-06-14 Thread Dimitrios Apostolou
On Sat, 14 Jun 2025, Dimitrios Apostolou wrote: On Fri, 13 Jun 2025, Nathan Bossart wrote: On Fri, Jun 13, 2025 at 01:00:26AM +0200, Dimitrios Apostolou wrote: By the way, I might have set the threshold to 1MB in my program, but lowering it won't show a difference in my test case,

[PING] fallocate() causes btrfs to never compress postgresql files

2025-05-28 Thread Dimitrios Apostolou
Hello, sorry for mass sending this, but I didn't get any response to my first email [1] so I'm now CC'ing the commit's 4d330a6 [2] author and the reviewers. I think it's an important issue, because I need to custom-compile postgresql to have what I had before: a transparently compressed databas

Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-06-10 Thread Dimitrios Apostolou
On Tue, 10 Jun 2025, Nathan Bossart wrote: I also wrote a couple of test programs to show the difference between fseeko-ing and fread-ing through a file with various sizes. On a Linux machine, I see this: log2(n) | fseeko | fread -+-+--- 1 | 109.288 | 5.52

Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-06-11 Thread Dimitrios Apostolou
On Wed, 11 Jun 2025, Nathan Bossart wrote: On Wed, Jun 11, 2025 at 12:32:58AM +0200, Dimitrios Apostolou wrote: what read-seek pattern do you see on the system call level (as shown by strace)? In pg_restore it was a constant loop of read(4K)-lseek(8-16K). For fseeko(), sizes less than 4096

Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-06-12 Thread Dimitrios Apostolou
On Tue, 10 Jun 2025, Nathan Bossart wrote: So, fseeko() starts winning around 4096 bytes. On macOS, the differences aren't quite as dramatic, but 4096 bytes is the break-even point there, too. I imagine there's a buffer around that size somewhere... This doesn't fully explain the results you

Re: [WIP PATCH v2] Implement "pg_restore --data-only --clean" as a way to skip WAL

2025-06-12 Thread Dimitrios Apostolou
I wonder about the following in pg_restore.c. Right now my implementation covers only parallel restore. In the case of non-parallel restore, I want to make the behaviour similar, i.e. each worker to issue a TRUNCATE before COPY starts. But then the StartTransaction() doesn't make sense, as everyt

pg_restore causing ENOSPACE on the WAL partition. Fundamental issue?

2025-06-10 Thread Dimitrios Apostolou
Hello list, I have previously raised an issue on pgsql-general, where PostgreSQL is logging without any boundaries to the WAL directory, even if other writer processes can't catch up with it. It ends up with WAL partition becoming full and a bad crash. Read more at the thread at: https://www

Re: [PING] fallocate() causes btrfs to never compress postgresql files

2025-06-12 Thread Dimitrios Apostolou
On Mon, 9 Jun 2025, Thomas Munro wrote: On Tue, Jun 3, 2025 at 1:58 AM Dimitrios Apostolou wrote: This sounds like the best solution IMO. People can then experiment with different settings and filesystems, and that way we also learn in the process. Thank you for the effort and patches so far

Re: pg_restore causing ENOSPACE on the WAL partition. Fundamental issue?

2025-06-12 Thread Dimitrios Apostolou
On Tue, 10 Jun 2025, David G. Johnston wrote: On Tuesday, June 10, 2025, Dimitrios Apostolou wrote: Hello list, I have previously raised an issue on pgsql-general, where PostgreSQL is logging without any boundaries to the WAL directory, even if other writer processes can't

Re: [PATCH v2] parallel pg_restore: move offset-building phase to before forking

2025-06-12 Thread Dimitrios Apostolou
On Tue, 10 Jun 2025, Dimitrios Apostolou wrote: Rebased and attached new patch. Should I add it to July's commitfest? Added to commitfest: https://commitfest.postgresql.org/patch/5817/ On Fri, 4 Apr 2025, Dimitrios Apostolou wrote: Hello list, based on the delays I experienc

Re: [PING] fallocate() causes btrfs to never compress postgresql files

2025-07-10 Thread Dimitrios Apostolou
On Thu, 12 Jun 2025, Dimitrios Apostolou wrote: On Mon, 9 Jun 2025, Thomas Munro wrote: On Tue, Jun 3, 2025 at 1:58 AM Dimitrios Apostolou wrote: This sounds like the best solution IMO. People can then experiment with different settings and filesystems, and that way we also learn in the

Re: [WIP PATCH v2] Implement "pg_restore --data-only --clean" as a way to skip WAL

2025-07-23 Thread Dimitrios Apostolou
On Friday 2025-06-13 03:42, Greg Sabino Mullane wrote: I think the overall idea is sound. But we need a better solution for the truncate fk failure. Can we introspect somehow and do a truncate or do a delete as necessary? I don't like the idea of simply ignoring the constraint, or of throwin

Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-07-23 Thread Dimitrios Apostolou
[1] https://commitfest.postgresql.org/patch/5817/ On Saturday 2025-06-14 00:04, Nathan Bossart wrote: On Fri, Jun 13, 2025 at 01:00:26AM +0200, Dimitrios Apostolou wrote: By the way, I might have set the threshold to 1MB in my program, but lowering it won't show a difference in my test case, since the lseek()s

Re: [PING] [PATCH v2] parallel pg_restore: avoid disk seeks when jumping short distance forward

2025-07-28 Thread Dimitrios Apostolou
On Saturday 2025-06-14 18:17, Dimitrios Apostolou wrote: Out of curiosity I've tried the same with an uncompressed dump (--compress=none). Surprisingly it seems the blocksize is even smaller. With my patched pg_restore I only get 4K reads and nothing else on the strace output. r

Re: [PING] fallocate() causes btrfs to never compress postgresql files

2025-07-28 Thread Dimitrios Apostolou
On Friday 2025-07-11 00:45, Thomas Munro wrote: On Fri, Jul 11, 2025 at 5:39 AM Dimitrios Apostolou wrote: I applied the patch on PostgreSQL v17 and am testing it now. I chose ftruncate method and I see ftruncate in action using strace while doing pg_restore of a big database. Nothing