PostgreSQL - CREATE TABLE AS vs INSERT INTO performance comparision

Question

I'm trying to insert couple of million rows into a PostgreSQL database. I am wondering what is the best way to do it.

CREATE TABLE AS
INSERT INTO

I'm looking to see which one is better and why? I have read through some blogs but still couldn't come to a conclusion.

I think INSERT INTO is a bulk insert operation. Please correct me if I'm wrong. Whether CREATE TABLE AS SELECT is a bulk insert operation?

Please advise.

You've not provided near enough information to be able to answer this question. There is not a single truth here, it depends greatly on what you are actually doing. — Nick
– Nick, Commented Jul 20, 2017 at 17:38
Quote from the manual for CREATE TABLE AS: "This command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers a superset of the functionality offered by SELECT INTO" — user330315
– user330315, Commented Jul 20, 2017 at 18:35

Florian Weimer · Accepted Answer · 2017-07-20 17:43:24Z

5

CREATE TABLE AS is a bulk insert operation as well. The main difference is that CREATE TABLE AS is easier to optimize for PostgreSQL; it is clear that no WAL information has to be written (unless WAL-based replication is active, of course). See the wal_level documentation and Disable WAL Archival and Streaming Replication for some other cases where this optimization applies.

answered Jul 20, 2017 at 17:43

Florian Weimer

34.1k4 gold badges59 silver badges103 bronze badges

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

3 Comments

RMu Over a year ago

Thank you for your response. When you say CREATE TABLE AS is easier to optimize for PostgreSQL, and no WAL information has to be written, INSERT INTO has to log everything to WAL? Have I understood right?

Florian Weimer Over a year ago

Correct. INSERT INTO has to log everything. I just checked PostgreSQL 9.4, and the optimization does not happen there for INSERT INTO, even if the table is created within the same transaction.

RMu Over a year ago

exactly what I needed to know. Thank you Florian

Collectives™ on Stack Overflow

PostgreSQL - CREATE TABLE AS vs INSERT INTO performance comparision

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related