4 ms·
Whenever I'm adding a new table, I almost always try to add an "internal_notes" text field and a "config" JSONB field, no matter how static we think the data de
by btown 6y ago
Whenever I'm adding a new table, I almost always try to add an "internal_notes" text field and a "config" JSONB field, no matter how static we think the data definition is going to be. Because at an early stage startup stage, you're going to need to hack some fixes together. And given a choice between needing to QA a database migration vs. just adding {{foo.config.extra_whatever}} in a template, or using it in a single line of logic, the latter is just so much more feasible and easy-to-understand for a same-day bugfix, and you can always move to a dedicated column with a data migration later.
Of course, if you're adding this to a few dozen rows, then you might need to jump into SQL to do this at scale. And then you run into the wonderful situations described in the article. I've had to write the following a great many times:
config = jsonb_set(coalesce(config, '{}'::jsonb), '{"some","key","path"}', to_jsonb(something::text))
Or this, which is great if you need to set a top-level key to something static; the || operator just mixes the two together similar to Object.assign:
config = coalesce(config, '{}'::jsonb) || '{"foo": "bar"}'::jsonb
And honestly, the syntax, especially the second one, isn't half bad once you get used to it. That said, the syntax from the article is amazing, and it will go a long way towards people reaching for Postgres even when their data model has a lot of unknowns.
- konschubert 6y agoIn an early stage startup, why not use an ORM with a built-in migration engine (django migrations, alembic,...) Using jsonb at such a early stage without a strong business reason seems to me like rolling the red carpet for tech debt.
- debarshri 6y agoI absolutely agree with you. It also affects how you can scale a team in early stage. It is super easy to tell the new member to be look up framework specific documentation.
- goto11 6y agoConceptually it seems simpler to add a column to table rather than adding a property to some serialized structure. I wonder if it isn't a question of process and tooling encouraging the wrong thing? At least in other contexts I have seen JSON or XML fields used as a trick to circumvent a heavyweight process for adding new columns. But IMHO the process should be improved rather than circumvented. Some times you need to quickly add a column, but adding a column is also a pretty safe operation. Doing the same thing "one level" up does not improve safety or convenience. In more traditional organizations I have seen the DBA act as a gatekeeper, which lead to developers inventing all kinds of hack to get around the barrier. I can understand the thinking which lead to this, but it is dysfunctional.
- Olreich 6y agoIf you don’t know the column will live forever, avoid adding it. Adding columns is easy, migrating and deleting columns is super annoying, even with auto-migration and an ORM, because the risk of data loss is significant.
- goto11 6y agoBut isn't this the same if you add some field to a JSON structure? If code depends on it, you can't remove it. The risk of data loss from removing a JSON field would be exactly the same as removing a column.
- pydry 6y agoPLEASE don't do this. I've worked on too many systems where these optional keys ended up being relied upon, misspelled, missing, unexpectedly null, etc. and a raft of bugs that made customers very unhappy necessitating a time consuming "data cleanse". Use a system which makes it easy to do migrations instead. It will take barely any longer to create that new column and it won't store up trouble in the future.
- piva00 6y agoSame camp here, even more if this is a startup, these once-optional keys and settings will be ossified into the database, the knowledge of why they existed will be lost (as most knowledge from startups after 5 years end up), untangling that mess will take an engineer's days to weeks doing discovery work of possible incantations of its usage, etc. If you give such a powerful feature to any table you are just shooting yourself on the foot on the whole modeling of your application, there is no static representation of the model, it will be dynamic depending on the JSONB configuration.
- Olreich 6y agoAs an industry, software development needs to get better at cleaning up TODOs. This is especially bad when business needs take prime spot always. Sometimes, technical health needs to take priority and drive down these issues. If there’s confidence in the organization that we can take on some tech debt and pay it down a little bit later, then you can move faster (just like leverage in finances). It’s when the debt you take on is so great or so well swept under the rug that it turns into a monster and ruins your day. If the tech debt items are allowed to live forever, the org gets into the position of quickly maxing out their credit and never being able to add more debt which slows delivery. In the scrum world, prioritizing a feature story over a debt story should require a strong reason with significant debate. Don’t let them live longer than a couple of sprints.
- pydry 6y agoI don't think there is any substitute for experience in this matter. Some hacks are fine to last essentially forever, some should never be allowed to happen and others are probably ok but only if they get unwound in under a week. It's rarely obvious which ones are which until you've felt the pain. I tend to believe that hacks that undermine the integrity of your data are the absolute worst (which is why my reaction to this was so visceral) but can sense I might just be a product of my environment - different industries and use cases vary quite wildly in their expectations of quality and attitudes to failure. Agree about Scrum. It creates a very high cost to unwind any tech debt that can't be smuggled into a story.
- nicoburns 6y ago> needing QA a database migration One thing I've found helpful is to notice that adding a column with a default value is always bakcwards compatible.
- xorcist 6y agoPersonally I wouldn't want to inherit something like this. These two special fields are going to be updated by some people, and not by others. Give it a few years and with some people turnover and there is plenty opportunity for bitrot and name collisions.