'How to speed up large copy into a Postgres table

I'm trying to load a large dataset (25 GB) into a Postgres table. The command below works, but it takes 2.5 hours and fully utilizes 3-4 cores on the machine the entire time. Can I speed it up a lot? The problem is I want to insert another 1700 such 25 GB files into the table too (these would be separate partitions). 2.5 hours per file is too slow. Or rather it's not too slow, but makes me think subsequent queries against the data will be too slow.

Probably my dataset is too big for Postgres, but the idea of being able to run an optimized query (once the data is all inserted and later indexed and partitioned) and get a result back in < 3 seconds is appealing, hence I wanted to try.

I'm mostly following the guidelines from here. I don't have an index on the table yet. I don't use foreign keys, I'm using a bulk copy, etc.

-- some of the numeric columns have "nan" values so they need to start as strings in raw form
CREATE TABLE raw_eqtl_allpairs (
  gene_id varchar,
    variant_id varchar,
    chr varchar,
    bp BIGINT,
    ref_ varchar,
    alt varchar,
    tss_distance bigint,
    ma_samples int,
    ma_count int,
    maf varchar,
    pval_nominal varchar,
    slope varchar,
    slope_se varchar,
    chromosome_ varchar,
    pos BIGINT,
    af varchar
);

COPY raw_eqtl_allpairs(gene_id, variant_id, chr, bp, ref_, alt, tss_distance, ma_samples, ma_count, maf, pval_nominal, slope, slope_se, chromosome_, pos, af)
FROM '/Downloads/genomics_data.tsv'
DELIMITER E'\t'
CSV HEADER;

Edit 1: I'm running on Docker on my Mac, which has 4 Intel i7 cores and 16 GB of ram. I have 500 GB of flash storage.

Edit 2: I'm using the default /var/lib/postgresql/data/postgresql.conf that comes with the DockerHub Postgres 14.2 tag. For simplicity, I grepped it for key details. Seems like most of the important things were commented out:

#maintenance_work_mem = 64MB        # min 1MB
#autovacuum_work_mem = -1       # min 1MB, or -1 to use maintenance_work_mem
max_wal_size = 1GB
#wal_level = replica            # minimal, replica, or logical
#checkpoint_timeout = 5min      # range 30s-1d
#archive_mode = off     # enables archiving; off, on, or always
#max_wal_senders = 10       # max number of walsender processes

Maybe I could speed things up a lot if I changed wal_level to minimal, and changed max_wal_size to something larger, like 4GB?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source