I'm using postgresql 9.6.11. I wanted to ask something about the functions I mentioned in the title :
postgres=# \d students;
Table "public. students "
Column | Type | Modifiers
----------+---------+-----------
id| integer |
name| text |
age| integer |
data | jsonb |
I inserted one row. When I query the table`s size with pg_total_relation_size I see that the data occupies 2 pages :
postgres=# select pg_total_relation_size(' students ');
pg_total_relation_size
------------------------
16384
(1 row)
postgres=# select pg_relation_size(' students ');
pg_relation_size
------------------
8192
(1 row)
When I used pgstattuple :
postgres=# select * from pgstattuple('pg_toast.pg_toast_1187222');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(1 row)
postgres=# select * from pgstattuple('students');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
8192 | 1 | 1221 | 14.9 | 0 | 0 | 0 | 6936 | 84.67
(1 row)
Which means, the toasted table is empty and you can see that the row I inserted should occupy only one page(8K in my system).
Then, why the pg_total_relation_size shows another page ?(16KB in total)