3 ms·
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. Wit
by 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 agoIt’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.
- munk-a 4y agoI think that for Notion (neat company btw) it might make a lot of sense to keep the schema pretty loose but I have managed to keep an agile DB alive and well with a lot of flexibility. A lot of migration infrastructure packages and tools will store the migration state in the DB itself - we're using Phinx[1] internally which creates a `phinxlog` table with a record of migrations that have been run - there is tooling to migrate only up to a specific version and since the record of all executed versions is stored in the DB the tool can easily figure out what works needs to be done in which circumstances. The result is that we can easily roll different environments onto different versions. Neither schema driven nor unstructured is always the right call - they both have their strengths and weaknesses - but I think that trust in data integrity is pretty important when writing flexible code on top of a data layer. Knowing that expected keys can't be omitted and that so-and-so column must conform to a given data domain can really alleviate defensive coding costs. If you'd like to talk some more I can shoot you an email and we can sit down sometime? 1. https://phinx.org/ https://phinx.org/