15

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.

Improve this question
1
  • Every layer of indirection requires another layer of abstraction. I work in a multi-tenant environment. Everything task-wise will become reliant on the necessity of duplication. The work pattern is basically if you can make it work against one, then it will work against all. The database size you posted is small but don't become complacent on that. I think you should treat it as if every database starts off at 40 GB and has 600+ tables. Commented Mar 22 at 1:44

4 Answers 4

Reset to default
9

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.

Improve this answer
1
  • 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). Commented Sep 10, 2012 at 4:42
2

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)

Improve this answer
1
  • 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? Commented Sep 10, 2012 at 4:45
2

TLDR; No limits if your database is on Linux using ext4, 31995 if it's using ext3. For Window on NFTS: 4,294,967,295. Mac users: 2.1 billion

Expanding on the answer:

Every Postgres database uses a subdirectory on the disk, hence the limitation. You will probably run out of IOPS before you hit a database count bottleneck. Unless you are on ext3, which is limited to 32k subdirectory, ext4 lets you have unlimited subdirectories.

Other answers are worried about management hell because the question itself is old, and the world has changed a lot.

Backups

  • If you are using a cloud solution, this is solved. Most cloud providers perform backups at the disk level, so the amount of databases is irrelevant.
  • If you are using an on-premises solution, you will need to loop through each database, backing up everything. Yeah, no biggie.

Multi-tenant security

  • Having each client using a different database:

pros:

  • each client can be on a different version of the software
  • each client can have custom tables
  • client access is moved from your software to Postgres
  • clients can be moved from database servers easily

cons:

  • monitoring scripts have to loop through each database
  • database migrations, indexes, maintenance has to be performed on each database

neutral:

  • less code on your sass, more code on your ops
Improve this answer
1

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.....

Improve this answer
2
  • 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? Commented Sep 25, 2012 at 8:13
  • 1
    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. Commented Sep 25, 2012 at 9:17

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.