Thanks Kevin.
Am in 9.1 and tested same scenario, how exactly storage metrics are calculated. Please comment.
Table Structure:
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
No. of rows:
postgres=# select relname,reltuples from pg_class where relname='test';
relname | reltuples
---------+-----------
test | 1001
(1 row)
Average Row size:
postgres=# select sum(avg_width) as average_row_size from pg_stats where tablename='test';
average_row_size
------------------
17
(1 row)
Occupied Space:
postgres=# select 17*reltuples/1024 as "No.of.Row_size * No.of.Rows = Occupied_Space" from pg_class where relname='test';
No.of.Row_size * No.of.Rows = Occupied_Space
----------------------------------------------
16.6181640625
Actual Table Size:
postgres=# select pg_size_pretty(pg_relation_size('test'));
pg_size_pretty
----------------
48 kB
(1 row)
or
postgres=# SELECT relname, reltuples, pg_size_pretty(relpages*8*1024) as size FROM pg_class, pg_namespace WHERE pg_namespace.oid = pg_class.relnamespace AND relkind = 'r' AND nspname = 'public' ORDER BY relpages DESC;
relname | reltuples | size
---------+-----------+-------
test | 1001 | 48 kB
(1 row)
Its different here:
postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+-------+-------------
public | test | table | postgres | 88 kB |
(1 row)
postgres=# select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty
----------------
88 kB
(1 row)
Free Space:
postgres=# SELECT pg_size_pretty(free_space) AS mb_free FROM pgstattuple('test');
mb_free
-----------
936 bytes
(1 row)
or
postgres=# select * from pgstattuple('test');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
49152 | 1001 | 41041 | 83.5 | 0 | 0 | 0 | 936 | 1.9
(1 row)
OS Level Storage:
bash-4.1$ ll -h 16447*
-rw------- 1 postgres postgres 48K Oct 2 17:40 16447
-rw------- 1 postgres postgres 24K Oct 2 17:40 16447_fsm
-rw------- 1 postgres postgres 8.0K Oct 2 17:40 16447_vm
What has occupied in extra 8KB ?
postgres=# select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty
----------------
88 kB
(1 row)
Thanks in advance.
---
Regards,
Raghavendra
EnterpriseDB Corporation
