One thing I found with SQL engines that feature a competent query optimizer (being SQL Server, Postgres, Sybase and Oracle) is that it's usually good to offload as much work to SQL as possible - it let's the optimizer figure out a lot more for you (having meaningful indexes, constraints and relationships also gives it valuable hints - they are not only there for data integrity). Don't transfer your MySQL knowledge to Postgres; they are nothing alike.
If you are worried about network chattiness (a valid concern), remember that you can have multiple select statements in one query. I'm not sure about the specific node APIs that you would use to move between the result sets, but I am sure they are there. Finally the only way to really figure out the nuances of Postgres would be to profile. Try both methods and see which is faster, and remember that it can change as more (and differently natured) data enters the database. On Dec 26, 2012 11:30 AM, "Ted Young" <[email protected]> wrote: > Hi Ryan, > > Really the best thing to do is get a refresher on SQL. Almost nothing > about the client being in node is relevant to performance, and programming > well in sql can't really be summed up into a set of handy tips, and more > that programming in javascript or c could be. That said, some handy tips :) > > 1. remember that you are programming in sql. That gets lost pretty > easily. > 2. basic performance tuning usually comes down to indexing, to prevent > your query from having to look at a large number of rows that aren't in > your query. So it's not that simpler queries are faster (if it's not > indexed, it will have to look at all the rows, no matter how "simple" it > is) so much that simpler queries are easier to index. > 3. Learn to use EXPLAIN and other analysis tools. It's pretty had to > guess about what's making something slow, especially if you are new at it. > I get gobsmacked on a regular basis. > > So you are (probably) on the right track with multiple simpler queries, > but it still really comes down to understanding wtf is actually happening. > I would also recommend getting in touch with a postgres community, you > will get better help there as really you will be having sql questions, not > node questions, unless it's specifically a driver issue. > > Cheers, > Ted > > On Dec 24, 2012, at 7:39 PM, Ryan Schmidt <[email protected]> > wrote: > > I'm writing a web site with nodejs 0.8, express 3, jade, and a PostgreSQL > 9 database. I had wanted to use a nosql database like mongodb which seemed > like a natural fit, but I have been asked to use postgres because we > already run a postgres server. I've used MySQL years ago but hadn't used > postgres and am now learning about its capabilities. > > For a particular page, I need to pull a lot of little bits of information > from the database: the document itself; information about all the tags > applied to the document; information about all of the document's authors; > information about all of the licenses under which the document is > available; information about a few different types of related documents. At > the moment I'm using a single complicated query which uses views, common > table expressions, sub-selects, array columns... It works, and is fast on > my development machine, but I'm unsure if it's the best strategy or how it > will perform under load. Particularly, I'm worried that as I want to add > even more information to the page, statements like this will become slower > and also more difficult for other fellow developers to understand. I'm > already having to use some constructs that seem weird to me, like "SELECT > array_to_json(ARRAY(SELECT ...)) ...", and then some post-processing in > JavaScript to put the final result object together. > > Is there any common wisdom about whether it would be better / faster / > more efficient to instead issue several smaller queries? I did not > initially use that strategy because I thought it would be best to minimize > the number of queries to avoid unnecessary overhead associated with each > query, but now that I think about it, with node I would be issuing all of > the queries at the about same time, and as the results from the simpler > queries come in, I could already begin processing them, instead of having > to wait for the whole result. And if the database server is multicore then > wouldn't several smaller queries put those cores to better use? > > I guess I'm looking for real world anecdotes and experience from others > who have already built node apps using SQL databases. Thanks and happy > holidays. > > > -- > Job Board: http://jobs.nodejs.org/ > Posting guidelines: > https://github.com/joyent/node/wiki/Mailing-List-Posting-Guidelines > You received this message because you are subscribed to the Google > Groups "nodejs" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/nodejs?hl=en?hl=en > > > -- > Job Board: http://jobs.nodejs.org/ > Posting guidelines: > https://github.com/joyent/node/wiki/Mailing-List-Posting-Guidelines > You received this message because you are subscribed to the Google > Groups "nodejs" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/nodejs?hl=en?hl=en > -- Job Board: http://jobs.nodejs.org/ Posting guidelines: https://github.com/joyent/node/wiki/Mailing-List-Posting-Guidelines You received this message because you are subscribed to the Google Groups "nodejs" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nodejs?hl=en?hl=en
