What we have (software):
- PostrgeSQL 9.3 with base configuration (no changes in
- Windows 7 64 bit
- Intel Core i7-3770 3.9 Ghz
- 32 Gb RAM
- WDC WD10EZRX-00L4HBAta Drive (1000Gb, SATA III)
So, we have to load into DB aprox. 100.000.000 rows with bytea column, and more simple 500.000.000 rows (without LOBs). There are 2
varchar indexes on 1st table (with 13, 19 length) and 2
varchar indexes on 2nd table (18, 10 lengths).
There are also sequences for id generation for each table.
By now these operations are doing with 8 connections in parallel with 50 JDBC batch size. The picture below demonstrates system load: it is zero-load on
postgresql processes. After 24 hours of loading we have loaded only 10.000.000 rows which is very slow result.
We are asking for help in tuning
PostrgreSQL configuration in purposes of:
1) for ultra fast loading this amount of data, it is once-only operation, so it could be temporary configuration
2) for production mode for doing moderate number of SELECTs into these 2 tables by their indexes without join and without sorting.