Adding a Primary Key to a Large PostgreSQL Table with High Traffic

Question

I need to add a primary key to a large PostgreSQL table (approximately 2TB) with high traffic. This is a critical operation, and I'm looking for guidance on how to do it efficiently.

I have already tried below steps:

-- Step 1: Add id identity column 
ALTER TABLE users
ADD COLUMN id BIGINT GENERATED ALWAYS as IDENTITY;
-- Step 2: Add unique index on (id, user_id) concurrently
CREATE UNIQUE INDEX CONCURRENTLY id_user_id_idx
   ON users (id, user_id);
-- verify that step 2 is completed
-- Step 3: Add primary key
ALTER TABLE users
   ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX id_user_id_idx;

I have facing two problems:

Table is locked completely on "Step 1" itself.

I know this is expected, but if there is any option to avoid that please suggest.
I get below error,

ERROR: could not extend file "base/16401/90996": No space left on device HINT: Check free disk space.

But I have around 600GB of storage left on my server.

As the table will be locked on "Step 1", and if there is no option to avoid that, I could take the downtime and add the id column first and then run the other two scripts.

I don't know whether that would solve the storage error.

Please provide any suggestions so that I can add the PK with the least downtime possible.

PostgreSQL v14.6

Erwin Brandstetter · Accepted Answer · 2023-09-30T00:30:38.320

Why?

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:

fiddle

Add a nullable column id with no default value, so it will be null initially.

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 a SEQUENCE manually:

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');

See:

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 timestamptz column users.inserted_at (ideally with an index on it!) something like this would work:

DO
$do$
DECLARE
   _ts timestamptz := (SELECT COALESCE(min(inserted_at), now()) FROM users);  -- must not be NULL
   _step interval  := '7 days';  -- adjust to your data !!!
BEGIN
   LOOP
      RAISE NOTICE 'Updating rows starting from %', _ts;  -- optional
  UPDATE users
  SET    id = nextval('users_id_seq')
  WHERE  inserted_at &gt;= _ts
  AND    inserted_at &lt;  _ts + _step
  AND    id IS NULL;  -- optional

  EXIT WHEN NOT FOUND AND _ts &gt;= 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;

END LOOP;
END
$do$;

Alternatively, loop in your client, and run VACUUM users; between iterations to make sure space is reused. (VACUUM cannot run inside a transaction.)

See:

Eventually, all old rows are updated.

Now create the unique index CONCURRENTLY, to avoid blocking inserts. Like your step 2, but only on (id):

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 NOT NULL.

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.

How to change a table ID from serial to identity?

I can add sample code later. The links should cover most. Out of time now. — Erwin Brandstetter, Sep 21 '23 at 05:49
I would appreciate if you add the code sample, the links and the answer is very helpful indeed. — sujeet, Sep 21 '23 at 06:05
@sujeet: Eventually, I added code and a fiddle. Notable changes: NOT NULL is added automatically with the PK constraint. And don't add user_id as 2nd key to the index / PK (makes no sense). — Erwin Brandstetter, Sep 22 '23 at 02:02
I tried your solution on a smaller table, 2GB+, and it worked. But, I also tried two other options: i. adding a uuid column and updating it with uuid not in batch but the whole table ii. adding a serial bigint column and updating it with default value but not in batch. Both the approaches work, and they don't lock the tables, queries slows down though. Will there be any other performance impact or it will cause the storage issue again? — sujeet, Sep 22 '23 at 13:30
Will this ALTER TABLE users ADD COLUMN id BIGINT GENERATED ALWAYS as IDENTITY; rewrite the entire table and block it? Why did I get the storage error? I am using Postgresl 14.6 — sujeet, Sep 22 '23 at 15:01
@sujeet: Updating in batches is typically considerably slower. The point is to reuse dead tuples to keep table bloat in check. The core item of your question! - which you seem to have abandoned now?) In other use cases with concurrent write access on the same set of rows, it also helps to keep locking contention in check. If all you do is insert, and storage is not an issue, then, you may just update all old rows at once. You might want to run (blocking!) CLUSTER of VACUUM FULL in a maintenance window to shrink the physical table size back to half the size .. — Erwin Brandstetter, Sep 22 '23 at 22:05
@sujeet: Yes, ALTER TABLE users ADD COLUMN id BIGINT GENERATED ALWAYS as IDENTITY; acquires an `ACCESS EXCLUSIVE lock and rewrites the whole table. — Erwin Brandstetter, Sep 22 '23 at 22:18
Why did you make AND id IS NULL; -- optional optional? If you don't put this it will dually update the rows which are newly inserted. — sujeet, Oct 03 '23 at 01:18
@sujeet: Assuming we target non-overlapping time slices and new rows are only inserted with a later timestamp, every row gets updated once. If we cannot assume that, add the predicate. (Hence "optional".) Might be a good backup safety anyway. — Erwin Brandstetter, Oct 03 '23 at 02:44

Adding a Primary Key to a Large PostgreSQL Table with High Traffic

1 Answers1

Why?

Minimize blocking AND minimize total storage requirement