Search Postgresql Archives

Re: Autovacuum Problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




However no space seems to be freed to the system.

Is there any way a bloody newbie can debug this behaviour?

In our experience, autovacuum is able to contain bloating of table data, but not bloating of indexes.

You could see where the bloating is by running the following queries:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

select c.relname, (pgstattuple(c.relname)).*
from pg_class c
join pg_namespace n on (n.oid = c.relnamespace and n.nspname = 'public')
where c.relkind = 'r'
order by c.reltuples desc;

select c.relname, (pgstatindex(c.relname)).*
from pg_class c
inner join pg_namespace n on (n.oid = c.relnamespace and n.nspname = 'public')
where c.relkind = 'i'
order by c.reltuples desc;


The first SELECT query gives some information about table data, the second SELECT query is about indexes. The queries will take quite some time to run, because they will have to scan through every data page on disk. Look at the following columns:

pgstattuple.free_percent - percent of table data which is allocated on disk, but does not currently contain any actual data (describes table data bloat).

pgstatindex.avg_leaf_density - percent of stored leaf index data within the totally allocated on disk (describes index bloat). It's 90% by default. It's not good when it drops too much (e.g. to 50% or 10%).

Look at the biggest tables/indexes first. In our case, from 1000+ tables there are only few which contribute most to DB size bloating. But in general that would depend on your specific case, and the write patterns which you application does. You can then do VACUUM FULL for those specific tables, or REINDEX TABLE/INDEX for specific tables/indexes. VACUUM FULL or REINDEX TABLE may work faster than doing REINDEX INDEX for several indexes of the same table. I do not know any efficient way to prevent bloating of indexes.

Regards,
Vitaliy






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux