Dropping indexes doesn't seem to release space to the OS. Can it?

Question

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?

Answer 1

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.