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


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

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.

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:



SELECT * FROM pgstattuple('tbl');


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
FROM   old_tbl

Add the bigserial column:

ALTER new_tbl ADD column bar bigserial;

INSERT data from old table, automatically filling the bigserial:

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.

|improve this answer|||||
  • 3
    Wrap the alternative in a single transaction, that will be much faster. It will avoid additional fsyncs – Frank Heikens Jul 13 '12 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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