Ok I definitely food for thought and that is what I was searching for, as stated in my first message. Thanks to Richard and Bill for that. But I have a few things that still I don't understand and I think I had not outlined enough. > > >> What really worries and puzzles me is the size of the table is not >> coherent with other copies of the same table with similar records number. >> > > Have you read the section on how MVCC and autovacuum work? The size of > the table is not a function of the # of records. It's the # of records, > plus the number of "dead rows" (which are records that have been deleted > or updated and thus replaced with new rows). > > I was not assuming the proportion between size and number of records. I was simply comparing the size of the table that troubles me with the size of other identical tables that have similar usage but are in other schemas inside the same DB. > The key is that autovacuum runs often enough that the size stabalizes > based on usage. For example, if you have a table that usually has 500 > rows in it and it's common for all the rows to be deleted and replaced, > then you can expect the table to _always_ be big enough to hold 1000 > rows. However, if all the rows are updated 5 times between each vacuum > run, the table will be big enough to hold 2500 rows most of the time. > Again, this is typical, it's how the system is designed to run. > In this table and all her sisters in the other schemas, records are only inserted and deleted. No update > I'm guessing (although a lot of the original email has been trimmed) that > the actual problem you're having is that autovacuum is taking too long, > and is slowing down modifications to table data. > > This is what I was thinking , but today I was able to look at the processes running while a client was doing a bunc of inserts. There was no autovacuum running and every insert was taking many seconds to e executed. Moreover every insert seemed to increase a lot the size of the table. Much more than the size of a record and the related index Then after the insert autovacuum started and it took a lot of time to complete. So the following suggestions certainly have interest to me. By the way we are running 8.4.2 on Windows 2003 server, but the same problem ha occurred on Windows (S)vista and Windows 7. So no chance to upgrade :-). > If autovacuum is taking too long and is slowing down other operations, > you have a few options (you can do one or many of these): > * Run autovacuum more frequently. > * Modify your application so it modifies less rows (yes, sometimes this > is possible and the best solution. The fact that the table blows > up to 180x the minimum size is a hint that you may be updating > very inefficiently.) > * Tune PostgreSQL to make more efficient use of RAM for caching (which > will speed everything up) > * Get faster hardware > * Upgrade to a newer version of PostgreSQL that has more efficient > vacuum code (if you mentioned which version you are using, it was > lost when the message was trimmed) > > -- ================================================== 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 ================================================== -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
