pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: veem v <veema0000@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Question on partitioning
Date: Tue, 06 Feb 2024 11:16:04 +0100
Message-ID: <fd0e0f65d2e3e6ae7d7811241197edd41dcfecbd.camel@cybertec.at> (raw)
In-Reply-To: <CAB+=1TUbA9U3UF9DHOk4aiSfNPHan-7Vqq3PvRZ+i_1iOQaTrg@mail.gmail.com>
References: <CAB+=1TUznj3Gk0EmUtahQVnc3AuvFFP2tqArWnWm+oBW4YJ7-g@mail.gmail.com>
	<b81be88284b39bac7f88bdf0a4c600d71ba71e64.camel@cybertec.at>
	<CAB+=1TUbA9U3UF9DHOk4aiSfNPHan-7Vqq3PvRZ+i_1iOQaTrg@mail.gmail.com>

On Tue, 2024-02-06 at 00:26 +0530, veem v wrote:
> On Mon, 5 Feb 2024 at 17:52, Laurenz Albe <laurenz.albe@cybertec.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





view thread (7+ messages)  latest in thread

Message-ID: <fd0e0f65d2e3e6ae7d7811241197edd41dcfecbd.camel@cybertec.at>
Permalink:  ../fd0e0f65d2e3e6ae7d7811241197edd41dcfecbd.camel@cybertec.at/
Also on:    postgresql.org/message-id/fd0e0f65d2e3e6ae7d7811241197edd41dcfecbd.camel@cybertec.at

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: laurenz.albe@cybertec.at, veema0000@gmail.com
  Subject: Re: Question on partitioning
  In-Reply-To: <fd0e0f65d2e3e6ae7d7811241197edd41dcfecbd.camel@cybertec.at>
  References: <CAB+=1TUznj3Gk0EmUtahQVnc3AuvFFP2tqArWnWm+oBW4YJ7-g@mail.gmail.com> <b81be88284b39bac7f88bdf0a4c600d71ba71e64.camel@cybertec.at> <CAB+=1TUbA9U3UF9DHOk4aiSfNPHan-7Vqq3PvRZ+i_1iOQaTrg@mail.gmail.com> <fd0e0f65d2e3e6ae7d7811241197edd41dcfecbd.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