Re: nooby Q: temp tables good for web apps?

Kenneth Tilton <kentilton(at)gmail(dot)com> writes:
> 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.

The main issue you should think about is whether the required lifespan
of the temp tables matches up with your application's use of database
connections. If you are going through a connection pooler, for example,
it can be pretty awkward to hold onto the connection that has got the
temp table instances you need. Web apps in general tend to have a hard
time maintaining such state across successive page references, so I'm
afraid this could be a show-stopper for you.

> 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

Yeah, whoever claimed that is simply uninformed, or at least is
well-informed about some other database.

regards, tom lane