Smaller data types use same disk space

From: "McGehee, Robert" <Robert(dot)McGehee(at)geodecapital(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Smaller data types use same disk space
Date: 2012-07-24 22:21:48
Message-ID: 17B09E7789D3104E8F5EEB0582A8D66FDF9CDAEDD2@MSGRTPCCRF2WIN.DMN1.FMR.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I've created two tables labeled "Big" and "Small" that both store the same 10 million rows of data using 493MB and 487MB of disk space respectively. The difference is that the "Big" table uses data types that take up more space (integer rather than smallint, float rather than real, etc). The "Big" table should need about 27 bytes/row versus 16 bytes/row for the "Small" table, indicating to me that the "Big" table should be 70% bigger in actual disk size. In reality, it's only 1% bigger or 6MB (after clustering, vacuuming and analyzing). Why is this? Shouldn't the "Small" table be about 110MB smaller (11 bytes for 10 million rows)? I'm estimating table size with \d+

Thanks, Robert

Table "Big"
Column | Type | Bytes
----------+------------------+-----------
rmid | integer | 4
date | date | 4
rmfactor | text | 7 (about 3 characters/cell)
id | integer | 4
value | double precision | 8
---------------------------------
Total Bytes/Row 27
Rows 10M
Actual Size 493MB

Table "Small"
Column | Type | Bytes
--------+----------+-----------
rmid | smallint | 2
date | date | 4
rmfid | smallint | 2 (rmfid is a smallint index into the rmfactor table)
id | integer | 4
value | real | 4
---------------------------------
Total Bytes/Row 16
Rows 10M
Actual Size 487MB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mark Wynter 2012-07-24 23:01:00 Re: Problem using a pl/pgsql function to populate a geometry column with x, y data - SOLVED
Previous Message Lonni J Friedman 2012-07-24 21:37:52 Re: insert binary data into a table column with psql