Determining dead/unused space in a table?

Started by David Spadeaover 16 years ago2 messagesgeneral
Jump to latest
#1David Spadea
david.spadea@gmail.com

Hi all,

I'm looking to do some reporting on tablespace usage, and wanted to be able
to determine how much of the space a table physically occupies is actually
in use.

1. I know I can get n_live_tup and n_dead_tup from pg_stat_all_tables, but I
don't think that really answers the question entirely. After a vacuum, my
understanding is that the n_dead_tup will go to zero, but the physical table
size will not have decreased. The pages simply remain available for future
use. How do I determine the amount of space which is not occupied by
live/dead tuples?

2. Is there a way to calculate the amount of space occupied by dead tuples,
or even to calculate the space used by live tuples so I can subtract it from
pg_relation_size()? I don't think it's valid to multiply n_dead_tup by page
size, as multiple tuples could be on a given page.

Thanks for your help!
Dave

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: David Spadea (#1)
Re: Determining dead/unused space in a table?

David Spadea <david.spadea@gmail.com> writes:

I'm looking to do some reporting on tablespace usage, and wanted to be able
to determine how much of the space a table physically occupies is actually
in use.

contrib/pgstattuple can help you with this.

regards, tom lane