X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 462239FA463 for ; Sun, 23 Jul 2006 07:56:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 46676-05 for ; Sun, 23 Jul 2006 10:56:06 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id 27BD09FA1E2 for ; Sun, 23 Jul 2006 07:56:05 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Unexplained growth of tables X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Sun, 23 Jul 2006 04:56:03 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Unexplained growth of tables Thread-Index: AcatC0N+t1fagnHLT162fFqhrMWKPABOGbEg From: "Benjamin Krajmalnik" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/177 X-Sequence-Number: 22423 This is subsequent to the previous issue which I am expperiencing. In my monitoring application, I have a few tables which contain few rows but are constantly pounded with updates. These tables are growing at very high rates. For example, a table with less than 4K rows, which when reloaded was about 8 Mb, is now 1.4GB! As these tables grow, the performance of the application - which only looks at these previously relativele small tables - is exremely slow. The huge tables are only used to calculate some statistical values on a nightly basis. I took another table which just started growing and ran analyze on it. This is the result: INFO: analyzing "public.tblkstests" INFO: "tblkstests": scanned 3000 of 81837 pages, containing 109 live rows and 10512 dead rows; 109 rows in sample, 2973 estimated total rows Total query runtime: 52702 ms. The actual number of physical rows in this table is 3404. Row width is 361. Table size =3D 639 MB. What can be causing this growth? Not vacuuming often enough? I hav pg_autovacuum running every 60 seconds. These tables have 10-15 insert/update statements per second. Any assistance or guidance will be deeply appreciated. I am pulling hairs on this one. >=20 > >=20