Postgres Table size & record counts

Question

I want to understand the below result behavior for finding a size for a table with x MB while try to count record in this table but no records found in postgres database

db_server=# \dt+
                                 List of relations
Schema  |             Name             | Type  |  Owner  |    Size    | Description 
--------+------------------------------+-------+---------+------------+-------------
user    | table                        | table | user    | 7592 MB    | 




db_server=# select count(*) from table  ;
 count 
-------
     0

score 0 · Answer 1 · answered Oct 12 '20 at 08:58

Most likely it's bloat (dead tuples). From VACUUM desctiption (https://www.postgresql.org/docs/13/sql-vacuum.html):

VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.

There are some complex queries to report this here: https://wiki.postgresql.org/wiki/Show_database_bloat This one is also informative: https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/ VACUUM should clean it up. In short it happens when you insert data and then update or delete it. Deleted space is not freed because older transactions may need it. Update is done as copy on write because older transactions may need older data. Another factor is that data is organized into 8kB pages. Page has to be empty for regular VACUUM to free it. If a page is partially used, then only VACUUM FULL can reclaim that space, but that operation takes longer, requires temporary space and locks the table as far as i remember. There should be also be autovacuum process running that should clean this up at some point. autovacuum is on by default since 8.3.

Postgres Table size & record counts

1 Answers1