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.

What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)?

EDIT:

  • I want the column to be incremented values for existing rows (NOT NULL).
  • I didn't set a fillfactor (which looks like a bad decision in retrospect).
  • I don't have a problem with disk space, just want it to be as fast as possible.
share|improve this question
1  
More details would help. Do you want existing rows filled with values or are you happy with NULL values? In what order? What's the current state of the table? Lots of reads / writes? Did you set a FILLFACTOR? Dead tuples? Free space? Can you afford to create a new table? I added some pointers to my answer ... –  Erwin Brandstetter Jul 12 '12 at 18:10
add comment

2 Answers 2

up vote 6 down vote accepted

What's wrong with:

ALTER TABLE foo ADD column bar bigserial;

Will be filled with unique values automatically (starting with 1).

If you want a number for every existing row, every row in the table has to be updated. Or do you not?

The table will be bloated to twice it's size if it cannot reuse dead tuples or free space on the data pages. Performance of the operation might benefit a lot from a FILLFACTOR lower than 100 or just random dead tuples spread out over the table. Else you may want to run VACUUM FULL ANALYZE afterwards to recover disk space. This won't be quick, though.

pgstattuple
You may be interested in this extension. It helps you gather statistics on your tables. To find out about dead tuples and free space:

Install extension once per databae:

CREATE EXTENSION pgstattuple;

Call:

SELECT * FROM pgstattuple('tbl');

Alternative

If you can afford to create a new table, which would break depending views, foreign keys, ...

Create an empty copy of the old table:

CREATE new_tbl AS
SELECT *
FROM   old_tbl
LIMIT  0;

Add the bigserial column:

ALTER new_tbl ADD column bar bigserial;

INSERT data from old table, automatically filling the bigserial:

INSERT INTO new_tbl
SELECT *    --  new column will be filled with default
FROM   old_tbl
ORDER  BY something; -- or don't order if you don't care: faster

The new bigserial column is missing in the SELECT of the INSERT and will be filled with its default value automatically. You can spell out all columns and add nextval() to the SELECT list to the same effect.

Make sure you got all your data in the new table.
Add indexes, constraints, triggers you had in the old table now.

DROP TABLE old_tbl;
ALTER TABLE new_tbl RENAME TO old_tbl;

Might be quite a bit faster overall. This leaves you with a vanilla table (and indexes) without any bloat.

You need free disk space - around the size of the old table, depending on the state of the table - as wiggle room. But you may need just as much with the first simple method because of table bloat. Again, the details depend on the state of your table.

share|improve this answer
    
In my computer that query took 2 hours to finish. I was asking if there's any faster way. –  Thi Duong Nguyen Jul 12 '12 at 17:54
    
@Thi: I added an alternative, assuming you can go that route ... –  Erwin Brandstetter Jul 12 '12 at 18:22
2  
Wrap the alternative in a single transaction, that will be much faster. –  Frank Heikens Jul 13 '12 at 4:33
    
@FrankHeikens Why exactly? –  dezso Jul 13 '12 at 7:12
    
To avoid additional fsync's –  Frank Heikens Jul 13 '12 at 7:24
show 1 more comment

I think that the biggest point is that one doesn't actually want such query to be fastest possible, but least-problematic - i.e. not locking other queries.

Solution for such thing is pretty simple:

alter table foo add column bar int8;
create sequence foo_bar_seq owned by foo.bar;
alter table foo alter column bar set default nextval('foo_bar_seq');

This will make all newly added rows to contain value in bar. And this is done with almost no locking.

Now - we need to fill values in old rows. You need to find a way to "partition" the data - for example, let's assume you have unique index on some integer field - just get min/max, and generate list of ranges of ~ 1000 rows.

And then write a script that will runs thousands of:

update foo set bar = nextval('foo_bar_seq') where range_id >= some_min and range_id < some_max;

of course changing some_min and some_max.

The critical thing is that all those updates should not be in a transaction.

Each update hold lock for just a 1000 of rows, and for a very short time.

After all updates will finish you're done.

The point is that doing any kind of magic, on such a table will take generally long time. And instead of making it shorter by 10%, why not just make it longer by 50%, but instead make it so that it will not interfere with concurrent traffic.

share|improve this answer
1  
It all depends on the requirements. This solution has an inherent flaw: if concurrent writes are possible, and you run multiple transactions, range_id may change mid-process and interfere with your regime. The reason the OP wants to add a serial column may well be the absence of a stable identifier. Be sure to pick a stable criteria or check for inconsistencies - or do it all in one transaction. –  Erwin Brandstetter Jul 13 '12 at 14:30
    
"Stable"? Update will not guarantee any order. –  user1593 Jul 13 '12 at 18:04
    
The problem is I don't have any unique index on the table yet.. that's why I want to create one by adding the serial column. –  Thi Duong Nguyen Jul 13 '12 at 18:06
    
@depesz: By "stable" I mean a column that won't be changed by concurrent writes between the slices of the UPDATE. –  Erwin Brandstetter Jul 13 '12 at 18:29
    
@Erwin: I find it hard to see how's that relevant. I mean - sure, concurrent update can set bar to some other value. And? –  user1593 Jul 13 '12 at 23:58
show 4 more comments

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.