Hello,
Postgres: 8.2
os: Linux 4CPU, 4 GB RAM, Raid 1, 32 bit system
work_mem: 600 Mb
I have some tables which may become quite large (currently up to 6 Gb) .
I initially fill them using copy from (files) .
The import is fast enough as I only have a primary key on the table: about 18 minutes
(over 300 Mb/minute)
Then I need 5 additional indexes on it. Creation time: 30 minutes
subsequently I compute some aggregations which need 4 hours and 30 minutes additional time
And now the problem:
If I get additional data for the table, the import become much more slower due to the indexes (about 30 times slower !):
The performance degradation is probably due to the fact that all indexs are too large to be kept in memory.
Moreover I guess that the indexes fill factors are too high (90%)
During this second import, I have about 20% iowait time.
The usual solution is to drop the indexes before the second import and rebuild them afterwards, but I feel unconfident doing this as I don't know how the system will react if some SELECT statements occures when the index are missing. I can hardly avoid this.
So my idea for the second import process:
1) make a copy of the table:
create table B as select * from table A;
alter table B add constraint B_pk primary key (id);
2) import the new data in table B
copy B from file;
3) create the required indexes on B
create index Bix_1 on B..
create index Bix_2 on B..
create index Bix_2 on B..
create index Bix_2 on B..
4) replace table A with table B
alter table A renam to A_trash;
alter table B renam to A;
drop table A_trash;
(and rename the indexes to get the original state)
This seems to work but with side effects:
The only objects that refer to the tables are functions and indexes.
If a function is called within a same session before and after the table renaming, the second attempt fails (or use the table A_trash if it still exists). So I should close the session and start a new one before further processing. Errors in other live sessions are acceptable, but maybe you know a way to avoid them?)
And now a few questions :-)
- do you see any issue that prevent this workflow to work?
- is there any other side effect to take care of ?
- what is the maximum acceptable value for the parameter work_mem for my configuration
(see the complete configuration below)
- has anybody built a similar workflow ?
- could this be a feature request to extend the capabilities of copy from ?
Thanks for your time and attention,
Marc Mamin