Re: Conversion to partitioning table

CC'ing this list

On Thu, Aug 3, 2023 at 1:24 PM Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com> wrote:
>
> I have one table with three columns (id, blocknumber, jsondata) , table is about 20GB. Only one thing happens with this table is that, some query checks only recent block number for some activity it seems. Is partitioning the right choice? how exactly can i partition this?
>
> step 1: Create a new table with the same schema as existing table but as a partition table.
> step 2: Create child partition tables for them
> step 3: INSERT INTO new table SELECT*FROM OLDTABLE; ( Will this step acquire lock)
>
> This helps? or a bad idea?
>
> On Thu, 3 Aug 2023 at 13:02, kyle Hailey <kylelf(at)gmail(dot)com> wrote:
>>
>> https://www.kylehailey.com/post/postgres-partition-pains-lockmanager-waits
>>
>> Partitioning can take your production database down
>>
>> https://www.kylehailey.com/post/postgres-partition-conversion-minimal-downtime
>>
>> Basically there are 4 steps:
>> 1 Rename old table
>> 2 Create new table with partitioning
>> 3 Add constraint on old table for it's proposed partition ranges
>> 4 Attach old table as a partition to new partition table
>> Sounds easy right?
>> What about indexes? What about Triggers?
>> And guess what, there are other traps to watch out for!
>>
>>
>>
>> asd
>> <br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu,
>> Aug 3, 2023 at 12:47 PM Rajesh Kumar
>> &lt;rajeshkumar(dot)dba09(at)gmail(dot)com&gt; wrote:<br></div><blockquote
>> class="gmail_quote" style="margin: 0px 0px 0px 0.8ex; border-left: 1px
>> solid rgb(204, 204, 204); padding-left: 1ex;"><div dir="ltr">Hi
>> friends,<div><br></div><div>I am using Redhat&nbsp;open shift
>> container and Postgres version 15.2. I wish to convert the normal
>> table into a partitioning table. Please help me with the
>> pre-requisite, each and every step to partition. As far as i know, we
>> can create a new partition(parent and child table) and simply insert
>> data from the old table into the new table and drop the old one. Is
>> that all?</div><div><br></div><div>Please provide me detailed answer
>> with detailed steps.</div><div><br></div><div>Thank you</div><font
>> color="#888888"><div>Kumar.</div></font></div>
>> </blockquote></div>