I would like to know which technique is better for supporting multi-tenancy= applications, going upto hundreds or even thousands of tenants.
1 - One database with difference schemas (one schema per tenant) or 2 - One database per tenant.
The points to be considered are:
1 - which is more light weight from resources point of view.
Multiple databases used to be more resource intensive, but that was mostly fixed in 9.3. Now I don't believe there is all that much difference while running (databases are much more heavy to create in the first place).
As others have said, different databases makes connection pooling less efficient, which could be very important to you, or could be irrelevant.
2 - which is easier for backup/restore
That depends on how you want to backup restore. If you use log archiving, it will make no difference. If you use sql dumps, then do you want to make it easier or harder to backup and restore all clients together, or all of them separately? Are you restoring because your whole system failed, or because one client did something wrong and needs just their data rolled back?
3 - Which is better from security p.o.v
Any implementation can be done wrong in a way that causes security problems. Do your clients authenticate directly to the database, or to the app server?
