Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm planning to import and update a huge table (3 Bil. rows) in the following step:

  1. create table with fill factor x
  2. import the data from csv files (6 files, ~21GB each)
  3. add a BIGSERIAL column
  4. create index for the BIGSERIAL column
  5. cluster the table by the BIGSERIAL column?
  6. update one column in the table in batches specified by a range of the BIGSERIAL column

It's a huge operation for me, so I really need your advice on the fill factor x I need to set in step 1, so that it'll have the best impact on the subsequent steps. All columns in the table definition are fixed size. Step 6 is supposed to be most CPU-intensive, since it involves spatial queries (postGIS).

share|improve this question

1 Answer 1

Assuming you are using COPY to populate the table I don't see a reason to create the table without the bigserial column.

Just create the table including the bigserial, but don't specify it in the COPY command. It will then automatically be filled with values during the import.

create table foobar (id bigserial, col1 text, col2 text);
copy foobar (col1, col2) 
   from 'foobar.txt'
   with (format csv, header false, delimiter ',');

That would remove steps 3,4 and 5.

I'm not sure what you mean with "range of bigserial" in step 6.

share|improve this answer
    
I tried this, but COPY FROM into a table with id is very slow, and seems like I would not be able to run 6 COPY FROMs concurrently. I heard that normally it's faster to COPY first and create index later, hence my plan. –  Thi Duong Nguyen Jul 14 '12 at 11:44
    
You don't need to create the index, just create the serial column. I doubt that importing into a table with a serial is slower than populating the serial afterwards. –  a_horse_with_no_name Jul 14 '12 at 12:13
    
sorry if this sounds dumb, but does having a serial column means I don't need to create an index for it? from what i read apparently i still need to create an index, so that i can have queries which use "WHERE id > 0 and id < 10" to use index scan and not sequential scan. –  Thi Duong Nguyen Jul 14 '12 at 14:24
    
No. A serial column does not imply an index. A primary key column will automatically create an index. Just create the index after the bulk load. –  a_horse_with_no_name Jul 14 '12 at 14:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.