Thread: Running vacuum after delete does not remove all space allocated

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


 

---

Don't miss a beat Get Messenger on your phone

On Wed, Mar 31, 2010 at 12:44 PM, Chris Barnes
<compuguruchrisbarnes@hotmail.com> wrote:
> 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?

It's how PostgreSQL is designed.  It recycles the space at a later
time, after vacuum has reclaimed the empty space and it puts new data
from inserts and updates into the now free space.  More efficient then
allocating and de-allocating all the time, and lowers fragmentation.