Postgres COPY, is it possible to load multiple files into the same table?

Question

Is it possible to execute several \copy commands in parallel against the same table? I know something similar exists with pg_restore, but that splits the work up amongst tables.

I'm working with files that range from 40-100M rows. Despite dropping indices and creating them later, it still takes a considerable amount of time.

• András Váczi: Have you maybe tried it? Nothing should prevent this, AFAIK.

Answer 1

You have two questions here,

1. Is it possible to load multiple files into the same table?
2. On the benefit of it, with concerns about time "in parallel against the same table" .. "Despite dropping indices and creating them later, it still takes a considerable amount of time."

Yes you can load up multiple files. Functionally, it doesn't matter how many times you run the COPY command -- it'll work more than once.

Normally, there isn't much an advantage to split up the copy command if the target is the same table, or even on the same tablespace. One background-writer process can easily saturate a disk. The only time this would matter is if you had something that was computationally intensive. Moreover, there are some reasons to not break up copys. For instance, if you create and load a table in the same transaction you have less WAL,

COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration only applies when wal_level is minimal as all commands must write WAL otherwise.

If you need faster writes, check out the Non-Durable Settings