Increased work_mem for "logical replication tablesync worker" only?

From: Dmitry Koterov <dmitry(dot)koterov(at)gmail(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Increased work_mem for "logical replication tablesync worker" only?
Date: 2025-02-02 11:43:27
Message-ID: CA+CZih6HTTy_2EtcfuoOzi4pHHGKpCNkMu1AKJkyFjxZ3O2z=w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi.

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.)
2. Is there a way to set work_mem only for "logical replication tablesync
worker"? I don't want to have it that high for all connections, but for
logical replication tablesync worker - it's fine to have it set to a huge
value (I have max_sync_workers_per_subscription=1, so there is not more
than 1 of such processes in the system).
3. Is this work_mem consideration relevant at all? Maybe it's a red herring?

Thanks!

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2025-02-02 13:21:33 Re: why there is not VACUUM FULL CONCURRENTLY?
Previous Message Andrey Borodin 2025-02-02 10:15:26 Re: UUID v7