I'm not him, but I've used the split schema pattern before. It helps a lot in a few cases. a) You want to provide a full export for the customer, you can dump the schema really simply. b) You don't want the serial columns to jump drastically between users. Like an Order ID. c) You want to upgrade customers on a rolling basis - you can apply migrations to each schema separately. d) You may move users to a dedicated server in the future, or shard them across more machines.
These can all be handled in other ways of course, but sometimes it makes it a lot simpler. -John 2014-11-25 10:04 GMT-08:00 Tim Davis <[email protected]>: > I'm really curious about why your data needs to be 'split correctly'. If > the table structures are the same, couldn't you include everything together > and just put Master/Userscheme info in WHERE clauses? > I guess I wasn't clear enough. > > 1) Each User has a Db (Master) containing schemes per user (UserScheme) > all containing the same type of tables, and datastructure. This is to make > sure all data is split correctly. > 2) Limiting the IP is dumb, in my opinion. An API is an API, should work > without applying limits in response to somewhat "broken" code. Hence the > question above regarding my code snippet, whether it was right or wrong. > > I rewrote most of it already and it's working much better now. I create > pools for each User/Scheme that when idled for too long (5+min) will be > destroyed. Everythings working flawlessly now so I guess this discussion > can be considered solved. > > Den tisdagen den 25:e november 2014 kl. 17:14:39 UTC+1 skrev svante > karlsson: >> >> Hmm, there are two things that feels funny here. >> >> 1) Each user has its own Database with a list of schemes >> 2) Spamming API requests would open up a massive amount of connection. >> >> What do you think postgres says about millions of databases? Normally you >> share your users in a single database and selects the correct "view" using >> a "where" clause. I think you should log in to your database once and use >> connection pooling. Put a HAProxy infront of your outward facing servers >> and limit the number of simultaneous connections per ip. >> >> /svante >> >> >> >> >> 2014-11-25 6:38 GMT+01:00 Alex Spencer <[email protected]>: >> >>> Thanks for the replies. The reason I built it this way was beacuse of >>> these reasons: >>> >>> - Each user has its own Database with a list of schemes. (IE new >>> connection every time, can't reuse a mass pool on a specific database) >>> - Spamming API requests would open up a massive amount of connections, >>> that are never killed off, resulting in the postgres instance to eventually >>> be smocked full. >>> >>> This is why I thought opening and closing was good. How would you >>> recommend handling the above issues? >>> >>> Den tisdagen den 25:e november 2014 kl. 01:36:44 UTC+1 skrev Alex >>> Kocharin: >>>> >>>> >>>> Opening and closing database connection per request is a very bad >>>> idea. Database connection should be opened permanently and re-used across >>>> many different requests, this way you save milliseconds establishing it. >>>> >>>> knex allows connection pooling. Use it. >>>> >>>> >>>> 25.11.2014, 01:09, "Alex Spencer" <[email protected]>: >>>> >>>> Hey. >>>> >>>> Need some advice on an api application built using: >>>> - express >>>> - knexjs >>>> - postgres >>>> >>>> My question is if im handling database operations correctly or if im >>>> doing it completely wrong.. See example >>>> >>>> var knex = require('knex'); >>>> app.get('/example/:param', >>>> loadWithParam(), >>>> updateWithParam(), >>>> render.staticOK() >>>> >>>> function loadWithParam() { >>>> return function(req, res, next) { >>>> var db = knex({conConfig}); >>>> db(tableName).where(field, req.params.param).select().then(function( >>>> rows) { >>>> db.destroy(function() { >>>> res.locals[tableName] = rows; >>>> next(); >>>> }); >>>> }).catch(function(err) { >>>> ... >>>> }); >>>> }; >>>> } >>>> >>>> function updateWithParam() { >>>> return function(req, res, next) { >>>> if(res.locals[tableName].length === 0) { >>>> return next(); >>>> } >>>> var db = knex({conConfig}); >>>> db(tableName).where(field, req.params.param).update({field: value}). >>>> then(function() { >>>> db.destroy(function() { >>>> next(); >>>> }); >>>> }).catch(function(err) { >>>> ... >>>> }); >>>> }; >>>> } >>>> >>>> >>>> In short my question is if its good or bad practise opening up and >>>> closing database connections per operation required to run? Ive been told >>>> setting up a database connection on res.locals upon receiving a request is >>>> better, but i disagree.. >>>> >>>> Thanks >>>> >>>> >>>> -- >>>> Job board: http://jobs.nodejs.org/ >>>> New group rules: https://gist.github.com/othiym23/9886289#file- >>>> moderation-policy-md >>>> Old group rules: 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 unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> To view this discussion on the web visit https://groups.google.com/d/ms >>>> gid/nodejs/408d3fd6-dd6c-4621-9030-99ff2e3fe7d6%40googlegroups.com >>>> <https://groups.google.com/d/msgid/nodejs/408d3fd6-dd6c-4621-9030-99ff2e3fe7d6%40googlegroups.com?utm_medium=email&utm_source=footer> >>>> . >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>>> -- >>> Job board: http://jobs.nodejs.org/ >>> New group rules: https://gist.github.com/othiym23/9886289#file- >>> moderation-policy-md >>> Old group rules: 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 unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To post to this group, send email to [email protected]. >>> To view this discussion on the web visit https://groups.google.com/d/ >>> msgid/nodejs/bb064078-9578-4082-b74a-284bac1092f5%40googlegroups.com >>> <https://groups.google.com/d/msgid/nodejs/bb064078-9578-4082-b74a-284bac1092f5%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- > Job board: http://jobs.nodejs.org/ > New group rules: > https://gist.github.com/othiym23/9886289#file-moderation-policy-md > Old group rules: > https://github.com/joyent/node/wiki/Mailing-List-Posting-Guidelines > --- > You received this message because you are subscribed to a topic in the > Google Groups "nodejs" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/nodejs/N9NUKgjbdko/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/nodejs/248de6f0-5589-499a-9bcf-39ad5218e8eb%40googlegroups.com > <https://groups.google.com/d/msgid/nodejs/248de6f0-5589-499a-9bcf-39ad5218e8eb%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > > -- > Job board: http://jobs.nodejs.org/ > New group rules: > https://gist.github.com/othiym23/9886289#file-moderation-policy-md > Old group rules: > 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 unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/nodejs/CAFDWy%2BtPPkHs70X3FnnfYt2584f2Nw2x0M9TChDzu4%3Dp0Vd3AA%40mail.gmail.com > <https://groups.google.com/d/msgid/nodejs/CAFDWy%2BtPPkHs70X3FnnfYt2584f2Nw2x0M9TChDzu4%3Dp0Vd3AA%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > For more options, visit https://groups.google.com/d/optout. > -- John R. Fitzgerald -- Job board: http://jobs.nodejs.org/ New group rules: https://gist.github.com/othiym23/9886289#file-moderation-policy-md Old group rules: 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 unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/nodejs/CAAsAE971RDcrPNad5wLJ3-ftf19uheE1ZU2GyRbOJ-oSJyOf2g%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
