Your step 1 would need a lot more than 600 GB (temporarily). The table has around 2 TB. About as much (minus possible bloat, plus 8 bytes per row for the new
bigint column) has to be available at least, because that change forces Postgres to rewrite the whole table.
Minimize blocking AND minimize total storage requirement
Do instead, in this order:
Add a nullable column
id with no default value, so it will be
ALTER TABLE users ADD COLUMN id bigint;
This way, Postgres can make do with tiny metadata changes. No table rewrite, no blocking.
I would name the PK column "user_id", not a fan of the wide-spread, non-descriptive, and highly duplicative name "id". But keeping "id" to stay in line with the question.
CREATE SEQUENCE users_id_seq;
Make the column "own" the sequence:
ALTER SEQUENCE users_id_seq OWNED BY users.id;
Add the column default, which only kicks in for new rows.
ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq');
Update pre-existing rows (still with
null values) in batches of like 1 % of the total size (or whatever). In separate transactions, to allow autovacuum to kick in and mark dead rows for reuse. This way, the table won't grow much, and 600 GB are easily enough wiggle room.
Since the addition of SQL procedures in Postgres 11, we can
COMMIT in an anonymous code block. Assuming a
users.inserted_at (ideally with an index on it!) something like this would work:
_ts timestamptz := (SELECT COALESCE(min(inserted_at), now()) FROM users); -- must not be NULL
_step interval := '7 days'; -- adjust to your data !!!
RAISE NOTICE 'Updating rows starting from %', _ts; -- optional
SET id = nextval('users_id_seq')
WHERE inserted_at >= _ts
AND inserted_at < _ts + _step
AND id IS NULL; -- optional
EXIT WHEN NOT FOUND AND _ts >= now(); -- adjust to your case !!!
COMMIT; -- Requires Postgres 11+ !!!
PERFORM pg_sleep(10); -- adapt to your setup: long enough so let autovacuum kick in
_ts := _ts + _step;
Alternatively, loop in your client, and run
VACUUM users; between iterations to make sure space is reused. (
VACUUM cannot run inside a transaction.)
Eventually, all old rows are updated.
Now create the unique index
CONCURRENTLY, to avoid blocking inserts. Like your step 2, but only on
CREATE UNIQUE INDEX CONCURRENTLY users_id_idx ON users (id);
I don't see a good reason to add
user_id to the PK. If you need it for index-only scans consider a covering index with
INCLUDE (user_id). But that's not always beneficial overall. See:
Now use the unique index to add the new PK without blocking inserts (your step 3):
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_id_idx;
This will also implicitly set the column
Finally, use Peter Eisentraut's function
upgrade_serial_to_identity(tbl regclass, col name) to convert the
serial to an
IDENTITY column. As superuser:
SELECT upgrade_serial_to_identity('users'::regclass, 'id')
Or stick with the
serial PK, might be good enough.