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.marlowe@xxxxxxxxx> > To: Kenneth Tilton <kentilton@xxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Sent: Tuesday, April 7, 2009 4:47:17 PM > Subject: Re: 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
