pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Colton Smith <smith@skio.peachnet.edu>
To: pgsql-admin@postgresql.org
Subject: pgstattuple, vacuum and free_space
Date: Thu, 24 Nov 2005 14:06:08 -0500
Message-ID: <43860F20.5080709@skio.peachnet.edu> (raw)

Hi:

  I did the following after installing the pgstattuple contrib code:

 select * from pgstattuple('wind');
-[ RECORD 1 ]------+----------
table_len          | 224854016
tuple_count        | 1492601
tuple_len          | 207535124
tuple_percent      | 92.3
dead_tuple_count   | 11569
dead_tuple_len     | 1361848
dead_tuple_percent | 0.61
free_space         | 3311416
free_percent       | 1.47


vacuum verbose wind;
INFO:  vacuuming "public.wind"
INFO:  index "wind_pkey" now contains 1492601 row versions in 4152 pages
DETAIL:  11569 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.56s/1.77u sec elapsed 36.73 sec.
INFO:  index "wind_measurement_date_index" now contains 1492601 row 
versions in 4156 pages
DETAIL:  11569 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.43s/1.83u sec elapsed 35.90 sec.
INFO:  "wind": removed 11569 row versions in 371 pages
DETAIL:  CPU 0.03s/0.03u sec elapsed 2.38 sec.
INFO:  "wind": found 11569 removable, 1492601 nonremovable row versions 
in 27448 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 2.01s/3.95u sec elapsed 79.39 sec.
VACUUM

select * from pgstattuple('wind');
-[ RECORD 1 ]------+----------
table_len          | 224854016
tuple_count        | 1492601
tuple_len          | 207535124
tuple_percent      | 92.3
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 4673256
free_percent       | 2.08


My question: when you vacuum a table and generate 'free_space', who is 
allowed to consume this 'free_space'? Is it released to the OS for 
general use? Or is it reserved just for the database? If the latter, is 
it reserved just for 'wind' (in this case)?

Thanks again!






view thread (4+ messages)  latest in thread

Message-ID: <43860F20.5080709@skio.peachnet.edu>
Permalink:  ../43860F20.5080709@skio.peachnet.edu/
Also on:    postgresql.org/message-id/43860F20.5080709@skio.peachnet.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: smith@skio.peachnet.edu
  Subject: Re: pgstattuple, vacuum and free_space
  In-Reply-To: <43860F20.5080709@skio.peachnet.edu>
  References: <43860F20.5080709@skio.peachnet.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox