Re: Temporary tables and disk activity

Phil Endecott <spam_from_postgresql_general(at)chezphil(dot)org> writes:
> Does this make sense? I imagine that the temporary table is being added
> to these tables and then removed again.

Yes, a temp table has the same catalog infrastructure as a regular
table, so creation and deletion of a temp table will cause some activity
in those catalogs. I thought you were concerned about the data within
the temp table, though.

> I do have quite a large number of tables in the database; I have one
> schema per user and of the order of 20 tables per user and 200 users. I
> can imagine that in a system with fewer tables this would be
> insignificant, yet in my case it seems to be writing of the order of a
> megabyte in each 5-second update.

That seems like a lot. How often do you create/delete temp tables?

> I should mention that I ANALYSE the temporary table after creating it
> and before using it for anything; I'm not sure if this does any good
> but I put it in as it "couldn't do any harm".

This is a good idea (if you analyze after filling the table) ... but it
will cause catalog traffic too, because again the pg_statistic rows go
into the regular pg_statistic catalog.

regards, tom lane