Tracking table modifications / table stats

From: Derrick Rice <derrick(dot)rice(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Tracking table modifications / table stats
Date: 2011-03-03 17:00:58
Message-ID: AANLkTikKofBG4PUQYU=Y6+-GhGoicvUwKjhpCMXdjWAw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey folks,

I was looking through the contrib modules with 8.4 and hoping to find
something that satisfies my itch.
http://www.postgresql.org/docs/8.4/static/pgstatstatements.html comes the
closest.

I'm inheriting a database which has mostly unknown usage patterns, and would
like to figure them out so that I can allocate tablespaces and set
autovacuum settings appropriately. To do this, it seems I need to know (at
least) the number of rows read, rows updated, rows deleted, and rows
inserted for each table (over time, or until reset).

I suppose things like disk usage and CPU usage would be interesting as well,
but I'm somewhat less concerned with those. For one, CPU usage can't be
tied to a table as easily and is more about query optimization than
PostgreSQL configuration (excluding cost coefficients and memory size
settings). For the other, disk usage can be mostly inferred from the row
size and and number of operations per table (this does exclude seq. scans
and heavy heavy index use, though). I realize those statements are fuzzy
and short-sighted, but I'm trying to get "good enough" information, not
optimize a space shuttle.

There's no way I'm the first person to feel the need for this. Is there a
doc or wiki which gives some recommendations? I'd like to avoid parsing
logs or installing triggers. I'd also like to avoid heavy statement-level
tracking like the above mentioned contrib does (sounds expensive, and I'm
not sure the users have parameterized SQL).

Thanks,

Derrick

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2011-03-03 17:22:14 Re: Why count(*) doest use index?
Previous Message Rob Sargent 2011-03-03 16:05:59 Re: opening connection more expensive than closing connection?