Re: ERROR: cannot create index on partitioned table concurrently

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux