5

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?

5

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

  1. 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)
  2. The index supports a constraint, and you want the constraint enforced during the loading process rather than at the end.
  3. You don't care much about the performance difference, and creating the index upfront is just syntactically easier to do.
  • 2
    In addition to being a lot faster, creating the index afterwards also produces a more compact, more efficient index. Often as much as half the size, because it's created as a balanced b-tree from the start, with no messy page splits etc. You can always REINDEX after a bulk load, but then you're paying all the I/O cost to create an index you then throw away and replace. (I wish PostgreSQL supported "disabling" an index so you could create it but then not have it usable until it's enabled/reindexed) – Craig Ringer May 30 '14 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.