-1

Recently I have received a data dump in csv format that I am trying to import in my PSQL database. The dump consists of 8 csv file each of size 7GB.

I have found that using the Copy command on one file is incredibly slow if the indexes are defined on the table. I have 2 indexes, one on 3 fields and one on 2. Whereas copy on table with no index takes roughly 2 minutes. I was wondering, what is the best practice to copy and index big data files from CSV, for now, copying then reindexing seems like my best options. Is there any best practice for that. Why is copy incredibly slow when indexing data?

1

It is a common technique is remove the indexes, load the data and then add the indexes back. For more information, please refer to the Populating a Database section of the Postgres documentation.

Let me explain how it works. When loading the data with indexes on, for every row insert, postgres tries to update each of the index.

for each row in csv file:
    insert the row -- read/write the disk blocks containing the table rows
    for each index:
        update the index -- read/write the disk blocks for that index

The indexes are updated multiple times, once for every row. That is lot of disk IO.

When you remove the indexes, load and add indexes, the execution pattern becomes:

for each index:
    remove the index

for each row in csv file:
    insert the row -- read/write the disk blocks containing the table rows

for each index:
    update the index -- read/write the disk blocks for that index

If you notice, each index is updated just once instead of once for every row insert, resulting a much less disk IO and faster load time.

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.