10 ms·

Rules of schema growth (2017)

172 points by iamwil 3y ago

packetlost 3y ago
I wish there were open source, less Clojure-centric implementations of Datomic, particularly as a plugin over something like Postgres. It has many good ideas
- yayitswei 3y ago
  There's XTDB which is still Clojure-centric but is at least open-source. It supports SQL queries.
- sweetsocks21 3y ago
  I've been keeping an eye on https://github.com/cozodb/cozo https://github.com/cozodb/cozo which is pretty close to something I've wanted, a sqlite version of datalog/datomic.
- stcg 3y ago
  Datahike [0] provides similar functionality to datomic and is open source. It lacks some features however that Datomic does have [1]. [0]: https://github.com/replikativ/datahike https://github.com/replikativ/datahike [1]: https://github.com/replikativ/datahike#when-to-choose-datahike-vs-datomic-vs-datascript https://github.com/replikativ/datahike#when-to-choose-datahi...
Swizec 3y ago
> 5. Never remove a name. > Removing a named schema component at any level is a breaking change for programs that depend on that name. Never remove a name. I agree with this in theory and have seen it go oh so very wrong in practice. Tables with dozens of columns, some of which may be unusued, invalid, actively deceiving, or at the very least confusing. Then a new developer joins and goes "A-ha! This is the way to get my data." ... except it's not and now their query is lying to users, analysts, leadership, anyone who thinks they're looking at the right data but isn't. You absolutely have to make time to deprecate and remove parts of the schema that are no longer valid. Even if it means breaking a few eggs (hopefully during a thorough test run or phased rollout)
- tkiolp4 3y ago
  But then why not addressing the real problem? If a table has a few columns which are not used or invalid or deceiving, why did we let developers introduce them? Lack of planning? Lack of peer review? Lack of talent? I understand these “ten rules” as: as long as you have a decent codebase and decent engineers, these ten rules will make your life easier. These rules are nothing if you are dealing with crap codebases (they can help, sure, but they will be just patches)
  - striking 3y ago
    Because sometimes you make assumptions that are seemingly correct but eventually found to be wrong or based on flawed inputs from sources beyond your control. Any system that ultimately relies on "engineers need to always do the right thing" is a flawed, brittle, ineffectual system. Because even the best engineers will make a mistake somewhere, and because you can't exclusively hire "the best" engineers. Let's spend our time figuring out how to recover from mistakes rather than trying to pretend they'll never happen.
  - phtrivier 3y ago
    Even the best team make design decision that turns out suboptimal when the requirement changes. Also, even the best team will sometimes make mistake. Db schemas are unforgiving.
  - __jem 3y ago
    I've worked with some databases that are 20+ years old and have outlived multiple application iterations. There's always going to be cruft in this kind of situation, it just comes with territory of supporting applications with real production users for a long time.
  - yxhuvud 3y ago
    Requirements change over time. Domain understanding change over tim. Business change over time. Environments change over time. Unless you are a seer with perfect precognition, most of what you have done will be invalidated over time. Hence: make your code and data easy to change, but simple, as you cannot predict in what way it will change.
koito17 3y ago
Interesting aside on rules 7 and 8: many Clojure users (myself included) did not often use namespaced keywords until clojure.spec was released, and in clojure.spec, you absolutely need namespacing since specs reside in a global registry. Though in the case of clojure.spec, we typically use the same namespace as our current ns, and there is syntax sugar for this. e.g. ::foo resolves to (keyword *ns* "foo") In Datomic, namespaces tend to represent your application's models, like :person/date-of-birth. I find it very useful mostly for human readability, it offers a way to distinguish what exactly :name refers to in your application's model. It also helps with editor autocomplete since you can type a namespace and see all keys up front, no need to consult a keys spec itself (or the schema of your database in Datomic). And when in doubt, in Datomic, you can always pull, and it is not too hard to run a query that extracts all attributes that exist in your database (this is actually an exercise in Learn Datalog Today[1], highly recommend going through this tutorial yourself if you want to play with databases like Datomic or XTDB). [1] Exercise 2 in https://www.learndatalogtoday.org/chapter/4 https://www.learndatalogtoday.org/chapter/4
pphysch 3y ago
> Data outlives code, and a valuable database supports many applications over time. Probably an unpopular opinion, but I think having a central database that directly interfaces with multiple applications is an enormous source of technical debt and other risks, and unnecessary for most organizations. Read-only users are fine for exploratory/analytical stuff, but multiple independent writers/cooks is a recipe for disaster. I prefer an architecture where the central "database" is a central, monolithic Django/Rails/NodeJS/Spring app that totally owns the actual database, and if someone needs access to the data, you whip up an HTTPS API for them. Yes, it is a tiny bit of effort to "whip up an API" but it deals with so many of the footguns implied by this article. "I need X+Y tables formatted as Z JSON" is a 5 minute dev task in a modern framework.
- wrs 3y ago
  In that architecture, the central API effectively becomes “the database”, because apps depend on it just as much. Most of the same rules would apply to the central API’s schema.
  - pphysch 3y ago
    Conceptually, yes they are the same unit. Which is part of the beauty of it; it's not a radical paradigm shift like monolith->microservices or systemd->k8s. But the developer experience/productivity of bending Django or Rails in different ways is far superior to bending e.g. MySQL or PostgreSQL*. HTTPS is plainly a much better, and more widely available, integration protocol than N different SQL dialects. *much respect to what the Crunchy Data folks have accomplished
    - sgarland 3y ago
      There are pros and cons to the abstraction that an ORM brings. Assuming it’s decent (like Django), you can be guaranteed that the queries it generates are good enough. They’re not always perfect, and sometimes need help depending on how convoluted your schema and desired query are, but they’re decent. The downside is this can cause devs to never have to think in terms of what the DB is capable of, so they may not consider writing the code such that the ORM decides to write the query differently. I’ve specifically seen a dearth of semijoins in output, even though they’re a common construct in RDBMS, and often far faster for the desired end goal. Sometimes the SQL planner itself will produce them, of course, but that’s not a guarantee either.
d3ckard 3y ago
Going to take the risk and politely say I do not agree with this article at all. Alternative advice: never allow more than one app to share the db and expose data through APIs, not queries. Then you can actually remove cruft and solve compatibility through API versioning that you probably need to do anyway. Also, never maintain more than two versions at the time.
- n0w 3y ago
  You've got more than one app sharing a db when you deploy a new version. Unless you're happy with downtime during deploys as the cost of not having to manage how your schema evolves. These kinds of best practices make sense regardless of how many apps access a db. Following the advice doesn't also prevent you from enforcing a strict contract for external access and modification of the data.
  - cogman10 3y ago
    > You've got more than one app sharing a db when you deploy a new version. Unless you're happy with downtime during deploys as the cost of not having to manage how your schema evolves. 2 deploys is all it takes to solve this problem. * 1 to deploy the new schema for the new version. * 1 to remove the old schema. This sort of "tick tock" pattern for removing stuff is common sense. Be it a database or a rest API, the first step is to grow with a new one and the second is to kill the old one which allows destructive schema actions without downtime.
    - koreth1 3y ago
      2 deploys isn't enough for robustness. It depends on what the change is, but the full sequence is often more like * Add the new schema * Write to both the new and old schemas, keep reading from the old one (can be combined with the previous step if you're using something like Flyway) * Backfill the new schema; if there are conflicts, prefer the data from the old schema * Keep writing to both schemas, but switch to reading from the new one (can often be combined with the previous step) * Stop writing to the old schema * Remove the old schema Leave out any one of those steps and you can hit situations where it's possible to lose data that's written while the new code is rolling out. Though again, it depends on the change; if you're, say, dropping a column that no client ever reads or writes, obviously it gets simpler.
bob1029 3y ago
In cloud native arrangements, we are looking at the database as the ultimate integration tool. These schema are not owned by any specific service. There is a totally separate universe responsible for how all of that works outside code. Allowing code to drive schema (and permitting the implication that this is the best way) is the biggest frustration I have with ORMs & their higher-order consequences. In my view, if the schema must change so radically that traditional migrations and other 'grow-only' techniques fall apart, you are probably looking at a properly-dead canary and in need of evacuating the entire coal mine. The Quote regarding flowcharts and tables applies here - if you radically alter the foundation, everything built upon it absolutely must adapt. Every flowchart into the trashcan instantly. Don't even think about it. They're as good as a ball & chain now. Allowing parts of the structure to dictate parts of the foundation is where we find ourselves with circular firing squads. Take things to the extreme - There is a reason you will start to find roles like "Schema Owner" in large, legacy org charts. These people cannot see the code or they will become tainted. They only have one class of allegiance - LOB owners. These are who they engage to develop & refine schema over time. The schema owner themselves has a full time job that is entirely dedicated to minimizing the impact of change over time to the org. This person is ideally the most ancient wizard in the org chart and has the prior Fred Brooks quote framed on their wall. You can make schema change a top-down event that touches the entire organization. This happens quite often in banking when the central system is completely swapped for a different vendor & tech stack. Most of a bank is just a SQL database, but every vendor has a different schema that has to be adapted to. This is known as "core conversion" in the industry and is one of the more hellish experiences I have ever seen. If a bank with 4 decades of digital records can pull something like this off with regularity, there aren't many excuses that remain for a hole-in-the-wall SaaS app with 6 months of customer data.
Aeolun 3y ago
They completely lost me when they said the database is the definite source of the schema. Sounds like 90s DB admins making a case for the bad old way of doing things.
- JackMorgan 3y ago
  Could you elaborate on this?
  - Aeolun 3y ago
    As another commenter on this posted, you should keep your schema in version control just like your code.
- iamwil 3y ago
  Same. Why is having the schema in the db a bad idea?
- butlerm 3y ago
  It has never been standard practice to store the database schema and its history, notes, and documentation in the database, although data dictionaries come close. There are database catalogs that can carry a subset of that information, but a more traditional "90s" approach is to use a data modeling tool. If you want detailed change history it is hard to avoid using DDL files with source code control, although notes go a long way. Most databases are not good at change history - not even good at representing it, unfortunately. It is also quite easy to do a mediocre job of anything, and I would be careful of counting anything as the "bad old way" with out a careful evaluation of the pros and cons of the "new good way" as well. A casual observer might wonder why so many modern web apps have response times that are ten to twenty times longer than they were two decades ago, for example. Perhaps - on occasion - the new good way isn't so good.
dekhn 3y ago
Many of these rules are things that only became obvious after I worked on an extremely large production database where downtime was counted in millions of dollars an hour, we didn't know who all our users were, some unknown users were load-bearing, and we had a bunch of postmortems describing historical incidents. I don't think the article mentions it, but one other technique we used- which still is a big question mark to me- is that we "trickled" changes to the database. Instead of changing all the rows in a single big transaction, the change was broken into thousands of little changes that were rolled out over a series of days. The reason for this is that if there is an unexpected problem, you have more time to stop the change and mitigate the damage.
- codethief 3y ago
  > the change was broken into thousands of little changes Could you elaborate on how this worked?
  - djbusby 3y ago
    Not OP but here what we have is some routine to get a fact from the DB (eg Customer->getOrders()) and the routine is schema aware. On v0 it queries a table with one rules, on v1 it's a different slightly different query. So, one small schema mod to eg add a column. Roll out. Another schema mod to populate, possibly a few days later, then roll out queries over a few days, then, later slowly remove the dead parts of the schema and then later drop the legacy code using the old columns. The process can take weeks. And some throwaway code (scaffolding I say) has to pass through the system. I recently was able to clean up and old view that took months to process through (we're a small team) One recent one was moving from bitflags in an int column to flags in a jsonb column. Tedious. What makes it work? Testing, testing, testing and management that gives time for that process.
  - dekhn 3y ago
    Literally, there were many thousands of "UPDATE WHERE" each targeting a different row, and the file was sharded, and then some process would read through a shard and apply statements one at a time with a delay. It would export stats (# of updates, # of errors) and could be easily paused/resumed. We called it trickling, see https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/TrickleLoading/TrickleLoadingData.htm https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/Ad...
deleted 3y ago
[deleted]
mvdtnz 3y ago
Wow these are shockingly bad rules. This is how we end up with DBs with hundreds of tables and duplicate columns like `name` and `name2`. My number one rule of growing schemas is to design your schemas and applications with a good custom field system. Some kind of flexible way of being able to add fields to items as data.
- AtlasBarfed 3y ago
  Ahhh the entity-attribute-value pattern. When your sql database needs another sql engine to translate to actual EAV queries. But they are fun schemas! The old joke that there are two actually hard problems in CS: caching and naming things. The older I get, naming things IMO is far more difficult.
  - cdchn 3y ago
    When you start looking at SQL tables that are just id,key,val then you know you've teleported into this forbidden realm. There are 2 hard problems in computer science: caching, naming things, and off-by-one errors.
    - sgarland 3y ago
      It’s even better when you find out that key is a text field, and stores ints, uuids, custom key formats…
      - cdchn 3y ago
        And there are a different 'value' column for every possibly data type.
  - yxhuvud 3y ago
    The thing about naming is that it is so closely related to how the problem domain (not business domain, though they are of course related) is understood, and how well it is conceptualized.
- JackMorgan 3y ago
riffic 3y ago
why is no one talking about modeling or ERD or anything of that sort?
- butlerm 3y ago
  I wouldn't design a non-trivial API without doing a data model of some sort, personally, because every non-trivial API exposes one.
exabrial 3y ago
Postulate #1: your most valuable resource is strong types, well defined business vocabulary, and the follow on generations of developers understanding these concepts Postulate #2: once in production, a schema is likely to outlive you. Spend an enormous amount of time minimizing its scope and making sure it’s correct. Postulate #3: you data has a schema no matter what the mongodb users try to say otherwise.
- klysm 3y ago
  I fight until and past exhaustion for a high quality and normalized schema. A lot of devs I work with don’t seem to like it but I still believe it’s worth it
skybrian 3y ago
Compare with the usual rules for protobuf evolution: All fields are optional. This is because fields have a lifecycle. Your code might be reading data generated by some other code written before the field was defined, or generated by some other code written after the field became obsolete. With protobufs, you can never reuse a field number. However, you can remove a field (keeping its number reserved), which effectively means ignoring it in new applications. It's neither read nor written, which is fine because see above. You can also rename the field itself. This doesn't affect the wire protocol. It seems more sensible than having misleading names around for people to trip over. Maybe we'd be better off if databases used field numbers to refer to columns in the wire protocol?
- sundbry 3y ago
  This is one nice property of Apache Iceberg (a data lake format, built on top of Parquet). The schemas in the data are evolvable, and require both a name and a field ID to identify the field. The names are changeable, but the ID's are immutable, so you can remove a field, and add a new field by the same name, having a different type.
lysecret 3y ago
Summary, reading the article and the comments. There are no rules, it is all one big DEPENDS.
hosh 3y ago
Another way I understand the idea of schema growth that, you should transform schemas with monoids.
ewams 3y ago
Anyone have good examples of rule 8, namespaces? Either in pg or SQL server?

172 points by iamwil 3y ago

I wish there were open source, less Clojure-centric implementations of Datomic, particularly as a plugin over something like Postgres. It has many good ideas

yayitswei 3y ago
There's XTDB which is still Clojure-centric but is at least open-source. It supports SQL queries.
sweetsocks21 3y ago
I've been keeping an eye on https://github.com/cozodb/cozo https://github.com/cozodb/cozo which is pretty close to something I've wanted, a sqlite version of datalog/datomic.
stcg 3y ago
Datahike [0] provides similar functionality to datomic and is open source. It lacks some features however that Datomic does have [1]. [0]: https://github.com/replikativ/datahike https://github.com/replikativ/datahike [1]: https://github.com/replikativ/datahike#when-to-choose-datahike-vs-datomic-vs-datascript https://github.com/replikativ/datahike#when-to-choose-datahi...

Swizec 3y ago

> 5. Never remove a name. > Removing a named schema component at any level is a breaking change for programs that depend on that name. Never remove a name. I agree with this in theory and have seen it go oh so very wrong in practice. Tables with dozens of columns, some of which may be unusued, invalid, actively deceiving, or at the very least confusing. Then a new developer joins and goes "A-ha! This is the way to get my data." ... except it's not and now their query is lying to users, analysts, leadership, anyone who thinks they're looking at the right data but isn't. You absolutely have to make time to deprecate and remove parts of the schema that are no longer valid. Even if it means breaking a few eggs (hopefully during a thorough test run or phased rollout)

tkiolp4 3y ago
But then why not addressing the real problem? If a table has a few columns which are not used or invalid or deceiving, why did we let developers introduce them? Lack of planning? Lack of peer review? Lack of talent? I understand these “ten rules” as: as long as you have a decent codebase and decent engineers, these ten rules will make your life easier. These rules are nothing if you are dealing with crap codebases (they can help, sure, but they will be just patches)
- striking 3y ago
  Because sometimes you make assumptions that are seemingly correct but eventually found to be wrong or based on flawed inputs from sources beyond your control. Any system that ultimately relies on "engineers need to always do the right thing" is a flawed, brittle, ineffectual system. Because even the best engineers will make a mistake somewhere, and because you can't exclusively hire "the best" engineers. Let's spend our time figuring out how to recover from mistakes rather than trying to pretend they'll never happen.
- phtrivier 3y ago
  Even the best team make design decision that turns out suboptimal when the requirement changes. Also, even the best team will sometimes make mistake. Db schemas are unforgiving.
- __jem 3y ago
  I've worked with some databases that are 20+ years old and have outlived multiple application iterations. There's always going to be cruft in this kind of situation, it just comes with territory of supporting applications with real production users for a long time.
- yxhuvud 3y ago
  Requirements change over time. Domain understanding change over tim. Business change over time. Environments change over time. Unless you are a seer with perfect precognition, most of what you have done will be invalidated over time. Hence: make your code and data easy to change, but simple, as you cannot predict in what way it will change.

koito17 3y ago

Interesting aside on rules 7 and 8: many Clojure users (myself included) did not often use namespaced keywords until clojure.spec was released, and in clojure.spec, you absolutely need namespacing since specs reside in a global registry. Though in the case of clojure.spec, we typically use the same namespace as our current ns, and there is syntax sugar for this. e.g. ::foo resolves to (keyword *ns* "foo") In Datomic, namespaces tend to represent your application's models, like :person/date-of-birth. I find it very useful mostly for human readability, it offers a way to distinguish what exactly :name refers to in your application's model. It also helps with editor autocomplete since you can type a namespace and see all keys up front, no need to consult a keys spec itself (or the schema of your database in Datomic). And when in doubt, in Datomic, you can always pull, and it is not too hard to run a query that extracts all attributes that exist in your database (this is actually an exercise in Learn Datalog Today[1], highly recommend going through this tutorial yourself if you want to play with databases like Datomic or XTDB). [1] Exercise 2 in https://www.learndatalogtoday.org/chapter/4 https://www.learndatalogtoday.org/chapter/4

pphysch 3y ago

> Data outlives code, and a valuable database supports many applications over time. Probably an unpopular opinion, but I think having a central database that directly interfaces with multiple applications is an enormous source of technical debt and other risks, and unnecessary for most organizations. Read-only users are fine for exploratory/analytical stuff, but multiple independent writers/cooks is a recipe for disaster. I prefer an architecture where the central "database" is a central, monolithic Django/Rails/NodeJS/Spring app that totally owns the actual database, and if someone needs access to the data, you whip up an HTTPS API for them. Yes, it is a tiny bit of effort to "whip up an API" but it deals with so many of the footguns implied by this article. "I need X+Y tables formatted as Z JSON" is a 5 minute dev task in a modern framework.

wrs 3y ago
In that architecture, the central API effectively becomes “the database”, because apps depend on it just as much. Most of the same rules would apply to the central API’s schema.
- pphysch 3y ago
  Conceptually, yes they are the same unit. Which is part of the beauty of it; it's not a radical paradigm shift like monolith->microservices or systemd->k8s. But the developer experience/productivity of bending Django or Rails in different ways is far superior to bending e.g. MySQL or PostgreSQL*. HTTPS is plainly a much better, and more widely available, integration protocol than N different SQL dialects. *much respect to what the Crunchy Data folks have accomplished
  - sgarland 3y ago
    There are pros and cons to the abstraction that an ORM brings. Assuming it’s decent (like Django), you can be guaranteed that the queries it generates are good enough. They’re not always perfect, and sometimes need help depending on how convoluted your schema and desired query are, but they’re decent. The downside is this can cause devs to never have to think in terms of what the DB is capable of, so they may not consider writing the code such that the ORM decides to write the query differently. I’ve specifically seen a dearth of semijoins in output, even though they’re a common construct in RDBMS, and often far faster for the desired end goal. Sometimes the SQL planner itself will produce them, of course, but that’s not a guarantee either.

d3ckard 3y ago

Going to take the risk and politely say I do not agree with this article at all. Alternative advice: never allow more than one app to share the db and expose data through APIs, not queries. Then you can actually remove cruft and solve compatibility through API versioning that you probably need to do anyway. Also, never maintain more than two versions at the time.

n0w 3y ago
You've got more than one app sharing a db when you deploy a new version. Unless you're happy with downtime during deploys as the cost of not having to manage how your schema evolves. These kinds of best practices make sense regardless of how many apps access a db. Following the advice doesn't also prevent you from enforcing a strict contract for external access and modification of the data.
- cogman10 3y ago
  > You've got more than one app sharing a db when you deploy a new version. Unless you're happy with downtime during deploys as the cost of not having to manage how your schema evolves. 2 deploys is all it takes to solve this problem. * 1 to deploy the new schema for the new version. * 1 to remove the old schema. This sort of "tick tock" pattern for removing stuff is common sense. Be it a database or a rest API, the first step is to grow with a new one and the second is to kill the old one which allows destructive schema actions without downtime.
  - koreth1 3y ago
    2 deploys isn't enough for robustness. It depends on what the change is, but the full sequence is often more like * Add the new schema * Write to both the new and old schemas, keep reading from the old one (can be combined with the previous step if you're using something like Flyway) * Backfill the new schema; if there are conflicts, prefer the data from the old schema * Keep writing to both schemas, but switch to reading from the new one (can often be combined with the previous step) * Stop writing to the old schema * Remove the old schema Leave out any one of those steps and you can hit situations where it's possible to lose data that's written while the new code is rolling out. Though again, it depends on the change; if you're, say, dropping a column that no client ever reads or writes, obviously it gets simpler.

bob1029 3y ago

In cloud native arrangements, we are looking at the database as the ultimate integration tool. These schema are not owned by any specific service. There is a totally separate universe responsible for how all of that works outside code. Allowing code to drive schema (and permitting the implication that this is the best way) is the biggest frustration I have with ORMs & their higher-order consequences. In my view, if the schema must change so radically that traditional migrations and other 'grow-only' techniques fall apart, you are probably looking at a properly-dead canary and in need of evacuating the entire coal mine. The Quote regarding flowcharts and tables applies here - if you radically alter the foundation, everything built upon it absolutely must adapt. Every flowchart into the trashcan instantly. Don't even think about it. They're as good as a ball & chain now. Allowing parts of the structure to dictate parts of the foundation is where we find ourselves with circular firing squads. Take things to the extreme - There is a reason you will start to find roles like "Schema Owner" in large, legacy org charts. These people cannot see the code or they will become tainted. They only have one class of allegiance - LOB owners. These are who they engage to develop & refine schema over time. The schema owner themselves has a full time job that is entirely dedicated to minimizing the impact of change over time to the org. This person is ideally the most ancient wizard in the org chart and has the prior Fred Brooks quote framed on their wall. You can make schema change a top-down event that touches the entire organization. This happens quite often in banking when the central system is completely swapped for a different vendor & tech stack. Most of a bank is just a SQL database, but every vendor has a different schema that has to be adapted to. This is known as "core conversion" in the industry and is one of the more hellish experiences I have ever seen. If a bank with 4 decades of digital records can pull something like this off with regularity, there aren't many excuses that remain for a hole-in-the-wall SaaS app with 6 months of customer data.

Aeolun 3y ago

They completely lost me when they said the database is the definite source of the schema. Sounds like 90s DB admins making a case for the bad old way of doing things.

JackMorgan 3y ago
Could you elaborate on this?
- Aeolun 3y ago
  As another commenter on this posted, you should keep your schema in version control just like your code.
iamwil 3y ago
Same. Why is having the schema in the db a bad idea?
butlerm 3y ago
It has never been standard practice to store the database schema and its history, notes, and documentation in the database, although data dictionaries come close. There are database catalogs that can carry a subset of that information, but a more traditional "90s" approach is to use a data modeling tool. If you want detailed change history it is hard to avoid using DDL files with source code control, although notes go a long way. Most databases are not good at change history - not even good at representing it, unfortunately. It is also quite easy to do a mediocre job of anything, and I would be careful of counting anything as the "bad old way" with out a careful evaluation of the pros and cons of the "new good way" as well. A casual observer might wonder why so many modern web apps have response times that are ten to twenty times longer than they were two decades ago, for example. Perhaps - on occasion - the new good way isn't so good.

dekhn 3y ago

Many of these rules are things that only became obvious after I worked on an extremely large production database where downtime was counted in millions of dollars an hour, we didn't know who all our users were, some unknown users were load-bearing, and we had a bunch of postmortems describing historical incidents. I don't think the article mentions it, but one other technique we used- which still is a big question mark to me- is that we "trickled" changes to the database. Instead of changing all the rows in a single big transaction, the change was broken into thousands of little changes that were rolled out over a series of days. The reason for this is that if there is an unexpected problem, you have more time to stop the change and mitigate the damage.

codethief 3y ago
> the change was broken into thousands of little changes Could you elaborate on how this worked?
- djbusby 3y ago
  Not OP but here what we have is some routine to get a fact from the DB (eg Customer->getOrders()) and the routine is schema aware. On v0 it queries a table with one rules, on v1 it's a different slightly different query. So, one small schema mod to eg add a column. Roll out. Another schema mod to populate, possibly a few days later, then roll out queries over a few days, then, later slowly remove the dead parts of the schema and then later drop the legacy code using the old columns. The process can take weeks. And some throwaway code (scaffolding I say) has to pass through the system. I recently was able to clean up and old view that took months to process through (we're a small team) One recent one was moving from bitflags in an int column to flags in a jsonb column. Tedious. What makes it work? Testing, testing, testing and management that gives time for that process.
- dekhn 3y ago
  Literally, there were many thousands of "UPDATE WHERE" each targeting a different row, and the file was sharded, and then some process would read through a shard and apply statements one at a time with a delay. It would export stats (# of updates, # of errors) and could be easily paused/resumed. We called it trickling, see https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/TrickleLoading/TrickleLoadingData.htm https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/Ad...

deleted 3y ago

[deleted]

mvdtnz 3y ago

Wow these are shockingly bad rules. This is how we end up with DBs with hundreds of tables and duplicate columns like `name` and `name2`. My number one rule of growing schemas is to design your schemas and applications with a good custom field system. Some kind of flexible way of being able to add fields to items as data.

AtlasBarfed 3y ago
Ahhh the entity-attribute-value pattern. When your sql database needs another sql engine to translate to actual EAV queries. But they are fun schemas! The old joke that there are two actually hard problems in CS: caching and naming things. The older I get, naming things IMO is far more difficult.
- cdchn 3y ago
  When you start looking at SQL tables that are just id,key,val then you know you've teleported into this forbidden realm. There are 2 hard problems in computer science: caching, naming things, and off-by-one errors.
  - sgarland 3y ago
    It’s even better when you find out that key is a text field, and stores ints, uuids, custom key formats…
    - cdchn 3y ago
      And there are a different 'value' column for every possibly data type.
- yxhuvud 3y ago
  The thing about naming is that it is so closely related to how the problem domain (not business domain, though they are of course related) is understood, and how well it is conceptualized.
JackMorgan 3y ago

riffic 3y ago

why is no one talking about modeling or ERD or anything of that sort?

butlerm 3y ago
I wouldn't design a non-trivial API without doing a data model of some sort, personally, because every non-trivial API exposes one.

exabrial 3y ago

Postulate #1: your most valuable resource is strong types, well defined business vocabulary, and the follow on generations of developers understanding these concepts Postulate #2: once in production, a schema is likely to outlive you. Spend an enormous amount of time minimizing its scope and making sure it’s correct. Postulate #3: you data has a schema no matter what the mongodb users try to say otherwise.

klysm 3y ago
I fight until and past exhaustion for a high quality and normalized schema. A lot of devs I work with don’t seem to like it but I still believe it’s worth it

skybrian 3y ago

Compare with the usual rules for protobuf evolution: All fields are optional. This is because fields have a lifecycle. Your code might be reading data generated by some other code written before the field was defined, or generated by some other code written after the field became obsolete. With protobufs, you can never reuse a field number. However, you can remove a field (keeping its number reserved), which effectively means ignoring it in new applications. It's neither read nor written, which is fine because see above. You can also rename the field itself. This doesn't affect the wire protocol. It seems more sensible than having misleading names around for people to trip over. Maybe we'd be better off if databases used field numbers to refer to columns in the wire protocol?

sundbry 3y ago
This is one nice property of Apache Iceberg (a data lake format, built on top of Parquet). The schemas in the data are evolvable, and require both a name and a field ID to identify the field. The names are changeable, but the ID's are immutable, so you can remove a field, and add a new field by the same name, having a different type.

lysecret 3y ago

Summary, reading the article and the comments. There are no rules, it is all one big DEPENDS.

hosh 3y ago

Another way I understand the idea of schema growth that, you should transform schemas with monoids.

ewams 3y ago

Anyone have good examples of rule 8, namespaces? Either in pg or SQL server?