Schemas and tables versus user-ids in a single table using PostgreSQL
- by gvkv
I'm developing a web app and I've come to a fork in the road with respect to database structure and I don't know which direction to take. I have a database with user information that I can structure one of two ways. The first is to create a schema and a set of tables for each user (duplicating the structure for each user) and the second is to create a single set of tables and query information based on user-id. Suppose 100000 users. Here are my questions:
Considering security, performance, scalability and administration where does each choice lie?
Would the answers change for 1000000 or 10000?
Is there a set of best practices that lead to one choice or the other?
It seems to me that multiple schemas are more secure since it's trivial to restrict user privileges but what about performance and scalability? Administration seems like a wash since dumping (and restoring) lots of schemas isn't any more difficult than dumping a few.