Postgres Pro
Downloads
Home   >   mailing lists

Re: relpages of btree indexes are not truncating even after deletingall the tuples from table and doing vacuum - Mailing list pgsql-hackers

From Peter Geoghegan
Subject Re: relpages of btree indexes are not truncating even after deletingall the tuples from table and doing vacuum
Date
Msg-id CAH2-WzkwsnhE0KCc_otExpvQZBH9tvm1zifA4KvRLzpo7kKx+Q@mail.gmail.com
Whole thread Raw
In response to relpages of btree indexes are not truncating even after deleting allthe tuples from table and doing vacuum  (Mahendra Singh <mahi6run@gmail.com>)
Responses Re: relpages of btree indexes are not truncating even after deletingall the tuples from table and doing vacuum  (Mahendra Singh <mahi6run@gmail.com>)
List pgsql-hackers
Tree view 
On Mon, Dec 23, 2019 at 11:05 AM Mahendra Singh <mahi6run@gmail.com> wrote:
> From above example, we can see that after deleting all the tuples from table and firing vacuum command, size of table
isreduced but size of index relation is same as before vacuum.
 

VACUUM is only able to make existing empty pages in indexes recyclable
by future page splits within the same index. It is not possible for it
to reclaim space for the filesystem. Workload characteristics tend to
determine whether or not this limitation is truly important.

You can observe which pages are "free" in this sense (i.e. whether
they've been placed by the FSM for recycling) by using
contrib/pg_freespacemap.

-- 
Peter Geoghegan

pgsql-hackers by date:

Previous
From: Ashwin Agrawal
Date:
Subject: Re: Should we rename amapi.h and amapi.c?
Next
From: Tom Lane
Date:
Subject: Re: unsupportable composite type partition keys