Vacuumdb --force-index-cleanup option not available in postgres 12.9

From: "Karthik Jagadish (kjagadis)" <kjagadis(at)cisco(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Cc: "Prasanna Satyanarayanan (prassaty)" <prassaty(at)cisco(dot)com>, "Chandruganth Ayyavoo Selvam (chaayyav)" <chaayyav(at)cisco(dot)com>
Subject: Vacuumdb --force-index-cleanup option not available in postgres 12.9
Date: 2022-11-15 14:45:37
Message-ID: IA1PR11MB61705AF334634DA8E220A976D8049@IA1PR11MB6170.namprd11.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

In the postgres document we notice that the --force-index-cleanup option is available in PostgreSQL server 12 and Later. We have postgres db running on 12.9 but we don’t see this option.

https://www.postgresql.org/docs/current/app-vacuumdb.html

[cid:image001(dot)png(at)01D8F92E(dot)779AB8B0]

[cid:image002(dot)png(at)01D8F92E(dot)928E97E0]

Is this option enabled by default? Any pointers here?

Also we notice that vacuum is happening at regular intervals but the space occupied by indexes is always increasing.

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2022-11-15 14:52:54 Re: Check return value of pclose() correctly
Previous Message Ranier Vilela 2022-11-15 14:42:34 Re: Avoid overhead open-close indexes (catalog updates)