BUG #16443: Too much memory usage on insert query - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      16443
Logged by:          Kurt Roeckx
Email address:      kurt@roeckx.be
PostgreSQL version: 12.3
Operating system:   Debian
Description:

Hi,

I have this in my config file:
shared_buffers = 2048MB                 # min 128kB
work_mem = 1024MB                       # min 64kB

I was executing this query:
insert into ct_entry_chain (ct_entry_id, certificate_id) select id,
unnest(certificate_chain_id) from ct_entry;

The process was using at least 14 GB, of the 8 GB of RAM that's available.
Swap was also using around the 8GB maximum, while normally around 200 MB is
swapped out. I didn't expect this process to use more than around 3 GB.

This has resulted in an out of memory condition.

I was converting my schema, ct_entry_chain is a new table that looks like:
                                Table "public.ct_entry_chain"
     Column     |  Type  | Collation | Nullable |                  Default
----------------+--------+-----------+----------+--------------------------------------------
 id             | bigint |           | not null |
nextval('ct_entry_chain_id_seq'::regclass)
 ct_entry_id    | bigint |           | not null |
 certificate_id | bigint |           | not null |
Indexes:
    "ct_entry_chain_pkey" PRIMARY KEY, btree (id)
    "ct_entry_chain_ct_entry_id_certificate_id_key" UNIQUE CONSTRAINT, btree
(ct_entry_id, certificate_id)
Foreign-key constraints:
    "ct_entry_chain_certificate_id_fkey" FOREIGN KEY (certificate_id)
REFERENCES raw_certificates(id)
    "ct_entry_chain_ct_entry_id_fkey" FOREIGN KEY (ct_entry_id) REFERENCES
ct_entry(id)

ct_entry itself looks like:
                                            Table "public.ct_entry"
        Column        |           Type           | Collation | Nullable |
           Default
----------------------+--------------------------+-----------+----------+--------------------------------------
 id                   | bigint                   |           | not null |
nextval('ct_entry_id_seq'::regclass)
 ct_log_id            | integer                  |           | not null |
 raw_certificate_id   | bigint                   |           | not null |
 log_entry            | bigint                   |           | not null |
 timestamp            | timestamp with time zone |           | not null |
 version              | integer                  |           | not null |
 entry_type           | integer                  |           | not null |
 leaf_type            | integer                  |           | not null |
 extensions           | bytea                    |           |          |
 issuer_key_hash      | bytea                    |           |          |
 pre_certificate_id   | bigint                   |           |          |
 certificate_chain_id | bigint[]                 |           |          |
Indexes:
    "ct_entry_pkey" PRIMARY KEY, btree (id)
    "ct_entry_raw_certificate_id_idx" btree (raw_certificate_id)
Foreign-key constraints:
    "ct_entry_ct_log_id_fkey" FOREIGN KEY (ct_log_id) REFERENCES
ct_logs(id)
    "ct_entry_pre_certificate_id_fkey" FOREIGN KEY (pre_certificate_id)
REFERENCES raw_certificates(id)
    "ct_entry_raw_certificate_id_fkey" FOREIGN KEY (raw_certificate_id)
REFERENCES raw_certificates(id)
Referenced by:
    TABLE "ct_entry_chain" CONSTRAINT "ct_entry_chain_ct_entry_id_fkey"
FOREIGN KEY (ct_entry_id) REFERENCES ct_entry(id)