(Apologies for thread-jacking; I saw something similar in the OP's description of his setup that I thought warranted a word of advice.) On Mon, Mar 14, 2011 at 1:50 AM, Gnanakumar <gnanam@xxxxxxxxxx> wrote: > Do you think that this "significantly bloated system catalogs" is caused > because of the old version 8.2.3 we're running? Will this be resolved in > latest PostgreSQL v9.0? BTW, what version of PostgreSQL are you handling? This isn't a version-specific issue; it's a consequence of MVCC. (See below for more details.) We're using 8.3 for this application, but we'd encounter the problem whatever version we were using. I'm working on getting it migrated to 8.4, but it's a multi-tbyte database, and we don't currently have space available on the SAN for an upgrade. 9.0 has a new VACUUM FULL which behaves more like CLUSTER. That may help to shorten the downtime windows we need to take, but it won't eliminate them. The only thing that will is changing the application to make less pathological use of temporary tables. > If temporary tables and its indexes are automatically dropped at the end of > the session, why would it cause bloat in system catalogs? The key to understanding the problem is to realize that system catalogs are pretty much regular tables in PostgreSQL, with some additional special rules (no triggers, can't be CLUSTERed or ALTERed, &c). Anything you'd do in a regular table that would create a dead tuple — UPDATEs, DELETEs, &c — will have the same effect on a catalog. A CREATE TABLE is an INSERT into pg_class for the table, plus one for each of any indexes you may create, and so on. It's also an INSERT into pg_attribute for each column the table has, including the "housekeeping" columns postgres uses, mostly to manage MVCC. DROPping a table is a DELETE from those catalogs, creating the corresponding number of dead tuples. Implicitly dropped temp tables still cause dead tuples in the catalogs; it's just postgres dropping them for you at commit time, rather than you issuing the DROP command explicitly. If you're just using a few temp tables, you shouldn't be experiencing the kinds of problems we are, particularly if you have autovacuum enabled. You'll have dead tuples, but they should quickly reach a steady-state, where the space consumed by them is re-used by a later creation of those temp tables. Our situation is probably somewhat unique, because of the sheer number of temp tables the application uses. I hope that helps you understand better. rls -- :wq -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
