Postgresql – Postgres 10 not returning disk space after dropping indexes

postgresqlpostgresql-10

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?

Best Answer

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.