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

Hi Kenneth,

One concern I have with SSD drives is that the performance degrades over time. If you were not familiar with this issue already, take a look at the following article.

http://www.anandtech.com/storage/showdoc.aspx?i=3531

It is not a huge problem and I have faith in Intel to come up with a good solution fairly quickly, but it is worth noting. Given the cost of SSD, it does make me think that perhaps a more cost effective solution is to have plenty of RAM on the box.

----
John L. Cheng

----- Original Message ----
> From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> To: Kenneth Tilton <kentilton(at)gmail(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> Sent: Tuesday, April 7, 2009 4:47:17 PM
> Subject: Re: [GENERAL] nooby Q: temp tables good for web apps?
>
> On Tue, Apr 7, 2009 at 5:05 PM, Kenneth Tilton 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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general