CODEWITHSUNDEEP
postgresql
Darshan Shah
Darshan Shah

Reputation: 417

How to find dead tuples size in postgresql?

How to find dead tuples size in postgresql ? I have created backup of database using pg_dump and restored it on other server. I see there is database size difference (5 GB)in both database. I have verified the table live tuples and dead tuples. There is numbers of row difference due to new data added in current database. However it is big difference in restored DB size. What is the cause of it ? I didn't do vacuum analyze on restored database yet.

I see there is no dead tuples on restored database this may be one reason. That's why I want to find deadtuples size.

Upvotes: 15

Views: 36820

Answers (2)

Frank N Stein
Frank N Stein

Reputation: 2508

This is the view that you need to check:

select n_live_tup, n_dead_tup, relname from pg_stat_all_tables;

Upvotes: 20

jjanes
jjanes

Reputation: 44202

You can use the extension pgstattuple. It will report dead_tuple_len.

Upvotes: 2

Related Questions