2

I am not understand differents between storage size of INTs (all this types has fixed size)

In official manual I see this description:

The types smallint, integer, and bigint store whole numbers, that is, numbers without fractional components, of various ranges. Attempts to store values outside of the allowed range will result in an error.

The type integer is the common choice, as it offers the best balance between range, storage size, and performance. The smallint type is generally only used if disk space is at a premium. The bigint type is designed to be used when the range of the integer type is insufficient.

SQL only specifies the integer types integer (or int), smallint, and bigint. The type names int2, int4, and int8 are extensions, which are also used by some other SQL database systems.

However simple test shows that change type of column do not change table size

create table test_big_table_int (
  f_int integer
);

INSERT INTO test_big_table_int (f_int )
    SELECT ceil(random() * 1000)
        FROM generate_series(1,1000000);


 SELECT
   pg_size_pretty(pg_total_relation_size(relid)) As "Size_of_table"
 FROM pg_catalog.pg_statio_user_tables 
 where relname = 'test_big_table_int';
 --"35 MB";


 alter table test_big_table_int ALTER COLUMN f_int TYPE bigint;


 SELECT
   pg_size_pretty(pg_total_relation_size(relid)) As "Size_of_table"
 FROM pg_catalog.pg_statio_user_tables 
 where relname = 'test_big_table_int';
--"35 MB";

  alter table test_big_table_int ALTER COLUMN f_int TYPE smallint;


 SELECT
   pg_size_pretty(pg_total_relation_size(relid)) As "Size_of_table"
 FROM pg_catalog.pg_statio_user_tables 
 where relname = 'test_big_table_int';
 --"35 MB";"0 bytes"

Everytime I get size of my table - 35MB. So where the profit to use integer or smallint insthead int8?

And second question - why postgee is doing rewrite tuple while change type of int (int2<->int4<->int8)?

S-Man
  • 22,521
  • 7
  • 40
  • 63
Akvel
  • 924
  • 1
  • 15
  • 32
  • 1
    You should at least run VACUUM FULL after altering the schema, to give the database a chance to be compacted. – nos Feb 08 '16 at 13:41
  • Already has tried. You will get same result if will do vacuum full test_big_table_int; aftre every alter :-) – Akvel Feb 08 '16 at 13:55
  • 2
    The columns are padded to word boundaries when stored on disk: http://stackoverflow.com/a/7431468/330315 –  Feb 08 '16 at 14:25

0 Answers0