Re: Recording insert, updates, and deletes

From: Richard Huxton <dev(at)archonet(dot)com>
To: Andy Dale <andy(dot)dale(at)gmail(dot)com>
Cc: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>, Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>, pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Recording insert, updates, and deletes
Date: 2007-01-11 10:24:41
Message-ID: 45A61069.5090205@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Andy Dale wrote:
> Hi,
>
> I turned on the stats_row_level in the postgresql.conf file and now the the
> calls to the stats functions work. I want to get the inserted, updated,
> and
> deleted numbers on a given database, so i have written a query to do so:
>
> SELECT
> sum(pg_stat_get_tuples_inserted(c.oid)) AS inserted,
> sum(pg_stat_get_tuples_updated(c.oid)) AS updated,
> sum(pg_stat_get_tuples_deleted(c.oid)) AS deleted
> FROM
> pg_class c, information_schema.tables i
> WHERE
> i.table_catalog = 'testdb' AND
> i.table_schema= 'public' AND
> i.table_name = c.relname
>
> I had to use the information schema as i could not figure out a way to
> fetch
> the tables of a particular database using only pg_* tables.

It's the pg_class table you'll want to start with. If you start psql
with -E and then do \dt you'll see the queries it uses.

> What i am
> really now concerned is reliability, is it possible that the stats can be
> incorrect ? and are they never reset ?. Also does using row level stats
> have a serious effect on the performance ?

Well, I'm not sure about incorrect. AFAIK the stats gatherer is "lossy",
so there's not a 100% guarantee that every read/write is measured.
Performance shouldn't be an issue unless you're already pushing the
limits of your hardware.

--
Richard Huxton
Archonet Ltd

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hannes Dorbath 2007-01-11 10:31:45 Cluster all tables in database to PK index
Previous Message Andy Dale 2007-01-11 10:18:50 Re: Recording insert, updates, and deletes