PostgreSQL: How does the implicit index work when using “COPY” for loading bulk rows into a newly created table

constraintcopyindexpostgresql

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?

Best Answer

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).

Related Question