Schemas and tables versus user-ids in a single table using PostgreSQL

Question

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 design 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.

1. Considering security, performance, scalability and administration where does each choice lie?
2. Would the answers change for 1000000 or 10000?
3. 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.

Answer 1

Separate schemas will give you more isolation and potentially easier security, because you can then assign a separate db role to each sschema and make sure access for one customer can't access that for another.

Separate schemas won't actually work if you have 100,000 users - if that would mean you'd have 100,000 schemas. Well, it would work, but the system is not designed to deal with that many schemas, and you'd likely have performance issues. So if you have that many users, you need to do it the other way, really.