Index not getting cleaned even though vacuum is running

From: "Karthik Jagadish (kjagadis)" <kjagadis(at)cisco(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Cc: "Prasanna Satyanarayanan (prassaty)" <prassaty(at)cisco(dot)com>, "Chandruganth Ayyavoo Selvam (chaayyav)" <chaayyav(at)cisco(dot)com>
Subject: Index not getting cleaned even though vacuum is running
Date: 2022-11-15 15:46:06
Message-ID: IA1PR11MB6170FA9594909200DD9D6BF4D8049@IA1PR11MB6170.namprd11.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Frank Cazabon 2022-11-15 17:44:13 Calling function from VFP changes character field to Memo
Previous Message Tom Lane 2022-11-15 15:28:11 Re: PostgreSQL server "idle in transaction"