| From: | David Spadea <david(dot)spadea(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Determining dead/unused space in a table? | 
| Date: | 2009-10-18 11:11:21 | 
| Message-ID: | 4ae0cb070910180411j49488d53h2f7044fdbe078102@mail.gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2009-10-18 15:53:17 | Re: Determining dead/unused space in a table? | 
| Previous Message | Thom Brown | 2009-10-18 10:46:06 | Re: How ad an increasing index to a query result? |