Put Postgres import and sql commands into a single script about discogs-xml2db HOT 3 OPEN

I have some concerns as to how having the indexes and keys present in the database before the import affects the speed of the import.

In most databases it tends to be slower, even in bulk load, because:

• every row has to be PK-checked, FK-checked, before insert; failure of a FK check would result in the entire import failing;
• having the FKs in place means that the files need to be loaded in a precise order, with the files providing the FK first, and the files using the FK last;
• after every row or row-threshold insert the indexes may have to be rebuilt, slowing the whole process down (rebuilding at the end is a single table scan)

By contrast, the current solution leaves a DB full of data at the end of the csv import; optimization (PK, FK, indexes) is then left at the latitude of the users.

All that aside, could you try the following two scenarios and see if the outcome is acceptable?

In a script:

Scenario A

1. Run CreateTables.sql
2. Import csv
3. Run the rest of the sql scripts

Scenario B

1. Run all sql scripts
2. Import csv

Run each script of the scripts above with the time command and if the results from scenario B are positive and faster than A, I'd be happy to start looking into it.

from discogs-xml2db.

Hi, if you look at my scripts you'll see indexes etc are created AFTER the import. Im just suggesting the user should be able to run one command that creates db tables, import and then creates indexes, keys and constraints, rather than having to run 5 different commands.

from discogs-xml2db.

--- is then left at the latitude of the users.
There maybe cases where they want to change this, but for most I cannot see why they would not want to run add keys and indexes after the db import, otherwise the db will be almost unusable.

from discogs-xml2db.