In a recent thread of mine I learned something very interesting. If a table is
created and data is loaded via COPY FROM within the same transaction, then PG
will be smart enough to not generate WAL logs because all it needs to do is to
track the status of the transaction and let the data load go to the new data
file created for the table. If committed, the table is released for other
sessions, if rolledback, vaccum will delete the data file later on.
I tested it as follows for a table with 50 milllion rows. No indexes.
Case 1 - create the table first. - in a separate transaction load the 50
million rows.
Took 3 min 22 seconds
Case 2 - start transaction - create table - load 50 million rows - commit
transaction
Took: 3 min 16 seconds.
Am I missing anything?
