1

I want to be able to have one app access multiple databases on the HEROKU "system". Can the connection to the database be changed dynamically?

Why I ask...

I have an app that has a lot of very processor heavy background jobs. If a given user uploads a product feed of say 50,000 product that have to be compared to existing products and update only the deltas it can take a "few" minutes.

Now to mitigate the delay I spin up multiple workers, each taking small bites out of the lot until there's none. I can get to about 20 workers before the GUI starts to feel sluggish because the DB is being hammered.
I've tuned some of the code and indexed the DB to some extent, and I'm sure there's more I could do, but it will eventually suffer the law of diminished returns.

For one user, I don't much care... if you upload 50k products you need to wait a bit..

But user one's choice to upload impacts user two. (different company so no cross over of data)..

Currently I handle different users by separating their data with schemas in postgresql. The different users however share the same db connection and even on the best plan I can see a time when 20 users try to upload 50,000 products at the same time.(first of month/quarter for example). User 21 would see a huge slow down on their system because of this..

So the question: Can I assign different users to different databases? User logs in, validates their info against a central DB, and then a different DB takes over?

My current solution is different instances of heroku. It's easy to maintain the code because it's one base and I just script the git push(es). The only issue is the different login URL's; which I suppose I could confront if I can't find an easy DB switch solution.

Improve this question

2 Answers 2

Reset to default
1

It sounds like you're able to shard your data by user, or set of users without much concern since you already separate them by schema. If that's the case, and you're using Ruby and ActiveRecord, look at https://github.com/tchandy/octopus. I imagine you're not looking to spin up databases on the fly, rather, you'll have them already built and ready to be used, and can add more as you go.

Granted, it sounds like what you're doing could be done a lot more effectively by using the right tool for that type of intensive processing like one of the Heroku Hadoop add-ons; nonetheless, if that's not an option for whatever reason, check out the gem above. There are a couple other gems like it, and of course you could technically manage your own ActiveRecord connections without this gem, but I think you'll find that will be painful really fast.

Of course, if you aren't using Ruby or ActiveRecord, still shard the data, and look for something like the gem above in your app's language :).

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Just wondering, what add on would you suggest... My team has skirted the issue for the time by just having different apps on heroku, each with their own code and DB... to be honest it's what we all suggested before the project even started. I will still dig out the answer to this though.
There's only one Hadoop add-on available on Heroku, Treasure Data Hadoop; that isn't too hard to implement in. The nice thing about it is that it just uses data outputted to STDOUT rather than needing to access your DB. You can additionally use Amazon's Hadoop solution directly, it would end up being cheaper, won't be as seamless to get going. I imagine you're already using some queuing implementation to do all this asynchronously, like Resque. Maybe instead of processing all the data comparisons in the database, move that to an application layer that you can break up across several dynos.
0

the postgres databases on heroku are configured with environment variables. when you run heroku config you should see:

DATABASE_URL:   postgres://xxx.compute.amazonaws.com:5432/xxx

you can use these variables to connect to databases on other heroku instances or share a single database on different heroku apps.

if you try to run this kind of stuff on free heroku instances, i think it is against their terms of services.

if it's about scalability, i think you will just have to pay for a more expensive database instance...

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.