Postgres Pro
Downloads
Home   >   mailing lists

Re: What to watch out for when ALTERing NUMERIC(38,0) to BIGINT? - Mailing list pgsql-admin

From David G. Johnston
Subject Re: What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?
Date
Msg-id CAKFQuwZ8su=VAeyNZ6Wj5cU1OoZLQB+B3iPWh49k2yjk4o4ujQ@mail.gmail.com
Whole thread Raw
In response to What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?  (Ron <ronljohnsonjr@gmail.com>)
Responses Re: What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?
List pgsql-admin
Tree view
On Thu, Jul 28, 2022 at 8:13 AM Ron <ronljohnsonjr@gmail.com> wrote:
AWS RDS Postgresql 12.10


We've got tables with columns of data type NUMERIC(38,0) which are a legacy from an Oracle migration.

Besides what's mentioned in https://www.postgresql.org/docs/12/ddl-alter.html#id-1.5.4.8.10, what happens internally when I run:
ALTER TABLE foo ALTER COLUMN bar TYPE BIGINT;

I'm thinking mostly of record fragmentation.


IIUC, that would be the silver lining in all of this - the rewritten table would have zero fragmentation and bloat.  You don't get clustering so the actual physical ordering will still be random but each page will contain all live tuples contiguously placed.

David J.

pgsql-admin by date:

Previous
From: Ron
Date:
Subject: What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?
Next
From: Tom Lane
Date:
Subject: Re: What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?