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

Here is the list of tmp files:

postgres@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.koterov@gmail.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.kapila16@gmail.com> wrote:

On Sun, Feb 2, 2025 at 5:13 PM Dmitry Koterov <dmitry.koterov@gmail.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.