Thread: Vacuum statistics

I recall reading posts in the past where one could query the stat tables and
see how well autovacuum was performing. Not finding the posts.


I found this query:
SELECT relname, relkind, reltuples, relpages FROM pg_class where relkind =
'r';

From the output how can I tell the number of dead tuples? Or how effective
autovacuum is in the particular table..

Recently inheritted several large Postgresql DBs (tables in the hundreds of
millions and some tables over a billion rows) and I am just starting to go
over them and see how autovacuum has been performing.

What version of Postgres you are running ?

If you are using 8.3, you can use pg_stat_all_tables.If Not you can use http://www.postgresql.org/docs/current/static/pgstattuple.html

Chirag

chirag.dave@gmail.com writes:

> What version of Postgres you are running ?

8.2

> If you are using 8.3, you can use pg_stat_all_tables.If Not you can use
> <URL:http://www.postgresql.org/docs/current/static/pgstattuple.html>http://
> www.postgresql.org/docs/current/static/pgstattuple.html

pgstattuple is also a 8.3 function. Anything simmilar to it in 8.2?

Francisco Reyes wrote:
> chirag.dave@gmail.com writes:
>
>> What version of Postgres you are running ?
>
> 8.2
>
>> If you are using 8.3, you can use pg_stat_all_tables.If Not you can
>> use
>> <URL:http://www.postgresql.org/docs/current/static/pgstattuple.html>http://
>> www.postgresql.org/docs/current/static/pgstattuple.html
>
> pgstattuple is also a 8.3 function. Anything simmilar to it in 8.2?
>

It is available as a contrib module in 8.2, but needs to be installed
(see contrib/pgstattuple).

regards

Mark