Tom,
Thanks for the tip about pgstattuple - I hadn't discovered that (and I
hadn't realised that it's bundled in the 9.4.4 distribution).
This is what I get:
SELECT * FROM pgstattuple('observation');
table_len 21,954,740,224
tuple_count 34,373,274
tuple_len 9,307,650,026
tuple_percent 42
dead_tuple_count 198,281
dead_tuple_len 52,524,135
dead_tuple_percent 0.2
free_space 12,093,978,284
free_percent 55
So I think my estimate of the space used by live rows wasn't far out
(albeit I accept the method I used may not be reliable).
I thought that autovacuum should recover the free space, however I see now
from the documentation that it doesn't (and that this is deliberate):
"The standard form of VACUUM removes dead row versions in tables and
indexes and marks the space available for future reuse. However, it will
not return the space to the operating system, except .... In contrast,
VACUUM FULL actively compacts tables by writing a complete new version of
the table file with no dead space. This minimizes the size of the table,
but can take a long time...
The usual goal of routine vacuuming is to do standard VACUUMs often enough
to avoid needing VACUUM FULL. The autovacuum daemon attempts to work this
way, and in fact will never issue VACUUM FULL."
http://www.postgresql.org/docs/9.4/static/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY
I'll do a VACUUM FULL, which I expect to reduce table_len.
Steve
On 23 September 2015 at 17:50, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Steve Pritchard <steve.pritch...@bto.org> writes:
> > -- Average length of a row in bytes:
> > select avg(octet_length(t.*::text)) FROM observation t;
> > -- 287 bytes
>
> That measurement technique doesn't have a lot to do with reality,
> I'm afraid.
>
> The contrib/pgstattuple module could give you a more reliable idea of
> how much space is in use or not in the table.
>
> regards, tom lane
>
