MIME-Version: 1.0
References: 
 <CAHOGQfVo0FWX8mS9FLTmpDOb5XC5-T4qx4ouhw0wOfNeLdahsA@mail.gmail.com>
In-Reply-To: 
 <CAHOGQfVo0FWX8mS9FLTmpDOb5XC5-T4qx4ouhw0wOfNeLdahsA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 14 May 2026 13:20:12 -0400
Message-ID: 
 <CANzqJaBiSXdTSzYS18fc_XUHKUHPkhBu-uz2cW691BLSHnbydw@mail.gmail.com>
Subject: Re: Alter the datatype on all tables present in the database (bigint
 to varchar)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c92ae40651ca4f88"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaBiSXdTSzYS18fc_XUHKUHPkhBu-uz2cW691BLSHnbydw%40mail.gmail.com>
Precedence: bulk

--000000000000c92ae40651ca4f88
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, May 14, 2026 at 11:07=E2=80=AFAM Gambhir Singh <gambhir.singh05@gma=
il.com>
wrote:

> Hi,
>
> Please help me with the best way to change the datatype of a column in a
> all tables in DB from bigint to varchar. Please consider the following
> facts.
>
>
>    - DB Size - 75 TB
>    - Number of tables - ~100
>    - Some tables are partitioned and some are not partitioned.
>    - All partitioned tables are big in size.
>    - For reference, the largest partition size is 4 TB, similarly the
>    size of 10-12 partitions ranges between 1 to 4 TB.
>    - We have indexes and FK on tables.
>
>
With tables that big, and all those FK constraints, logical replication is
probably your only hope for minimal downtime.

If you're running an older major version, this would also be a good time to
upgrade to 17.latest or 18.latest.

And Adrian's question is quite valid: *why*?  Normally, people go the
other way, for efficiency.


>
>    - Physical Replication is configured with 2 standby DBs.
>
> At least you have an existing server with enough disk space!!!


--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000c92ae40651ca4f88
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, May 14, 2026 at 11:07=E2=80=AFAM =
Gambhir Singh &lt;<a href=3D"mailto:gambhir.singh05@gmail.com">gambhir.sing=
h05@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_con=
tainer"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">=
<div><div style=3D"font-family:tahoma,sans-serif;font-size:small;color:rgb(=
0,0,0)">Hi,</div><div style=3D"font-family:tahoma,sans-serif;font-size:smal=
l;color:rgb(0,0,0)"><br></div><div style=3D"font-family:tahoma,sans-serif;f=
ont-size:small;color:rgb(0,0,0)"><span style=3D"font-family:Arial,Helvetica=
,sans-serif;color:rgb(34,34,34)">Please help me with the best way to change=
 the datatype of a column in a all tables in DB from bigint to varchar. Ple=
ase consider the following facts.</span></div><br><ul><li>DB Size - 75 TB</=
li><li>Number of tables - ~100</li><li>Some tables are partitioned and some=
 are not partitioned.</li><li>All partitioned tables are big in size.</li><=
li>For reference, the largest partition size is 4 TB, similarly the size of=
 10-12 partitions ranges between 1 to 4 TB.</li><li>We have indexes and FK =
on tables.=C2=A0</li></ul></div></div></blockquote><div><br></div><div>With=
 tables that big, and all those FK constraints, logical replication is prob=
ably your only hope for minimal downtime.</div><div><br></div><div>If you&#=
39;re running an older major version, this would also be a good time to upg=
rade to 17.latest or 18.latest.</div><div><br></div><div>And Adrian&#39;s q=
uestion is quite valid: <b>why</b>?=C2=A0 Normally, people go the other=C2=
=A0way, for efficiency.</div><div>=C2=A0</div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex"><div dir=3D"ltr"><div><ul><li>Physical Replication is =
configured with 2 standby DBs.=C2=A0=C2=A0</li></ul></div></div></blockquot=
e><div>At least you have an existing server with enough disk space!!!</div>=
<div><br></div><div><br></div><div><br></div></div><span class=3D"gmail_sig=
nature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><di=
v dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t bo=
il me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></d=
iv></div></div></div></div>

--000000000000c92ae40651ca4f88--