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

On Tue, Apr 7, 2009 at 5:05 PM, Kenneth Tilton <kentilton(at)gmail(dot)com> wrote:
>
>
> Greg Smith wrote:
>>
>> Temp tables can be great for simplifying your code into more logical
>> sections.  When making a case for using them, make sure to point out that
>> using them more aggressively can cut down on the amount of indexing you need
>> on the big tables, which has positive implications in terms of getting
>> simpler and robust query plans and cutting down on insertion overhead.
>>
>> You should be sure to turn on log_temp_files (which is handy in general,
>> that's not specific to temp tables).  One specific thing to look for to
>> support your case is that sorts that used to execute in RAM and spill to
>> disk when they exceed work_mem might instead execute with less memory usage;
>> you'll be doing the final sort/filter steps using the temp tables instead.
>>  If that is already happening, the overhead of using the temp table can end
>> up looking pretty good.
>>
>> One thing I like doing when in the early development stages is to create a
>> seperate disk partition for the temporary tables, turn that into a
>> tablespace, and then use temp_tablespaces to point the temp tables toward
>> it.  The idea is to separate out I/O to the temp tables so that you can
>> measure it to see how significant it is.
>
> Thx, I will keep that in mind as a good way of really seeing what is going
> on. I did notice the tablespace feature but wasn't sure how to leverage it.
> Mgmt has been lusting after those new solid-state memory disks (SSDs?), this
> could be a good excuse for a PO. We are a skunkworks project getting as much
> praise so far for the speed of the web app as anything else so we don't want
> to give up this plus.

Make sure the newer generation like Intel's that are fast under
concurrent access. Most of the older SSDs are horrificall slow when
handling multiple random accesses.

You can use a different method if you need a table available to the
same session. Create a schema based on the session id, and put your
temp tables there, only don't call them temp tables. You'll either
need to make sure you always clean up your temp schema your session
created or come up with a daemon that comes along every hour or so and
kills off old schemas that aren't in use anymore.