0

What I know for fast loading bulk rows into a new table is:

  1. Create the table(without creating the index)
  2. Use "COPY" to load data from a file into the table
  3. Create the index

What if I added a primary key when creating the table, according to PostgreSQL: "Adding a primary key will automatically create a unique btree index on the column or group of columns used in the primary key.", will the rows be indexed during "COPY" since the table has an implicit index? if the answer is yes, the "COPY" will slow down?

1

will the rows be indexed during "COPY" since the table has an implicit index?

Yes.

if the answer is yes, the "COPY" will slow down?

Yes.

You can LOCK TABLE ... IN ACCESS EXCLUSIVE MODE then drop the PRIMARY KEY constraint for the duration if you need to.

PostgreSQL doesn't have a way to mark indexes as invalid then revalidate them for use without dropping and recreating them (yet).

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.