1

Table queried size is not same as on physical layout and table fsm file is of same size as table file. Ideally It should have been very small comparatively.

Postgresql Engine= 13 OS = centos7

Table file size:
 # du -sh 16385_vm
8.0K    16385_vm
# du -sh 16385
24K 16385
# du -sh 16385_fsm
24K 16385_fsm

but when I query table sizes are below:

testing=# select pg_size_pretty(pg_relation_size('test1'));
 pg_size_pretty 
----------------
 24 kB
(1 row)

testing=# select pg_size_pretty(pg_total_relation_size('test1'));
 pg_size_pretty 
----------------
 64 kB
(1 row)

testing=# select pg_size_pretty(pg_table_size('test1'));
 pg_size_pretty 
----------------
 64 kB
(1 row)

testing=# \d+ test1
                                        Table "public.test1"
 Column |       Type        | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer           |           |          |         | plain    |              | 
 name   | character varying |           |          |         | extended |              | 
Access method: heap

testing=# 

**Free space Map:**
testing=# SELECT * FROM pg_freespace('test1');
 blkno | avail 
-------+-------
     0 |  1088
     1 |  1120
     2 |  3456
(3 rows)

toast table size is also zero:
testing=# select * from pg_class where oid=16385;
-[ RECORD 1 ]-------+------
oid                 | 16385
relname             | test1
relnamespace        | 2200
reltype             | 16387
reloftype           | 0
relowner            | 10
relam               | 2
relfilenode         | 16385
reltablespace       | 0
relpages            | 3
reltuples           | 17
relallvisible       | 3
reltoastrelid       | 16388
relhasindex         | f
relisshared         | f
relpersistence      | p
relkind             | r
relnatts            | 2
relchecks           | 0
relhasrules         | f
relhastriggers      | f
relhassubclass      | f
relrowsecurity      | f
relforcerowsecurity | f
relispopulated      | t
relreplident        | d
relispartition      | f
relrewrite          | 0
relfrozenxid        | 487
relminmxid          | 1
relacl              | 
reloptions          | 
relpartbound        | 

testing=# 
[root@ip-10-15-11-219 16384]# du -sh 16388
0   16388
[root@ip-10-15-11-219 16384]#
  1. Then how come sql query is returning 64 KB table and total relation size instead of 24 KB.
  2. Why table fsm file size is 24 kB which is equal to actual table size of 24 KB?
Monika Yadav
  • 381
  • 2
  • 12
  • 1
    `pg_total_relation_size` and `pg_table_size` include the `_fsm` and `_vm` values so that would add up to 24K(data fork of table) + 24k(_fsm) + 8k(_vm) = 56K. That leaves 8K unaccounted for. You sure there is nothing in the TOAST table? – Adrian Klaver Sep 04 '22 at 20:44
  • @AdrianKlaver: reltoastrelid is 16388 and you can see it is showing 0 size. I have added this information with the question. – Monika Yadav Sep 05 '22 at 07:23
  • I cannot replicate so I don't have an answer. – Adrian Klaver Sep 05 '22 at 16:01
  • @AdrianKlaver: Also can you help me understand why data file and _fsm file both are of same size? IMO _fsm should be very small file holding free space data. Data bloat file represented by reltoastrelid is of 0 size. – Monika Yadav Sep 06 '22 at 12:21
  • Hard to say without knowing the history of the table. Best guess is that at some point the table held more items and they where deleted or updated(which is roughly a delete/insert). At that point a VACUUM marked the space occupied by the deleted tuples as free for use and recorded that in `_fsm`. You could try a `VACUUM FULL` on the table to see what happens. FYI, `VACUUM FULL` is something you generally want to avoid as it locks the table and requires disk space that is approximately double the table size until it finishes. – Adrian Klaver Sep 06 '22 at 13:48

0 Answers0