I am seeking advice on what the best setup for the following would be.
My company provides a hosted web calendaring solution for school districts. For each school district we have a separate database. Each database has 57 tables. There are a total of 649 fields in those tables. Here is a table of the different kinds of field and how many there are:
time without time zone bytea date smallint boolean integer timestamp without time zone numeric text | 9 4 8 1 79 195 36 8 309 |
Over the next couple of months we will be creating an instance of our solution for each public school district in the US which is around 18,000. That means currently we would be creating 18,000 databases (all on one server right now – which is running 8.4). I am assuming this is probably not the best way of doing things.
I have read up on schemas and it looks like a good change to make would be to create 1 database with 18,000 schemas.
Would that be a good idea? What sort of issues should I be aware of (administrative, management, performance, etc…)? Is that too many schemas to put into 1 database? What are the limits on the number of databases and schemas you can create?
Should I try to re-engineer things so that all 18,000 instances only use 1 database and 1 schema?
Let me know if you need any more info.
Any advice and information would be greatly appreciated.
Regards,
Scott Otis
CIO / Lead Developer
Intand
www.intand.com