1

I had several indexes that totaled ~1TB, according to pg_relation_size(). I dropped them.

I expected to get a TB of disk space back at the OS level, but this did not happen. Maybe 15GB total (I didn't note the precise number at the time).

I used the following query to determine the sizes which I pulled from the wiki (https://wiki.postgresql.org/wiki/Index_Maintenance):

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

I used the value in the "index_size" column. The size matched my expectations based on the fields in the index and the size of the table.

For example, the remaining index on one of the tables is for the primary key and is, according to that query, 147GB on a 448GB table containing 7 billion records. Given that there are only a few fields, this seems like a reasonable size to me.

It's not table data so a VACUUM FULL/pg_repack should not be necessary. lsof shows no open deleted files. I can't even address them at this point.

How do I reclaim this space? Why didn't it come back immediately?

1

Have you previously used pg_upgrade -k to get to this version, and then didn't delete the old version's cluster directory? If so, that old directory has hard links to the index data files which cause the underlying storage to be retained even when the link in the new version's directory gets unlinked.

  • Mate, you're awesome! Thank you very much! That was precisely it. 157GB free to 1.3TB free after removing the old cluster. – nrb Sep 8 '18 at 3:45
-1

I expected to get a TB of disk space back at the OS level, but this did not happen.

Why would expect that? Database != File.

Postgres, like most DBMSs, manages a whole load of files on your behalf and will only release space back to the operating system when it really, really has to (usually when you force it to).

Question: What is the most likely operation after dropping an Index?

Answer: Re-creating that Index (or something very like it).

Indexes are generally there to improve query performance so permanently getting rid of one would be unusual.

Because of this (and many other reasons) Postgres tends to "hoard" space for itself, on the assumption that it will use that space for something at some point. Reusing existing space is quick, as opposed to releasing space and later extending files at the operating system level, which is really slow, by database standards.

  • 1
    Your assumption is wrong. Each index maps to one (or more) files. So if you drop the index, the files should be removed immediately. Just like with a table. Postgres is not Oracle or SQL Server where everything is stored in one giant "tablespace file". – a_horse_with_no_name Sep 7 '18 at 10:11

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.