1

I am importing some data from a 10Gb file to a postgres database tables using java (jdbc). Import process taking more 12 hours to complete, so need to improve the importing process. I tried copy command for inserting. Some select commands are also running with the inserting tables. Can anyone suggests the way to improve the speed?

J. Steen
  • 15,470
  • 15
  • 56
  • 63
AKR
  • 359
  • 1
  • 5
  • 11

1 Answers1

4

Standard SQL INSERT statement typically has a too big overhead when millions of rows are needed. 10 GiB of data isn't really that much, but certainly too much for INSERT (you either have a huge transaction or commit/rollback every INSERT).

There is a nice 14.4. Populating a Database chapter in official documentation. 14.4.2. Use COPY is especially interesting for you:

Use COPY to load all the rows in one command, instead of using a series of INSERT commands. The COPY command is optimized for loading large numbers of rows; it is less flexible than INSERT, but incurs significantly less overhead for large data loads. Since COPY is a single command, there is no need to disable autocommit if you use this method to populate a table.

See also:

Community
  • 1
  • 1
Tomasz Nurkiewicz
  • 334,321
  • 69
  • 703
  • 674
  • I am dropping indexes before going to use copy command,is that going to affect the select command?? – AKR Nov 25 '12 at 17:16
  • @user1782877: according to *14.4.3. Remove Indexes* it will make import faster but `select` statement slower (at least for the time when you do an import). – Tomasz Nurkiewicz Nov 25 '12 at 17:26