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

On Tue, Apr 7, 2009 at 3:11 PM, Kenneth Tilton <kentilton(at)gmail(dot)com> wrote:
> 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,

People are often resistant to new ideas, even good ones.

> but one reason given was the absence of
> indices and I see (a) temporary tables *can* be indexed

Correct

> 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).

Correct again.

> I thought it would not hurt to check with the gurus before spending a week
> on the wrong code, so... dumb idea?

Good idea to at least come up with some tests to prove (or disprove)
your point. We can wait for the gurus to check in later...