all in one multi-tenant database?

you have a ceiling on idents, 32k items if i recall

I was working on an application a couple months ago that had a similar use-case of allowing users to define custom forms - collection of form types. We decided to not allow users to create schema programmatically. In the end I think we agreed the system design would have been a lot simpler to allow them to actually transact schema attributes though. So I would recommend thinking through a system design that allows that. Namespaced keywords make this sort of thing tractable

Do the user schemas overlap or do they naturally shard per tenant? Idea: one database per user tenant

We have done this at Nextdoc. I can’t share all the IP but can show you the base storage design which simplifies how you store attributes.

there’s another set of meta data that maps customer entities to these entities and a layer of CRUD fns to abstract it all. that’s too much to share but the underlying storage can point you in a direction that works

we use Lacinia and it makes the abstraction of customer entities easier too

The approach I’ve taken is to create attributes for each value type, allow users to create attributes as data entities referencing one of them, and model “assertions” as entities with an attribute ref plus value ref. E.g.

;; schema attributes to support user-attribute models
 [{:db/ident :user-defined-attribute/name
   :db/cardinality :db.cardinality/one
   :db/valueType :db.type/string
   :db/unique :db.unique/value}
  {:db/ident :user-defined-attribute/valueType
   :db/cardinality :db.cardinality/one
   :db/valueType :db.type/ref}
  
  {:db/ident       :user-data/value-string
   :db/cardinality :db.cardinality/one
   :db/valueType   :db.type/string}
  {:db/ident       :user-data/value-strings
   :db/cardinality :db.cardinality/many
   :db/valueType   :db.type/string}
  ,,,
  ]
 ;; User attribute definition
 [{:user-defined-attribute/name      "my-attribute"
   :user-defined-attribute/valueType :user-data/value-string ,,,}]
 ;; User attribute "assertion"
 [{:user-data/attribute [:user-defined-attribute/name "my-attribute"]
   :user-data/value-string "my-value"
   ,,,}]
 
 (d/q '[:find ?data-e ?data-attr ?data-val
        :where
        [?data-e :user-data/attribute ?data-attr]
        [?data-attr :user-defined-attribute/valueType ?datomic-a]
        [?data-e ?datomic-a ?data-val]
        ])
 
 

It’s also possible to write a pull expression + xform that will “lift” an entity which represents a “user data” into a single map entry

e.g.

(defn lift-user-data [elem]
  {(-> elem :user-data/attribute :user-defined-attribute/name)
   (get elem (-> elem :user-data/attribute :user-defined-attribute/valueType :db/ident))})

(pull db [{(:entity/user-data-element :xform 'lift-user-data)
           [{:user-data/attribute [:user-defined-attribute/name
                                   {:user-defined-attribute/valueType [:db/ident]}]}
            :user-data/value-string
            :user-data/value-strings
            ]
           }]
      e)

as you can use multiple databases in queries, wouldn’t the approach of having a “tenant custom db” for each tenant be good? having a metamodel on top of the actual model seems cumbersome

like having 1 shared main database that has all the common things and then each tenant would have a separate custom db for their attrs

I don't think Datomic Cloud supports multiple databases in queries, but OnPrem might

on-prem does yes

using multiple dbs isn’t that great operationally unless you have a fixed number and they are smallish

Connecting is slow, so they need to be connected all the time practically speaking. If they all share a transactor, a need for indexing on one blocks indexing on the others. It’s hard to predict object cache utilization. Maybe it makes sense sometimes. However I think both schema and databases are meant to be provisioned and manipulated by devs not users (i.e. carefully and thoughtfully)

yes, all dbs in use need to fit their roots into peer memory

good to know

Hi @U0509NKGK (@U09K620SG and @U11SJ6Q0K too) , ideally, everything in one database, about 90% of the data will be shared with other tenants. But I may be wrong in my reasoning. And thanks for the idents, I didn't know that, I'll add it in our note.

@U0CKDHF4L, we are on Datomic Cloud

@U051V5LLP, thanks for your experience!

@U0510KXTU what happens if someone needs a 15th field?

@U09R86PA4, can you tell us how big your table is at this point? How long does it take to search on a user defined attribute?

We have a 17 billion datom db, but these user attributes are a fraction of that, tens of millions of entities at most, tens of thousands of tenants. You’ll have to be more specific about what you mean by “search”--the operations we do usually start from a user attribute (unique per tenant) or a thing which has an attribute on it. Both of these are fast enough that we don’t notice.

as you can see it’s an extra join or two onto likely-to-be-loaded (low-cardinality, very shared) entities.

Thanks for the details! You've also responded to the "search" question.

We just add more attributes as needed. You will always have some limits to # of columns or you can be DOS’d or DOW’d so having limits in this dimension is consistent with that

yeah, even in your datomic schema cardinality limits are a good idea (i.e. cardinality-many rarely really means “infinity”)