On Mon, 5 Feb 2024 at 17:52, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
On Mon, 2024-02-05 at 03:09 +0530, veem v wrote:
> In postgresql, Is it possible to partition an existing nonpartitioned table having data
> already residing in it and indexes and constraints defined in it, without the need of
> manually moving the data around, to make it faster? Similarly merging multiple partitions
> to one partition or splitting a single partition into multiple partitions?
There is no way to do that.
Yours,
Laurenz Albe
Thank you very much Laurenz.
Actually in other databases (for example like in Oracle) there exists sql syntax to split one partition into multiple and merge multiple partitions back to one. So I was hoping there may be some way to do it in postgres. Anyway, thanks for clarifying my doubt on this.
Which means for any such operation we need to create a new partition table with that structure and load that with the data from the existing partition table. Also for making or converting a non partition table to a partitioned one , we have to first create the blank partition table structure as per our need and then pump the data from the non partition table to the newly created partitioned table, which means we need to take some downtime to switch from non partitioned table to partitioned one. Please correct if wrong.
Additionally I see a lot of other restrictions like
1)When creating indexes on the partition table "concurrently" keywords are not allowed.
2)While creating foreign key , it does not allow a "not valid" clause if the table is partitioned.
3) While creating indexes on this table or running any ALTER command, the SELECT queries running from other processes run longer. Does it take any lock while doing DDL on the base table in postgres?
On Mon, 5 Feb 2024 at 17:52, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
On Mon, 2024-02-05 at 03:09 +0530, veem v wrote:
> In postgresql, Is it possible to partition an existing nonpartitioned table having data
> already residing in it and indexes and constraints defined in it, without the need of
> manually moving the data around, to make it faster? Similarly merging multiple partitions
> to one partition or splitting a single partition into multiple partitions?
There is no way to do that.
Yours,
Laurenz Albe
