PostgreSQL performance of INSERT INTO table SELECT vs. COPY

Question

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.

"requires both reads and writes from the same disk" It is possible that doing that would interfere with each other and cause performance problems, but it really depends on how clever your kernel and filesystem are. That is not something that PostgreSQL can address. You will have to try it on your system and see. — jjanes
– jjanes, Commented Oct 9, 2019 at 17:18

Laurenz Albe · Accepted Answer · 2019-10-09 15:16:19Z

1

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.

answered Oct 9, 2019 at 15:16

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Gaynor Over a year ago

One consideration that occurs to me -- does INSERT ... SELECT require buffering the entire results of the SELECT query in memory, or does it stream the results?

Laurenz Albe Over a year ago

It should stream, unless you have things like ORDER BY or DISTINCT in the query.

Collectives™ on Stack Overflow

PostgreSQL performance of INSERT INTO table SELECT vs. COPY

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related