According to the doc [1],
pg_total_relation_size add toasted data *and* indexes to the mix.
Any index, unique constraint, or primary key on your table ?
Le mer. 30 janv. 2019 à 11:42, Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> a écrit :
Hey,I'm using postgresql 9.6.11. I wanted to ask something about the functions I mentioned in the title :I created the next table :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)
