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.

1. CREATE TABLE AS
2. 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.

Answer 1

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.