How to Reorder Primary Key Constraint on a Large TimescaleDB Table Without Downtime?

Question

I am trying to reorder the primary key constraint on sampleTable to improve query performance.

Here are the details:

Database: PostgreSQL (12.18), TimescaleDB (1.7.5) Table Name: sampleTable

DDL:

CREATE TABLE sampleTable (
    ts timestamp NOT NULL,
    col1 int8 NOT NULL,
    col2 varchar(30) NOT NULL,
    col3 int8 NOT NULL,
    CONSTRAINT idx_sampleTable_pk PRIMARY KEY (ts, col1, col2, col3)
);

The sampleTable contains a large amount of data (approximately 500GB). I attempted to reorder the primary key constraint with the following queries:

-- Drop the old primary key constraint
ALTER TABLE sampleTable
DROP CONSTRAINT idx_sampleTable_pk;

-- Add the new primary key constraint with the updated column order
ALTER TABLE sampleTable
ADD CONSTRAINT idx_sampleTable_pk PRIMARY KEY (col1, col2, col3, ts);

However, creating the new constraint takes approximately 4 hours, during which time the database is inaccessible, resulting in complete downtime.

To avoid this downtime, I considered an alternative approach suggested in TimescaleDB documentation, known as CREATE INDEX (Transaction Per Chunk). I plan to create the temporary index in chunks and, once the index is built, drop the existing constraint and create the new one using the new index. The steps are as follows:

-- Create the temporary index chunkwise
CREATE INDEX sampleTable_temp_idx ON sampleTable (col1, col2, col3, ts)
WITH (timescaledb.transaction_per_chunk);

-- Drop the existing primary key constraint
ALTER TABLE sampleTable
DROP CONSTRAINT idx_sampleTable_pk;

-- Add the new primary key constraint using the temporary index
ALTER TABLE sampleTable
ADD CONSTRAINT idx_sampleTable_pk
PRIMARY KEY USING INDEX sampleTable_temp_idx;

However, I encounter the following error when creating the new constraint with the temporary index:

SQL Error [42809]: ERROR: "sampleTable_temp_idx" is not a unique index
Detail: Cannot create a primary key or unique constraint using such an index.

To address this error, I updated the index creation query to:

CREATE UNIQUE INDEX sampleTable_temp_idx ON sampleTable (col1, col2, col3, ts)
WITH (timescaledb.transaction_per_chunk);

But now I get the following error:

SQL Error [0A000]: ERROR: cannot use timescaledb.transaction_per_chunk with unique or primary key

How can I resolve this issue and avoid downtime? I have also tried using CONCURRENTLY for index creation, but it is not supported on hypertables.

Answer 1

Want to improve performance?

Database: PostgreSQL (12.18), TimescaleDB (1.7.5) Table Name: sampleTable

Please, update your postgresql to 16 and TimescaleDB latest versions! You'll get a lot of improvements in performance.

Remember that reordering will just update all lines which will create new lines for almost every update. In the Postgresql internals old lines just get invalidated and mark as unused. It will give a heavy work for Postgresql to be vacuuming it and give space for new data come in.

You may choose to just start a new hypertable, and have an intermediate code that hides the complexity, joining the data from both sides and then after migrated, you remove the old table and rename the new one to become the official.