Size on disk of INT and BIGINT - not sure I'm getting it?
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...
Pól:
On Fri, May 28, 2021 at 11:36 AM Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:
BIGINTEGER and INTEGER tables the same size? Comparing tables of 100M records.
...
test=# CREATE TABLE big_int (x BIGINT NOT NULL);
test=# CREATE TABLE int_32 (y INT);
Bear in mind a row has more data than just the fields ( thingx like
xmin,xmax and some others ) and there are alignment issues, and other
things.
If, for example, the whole row needs to be 8-byte aligned on a
synthetic example with a single field the rows may be the same lengths
for both cases, note how your tables ocupy
3458Mb but the data is just 800/400 Mb.
You may want to investgate putting some more realistic data ( i.e.,
try 2/4/8 fields per row ) to gain some insight, and reading
https://www.postgresql.org/docs/current/storage-page-layout.html which
gives some pointers on where to go next.
Francisco Olarte.