Re: Alter the column data type of the large data volume table.

On Fri, Dec 4, 2020 at 5:22 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

>
> On Fri, Dec 4, 2020 at 9:04 AM Kevin Brannen <KBrannen(at)efji(dot)com> wrote:
>
>> *>From:* Olivier Gautherot <ogautherot(at)gautherot(dot)net>
>>
>> >>5) If you're brave enough, convert your current table as a partition
>> (rename it to something like table_hist), duplicate the table model under
>> the same name as now (adjusting the primary key type) and set the INHERITS
>> on the primary key range. The inheritance should take care of the type
>> conversion (haven't tried it but it's worth a try). If it works, you will
>> reach your goal without downtime or significant overhead.
>>
>> >Sorry, just tried this one and it failed: type mismatch.
>>
>>
>>
>> Seems like a sound idea in general. I’d probably rename the tables, let’s
>> call them “big_hist” for the old big table and “big_split” for the new
>> partitioned table that being used go forward – assuming the original table
>> was called “big”. Then create a View that will look at both of those but
>> call it the same as the old table, and let the view do a type cast on the
>> old key like big_hist.id::bigint so it matches the new type, because the
>> view will probably be a union and the type need to match. That way your
>> application only has to pause long enough to do a few meta-commands then it
>> all can resume, and like Olivier pointed you, you can fix the data by
>> moving it from big_hist to big_split in the background as you have time.
>>
>>
>>
>> I’d probably put it all in a transaction too:
>>
>>
>>
>> Create table … -- all the commands to create your patitioned table
>> big_split here
>>
>> Begin;
>>
>> Alter table big rename to big_hist;
>>
>> Create view big select * from big_split union select id::bigint, /* other
>> cols */ from big_hist;
>>
>> Commit;
>>
>>
>>
>> Try it on a dev system and if it works you’re off and running. I’d expect
>> the view to slow things down a little, but probably not too much if you
>> have good indexes. But at least you could transition without major downtime
>> and then rename “big_split” back to “big” and drop “big_hist” when you’ve
>> finished the transition. I might even be tempted to add a trigger so that
>> all new inserts into “big” really go into “big_split” so “big_hist” doesn’t
>> grow any more. Your imagination is probably the limit. 😊
>>
>
> Yes, "instead of" triggers for insert/update/delete will make the change
> transparent to the application side, other than the potential for slowness
> while in the process of moving the data and still using the view.
>
> Also, I'd advocate for UNION ALL to avoid the "distinct" work that is
> required for plain UNION. I wish ALL were default behavior and "UNION
> DISTINCT" was how get that behavior.
>
> If partitioning is going to happen anyway in the near future, now is a
> great time to make that happen. Unfortunately, PG13 is required to use
> logical replication from a non-partitioned table to a partitioned table so
> moving the data still requires some creative work.
>

Rather than union, use inheritance with a constraint on the primary key:
it will take care of the union transparently for you.
--
Olivier Gautherot

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>