Re: Unusual table size and very slow inserts

On 02/02/10 14:46, Ivano Luberti wrote:
> Sorry to post this again, but I have seen no response at all and this is
> strange on this list.
> Maybe I have not properly submitted my question ?

You've replied to an existing question, which means your message is
hidden in amidst the replies to that.

> I wish also to add another parameter: the size problem is usually
> associated with the following log messages:
>
> 2010-02-02 00:00:14 GMTLOG:  checkpoints are occurring too frequently
> (15 seconds apart)
> 2010-02-02 00:00:14 GMTHINT:  Consider increasing the configuration
> parameter "checkpoint_segments".
>
> Where the number of seconds apart of course changes

Not directly related, although you might want to do as it says.

>
>
> 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.

That suggests the autovacuum system isn't checking the table often
enough. Or, perhaps that you have a long-lived transaction that is
preventing it from reclaiming space.

Autovacuum is disussed at the bottom of this page:
http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html
The "storage parameters" link has details on setting vacuum parameters
for a single table.

If your application is sat there holding open a transaction without
doing anything stop doing that. It means the system can't be sure it's
safe to reclaim the space used by old versions of rows.

--
   Richard Huxton
   Archonet Ltd