Re: Question on partitioning

On Mon, 5 Feb 2024 at 17:52, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> 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(dot)albe(at)cybertec(dot)at> 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
>