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

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
sizeof 17/main/base/pgsql_tmp on the destination node grows (tens of gigabytes) as the COPY command (running internally
onthe publisher) progresses. Then in the end (when its "EXPLAIN SELECT 1 FROM tbl" on the destination shows the
approximatenumber of rows equals to the number of rows on the source node) it hangs for several minutes, and then
17/main/base/pgsql_tmpempties, 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
subscribercreate 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.