Index creation before or after loading data

I have a situation where I need to create a table in Postgres and add approximately 3 million rows. Currently I am adding an index to one field after all the data is loaded, but I was wondering whether adding an index before loading the data (at the time the table is created) is better, worse, or neutral.

What is considered best practice for when indexes should be added to tables in Postgres? Does Postgres pause indexing during transactions?

Adding the index after the table is populated will be faster, perhaps hugely faster. The only reasons to have the index first are:

• Other transactions must not be allowed to see the table without the index (but in this case, try to create the table, populate the table, and create the index all in one transaction)

• The index supports a constraint, and you want the constraint enforced during the loading process rather than at the end.

• You don't care much about the performance difference, and creating the index upfront is just syntactically easier to do.

StackExchange Database Administrators Q#66182, answer score: 7