From: Laurenz Albe <laurenz.albe@cybertec.at>
To: SOzcn <selahattinozcnma@gmail.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Index - Concurrently Method
Date: Wed, 12 Jun 2024 08:38:59 +0200
Message-ID: <3566ecd44c6b5d98492d9384bc97c1ddb58a125d.camel@cybertec.at> (raw)
In-Reply-To: <CAJyV5Abye51ReNuQCcBXpGPYs=VXw6XzQfBHe1HBWafdi66=Fw@mail.gmail.com>
References: <CAJyV5Abye51ReNuQCcBXpGPYs=VXw6XzQfBHe1HBWafdi66=Fw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, selahattinozcnma@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Index - Concurrently Method
In-Reply-To: <3566ecd44c6b5d98492d9384bc97c1ddb58a125d.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox