What Database Would You Use for User Defined Data?

Question

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, MongoDB only supports 64 indexes per collection. Ideally we should be able to dynamically create an arbitrary number of indexes based on common user query patterns. Now I am thinking that maybe using Postgres's JSONB type on a single table similarly sharded would make more sense. Or maybe I'm going about this the wrong way, and we should just create a regular Postgres table per user taking advantage of the fact that data definitions are strongly typed? I'm not sure how sharding would work though.Has anyone dealed with something similar, and has some advice?

edmundsauto · Accepted Answer

For me, it would be a bit of a red flag to have large number of indeces on a flexible schema where each customer uses their own schema, embedded in a single table (or collection). This could just be from my scarring at the hands of an ambitious EAV system I inherited.
Step back and ask yourself why you need an index on so many fields. If your cardinality is high, your index won't help much anyhow. Think about alternate approaches - what about using one table per customer? Or using an EAV to store the truth data, then project into a MATVIEW for optimized reading via index.
Try out a few options to get a sense for the design. Yes, it will take a few days to spam the database and run performance tests. However, because you are making a critical design decision and doing something less-than-standard, you need to accept the extra work - or figure out how to fit into a more standard pattern.