Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

Developing a multicustomer application we plan to use a different database for each customer. But it could be more than 1000 customers (applications).

Will PostgreSQL handle it without any problems?

Has anybody tried something similar?

Note: 35 tables for each one, with up to 3000 records as an average, for each database.

share|improve this question
add comment (requires an account with 50 reputation)

3 Answers

up vote 1 down vote

Sounds like a messy thing to do from a management point of view. Just how do you plan to backup that many databases? with a script that loops though each one?

Unless you have a really good reason, why not just have one database where the structure is designed so that all the data will link to back to a customer ID. Add Indexes/Foreign Key/Primary Keys based on this field which will ensure data integrity.

Then you just need to have a where clause in all your queries to access only one customer ID. This will be much simpler to maintain and is just as easy to develop (cause in either case you need to allow for the customer identification)

share|improve this answer
This is a very good and valid point. Maybe the OP should think about using schemas, and only have a few tables in the public schema, available for JOINing with tables in the private customer's schema. – François Beausoleil Sep 10 '12 at 0:30
Thanks, but this option was discarded from the very beginning. This is a port of a already developped applicacion, and change ALL the code its not so trivial at this stage. But yes, daily management for more than 100 databases will be... interesting...isn't it? – Juanin Sep 10 '12 at 4:45
Going from separate databases to separate schemas shouldn't imply any significant change in code. In particular you don't need to prefix the objects with their schemas because search_path does it for you. – Daniel Vérité Sep 10 '12 at 18:48
Wal Archiving for a backup would make sense here. – Jharwood Sep 24 '12 at 11:59
add comment (requires an account with 50 reputation)
up vote 1 down vote

I haven't tried it myself, but there are others around who have. Here you can see that even 10,000 databases run without problem on a single instance. You can even find some pratical aspects on ServerFault as well.

Since your databases are quite small, you will not run into any sort of file number limitation of the host OS. The only problem I can think of is that when all these databases will be accessed concurrently, handling all those connections will be tricky.

And, as a last note: you are very welcome on this site. We hope you will remain with us for a long time.

share|improve this answer
Thanks for your comments. Yes, concurrent connectios could be a headache, but the other option is a shared table for each applicacion, incredible more complex (need reprograming for the app). – Juanin Sep 10 '12 at 4:42
add comment (requires an account with 50 reputation)
up vote 0 down vote

There are people who do this, particularly for shared server hosting.

Thinking through the issues here there is no free lunch. You could probably do it with schemas in an application transparent way. However then you get to thousands of schemas and tens of thousands of tables, which will pose additional problems.

I think on the whole, the multiple db approach is sanest given your comments.

Management (like backups) will become interesting. Also I would think that at some point connections to the db will start to take longer. If you are using pg_hba.conf to restrict access (which you shoudl be doing) that will become a headache too and you will probably want to build a solution to generate that file for you.....

share|improve this answer
I cann't see the problem with pg_hba.conf. Our app uses Ruby on Rails and it switchets connections for differents databases, but in the same linux box all the time. are talking about concurrency problems accessing the file? – Juanin Sep 25 '12 at 8:13
No, just if you want to manage which dbs can be accessed by which hosts, it will become a long file and management may become a bit annoying. – Chris Travers Sep 25 '12 at 9:17
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.