2

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?

6

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.

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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