Re: Index - Concurrently Method

On Wed, 2024-06-12 at 00:15 +0300, SOzcn wrote:
> Is there any other way to use the concurrent method on the table which  has partitioning?
>
> I found something, first of all, it's advised to create the indexes concurrently on your partitioning tables and then create the index on your main after this progress attach the indexes.
>
> Does anybody have any experience with this issue?
>
> I leave the example in the belong. Is that way healthy? At the last time, we gonna create on the main table anyway. So it's kinda confusing me.
>
> By Example;
> CREATE INDEX CONCURRENTLY idx_sales_2020_order_date ON sales_2020 (order_date);
> CREATE INDEX CONCURRENTLY idx_sales_2021_order_date ON sales_2021 (order_date);
> CREATE INDEX CONCURRENTLY idx_sales_2022_order_date ON sales_2022 (order_date);
>
> ALTER INDEX idx_sales_2020_order_date ATTACH PARTITION idx_sales_order_date;
> ALTER INDEX idx_sales_2021_order_date ATTACH PARTITION idx_sales_order_date;
> ALTER INDEX idx_sales_2022_order_date ATTACH PARTITION idx_sales_order_date;

No, there is no other way. But the commands you quote seem to be the wrong
way around. It should be:

CREATE INDEX idx_sales_order_date ON ONLY sales (order_date);

CREATE INDEX CONCURRENTLY idx_sales_2020_order_date ON sales_2020 (order_date);
CREATE INDEX CONCURRENTLY idx_sales_2021_order_date ON sales_2021 (order_date);
CREATE INDEX CONCURRENTLY idx_sales_2022_order_date ON sales_2022 (order_date);

ALTER INDEX idx_sales_order_date ATTACH PARTITION idx_sales_2020_order_date;
ALTER INDEX idx_sales_order_date ATTACH PARTITION idx_sales_2021_order_date;
ALTER INDEX idx_sales_order_date ATTACH PARTITION idx_sales_2022_order_date;

Yours,
Laurenz Albe