I'm working on a system where each user defines a strongly typed definition of data they can store, consisting of classes with specified fields. Embedded classes are also supported. My first instinct was that NoSQL would be perfect for this, because we wouldn't have to worry about keeping track of changes to the data types. Because MongoDB only supports sharding based on indexes, I thought it would make sense to put everything in a single collection, sharded by user_id. However, we now have to implement sorting/filtering on the data. Ideally we want to dynamically create indexes based on common user query patterns. A single collection doesn't sound great for this, and MongoDB only supports 64 indexes per collection anyways.
Now I am thinking that maybe we should create a PostgreSQL table per user. The relational aspect of this will be helpful to expose queries across class relationships to users. However, I'm not sure how well a large number of tables will scale. I can't seem to find any resources related to sharding by table, vs. by a field.
Does the table per user make sense, or maybe there's another way to architecture this I haven't thought of? How would sharding work?