Running vacuum after delete does not remove all space allocated

From: Chris Barnes <compuguruchrisbarnes(at)hotmail(dot)com>
To: Postgres General Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Running vacuum after delete does not remove all space allocated
Date: 2010-03-31 18:44:20
Message-ID: BLU149-W12A8DA89A8DB49E61D34D5D41E0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I have deleted the rows in a table and vacuumed full, there appears to be space allocated that after a truncate it removes.

Why is this?

\d t8000_us_ts_size_test_2d
Table "dbprc001.t8000_us_ts_size_test_2d"
Column | Type | Modifiers
---------------+-----------------------+-----------
instrument_id | character varying(13) | not null
value | numeric(18,6)[] |
Indexes:
"t8000_us_ts_size_test_2d_pkey" PRIMARY KEY, btree (instrument_id)

pgdb001=# select pg_size_pretty(pg_total_relation_size('t8000_us_ts_size_test_2d'));
pg_size_pretty
----------------
13 MB

pgdb001=# delete from t8000_us_ts_size_test_2d;
DELETE 6

pgdb001=# vacuum full t8000_us_ts_size_test_2d;
VACUUM

pgdb001=# select pg_size_pretty(pg_total_relation_size('t8000_us_ts_size_test_2d'));
pg_size_pretty
----------------
12 MB

pgdb001=# truncate t8000_us_ts_size_test_2d;
TRUNCATE TABLE

pgdb001=# select pg_size_pretty(pg_total_relation_size('t8000_us_ts_size_test_2d'));
pg_size_pretty
----------------
16 kB



_________________________________________________________________
IM on the go with Messenger on your phone
http://go.microsoft.com/?linkid=9712960

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sylvain Lara 2010-03-31 19:26:24 Select in temporary table
Previous Message Raymond O'Donnell 2010-03-31 16:59:31 Re: prevent connection using pgpass.conf