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

On Tue, Nov 15, 2022 at 02:45:37PM +0000, Karthik Jagadish (kjagadis) wrote:
> 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

Those are the docs for the current version (v15).

vacuumdb is a client, which can run against older (or newer) servers.

The --force-index-cleanup option was added in v14, and can be used
against servers back to v12.  But the command-line option to the
vacuumdb client doesn't exist before v14 (even though the server-side
supports it).

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

I don't know.  But this busy mailing list is for development; these
questions would be better addressed to pgsql-user.

-- 
Justin

On Tue, Nov 15, 2022 at 03:40:58PM +0000, Karthik Jagadish (kjagadis) wrote:
> Thanks Justin for prompt response. Could you please provide the full email for pgsql-user?
pgsql-user@postgresql.org<mailto:pgsql-user@postgresql.org>is not working
 

Of course, I intended to say pgsql-general@lists.postgresql.org

https://www.postgresql.org/list/

Hi

------- Original Message -------
On Tuesday, November 15th, 2022 at 12:38, Karthik Jagadish (kjagadis) <kjagadis@cisco.com> wrote:


> Hi,
>
> We notice that vacuum is happening at regular intervals but the space occupied by indexes is always increasing. Any
pointersas 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
frominformation_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

As far as I know vacuum just mark the space of dead rows available for future
reuse, so I think it's expected that the size doesn't decrease.


"The standard form of VACUUM removes dead row versions in tables and indexes
and marks the space available for future reuse. However, it will not return the
space to the operating system, except in the special case where one or more
pages at the end of a table become entirely free and an exclusive table lock
can be easily obtained. In contrast, VACUUM FULL actively compacts tables by
writing a complete new version of the table file with no dead space. This
minimizes the size of the table, but can take a long time. It also requires
extra disk space for the new copy of the table, until the operation completes."

https://www.postgresql.org/docs/current/routine-vacuuming.html




--
Matheus Alcantara