On Mon, 3 Feb 2025 at 17:51, 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
> );
I prepare a logical replication with the following:
CREATE TABLE mytable (
id bigint NOT NULL PRIMARY KEY,
snippet bytea default decode(repeat('1234567890',10000) ,'hex'),
title bytea default decode(repeat('1234567890',10000),'hex'),
updated_at timestamp with time zone default 'now',
rich_snippet bytea default decode(repeat('1234567890',10000),'hex');
-- Specify the data only for first column, the rest of the column will
use default value
insert into mytable values(generate_series(1,100000));
These did not create temp files, I had used a low work_mem
configuration i.e. 64kB. I have enabled "log_temp_files = 0" to
confirm that no temp files were created.
Generally when we use ORDER BY, DISTINCT, hash joins, hash-based
aggregation, memoize nodes, hash-based processing, merge joins, these
temp files will be created.
Ex: The following creates temp files:
select * from mytable order by 2,3,4,5;
2025-02-07 15:10:25.409 IST [586494] LOG: temporary file: path
"base/pgsql_tmp/pgsql_tmp586494.0", size 65265664
2025-02-07 15:10:25.464 IST [586495] LOG: temporary file: path
"base/pgsql_tmp/pgsql_tmp586495.0", size 60481536
2025-02-07 15:10:25.514 IST [586492] LOG: temporary file: path
"base/pgsql_tmp/pgsql_tmp586492.0", size 54730752
Does the table have any triggers that could invoke another statement?
Could you provide more details or simulate a scenario using a table
like the one above with insert operations? That would be really
helpful.
Regards,
Vignesh
