PostgreSQL performance of INSERT INTO table SELECT vs. COPY

user_11771349 0
Viewed 1259

I'm attempting to move medium amounts of data around in PostgreSQL (tens-to-hundreds of millions of rows).

In designing the system, I'm trying to understand: How does the performance of INSERT INTO table(field1, field2) SELECT field1, field2 FORM other_table compared with COPY FROM ... BINARY in PostgreSQL?

I can't find any documentation that directly speaks to that question. Some considerations I can see:

  • INTO INTO ... SELECT requires both reads and writes from the same disk
  • COPY FROM ... BINARY requires either a client that has the data, or doing a round-trip COPY TO ... piped to COPY FROM ...

But I'm sure there are others, I'm hoping there's some form of canonical performance guidance around comparative expectations for these.

postgresql performance
1 Answers

Ultimately questions like this can only be answered by tests.

But if you want to copy data from one table to another, INSERT ... SELECT ... should perform better, because it does not require saving the data to an intermediary file or going through a client-server connection.

Tips for speed:

  • Have no constraints and indexes on the new table when you load the data, but add them afterwards.

  • Make sure max_wal_size is high.

I'd VACUUM (FREEZE) the new table afterwards (which doesn't disturb normal work on the table) to make future anti-wraparound autovacuum runs fast.

user_64643080
Related