0

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).

1 Answer 1

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.

4
  • 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. Jul 14, 2012 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.
    – user1822
    Jul 14, 2012 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. Jul 14, 2012 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.
    – user1822
    Jul 14, 2012 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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