9

I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row.

Now I want to "compact" the table by restarting the sequence and reassigning the ids in such a way that the original id order is preserved. Is it possible?

Example:

  • Now:

 id | data  
----+-------
  1 | hello
  2 | world
  4 | foo
  5 | bar
  • After:

 id | data  
----+-------
  1 | hello
  2 | world
  3 | foo
  4 | bar

I tried what was suggested in a StackOverflow answer, but it didn't work:

# alter sequence t_id_seq restart;
ALTER SEQUENCE
# update t set id=default;
ERROR:  duplicate key value violates unique constraint t_pkey
DETAIL:  Key (id)=(1) already exists.
  • Restarting the sequence does not affect existing rows. If you want to close the gaps, you need to renumber the rows. – Lennart Jun 28 at 15:27
9

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer:

Here are some recipes to remove gaps.

1. New, pristine table

Avoids complications with unique violations and table bloat and is fast. Only for simple cases where you are not bound by FK references, views on the table or other depending objects, or by concurrent access. Do it in one transaction to avoid accidents:

BEGIN;
LOCK tbl; -- optionally: IN SHARE MODE to allow concurrent reads

CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL);

INSERT INTO tbl_new -- no target list in this case
SELECT row_number() OVER (ORDER BY id), data  -- all columns in default order
FROM   tbl;

ALTER SEQUENCE tbl_id_seq OWNED BY tbl_new.id;  -- make new table own sequence

DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL) copies the structure incl. constraints and defaults from the original table. Then make the new table column own the sequence:

And reset it to the new maximum:

This carries the advantage that the new table is bloat-free and clustered on id.

2. UPDATE in place

This produces a lot of dead rows and requires (auto-) VACUUM later.

If the serial column is also the PRIMARY KEY (like in your case) or has a UNIQUE constraint, you must avoid unique violations in the process. The (cheaper) default for PK / UNIQUE constraints is to be NOT DEFERRABLE, which forces a check after every single single row. All details under this related question on SO:

You could define your constraint as DEFERRABLE (which makes it more expensive).
Or you can drop the constraint and add it back when you are done:

BEGIN;

LOCK tbl;

ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;  -- remove PK

UPDATE tbl t  -- intermediate unique violations are ignored now
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY(id); -- add PK back

COMMIT;

Neither is possible while you have FOREIGN KEY constraints referencing the column(s) because (per documentation):

The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.

You would need to (lock all involved tables and) drop / recreate FK constraints and update all FK values manually (see option 3.). Or you have to move values out of the way with a second UPDATE to avoid conflicts. For instance, assuming you have no negative numbers:

BEGIN;
LOCK tbl;

UPDATE tbl SET id = id * -1;  -- avoid conflicts

UPDATE tbl t
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id DESC) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

Drawbacks as mentioned above.

3. Temp table, TRUNCATE, INSERT

One more option if you have plenty of RAM. This combines some of the advantages of the first two ways. Almost as fast as option 1. and you get a pristine, new table without bloat but keep all constraints and dependencies in place like in option 2.
However, per documentation:

TRUNCATE cannot be used on a table that has foreign-key references from other tables, unless all such tables are also truncated in the same command. Checking validity in such cases would require table scans, and the whole point is not to do one.

Bold emphasis mine.

You could drop FK constraints temporarily and use data-modifying CTEs to update all FK columns:

SET temp_buffers = 500MB;   -- example value, see 1st link below

BEGIN;

CREATE TEMP TABLE tbl_tmp AS
SELECT row_number() OVER (ORDER BY id) AS new_id, *
FROM   tbl
ORDER  BY id;  -- order here to use index (if one exists)

-- drop FK constraints in other tables referencing this one
-- which takes out an exclusive lock on those tables

TRUNCATE tbl;

INSERT INTO tbl
SELECT new_id, data  -- list all columns in order
FROM tbl_tmp;        -- rely on established order in tbl_tmp
-- ORDER BY id;      -- only to be absolutely sure (not necessary)

--  example for table "fk_tbl" with FK column "fk_id"
UPDATE fk_tbl f
SET    fk_id = t.new_id  -- set to new ID
FROM   tbl_tmp t
WHERE  f.fk_id = t.id;   -- match on old ID

-- add FK constraints in other tables back

COMMIT;

Related, with more details:

| improve this answer | |
  • If all FOREIGN KEYS are set to CASCADE couldn't you simply loop over the old primary keys and update their values in-place (from the old value to the new one)? Essentially this is option 3 without TRUNCATE tbl, replacing INSERT with an UPDATE, and without needing to update foreign keys manually. – Gili Apr 14 '17 at 6:15
  • @Gili: You could, but that kind of loop is extremely expensive. Since you cannot update the whole table at once due to unique key violations in the index, you need a separate UPDATE command for every row. See explanation in ② or try and see for yourself. – Erwin Brandstetter Apr 14 '17 at 12:41
  • I don't think that performance is an issue in my case. The way I see it, there are two kinds of algorithms: ones that "stop the world" and ones that runs quietly in the background without having to take down the server. Assuming that compaction only occurs once in a blue moon (e.g. when approaching the upper limit of a data type), there isn't really an upper-limit on the amount of time it should take. So long as we are compacting records faster than new ones are being added, we should be fine. – Gili Apr 15 '17 at 3:24

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.