Unusual table size and very slow inserts

From: Ivano Luberti <luberti(at)archicoop(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Unusual table size and very slow inserts
Date: 2010-02-01 11:01:00
Message-ID: 4B66B46C.6060003@archicoop.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello, I have a software that uses Posgtres 8.4.2 on Windows.
I have a database with data splitted into schemas, so that every schema
replicates the same set of tables.
One of the table is called "code": it has 16 columns, almos all numerics
except for a carachtervarying(1024) and two text fields. It holds
usually a few thousands record at most, then the file size of the table
is usually around few hundred kbytes.

In only one case so far, the "code" table with 442 record has a size of
18MB. If I run an vacuum full and a reindex it shrinks to less than 100KB.
If I use the software to delete the rows and reinsert the same records
it explodes again to 18MB.

I have backed up the table (from a WIndows7 instance running on a
virtual machine) and recovered it on another database (running on
a"real" Windows Vista) and tried the same things with the same results.

Then I have dropped the table, recreated it and reinserted the records:
the anomaly has disappeared.

I really don't know what to look for.
I cannot ignore the problem because schemas are created by the software
on software users request and it could reappear in any schema.

I really don't have a clue: I would be happy to further study the
problem but I don't know in which direction I have to go. Can someone
point me in some (hopefully good) direction?

--
==================================================
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==================================================

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Leo Mannhart 2010-02-01 11:17:19 Re: combine SQL SELECT statements into one
Previous Message ray joseph 2010-02-01 10:48:08 Re: How to test my new install