Postgres is taking too long to attach partition to a table. Want to understand why

Question

I have a table T1(non-partitioned). Size of T1 is approx 4TB. I have created another table T2(partitioned table). Now I want to attach T1 as child table of T2. So I am running below query to achieve the same.

ALTER TABLE T1
  ADD CONSTRAINT partition_check_skip 
  CHECK ( "created_at" BETWEEN ( '-infinity' ) 
  AND ( DATE_TRUNC('week', CURRENT_DATE::timestamp) + '7 days'::interval )) NOT VALID;

ALTER TABLE public.T2
  ATTACH PARTITION T1 FOR VALUES FROM 
  ('-infinity') TO (DATE_TRUNC('week', CURRENT_DATE::timestamp) + '7 days'::interval);

Even though I have mentioned NOT VALID, still Postgres is taking too long to attach T1 as partition of T2.

Schema of T1

CREATE TABLE T1
(
  uuid         uuid DEFAULT gen_random_uuid() NOT NULL,
  created_at   timestamp WITHOUT TIME ZONE    NOT NULL,
  updated_at   timestamp WITHOUT TIME ZONE    NOT NULL
);

Schema of T2

CREATE TABLE T2
(
  uuid         uuid DEFAULT gen_random_uuid() NOT NULL,
  created_at   timestamp WITHOUT TIME ZONE    NOT NULL,
  updated_at   timestamp WITHOUT TIME ZONE    NOT NULL
) PARTITION BY RANGE (created_at);

Tried adding NOT VALID but still it is taking too long.

Here are the actual SQL statements:

Non Partitioned Table

CREATE TABLE public.random_txn_old (
    id int8 NOT NULL DEFAULT id_generator(),
    ref_id text NULL,
    txn_ref_id text NULL,
    msg_id text NULL,
    api text NULL,
    req_payload jsonb NULL,
    res_payload jsonb NULL,
    bi_req jsonb NULL,
    bi_res jsonb NULL,
    status text NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp_utc(),
    modified_at timestamp NOT NULL DEFAULT current_timestamp_utc(),
    is_deleted bool NULL DEFAULT false,
    CONSTRAINT t2_check CHECK (((created_at >= '2021-02-23 00:00:00') AND (created_at <= '2023-02-24 00:00:00'))),
    CONSTRAINT transaction_old_pkey PRIMARY KEY (id, created_at)
);
CREATE INDEX random_ref_id ON public.random_txn_old USING btree (ref_id);
CREATE INDEX ran_ref_api_idx ON public.random_txn_old USING btree (txn_ref_id, api);

Table Triggers:

create trigger set_timestamp_txn before
update
    on
    public.random_txn_old for each row execute function trigger_set_timestamp_modify();

Partitioned Table:

CREATE TABLE public.random_table (
    id int8 NOT NULL DEFAULT id_generator(),
    ref_id text NULL,
    txn_ref_id text NULL,
    msg_id text NULL,
    api text NULL,
    req_payload jsonb NULL,
    res_payload jsonb NULL,
    bi_req jsonb NULL,
    bi_res jsonb NULL,
    status text NULL,
    created_at timestamp NOT NULL DEFAULT current_timestamp_utc(),
    modified_at timestamp NOT NULL DEFAULT current_timestamp_utc(),
    is_deleted bool NULL DEFAULT false,
    CONSTRAINT transaction_part_pkey PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (created_at);
CREATE INDEX random_part_ref_id ON ONLY public.random_table USING btree (ref_id);
CREATE INDEX ran_part_ref_api_idx ON ONLY public.random_table USING btree (txn_ref_id, api);

What I trying to do?

Trying to attach random_txn_old to random_table using this query:

ALTER TABLE random_table
     ATTACH PARTITION random_txn_old FOR VALUES FROM ('2021-02-23 00:00:00.000')
        TO ('2023-02-24 00:00:00.000');

Laurenz Albe · Accepted Answer · 2023-02-22T07:23:03.503

There are a number of conditions that T1 must fulfill for this to work quickly:

the table must have indexes that can be used for partitions of all indexes on the partitioned table
there must be a check constraint that matches the partitioning constraint and (unless the column is defined NOT NULL) prohibits NULL values

You created a constraint that matches the partitioning constraint, so make sure that the column is defined NOT NULL and validate the constraint:

ALTER TABLE t1 VALIDATE CONSTRAINT partition_check_skip;

That won't block work on the table. Make sure you have all required indexes, then attaching the table as partition should be fast.

In your case, the check constraint isn't defined correctly. Instead of

created_at <= '2023-02-24 00:00:00'

you have to test for the upper end of the range like this:

created_at < '2023-02-24 00:00:00'

This is because the upper end is not included in range partitioning.

With this added information, the case is clear. – Laurenz Albe Feb 22 '23 at 07:23 — Laurenz Albe, Feb 22 '23 at 07:23

Postgres is taking too long to attach partition to a table. Want to understand why

1 Answers1