Size on disk of INT and BIGINT - not sure I'm getting it? - Mailing list pgsql-general

From Pól Ua Laoínecháin
Subject Size on disk of INT and BIGINT - not sure I'm getting it?
Date
Msg-id CAF4RT5QJA0kP-e1s5Of6HCx9GOXeE0=3WqnrBg8L-hmfF1dixw@mail.gmail.com
Whole thread Raw
Responses Re: Size on disk of INT and BIGINT - not sure I'm getting it?
List pgsql-general
Hi all,

I did the following (Asus laptop, Intel core i5, SATA SSD):

BIGINTEGER and INTEGER tables the same size? Comparing tables of 100M records.

Create tables:

=================================================

test=# CREATE TABLE big_int (x BIGINT NOT NULL);
CREATE TABLE


test=# CREATE TABLE int_32 (y INT);
CREATE TABLE

================================================

Do my inserts:


=================================================

test=# INSERT INTO big_int SELECT 1 FROM GENERATE_SERIES(1, 100000000);
INSERT 0 100000000

test=# INSERT INTO int_32 SELECT 1 FROM GENERATE_SERIES(1, 100000000);
INSERT 0 100000000
Time: 115153.540 ms (01:55.154)

=================================================

Checks on my table/record sizes:


tables:

====================================================

test=# SELECT pg_size_pretty( pg_total_relation_size('big_int'));
 pg_size_pretty
----------------
 3458 MB
(1 row)

Time: 0.494 ms



test=# SELECT pg_size_pretty( pg_total_relation_size('int_32'));
 pg_size_pretty
----------------
 3458 MB
(1 row)

Time: 0.828 ms

Ooops! *_Exactly_* the same size!

======================================================

Double checked and tried to be more precise:

=====================================================

Time: 0.639 ms
test=# SELECT pg_total_relation_size('big_int');
 pg_total_relation_size
------------------------
             3625803776
(1 row)

Time: 0.355 ms

test=# SELECT pg_total_relation_size('int_32');
 pg_total_relation_size
------------------------
             3625803776
(1 row)


Same size to the byte!!

=================================================

Final check to see what's going on?

=================================================

test=# SELECT SUM(pg_column_size(x))/100000000 FROM big_int;
 ?column?
----------
        8
(1 row)

Time: 3873.954 ms (00:03.874)
test=# SELECT SUM(pg_column_size(y))/100000000 FROM int_32;
 ?column?
----------
        4
(1 row)

Time: 3913.429 ms (00:03.913)
test=#

===================================================

I'm trying to make sure that my table takes up 8 bytes for each entry
in the bit_int table and 4 bytes for every entry in int_32.

I'd be grateful for

a) an explanation as to what's going on?

and

b) what do I have to do to ensure that records will take up the space
that I want them to?

This latter part would possibly be covered by a good answer to a).


Any decent references, URLs... &c. much appreciated - although not to
source code please - a bit above my pay grade! :-)


TIA and rgs,


Pól...

pgsql-general by date:

Previous
From: "Dean Gibson (DB Administrator)"
Date:
Subject: Re: How long to get a password reset ???
Next
From: Francisco Olarte
Date:
Subject: Re: Size on disk of INT and BIGINT - not sure I'm getting it?