4 ms·

Adding bidirectional lenses to “normalize” and “denormalize” data before it enters and after it exits a SQL database means you need to maintain those lenses sep

by jitl 4y ago

Adding bidirectional lenses to “normalize” and “denormalize” data before it enters and after it exits a SQL database means you need to maintain those lenses separately from the SQL database schema. You would have three things to keep track of - the upstream data source (the thing supplying the JSON, might not be under your control), the lenses in your SQL client source code, and the SQL database schema. Using virtual columns like this encodes the “normalizing” lens into the SQL schema in a purely-functional way. This seems like a pure win to me for clients that need to cache data but never update it. The client code can be a “dumb pipe” on the write side and just dump whatever received data into the SQL database. In essence, you are getting CQRS (command/query representation separation) for “free”, built into the storage system. Why spend the engineering cycles in the SQL client? For a real world example - at Notion we have a SQLite database running in our client apps. Most of the rows come from the back-end API and serve as a local cache. There are some object properties the clients need to query, and other object properties the clients don’t care to query, but are needed to render the data in the UI, so it still needs to be stored locally. Over time, those needs change — as do the shape of the upstream API data source. So, we put un-queried object properties into catch-all JSON columns. When we introduce a new query pattern to the client, we can run a number of different migration strategies to extract existing data from the JSON column and put it into a newly created column, or use a virtual column/index. Having the catch-all JSON column also means we can add an object property in our backend for web clients, and then later when we roll the feature out to native apps the data is already there - it just needs a migration to make it efficient to query.

nbevans 4y ago
+1 for lenses. A FP concept.
munk-a 4y ago
In Postgres there is a family of INSTEAD OF triggers that allow you to do all this lensing within the database so that the application can have multiple views of the data that all resolve to a single normalized structure internally but present in a variety of ways. To my googling knowledge SQLite also supports INSTEAD OF triggers - is there a benefit to using the predefined JSON columns instead of writing the data into an actual normalized structure and then zipping it up at read time or during the regeneration of a materialized view?
- jitl 4y ago
  Using `INSTEAD OF` or writing to a view implies that the SQL schema was designed with foreknowledge of the data that will be stored in the schema. These tools let you lens from one known, totally-specified schema (a "view") to another known schema (a "table") at write time. In contrast, the catch-all JSON column strategy allows us to store data the schema has no idea about -- or may never need to understand. You're right that once we move a property from the "don't query, just store" category to the "query and/or update locally" category, we could use views/INSTEAD OF trigger to keep the JSON column and the normalized view in sync.
  - munk-a 4y ago
    In the example given in the article foreknowledge of the property does exist - there's a specific DDL statement to run before the column-y thing is usable. With the advent of schemaless I think there's been far too strong of a reactionary movement against schema adjustment. With some trivial investment in migration management infrastructure you can arrive at a point where changing the DB (even in drastic ways) is as easy as committing a new file to the code base. At my company we do make use of postgres JSON types for some limited fields related to logging and auditing where live queries never need to be executed - where the data is essentially serving as WOM - we've also got once instance where our DB is essentially a pass-thru between two services that don't talk to each other directly, for this usage JSON works great since those two services just coordinate on the expected format. In most other cases we'll just write quick migrations to expand our data definition as needed to fit whatever we need to put into it. Relational databases are both powerful and quite flexible - so long as you don't enshrine the schema to an extent where it feels immutable.
    - jitl 4y ago
      It’s easier to evolve a schema to be strictly normalized when you have only a few (<1000) SQL databases running in an environment you control, especially if you also control the deployment of code that talks to those databases. In that kind of setting you can easily manage any kind of migration, and you know when a migration is fully rolled out and you can drop backwards compatibility. In Notion’s SQLite use-case, we have many millions of instances of this schema running on hardware owned by our users, and those users ultimately control when our app updates & runs migrations on those devices. If I commit a new migration today, it’ll start rolling out tomorrow, but it will take weeks to saturate the install base of our app, and we can never be 100% sure all existing clients have upgraded. Having JSON catch-all columns means we can evolve our API data types in parallel with that slow SQL schema rollout, instead of needing a strict order of “migrate first, then start writing” for any new bit of data we want to render & cache on the client.
remram 4y ago
If it is a cache, is it really beneficial to store that data (wasting space) rather than query it again after migration (wasting bandwidth)? If a lot of my cached data is never used again and waiting to be replaced by e.g. LRU strategy, storing all those fields that can't even be used in the current version seems like a huge waste of space. It seems that it would only be a good idea if most of the fields of the JSON objects are extracted and used, and most of the cached objects are used again. Which might be the case at Notion (where the cached data is probably pages) but probably not universally.
- conradev 4y ago
  You can choose whether to store the values or to compute them every time in SQLite But in terms of the overall performance budget, for a mobile app, I don't think the choice matters much. JSON in SQLite is fast and even in the case of "storing every generated column with a lot of user data", I imagine the app binary is going to be larger than the database it produces.
  - remram 4y ago
    I'm talking about a storage space/bandwidth tradeoff here, I don't know what you are saying about compute.
- jitl 4y ago
  There are some object properties the clients need to query, and other object properties the clients don’t care to query, but are needed to render the data in the UI, so it still needs to be stored locally. For example, today we run queries on "parent page ID", so that is a normalized parent_id column in SQLite, instead of being stored in the JSON column. We don't currently run queries on "text color" or "image width" object properties, but we still need to store that data in the cache to render a block. So, it's fine to leave those things in a JSON column. If we need to run a query like "find all images that are full-width" in the future, we can add a new image_width column/virtual column/index at that time.
  - remram 4y ago

by jitl 4y ago

nbevans 4y ago

+1 for lenses. A FP concept.

munk-a 4y ago

In Postgres there is a family of INSTEAD OF triggers that allow you to do all this lensing within the database so that the application can have multiple views of the data that all resolve to a single normalized structure internally but present in a variety of ways. To my googling knowledge SQLite also supports INSTEAD OF triggers - is there a benefit to using the predefined JSON columns instead of writing the data into an actual normalized structure and then zipping it up at read time or during the regeneration of a materialized view?

jitl 4y ago
Using `INSTEAD OF` or writing to a view implies that the SQL schema was designed with foreknowledge of the data that will be stored in the schema. These tools let you lens from one known, totally-specified schema (a "view") to another known schema (a "table") at write time. In contrast, the catch-all JSON column strategy allows us to store data the schema has no idea about -- or may never need to understand. You're right that once we move a property from the "don't query, just store" category to the "query and/or update locally" category, we could use views/INSTEAD OF trigger to keep the JSON column and the normalized view in sync.
- munk-a 4y ago
  In the example given in the article foreknowledge of the property does exist - there's a specific DDL statement to run before the column-y thing is usable. With the advent of schemaless I think there's been far too strong of a reactionary movement against schema adjustment. With some trivial investment in migration management infrastructure you can arrive at a point where changing the DB (even in drastic ways) is as easy as committing a new file to the code base. At my company we do make use of postgres JSON types for some limited fields related to logging and auditing where live queries never need to be executed - where the data is essentially serving as WOM - we've also got once instance where our DB is essentially a pass-thru between two services that don't talk to each other directly, for this usage JSON works great since those two services just coordinate on the expected format. In most other cases we'll just write quick migrations to expand our data definition as needed to fit whatever we need to put into it. Relational databases are both powerful and quite flexible - so long as you don't enshrine the schema to an extent where it feels immutable.
  - jitl 4y ago
    It’s easier to evolve a schema to be strictly normalized when you have only a few (<1000) SQL databases running in an environment you control, especially if you also control the deployment of code that talks to those databases. In that kind of setting you can easily manage any kind of migration, and you know when a migration is fully rolled out and you can drop backwards compatibility. In Notion’s SQLite use-case, we have many millions of instances of this schema running on hardware owned by our users, and those users ultimately control when our app updates & runs migrations on those devices. If I commit a new migration today, it’ll start rolling out tomorrow, but it will take weeks to saturate the install base of our app, and we can never be 100% sure all existing clients have upgraded. Having JSON catch-all columns means we can evolve our API data types in parallel with that slow SQL schema rollout, instead of needing a strict order of “migrate first, then start writing” for any new bit of data we want to render & cache on the client.

remram 4y ago

If it is a cache, is it really beneficial to store that data (wasting space) rather than query it again after migration (wasting bandwidth)? If a lot of my cached data is never used again and waiting to be replaced by e.g. LRU strategy, storing all those fields that can't even be used in the current version seems like a huge waste of space. It seems that it would only be a good idea if most of the fields of the JSON objects are extracted and used, and most of the cached objects are used again. Which might be the case at Notion (where the cached data is probably pages) but probably not universally.

conradev 4y ago
You can choose whether to store the values or to compute them every time in SQLite But in terms of the overall performance budget, for a mobile app, I don't think the choice matters much. JSON in SQLite is fast and even in the case of "storing every generated column with a lot of user data", I imagine the app binary is going to be larger than the database it produces.
- remram 4y ago
  I'm talking about a storage space/bandwidth tradeoff here, I don't know what you are saying about compute.
jitl 4y ago
There are some object properties the clients need to query, and other object properties the clients don’t care to query, but are needed to render the data in the UI, so it still needs to be stored locally. For example, today we run queries on "parent page ID", so that is a normalized parent_id column in SQLite, instead of being stored in the JSON column. We don't currently run queries on "text color" or "image width" object properties, but we still need to store that data in the cache to render a block. So, it's fine to leave those things in a JSON column. If we need to run a query like "find all images that are full-width" in the future, we can add a new image_width column/virtual column/index at that time.
- remram 4y ago