Re: Question on partitioning

Thank you Laurenz. Got it.

So basically , you mean to say any DDL on a table won't allow the table to
be read by other processes. I was under the assumption that it should allow
the read queries to move ahead at least. I must be wrong here. Thanks for
correcting me.

On Tue, 6 Feb 2024 at 15:46, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Tue, 2024-02-06 at 00:26 +0530, veem v wrote:
> > 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?
> > >
> > > There is no way to do that.
> >
> > 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.
>
> You could use logical replication to keep the downtime short.
>
> > Additionally I see a lot of other restrictions like
> > 1)When creating indexes on the partition table "concurrently" keywords
> are not allowed.
>
> That is no problem.
> You create the index on ONLY the partitioned table,
> then you create indexes CONCURRENTLY on the partitions,
> then you attach these indexes as partitions to the index you created in
> the beginning.
> As soon as the last index is attached, the index on the partitioned table
> becomes valid.
>
> > 2)While creating foreign key , it does not allow a "not valid"
> clause if the table
> > is partitioned.
>
> Create the foreign key on the partitions; the effect is the same.
>
> > 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?
>
> I am not sure what the "base table" is. Most DDL always locks the table in
> ACCESS EXCLUSIVE mode; that is not specific to partitioned tables.
>
> Yours,
> Laurenz Albe
>