Re: Increased work_mem for "logical replication tablesync worker" only?

Here is the list of tmp files:

postgres(at)pg-101a:~/17/main/base/pgsql_tmp$ ls -la
total 5422297
drwx------ 2 postgres postgres 9 Feb 3 04:08 .
drwx------ 8 postgres postgres 8 Jan 29 01:27 ..
-rw------- 1 postgres postgres 1073741824 Feb 3 04:05 pgsql_tmp196534.0
-rw------- 1 postgres postgres 1073741824 Feb 3 04:05 pgsql_tmp196534.1
-rw------- 1 postgres postgres 1073741824 Feb 3 04:05 pgsql_tmp196534.2
-rw------- 1 postgres postgres 1073741824 Feb 3 04:06 pgsql_tmp196534.3
-rw------- 1 postgres postgres 1073741824 Feb 3 04:07 pgsql_tmp196534.4
-rw------- 1 postgres postgres 1073741824 Feb 3 04:08 pgsql_tmp196534.5
-rw------- 1 postgres postgres 819396608 Feb 3 04:08 pgsql_tmp196534.6

With work_mem=4GB, all those files *on the destination node* seemed to
appear immediately with 4GB size and keep growing since then, while COPY
progresses on the source node (i.e. it looked like PG tried hard to utilize
work_mem, but after reaching the limit, dumped everything to pgsql_tmp
still).

The table structure being copied (just 1 index there):

CREATE TABLE mytable (
id bigint NOT NULL PRIMARY KEY,
snippet bytea,
title bytea,
updated_at timestamp with time zone,
rich_snippet bytea
);

Directories sizes on the destination node while tablesync is working (it's
copied in to an almost empty database):

$ watch du -sh 17/main/base/* 17/main/pg_wal
2.2M 17/main/base/1
14G 17/main/base/16385
2.3M 17/main/base/16387
2.2M 17/main/base/4
2.3M 17/main/base/5
12G 17/main/base/pgsql_tmp
6.3G 17/main/pg_wal

So the question, why does it use temp files. Why not just writes directly
to WAL+data.

On Mon, Feb 3, 2025 at 3:04 AM Dmitry Koterov <dmitry(dot)koterov(at)gmail(dot)com>
wrote:

> Hi.
>
> 1. Those are temp files on the destination node (where the logical
> subscription exists and tablesync worker runs), not on the source. On the
> source, it’s all clear.
>
> 2. No “spill” suffix/substring in the file names. I tried to look at the
> content of these temp files, I I saw some text fragments from the original
> table’s text column there. I.e. it looks like for some reason, the stream
> received from the source node’s COPY command goes to that temp files (at
> least partially).
>
> 3. I made several more experiments, increasing work_mem to several GB (for
> the role which tablesync worker uses when copying) definitely helps with
> temp files.
>
> Thanks!
>
> On Sun, Feb 2, 2025 at 19:10 Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
>> On Sun, Feb 2, 2025 at 5:13 PM Dmitry Koterov <dmitry(dot)koterov(at)gmail(dot)com>
>> wrote:
>> >
>> > Trying to monitor perf during the initial tablesync phase (COPY) right
>> after CREATE SUBSCRIPTION. I noticed that the size of
>> 17/main/base/pgsql_tmp on the destination node grows (tens of gigabytes) as
>> the COPY command (running internally on the publisher) progresses. Then in
>> the end (when its "EXPLAIN SELECT 1 FROM tbl" on the destination shows the
>> approximate number of rows equals to the number of rows on the source node)
>> it hangs for several minutes, and then 17/main/base/pgsql_tmp empties, and
>> the subscription progresses.
>> >
>> > It seems like if I increase work_mem to several GB, then the growth of
>> 17/main/base/pgsql_tmp becomes less significant.
>> >
>> > Questions:
>> >
>> > 1. Are there some diagnostics commands that would allow me to figure
>> out what is in those tmp files? Why does the subscriber create those tmp
>> files and not just write directly to the data files and WAL? (The table has
>> 2 bytea columns, i.e. it's TOASTed for sure.)
>> >
>>
>> We do write spill files (ending with '.spill') if the changes are
>> large. Can you please share the name of tmp files to avoid any
>> assumptions?
>>
>> --
>> With Regards,
>> Amit Kapila.
>>
>