How to safely reindex primary key on postgres?

Question

We have a huge table that contains bloat on the primary key index. We constantly archive old records on that table.

We reindex other columns by recreating the index concurrently and dropping the old one. This is to avoid interfering with production traffic. But this is not possible for a primary key since there are foreign keys depending on it. At least based on what we have tried.

What's the right way to reindex the primary key safely without blocking DML statements on the table?

Answer 1

REINDEX CONCURRENTLY seems to work as well. I tried it on my database and didn't get any error.

REINDEX INDEX CONCURRENTLY <indexname>;

I think it possibly does something similar to what @jlandercy has described in his answer. While the reindex was running I saw an index with suffix _ccnew and the existing one was intact as well. Eventually I guess that index was renamed as the original index after dropping the older one and I eventually see a unique primary index on my table.

I am using postgres v12.7.

Answer 2

You can use pg_repack for this.

pg_repack is a PostgreSQL extension which lets you remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes.

It doesn't hold exclusive locks during the whole process. It still does execute some locks, but this should be for a short period of time only. You can check the details here: https://reorg.github.io/pg_repack/

To perform repack on indexes, you can try:

pg_repack -t table_name --only-indexes

Answer 3

TL;DR

Just reindex it as other index using its index name:

REINDEX INDEX <indexname>;

MCVE

Let's create a table with a Primary Key constraint which is also an Index:

CREATE TABLE test(
    Id BIGSERIAL PRIMARY KEY
);

Looking at the catalogue we see the constraint name:

SELECT conname FROM pg_constraint WHERE conname LIKE 'test%';
-- "test_pkey"

Having the name of the index, we can reindex it:

REINDEX INDEX test_pkey;

You can also fix the Constraint Name at the creation:

CREATE TABLE test(
    Id BIGSERIAL NOT NULL
);
ALTER TABLE test ADD CONSTRAINT myconstraint PRIMARY KEY(Id);

If you must address concurrence, then use the method a_horse_with_no_name suggested, create a unique index concurrently:

-- Ensure Uniqueness while recreating the Primary Key:
CREATE UNIQUE INDEX CONCURRENTLY tempindex ON test USING btree(Id);
-- Drop PK:
ALTER TABLE test DROP CONSTRAINT myconstraint;
-- Recreate PK:
ALTER TABLE test ADD CONSTRAINT myconstraint PRIMARY KEY(Id);
-- Drop redundant Index:
DROP INDEX tempindex;

To check Index existence:

SELECT * FROM pg_index WHERE indexrelid::regclass = 'tempindex'::regclass