13

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.

7

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.

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).
    – Juanin
    Sep 10 '12 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)

4
  • 2
    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. 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
  • 1
    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. Sep 10 '12 at 18:48
  • Wal Archiving for a backup would make sense here.
    – Jharwood
    Sep 24 '12 at 11:59
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.....

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?
    – Juanin
    Sep 25 '12 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. Sep 25 '12 at 9:17
1

I hope this link is better to read: 10,000 databases on a PostgreSQL cluster by Jon Jensen, 2008.

One excerpt:

The short answer: Postgres 8.1 handles 10,000 databases just fine. \l in psql generates a long list of databases, of course, but returns quickly enough. Ad-hoc concurrency testing was fine. Running queries, inserts, etc. on a handpicked group of the various play databases worked fine, including while new databases were being created.

[...]

The actual limit on this [Linux ext3] platform is probably 31995 databases, because each database occupies a subdirectory in data/base/ and the ext3 filesystem has a limit of 31998 sub-directories per one directory, stemming from its limit of 32000 links per inode.

1
  • 1
    Answers that only contain links are not very useful, because links tend to go stale over time. Please consider adding the summary of whatever you're linking to in your answer.
    – mustaccio
    Feb 12 '17 at 17:45
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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