I've always avoided using temporary tables for OLTP applications because I was
afraid the rapid creating and deleting of temporary data would limit
scalability.
Now I'm finding they might be necessary for a particular problem so I'm
looking for information on how efficient they are with Postgres. In my case
it's being used for a set of purely read-only queries for performance reasons.
I won't be updating the temporary table at all once created.
Will postgres write out the data to disk or just store it in local memory in
the single backend using it? Will it be able to handle lots of users
simultaneously creating and destroying these temporary tables without
performance degrading?
I'm only looking at using them because I have four separate queries based on a
data set that is the result of aggregating a much larger data set. The only
reason to use the temporary tables is for performance reasons, so if it won't
help over rerunning the slow aggregate subquery four times then, well, I have
a problem, because that would be too slow.
Incidentally, temporary tables don't seem to appear anywhere in the online
docs, and are only mentioned briefly in my book (yes, the same book). Is there
any way to control when the temporary tables are destroyed other than at the
end of the session? Is there any way to tell Postgres to destroy them at the
end of a transaction or do i assume i have to do it manually? Do I use drop
table normally?
--
greg