Обсуждение: One Large Table or Multiple DBs?
Hi, What is efficient approach toward storing a web application's user data. How do applications such as basecamp, SalesForce or QuickBooks online store their data? Is it in one DB with huge tables each record having a user account's foreign key or do they create a separate database for each of their accounts? Which one is more efficient? My guess was in one large DB with large tables. Thank you, Mike
On 07/09/07 16:18, Mike wrote: > Hi, > > What is efficient approach toward storing a web application's user > data. How do applications such as basecamp, SalesForce or QuickBooks > online store their data? Is it in one DB with huge tables each record > having a user account's foreign key or do they create a separate > database for each of their accounts? Which one is more efficient? My > guess was in one large DB with large tables. How big is "big"? What is "efficient"? Speed, management, upgrades, backups, scalability? If each customer has 80GB of data, then separate databases are the way to go, since it eases scalability and allows for parallel backups. But then upgrades must be applied to each of thousands of databases. If each customer has 100MB of data, then unified tables keyed off of account number would be simpler. In between is schema-per-account. But upgrades are still a chore. -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good!
"Mike" <akiany@gmail.com> writes: > Hi, > > What is efficient approach toward storing a web application's user > data. I would recommend one large DB with large tables. You might consider using partitioning to actually store the data separately. But even then I would not consider it until it was actually a problem. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
On Monday 09 July 2007 17:18, Mike wrote: > Hi, > > What is efficient approach toward storing a web application's user > data. How do applications such as basecamp, SalesForce or QuickBooks > online store their data? Is it in one DB with huge tables each record > having a user account's foreign key or do they create a separate > database for each of their accounts? Which one is more efficient? My > guess was in one large DB with large tables. > Well, generally I'd say you want to try and scale vertically as far as you can, because database (especially postgresql) scale vertically fairly well, and trying to spread a database across multiple servers introduces several different kins of complexity and other issues. Thats said, most "as a service" solutions try to segment thier database in a way that gives individual customers or groups of customers onto thier own databases/servers, often with the result of shooting themselves in the foot when they realize that the obvious segmenting key doesn't lead to balanced loads (ie. if you have two boxes, and you segment your largest customer to thier own box, but they are 75% of your buisness, your solution doesn't scale well). -- Robert Treat Database Architect http://www.omniti.com/