Index not getting cleaned even though vacuum is running - Mailing list pgsql-hackers

From Karthik Jagadish (kjagadis)
Subject Index not getting cleaned even though vacuum is running
Date
Msg-id IA1PR11MB6170DCAF11B76E01972775A8D8049@IA1PR11MB6170.namprd11.prod.outlook.com
Whole thread Raw
In response to Vacuumdb --force-index-cleanup option not available in postgres 12.9  ("Karthik Jagadish (kjagadis)" <kjagadis@cisco.com>)
Responses Re: Index not getting cleaned even though vacuum is running
List pgsql-hackers

Hi,

 

We notice that vacuum is happening at regular intervals but the space occupied by indexes is always increasing. Any pointers as to why would this happen?

 

Some outputs below. Auto vacuum is enabled but we notice index size is growing.

 

$ psql -U postgres -d cgms -c "SELECT pg_size_pretty(SUM(pg_relation_size(table_schema||'.'||table_name))) as size from information_schema.tables"

 

size

-------

25 GB

(1 row)

 

$ psql -U postgres -d cgms -c "SELECT pg_size_pretty(SUM(pg_indexes_size(table_schema||'.'||table_name) + pg_relation_size(table_schema||'.'||table_name))) as size from information_schema.tables"

  size

--------

151 GB

(1 row)

 

$ sudo du -hsc /var/lib/pgsql/12/data

154G    /var/lib/pgsql/12/data

154G    total

 

Appreciate if someone can give some pointers.

 

Regards,

Karthik

pgsql-hackers by date:

Previous
From: Justin Pryzby
Date:
Subject: Re: Vacuumdb --force-index-cleanup option not available in postgres 12.9
Next
From: "Karthik Jagadish (kjagadis)"
Date:
Subject: Re: Vacuumdb --force-index-cleanup option not available in postgres 12.9