pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat to watch out for when ALTERing NUMERIC(38,0) to BIGINT?
4+ messages / 3 participants
[nested] [flat]
* What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?
@ 2022-07-28 15:13 Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ron @ 2022-07-28 15:13 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?
@ 2022-07-28 15:19 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2022-07-28 15:19 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?
@ 2022-07-28 15:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2022-07-28 15:26 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What to watch out for when ALTERing NUMERIC(38,0) to BIGINT?
@ 2022-07-28 17:41 Ron <ronljohnsonjr@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Ron @ 2022-07-28 17:41 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-07-28 17:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-07-28 15:13 What to watch out for when ALTERing NUMERIC(38,0) to BIGINT? Ron <ronljohnsonjr@gmail.com>
2022-07-28 15:19 ` David G. Johnston <david.g.johnston@gmail.com>
2022-07-28 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-07-28 17:41 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox