From: Evan Bauer <evanbauer@mac.com>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: change bigint column to int
Date: Thu, 01 Nov 2018 09:52:19 -0400
Message-ID: <9D2383E2-2492-4F3F-BE51-D665E2AE0D86@mac.com> (raw)
In-Reply-To: <CA+t6e1mBZDs34MZt7jb01S09DwW9Lwqha--+5bSQmDCss8zXbg@mail.gmail.com>
References: <CA+t6e1nSubJm8pJ4WsKpR_HHiNsLPtCLLGDzMNQLcXwhYgijkA@mail.gmail.com>
<117C35AF-8395-4D7F-842B-A63B7141269B@mac.com>
<CA+t6e1mBZDs34MZt7jb01S09DwW9Lwqha--+5bSQmDCss8zXbg@mail.gmail.com>
Mariel,
My suggestion was that you have confidence that you don’t find yourself 90% through the shrink process and unexpectedly find that it fails on an "integer out of range error” like this:
ares_net=# create table test_int (test_big bigint, test_int int);
CREATE TABLE
ares_net=# insert into test_int values (5000000000, 0);
INSERT 0 1
ares_net=# select * from test_int;
test_big | test_int
------------+----------
5000000000 | 0
(1 row)
ares_net=# update test_int set test_int = test_big;
ERROR: integer out of range
ares_net=#
If you already know (because of domain knowledge or because of constraints on how the data was loaded an updated) that all of your values are in the INT range, then this is unnecessary. If you aren’t sure and you need to check the value of unindexed columns (which would require a full table scan), then do the update in a procedure that checks that the 8-byte value value will fit into 4-byte int — and does appropriate things should it not fit.
Cheers,
- Evan
Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer
> On Nov 1, 2018, at 08:54, Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
>
> I want to do it on a very big tables(100M records). Do you think that it will be slow ? I mean if I have an AccessExclusiveLock for a lot of time I cant just run a query and take my time until it finishes..
>
> בתאריך יום ה׳, 1 בנוב׳ 2018 ב-13:55 מאת Evan Bauer <evanbauer@mac.com <mailto:evanbauer@mac.com>>:
> Mariel,
>
> Just run a query and make certain that all values currently stored in the column fit in 4 bytes (+/- 2,147,483,648) before doing the ALTER.
>
> Cheers,
>
> Evan
>
> Sent from my iPhone
>
> On Nov 1, 2018, at 06:20, Mariel Cherkassky <mariel.cherkassky@gmail.com <mailto:mariel.cherkassky@gmail.com>> wrote:
>
>> Hi,
>>
>> I want to change the column type from bigint to int to save space. By doing so, does it include some risks ? As I see it, I should only run vacuum full afterwards if I want to return space to the os right ?
>>
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: evanbauer@mac.com, mariel.cherkassky@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: change bigint column to int
In-Reply-To: <9D2383E2-2492-4F3F-BE51-D665E2AE0D86@mac.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