pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Greg Sabino Mullane <htamfids@gmail.com>
To: veem v <veema0000@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Peter J. Holzer <hjp-pgsql@hjp.at>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: How to do faster DML
Date: Thu, 15 Feb 2024 08:43:17 -0800
Message-ID: <9da45458-e5dc-44e2-80f3-6a0462443a1c@aklaver.com> (raw)
In-Reply-To: <CAKAnmmLYTVfeiwPKURKK3qWsWHm4Cbhc5+zBY=qTsw+5bcP=KQ@mail.gmail.com>
References: <CAB+=1TUfA7dra5DRqAoVvKvjTv5WndyNDiuvFw+bBR0qi6_tWQ@mail.gmail.com>
	<20240211133203.zw6ezhdzefyxmkd7@hjp.at>
	<CAB+=1TVP9xFM16J_ipQaL05UYQPAYyP51xzOSnvcmppBLKvYpA@mail.gmail.com>
	<CANzqJaDOjP=DY8ArVae0py_iSiS+JneQatB7seRhU29y7DqbXw@mail.gmail.com>
	<20240211222230.tt3mpjdvgrimeodu@hjp.at>
	<CAB+=1TVjov+y-8=d3ey-B7-Wj-23KPAKQQAejkjXcS50_KZ=pg@mail.gmail.com>
	<CAKAnmmLcTS7zpeZ3m7mkJxGsu+f+Tv9bp5KV3T52Q+c8DMTm+A@mail.gmail.com>
	<CAB+=1TXv=RNOttpmzJOwbczSsaS0VnigFMLkPVY051fiVAxPdg@mail.gmail.com>
	<20240213150234.ydiitfoowojhj2n3@hjp.at>
	<CAB+=1TVCHMPjOS-uoFj36zBFPTzybY+VhrRC8WTa0pgg-0_xhA@mail.gmail.com>
	<CANzqJaDg4C-i=koB4v1x_nrherdKsHVQm5dP1GMTc-yooKfd3A@mail.gmail.com>
	<CAB+=1TV2i5rvO-dVhj+8NObgp8LTwBBNvi4VX5odh5onKNa7UQ@mail.gmail.com>
	<bcb0516c-f2ba-4693-90e1-60063fe96601@aklaver.com>
	<CAB+=1TWZQ6gQjRJzysMHw6YUZw150gvxOAZ3KnK1i0pLF6SaOg@mail.gmail.com>
	<CAKAnmmLYTVfeiwPKURKK3qWsWHm4Cbhc5+zBY=qTsw+5bcP=KQ@mail.gmail.com>

On 2/15/24 08:16, Greg Sabino Mullane wrote:
>     So as I also tested the same as you posted, there has been no change
>     in "ctid" , when I altered the column data type from 'int' to
>     'bigint' in the table, so that means full table rewriting
>     won't happen in such a scenario.
> 
> 
> No it was definitely rewritten - do not depend on the ctid to verify 
> that. Take our word for it, or 
> use*pg_relation_filenode('int_test');* before and after, as well as 
> *pg_relation_size('int_test')*;

That is a mixed bag:

test=# select pg_relation_filenode('int_test');
  pg_relation_filenode
----------------------
                 69999
(1 row)

test=# select pg_relation_size('int_test');
  pg_relation_size
------------------
            368640
(1 row)

test=# alter table int_test alter column int_fld set data type bigint;
ALTER TABLE
test=# select pg_relation_filenode('int_test');
  pg_relation_filenode
----------------------
                 70002
(1 row)

test=# select pg_relation_size('int_test');
  pg_relation_size
------------------
            368640

> 
> Cheers,
> Greg
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






view thread (67+ messages)  latest in thread

Message-ID: <9da45458-e5dc-44e2-80f3-6a0462443a1c@aklaver.com>
Permalink:  ../9da45458-e5dc-44e2-80f3-6a0462443a1c@aklaver.com/
Also on:    postgresql.org/message-id/9da45458-e5dc-44e2-80f3-6a0462443a1c@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, htamfids@gmail.com, veema0000@gmail.com, ronljohnsonjr@gmail.com, hjp-pgsql@hjp.at, laurenz.albe@cybertec.at
  Subject: Re: How to do faster DML
  In-Reply-To: <9da45458-e5dc-44e2-80f3-6a0462443a1c@aklaver.com>
  References: <CAB+=1TUfA7dra5DRqAoVvKvjTv5WndyNDiuvFw+bBR0qi6_tWQ@mail.gmail.com> <20240211133203.zw6ezhdzefyxmkd7@hjp.at> <CAB+=1TVP9xFM16J_ipQaL05UYQPAYyP51xzOSnvcmppBLKvYpA@mail.gmail.com> <CANzqJaDOjP=DY8ArVae0py_iSiS+JneQatB7seRhU29y7DqbXw@mail.gmail.com> <20240211222230.tt3mpjdvgrimeodu@hjp.at> <CAB+=1TVjov+y-8=d3ey-B7-Wj-23KPAKQQAejkjXcS50_KZ=pg@mail.gmail.com> <CAKAnmmLcTS7zpeZ3m7mkJxGsu+f+Tv9bp5KV3T52Q+c8DMTm+A@mail.gmail.com> <CAB+=1TXv=RNOttpmzJOwbczSsaS0VnigFMLkPVY051fiVAxPdg@mail.gmail.com> <20240213150234.ydiitfoowojhj2n3@hjp.at> <CAB+=1TVCHMPjOS-uoFj36zBFPTzybY+VhrRC8WTa0pgg-0_xhA@mail.gmail.com> <CANzqJaDg4C-i=koB4v1x_nrherdKsHVQm5dP1GMTc-yooKfd3A@mail.gmail.com> <CAB+=1TV2i5rvO-dVhj+8NObgp8LTwBBNvi4VX5odh5onKNa7UQ@mail.gmail.com> <bcb0516c-f2ba-4693-90e1-60063fe96601@aklaver.com> <CAB+=1TWZQ6gQjRJzysMHw6YUZw150gvxOAZ3KnK1i0pLF6SaOg@mail.gmail.com> <CAKAnmmLYTVfeiwPKURKK3qWsWHm4Cbhc5+zBY=qTsw+5bcP=KQ@mail.gmail.com> <9da45458-e5dc-44e2-80f3-6a0462443a1c@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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