{"post":{"data":{"id":"27589","type":"post","attributes":{"title":"Technical Details","authors":"Chet Corcos","publisher":"","id":27589,"created_at":"2021-05-15T01:31:53.400Z","updated_at":"2021-05-15T01:32:06.320Z","plugins":"{}","slug":"zjqzib-technical-details","form_url":"/posts/zjqzib-technical-details","body":"

Underneath any end-user database is another database and so much complexity arises from the challenge of building a database inside a database. Solutions to this problem vary widely and make some sort of trade-off between scalability (performance) and complexity.

Some applications load the entire end-user database in-memory. This creates long initial load times, but really performant interactions once the application loads. There are obvious scalability issues with this approach, but it is fairly simple to implement.

Other applications resort to O(n) scans on the server to fetch data and filter/sort records in memory with a few caching and indexing optimizations when convenient. The problem here is that users can create their own columns and filters dynamically. If your cloud application is backed by a SQL database, for example, it’s worrisome to allow users to edit the database schema and create an unbounded number of tables and indexes. Thus, you must resort to filtering records in-memory.

If you're dedicated to O(log n) queries, you'll need to build indexes yourself (unless there's some amazing database I am unaware of). On every write, you can compute what indexes need to be updated and you write to a NoSQL database to incrementally update a cached query result. This can lead to a good solution, but it comes at the cost of consistency. Now that you're building your own indexes outside of the database, writes are no longer transactional and you lose the guarantee of being able to read your own write.

Another solution is to have a separate database for every user and allow users to edit the schema of their own isolated database. This allows writes to be transactional, but creates other kinds of collision problems (i.e. dealing with merge conflict) when you want users to be able to collaborate. With separate databases for each user, you're also doing a lot more computing labor, especially if the same records are shared amongst many people.

Long story short, existing databases are not great for building end-user databases.

","cable_url":"wss://jellypbc.com/cable","upload_url":null,"tags":[],"title_comments":[],"body_comments":[],"contributors":"Cindy Wu","body_citations":[]},"relationships":{"uploads":{"data":[]},"citations":{"data":[]},"backlinks":{"data":[{"id":"26692","type":"backlink"}]},"figures":{"data":[]}}},"included":[{"id":"26692","type":"backlink","attributes":{"id":26692,"imprint_type":"","imprint_date":"","publisher":"","target":null,"post_id":27588,"generated_post_id":27589,"source_post_path":"/posts/vichmz-a-more-human-approach-to-databases","title":" A more human approach to databases ","authors":"Chet Corcos"}}]},"currentUser":{"data":{"attributes":{"name":"Anonymous","avatar_url":"/assets/avatar-bf489bd933e198667b26b776cbd7d0fc921ee585791fc68cca86a6187a8781f6.png"}}},"isProcessing":false,"editable":false}

Technical Details

Backlinks