Thread: What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?
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.
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.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
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.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Thu, Jul 28, 2022 at 8:13 AM Ron <ronljohnsonjr@gmail.com> wrote: >> 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; > IIUC, that would be the silver lining in all of this - the rewritten table > would have zero fragmentation and bloat. Yeah. What happens internally is a table rewrite: the entire content of the table (and its indexes) is written into a new set of files. At commit, the old files are deleted. The main gotchas, for a large table, are the transient disk space consumption and the fact that the table stays exclusively locked the whole time. regards, tom lane
On 7/28/22 10:26, Tom Lane wrote: > "David G. Johnston" <david.g.johnston@gmail.com> writes: >> On Thu, Jul 28, 2022 at 8:13 AM Ron <ronljohnsonjr@gmail.com> wrote: >>> 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; >> IIUC, that would be the silver lining in all of this - the rewritten table >> would have zero fragmentation and bloat. > Yeah. What happens internally is a table rewrite: the entire content > of the table (and its indexes) is written into a new set of files. > At commit, the old files are deleted. The main gotchas, for a large > table, are the transient disk space consumption and the fact that the > table stays exclusively locked the whole time. Rewriting a 3TB table doesn't seem appetizing. Fortunately, there's only one table like that... -- Angular momentum makes the world go 'round.