Hi ------- Original Message ------- On Tuesday, November 15th, 2022 at 12:38, Karthik Jagadish (kjagadis) <kjaga...@cisco.com> wrote:
> 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 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
