Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm running PostgreSQL 9.1. I have noticed really alarmingly bad indexes are taking up a lot of space on my server, e.g:

evlampts=# SELECT 
  pg_size_pretty(pg_relation_size('mcdata_2011_07')) AS relation_size, 
  pg_size_pretty(pg_total_relation_size('mcdata_2011_07')) AS total_relation_size, 
  pg_size_pretty(pg_indexes_size('mcdata_2011_07')) AS indexes_size;

 relation_size | total_relation_size | indexes_size
---------------+---------------------+--------------
 43 GB         | 100 GB              | 57 GB
(1 row)

I can then see that, while a good chunk of this is the primary key, I have 11 GB in one index and 16 in another:

evlampts=# SELECT
  pg_size_pretty(pg_relation_size('mcdata_2011_07_timestamp_idx')) AS timestamp_idx_size, 
  pg_size_pretty(pg_relation_size('mcdata_2011_07_host_idx')) AS host_idx_size;

 timestamp_idx_size | host_idx_size
--------------------+---------------
 11 GB              | 16 GB
(1 row)

If I then proceed to drop these indexes, I see no dramatic improvement in disk space in the OS:

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              1.8T  1.8T   32G  99% /export/home/lando

$ psql evlampts
evlampts=# drop index mcdata_2011_07_host_idx;
DROP INDEX
evlampts=# \q

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              1.8T  1.8T   32G  99% /export/home/lando

If the space was released to the OS, I'd expect there to be another 16 GB available. Is there a VACUUM step I'm missing? I have tuned autovacuum to run less frequently and may have gone too far, but I don't see anything in the documentation saying a VACUUM is necessary to free index space. What am I not doing I should be doing?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

The space should be freed as soon as the index is dropped. Possible explanations for what you show above are:

  1. The index is in a tablespace on a different filesystem from the one you're checking.
  2. The OS/filesystem has some lag in providing up-to-date free space information.
  3. Something else is eating the free space as soon as it becomes available.
  4. Something is holding open index files; lsof might be useful here.

If you run this query, you can see a little information about the index, including the filesystem number and the number used for filenames making up the index.

select oid::regclass, reltuples, relpages, reltablespace, relfilenode
  from pg_class
  where oid = 'mcdata_2011_07_host_idx'::regclass;

Look for files with filenames based on relfilenode both before and after dropping the index.

share|improve this answer
    
It's not #1. #2 is worth noting, because the filesystem is ext4 which apparently cannot report disk usage deterministically. #3 seems unlikely, there's nothing else really running on this box. I'm going to investigate #4. Thanks! –  Daniel Lyons Apr 23 '12 at 21:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.