0

I am currently importing Wikipedia pagelinks in my postgres database, and it takes forever (three days and still running). Is it possible to drop the primary key index while the inserts are still running, and would this speed things up?

Actually, if this changes anything, it is a uniqueness constraint over three columns that creates the index.

  • It's best to tag [postgres] as well. Many only subscribe to the main tag. – Erwin Brandstetter Feb 20 '15 at 1:26
2

Yes, a plain INSERT is fastest when no indexes have to be maintained. So it may pay to drop indexes before huge bulk inserts and add them later. Depends on the whole situation, of course.

No, you cannot drop a PK or a UNIQUE constraint or even a plain index in the middle of an INSERT, which also writes to the index.

No, I don't think your query should take more than three days, even if you import every link that every Wikipedia site on earth ever had to offer. There is probably something less than ideal in your command or setup or hardware or how you read / write the data.
For starters, an operation of that magnitude would rather use COPY:

  • I deleted the constraint, and the insert sped up a lot. Since these are lots of single inserts, I think, the drop did work. Now, you say I can't drop it in the middle of a single INSERT, what about a transaction with many inserts, is the index also part of the transaction environment? – kutschkem Feb 20 '15 at 9:58
  • @kutschkem: The index is "part of the transaction", but locks don't block statements inside the same transaction. So it wouldn't be a problem to DROP (or CREATE) the index between statements inside a single transaction. Concerning single inserts: dba.stackexchange.com/questions/63055/… – Erwin Brandstetter Feb 20 '15 at 17:25

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.