public inbox for pgsql-admin@postgresql.orghelp / color / mirror / Atom feed
Alter the datatype on all tables present in the database (bigint to varchar) 4+ messages / 3 participants [nested] [flat]
* Alter the datatype on all tables present in the database (bigint to varchar) @ 2026-05-11 12:31 Gambhir Singh <gambhir.singh05@gmail.com> 0 siblings, 3 replies; 4+ messages in thread From: Gambhir Singh @ 2026-05-11 12:31 UTC (permalink / raw) To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-general@lists.postgresql.org 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. - Physical Replication is configured with 2 standby DBs. -- Thanks & Regards Gambhir Singh ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Alter the datatype on all tables present in the database (bigint to varchar) @ 2026-05-14 17:00 Adrian Klaver <adrian.klaver@aklaver.com> parent: Gambhir Singh <gambhir.singh05@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2026-05-14 17:00 UTC (permalink / raw) To: pgsql-general@lists.postgresql.org On 5/11/26 5:31 AM, Gambhir Singh 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. 1) Do not cross post to multiple lists. 2) What is the purpose behind this change? > > * 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. > * Physical Replication is configured with 2 standby DBs. > > > -- > Thanks & Regards > Gambhir Singh > -- Adrian Klaver adrian.klaver@aklaver.com ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Alter the datatype on all tables present in the database (bigint to varchar) @ 2026-05-14 17:20 Ron Johnson <ronljohnsonjr@gmail.com> parent: Gambhir Singh <gambhir.singh05@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: Ron Johnson @ 2026-05-14 17:20 UTC (permalink / raw) To: Pgsql-admin <pgsql-admin@lists.postgresql.org> On Thu, May 14, 2026 at 11:07 AM Gambhir Singh <gambhir.singh05@gmail.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!!! -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Alter the datatype on all tables present in the database (bigint to varchar) @ 2026-05-14 20:11 Adrian Klaver <adrian.klaver@aklaver.com> parent: Gambhir Singh <gambhir.singh05@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2026-05-14 20:11 UTC (permalink / raw) To: Gambhir Singh <gambhir.singh05@gmail.com>; +Cc: pgsql-general <pgsql-general@postgresql.org> On 5/14/26 11:34 AM, Gambhir Singh wrote: Please reply to list also. Ccing list > Hi Adrian, > > I've received this requirement from the application team. My main > concern is the partitioned tables. I would think there would be a least some rationale for doing this, if for no other reason then to determine whether this is the best solution. From here: https://www.postgresql.org/docs/17/sql-altertable.html "Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed. However, indexes must always be rebuilt unless the system can verify that the new index would be logically equivalent to the existing one. For example, if the collation for a column has been changed, an index rebuild is always required because the new sort order might be different. However, in the absence of a collation change, a column can be changed from text to varchar (or vice versa) without rebuilding the indexes because these data types sort identically. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space." You also mentioned FKs, do these involve the columns being changed? > > Thanks & Regards > Gambhir Singh > -- Adrian Klaver adrian.klaver@aklaver.com ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-05-14 20:11 UTC | newest] Thread overview: 4+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-05-11 12:31 Alter the datatype on all tables present in the database (bigint to varchar) Gambhir Singh <gambhir.singh05@gmail.com> 2026-05-14 17:00 ` Adrian Klaver <adrian.klaver@aklaver.com> 2026-05-14 17:20 ` Ron Johnson <ronljohnsonjr@gmail.com> 2026-05-14 20:11 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox