I am porting a datamining web app to postgres from a non-sql datastore
and plan to use temporary tables quite a bit, to manage collections the
user will be massaging interactively. They might search and find
anywhere from 50 to 50k items, then filter that, unfilter, sort, etc.
Currently I manage those collections in the server application, meaning
everything gets pulled from the datastore into RAM. I see postgres
temporary tables and postgres features in general can greatly simplify
my code because so much of what I do can be expressedin postgres-ese. Yayyy.
Some on the team think I am nuts, but one reason given was the absence
of indices and I see (a) temporary tables *can* be indexed and (b)
postgres does not even use an index for small sets, and many collections
will be relatively small (as a design goal in fact--we hope to make
search smarter and return fewer hits).
I thought it would not hurt to check with the gurus before spending a
week on the wrong code, so... dumb idea?
kenny