From: Laurenz Albe <laurenz.albe@cybertec.at>
To: James Pang <jamespang886@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: cannot create index on partitioned table concurrently
Date: Tue, 19 Mar 2024 14:36:36 +0100
Message-ID: <26332d3f68c3dbc14d662e65e3a6ed256ee26dcc.camel@cybertec.at> (raw)
In-Reply-To: <CAHgTRfc16_Riu9j=erY9GeY7a6bw1n8fHNhhJuqJ4o24obJxpw@mail.gmail.com>
References: <CAHgTRfc16_Riu9j=erY9GeY7a6bw1n8fHNhhJuqJ4o24obJxpw@mail.gmail.com>
On Tue, 2024-03-19 at 16:14 +0800, James Pang wrote:
> PGv14 , we migrated from Oracle to Postgresql, recently, we migrate
> index job from Oracle to PG. in Oracle, it's ok to create index online
> on partitioned table, but in Postgresql, ERROR: cannot create index
> on partitioned table concurrently. Instead, I go to create index
> concurrently on each child table separately.
> my question is, from SQL optimizer perspective, any difference
> between "create index on partitioned root table" and "create each
> index on child table directly", looks like optimizer still can use
> indexes with directly created on each child table.
Right.
But it is good to have the partitioned index to make sure that all
partitions have an index.
See https://www.postgresql.org/docs/current/sql-createindex.html
"Concurrent builds for indexes on partitioned tables are currently
not supported. However, you may concurrently build the index on
each partition individually and then finally create the partitioned
index non-concurrently in order to reduce the time where writes to
the partitioned table will be locked out. In this case, building
the partitioned index is a metadata only operation."
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, jamespang886@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: cannot create index on partitioned table concurrently
In-Reply-To: <26332d3f68c3dbc14d662e65e3a6ed256ee26dcc.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