How to choose the right metadata store for applications at scale

Why metadata is important

Challenges maintaining metadata

Challenges with maintaining metadata in SQL-based databases:

1. Scalability and cost: SQL databases typically scale vertically (i.e., by adding more resources to a single server) rather than horizontally (i.e., by adding more servers). With the sheer volume of metadata, it is more reasonable to scale horizontally. However, horizontal scaling is complex and requires database and infrastructure expertise in your team to manage replicas and shards. You also have the option to choose an autoscaling strategy with a cloud provider, however, the cost and engineering time spent could be significant in either case.
2. Schema rigidity: SQL databases require a predefined schema, which might not be suitable for metadata that can be diverse and dynamic. For instance, let's assume you are capturing the user's device metadata. If you were to use SQL, you would create a schema with predefined columns. In this case, you have 4 columns, userID, location, deviceId, and IpAddress. However, let's assume that many users are starting to use your application from a different device with device-type information, and you need to capture this information. To do so now, you have to update your schema. In this case, it will be a simple change as follows.

   ALTER TABLE UserLoginInfo
   ADD COLUMN deviceType VARCHAR(255);

   However, as unstructured metadata is dynamic and changing all the time you may have to do similar operations over and over again. This could be a time-consuming effort depending on the complexity of your dataset.
3. Distribution and replication: Data replication across multiple geographical locations can be a challenge with SQL databases. There is no out-of-the-box solution for this problem — you must invest significant engineering resources to do it correctly.
4. Complexity of relationships: While relational databases are excellent at handling complex relationships, they can become difficult to manage when relationships among metadata become too intricate. It may lead to complicated queries that impact performance. Handling unstructured JSON documents and defining relationship is complex and can not be easily translated to table structure with data entity relationships.

Challenges with maintaining metadata in NoSQL databases

1. Consistency: NoSQL databases are not strongly consistent. NoSQL databases typically follow the CAP theorem (Consistency, Availability, Partition tolerance), which means that you may have to choose between consistency and availability in case of a network partition. This could be problematic for a metadata store where consistency might be critical.
   For instance, let's assume you have an e-commerce application collecting metadata such as user location, product bounce rate, etc. You want to provide users with personalized recommendations based on the data collected. If there is inconsistency in the data (i.e., not updated or corrupted), your recommendation will not be accurate.
2. Complexity of relationships: NoSQL databases are not designed to handle complex relationships between entities. If your metadata has intricate relationships, querying and managing it in a NoSQL database might become complicated. You may also end up creating many indexes for querying your data for a diverse dataset, which can lead to performance issues.
   Let's say we are working with a blogging application where we have two collections: Users and Posts. Each post document in the Posts collection has a user_id field, a reference to a user document in the Users collection. We want to find all posts by a specific user along with the user's information. Here's how you might perform this complex query in a popular NoSQL database (in this case, MongoDB) using $lookup:

   db.Posts.aggregate([
       {
           $match: {
               user_id: ObjectId('...')  // replace with the actual user_id
           }
       },
       {
           $lookup: {
               from: 'Users',  // join with the Users collection
               localField: 'user_id',  // field from the Posts collection
               foreignField: '_id',  // field from the Users collection
               as: 'user_info'  // output array with matching user documents
           }
       },
       {
           $unwind: '$user_info'  // flatten the output array
       }
   ]);

   This query is already quite lengthy. Metadata is usually a JSON object with many fields; you need only a few in most cases. Let's say you are only interested in user_location and user_language fields. To do so, you can modify the query as shown below.

   db.Posts.aggregate([
       {
           $match: {
               user_id: ObjectId('...')  // replace with the actual user_id
           }
       },
       {
           $lookup: {
               from: 'Users',  // join with the Users collection
               let: { user_id: '$user_id' },  // define variables to use in the pipeline
               pipeline: [
                   {
                       $match: {
                           $expr: {
                               $eq: ['$_id', '$$user_id']  // match user_id from Posts with _id from Users
                           }
                       }
                   },
                   {
                       $project: {
                           _id: 0,  // exclude this field
                           user_location: 1,  // include this field
                           user_language: 1  // include this field
                       }
                   }
               ],
               as: 'user_info'  // output array with matching user documents
           }
       },
       {
           $unwind: '$user_info'  // flatten the output array
       }
   ]);

   Notice at this point the query is getting really complicated.
   You can achieve the same result with better performance in Fauna with the following FQL query.

   Post.where(.user_id == "1231233") {
    user {
      user_location
      user_language
    }
   }

3. Schema-less design: While NoSQL's schema-less design provides flexibility and is good for unstructured or semi-structured data, it can also lead to problems. Without a rigid schema, there can be a lack of data integrity and standardization, potentially leading to inconsistencies in the metadata.

Why use Fauna as your Metadata store?

User.where(.lastlogin > '12-07-2022' && .location == 'US')

Who is using Fauna?

1. Hannon Hill is a leading software provider that choose Fauna as their primary database as well as their metadata store. Fuana currently powers over 5 million daily transactions for Hannon Hill’s new tool Clive. Hannon Hill selected Fauna primarily because of its data model flexibility, efficient API delivery model, auto-scaling capabilities, parallel transaction support, and cost-efficiency.
2. Insights.gg, a platform designed for sharing and reviewing video game performance, chose to leverage Fauna for its global distribution, serverless delivery, flexibility, and low latency. As their user base rapidly expanded beyond North America, the limitations of their previous single-region PostgreSQL database became apparent. They needed a technology stack that would enable global distribution and reduce latency without extensive engineering effort while flexibly scaling up or down based on demand.