Only one index, the primary key. ChatGPT tells that temp files may be used, when bytea columns (or heavily toasted ones) are utilized, but it can’t explain, why. To me, it’s hard to believe that on the destination, the COPY stream receiver puts almost all of the received data to temp files, then waits for COPY to finish, and only then moves that temp files to data area, because of the large transaction or something. Would be cool to understand the reason.
On Mon, Feb 3, 2025 at 22:31 Amit Kapila <amit.kapil...@gmail.com> wrote: > On Mon, Feb 3, 2025 at 5:51 PM Dmitry Koterov <dmitry.kote...@gmail.com> > wrote: > > > > 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 > > ); > > > > Do you have any indexes on the table? I am not aware if a direct copy > to table from tablesync uses tmp files. > > -- > With Regards, > Amit Kapila. >
