4 ms·

> I never understood the appeal of the JSON to SQL columns workflow. The appeal is non-technical. Writing good migrations, having tests around them to ensure

> I never understood the appeal of the JSON to SQL columns workflow. The appeal is non-technical. Writing good migrations, having tests around them to ensure they didn't leave the DB in an inconsistent state if a subset of them failed requires good understanding of a RDBMS and the specific product. You'll be surprised how many engineers don't meet that criteria. The JSON to SQL columns workflow allows any developer to offload what a RDBMS does into the client/server/bootstrap code. At that point the DB is like a key-value store and you get to claim you are using Postgresql. Is that what I would use if I had reliability and performance in mind? No... but it's way cheaper (and quicker) to find devs who can do this (JSON to SQL columns workflow) than devs who can write well tested, reliable and non hacky migrations. Often at a startup, reliability and performance have lower priority than getting some features out the door in a day.

benglish11 6y ago
>At that point the DB is like a key-value store and you get to claim you are using Postgresql. I know this statement is a little tongue in cheek but there is a lot of value organizationally to using Postgres as a key-value store vs MongoDb or some other flavor of no-sql. Even if you're not really getting all of the benefits of a relational db, you are still getting ACID transactions, envryption, user/access controls, backup/restore, libraries, etc. You're getting this in a tool that you are comfortable with and that you may be using in other areas where a relational model is more appropriate and thus have experience with a lot of the management aspects. Obviously if you are a mongo expert and using mongo everywhere this doesn't apply but in my experience software engineers/data engineers/DBA with postgres experience are easier to find.
- subhobroto 6y ago
  Hey there, thanks for reading my comments and sharing your thoughts. > in my experience software engineers/data engineers/DBA with postgres experience are easier to find. I need to understand what "postgres experience" means in this context. Having experience writing postgres client software using some postgres driver is a different kind of experience than installing, provisioning, monitoring and turning postgres itself. The former is a days worth of work for any good engineer; the later can take decades and those people are really hard to find. > you are still getting ACID transactions, envryption, user/access controls, backup/restore, libraries, etc Sure, at a high level, making a KV-store on top of a RDBMS feels like a grand idea - hey you get ACID for free and all the RDBMS goodness while getting the flexibility of a KV-store to boot. As they say - when something sounds too good to be true ... One small scenario worth thinking about: What does a KV-store on top of a RDBMS with ACID semantics even mean? How do you handle merging objects? Does the RDBMS even supporting merging objects inside a column? What would happen if I have the different attributes in the same object that I then update one after the other into a JSON column on a RDBMS? Does it fail one of the updates with an error message that data is lost? Does it automagically merge the data? How does it even know how to handle conflicts? Hint: No it cannot - the reason why a RDBMS can even offer ACID semantics is because RDBMS operations assume certain rules have been followed. Database Decomposition is important for a number of reliability reasons. There are KV-stores out there that handle all these scenarios natively without the client having to worry about it (and doing a halfassed, buggy job about it because they are not DB designers). They have conflict resolution algorithms, offer CRDTs for the clients to use and engineers who know what they are doing, use them. Using the right tool for the right job is what experienced engineers do because it not only makes their lives easier but the businesses they help run more reliable and resilient. There is no magic.
marcus_holmes 6y ago
This is not true. I'm using it because the cost of changing the schema for a feature that may only be experimental is too high. I started off changing the schema every time, and it just got too expensive. Using the JSON-to-SQL workflow, I can mess around with feature design and iterate fast, and then crystallise the schema into SQL once it has stopped changing. Writing solid migrations can end up being more complex than the feature code itself, and when features are changing fast, writing solid migrations is kinda pointless. The feature may not even make it to production. But yeah, sure, I'm an idiot who doesn't know how to write proper SQL. rolleyes
- subhobroto 6y ago
  > This is not true. Everything or certain parts of it? > Using the JSON-to-SQL workflow, I can mess around with feature design and iterate fast, and then crystallise the schema into SQL once it has stopped changing. Sure, you're experimenting and things are in a state of flux. I get it. Been there done that, but not in production. Your customers want features over reliability. This is perfectly OK at a startup - the customers already depend on a system elsewhere but that system does not meet all their needs so they are experimenting with you but if your company runs to the ground tomorrow, they still have not migrated 100% over to you, so they are "safe". If your software loses data, they probably won't even notice until they really start to move over for real and lose money (if they did not do testing for consistency). In your position I would be honest and say data can be lost or corrupted and thats the price I am willing to pay for flexibility given the resource constraints I have. It's too expensive to do things properly and customers don't really want that reliability so we can take some risks. However, it would be naive to say you just designed an ACID complaint KV store by implementing some adhoc KV operations using JSON columns in a RDBMS. I make a lot of money based off the disasters software like this creates when the original architect of such a system have "moved on" to other companies. Data corruptions, losses, inconsistencies, meaningless key relationships, subpar performance (one place had two columns - an ID and a JSONB and were wondering why their query performance was poor, the DB locked so much and some updates never appeared to go through or "reset" the data). There are KV-stores out there, that sell for a pretty penny, handle all KV conflict scenarios natively without the KV-store client having to worry about it (and doing a halfassed, buggy job about it because they are not DB designers and might not even know the bombs they are planting in their code. Maybe ignorance is bliss?). These systems have conflict resolution algorithms, offer CRDTs for the clients to use and engineers who know what they are doing, use them. > The feature may not even make it to production Feel free to do whatever you want with code that never makes it to production and affect people's livelihood. I would argue you don't even need a DB to give you the warm fuzzy feelings and just do everything in memory. Afterall, memory is cheap and configuring a DB correctly can get too expensive. As an aside - if you're working at a company that regularly pushes features that do not even make it to production, there's a miscommunication issue. Your business is bleeding money. This is not to say overall, your business is not profitable - its just that it's bleeding money in that specific project and other more profitable ones are making up the slack. At the minimum, the deliverable should be broken down into a POC and, when it's clear a production need absolutely exists, that POC is delivered production ready. > Writing solid migrations can end up being more complex than the feature code itself Sure, thats the price you pay for ACID compliance. Nothing is free. There is no magic. > I'm using it because the cost of changing the schema for a feature that may only be experimental is too high. I started off changing the schema every time, and it just got too expensive. Again, you are free to do whatever you want in an experimental setup but we are talking production here. If your proposal is you push the same design to production that you use in your experimental/POC setup, in a serious, well used and depended on production environment without anyone noticing, I would really like to know more! My contact info in the profile. If your claim is you have figured out a way to get something for nothing - hmm. The ROI of the businesses you help run just trended towards infinity!
smoe 6y ago
Thing is, for the early stage development or prototyping, where JSON to SQL is most praised for, to be completely honest, I don't write good migrations either. I change the application code, trigger the cli to generate the sql migration and be done with it. In 99% of cases I don't even look at the generate file, I don't write specific migration tests, etc. If migration runs without error and the application tests pass in development and staging there is a good chance it will as well in production or auto rollback saving my ass. This works until it doesn't and you have to become much more vigilant. But at that point I much rather build upon the relative robustness and consistency of the sql I got so far, than JSON that is all over the place. Maybe it's my tooling, but almost every time I made a JSON column in Postgres for the sake of saving time in the past, it slowed me down almost immediately after adding it.
- subhobroto 6y ago
  > I much rather build upon the relative robustness and consistency of the sql I got so far, than JSON that is all over the place. Yup. > almost every time I made a JSON column in Postgres for the sake of saving time in the past, it slowed me down almost immediately after adding it Backs my experience fixing software from companies that had terrible data loss issues after these JSON hacks reared their ugly head. I am not saying JSON does not belong in a DB - I have many well tested and audited code that does do that. What I am saying is I have seen things go wrong far too much, for me to trust that someone who proposes that approach to actually know what they are getting themselves into. Plus, it's way cheaper to use a custom built KeyValDB that actually supports object semantics if one does not mean to use RDBMS semantics. Anyways, I am being severely downvoted and cant respond until a few hours between posts so I wont be responding any longer on this thread as I have a lot of work to do the next few days and cant wait around. See my other comments. Contact info in my profile, would love to be in touch!
  - aabbcc1241 6y ago
    I'm not sure what "custom built KeyValDB" means here.
    - 6y ago

benglish11 6y ago

>At that point the DB is like a key-value store and you get to claim you are using Postgresql. I know this statement is a little tongue in cheek but there is a lot of value organizationally to using Postgres as a key-value store vs MongoDb or some other flavor of no-sql. Even if you're not really getting all of the benefits of a relational db, you are still getting ACID transactions, envryption, user/access controls, backup/restore, libraries, etc. You're getting this in a tool that you are comfortable with and that you may be using in other areas where a relational model is more appropriate and thus have experience with a lot of the management aspects. Obviously if you are a mongo expert and using mongo everywhere this doesn't apply but in my experience software engineers/data engineers/DBA with postgres experience are easier to find.

subhobroto 6y ago
Hey there, thanks for reading my comments and sharing your thoughts. > in my experience software engineers/data engineers/DBA with postgres experience are easier to find. I need to understand what "postgres experience" means in this context. Having experience writing postgres client software using some postgres driver is a different kind of experience than installing, provisioning, monitoring and turning postgres itself. The former is a days worth of work for any good engineer; the later can take decades and those people are really hard to find. > you are still getting ACID transactions, envryption, user/access controls, backup/restore, libraries, etc Sure, at a high level, making a KV-store on top of a RDBMS feels like a grand idea - hey you get ACID for free and all the RDBMS goodness while getting the flexibility of a KV-store to boot. As they say - when something sounds too good to be true ... One small scenario worth thinking about: What does a KV-store on top of a RDBMS with ACID semantics even mean? How do you handle merging objects? Does the RDBMS even supporting merging objects inside a column? What would happen if I have the different attributes in the same object that I then update one after the other into a JSON column on a RDBMS? Does it fail one of the updates with an error message that data is lost? Does it automagically merge the data? How does it even know how to handle conflicts? Hint: No it cannot - the reason why a RDBMS can even offer ACID semantics is because RDBMS operations assume certain rules have been followed. Database Decomposition is important for a number of reliability reasons. There are KV-stores out there that handle all these scenarios natively without the client having to worry about it (and doing a halfassed, buggy job about it because they are not DB designers). They have conflict resolution algorithms, offer CRDTs for the clients to use and engineers who know what they are doing, use them. Using the right tool for the right job is what experienced engineers do because it not only makes their lives easier but the businesses they help run more reliable and resilient. There is no magic.

marcus_holmes 6y ago

This is not true. I'm using it because the cost of changing the schema for a feature that may only be experimental is too high. I started off changing the schema every time, and it just got too expensive. Using the JSON-to-SQL workflow, I can mess around with feature design and iterate fast, and then crystallise the schema into SQL once it has stopped changing. Writing solid migrations can end up being more complex than the feature code itself, and when features are changing fast, writing solid migrations is kinda pointless. The feature may not even make it to production. But yeah, sure, I'm an idiot who doesn't know how to write proper SQL. rolleyes

subhobroto 6y ago
> This is not true. Everything or certain parts of it? > Using the JSON-to-SQL workflow, I can mess around with feature design and iterate fast, and then crystallise the schema into SQL once it has stopped changing. Sure, you're experimenting and things are in a state of flux. I get it. Been there done that, but not in production. Your customers want features over reliability. This is perfectly OK at a startup - the customers already depend on a system elsewhere but that system does not meet all their needs so they are experimenting with you but if your company runs to the ground tomorrow, they still have not migrated 100% over to you, so they are "safe". If your software loses data, they probably won't even notice until they really start to move over for real and lose money (if they did not do testing for consistency). In your position I would be honest and say data can be lost or corrupted and thats the price I am willing to pay for flexibility given the resource constraints I have. It's too expensive to do things properly and customers don't really want that reliability so we can take some risks. However, it would be naive to say you just designed an ACID complaint KV store by implementing some adhoc KV operations using JSON columns in a RDBMS. I make a lot of money based off the disasters software like this creates when the original architect of such a system have "moved on" to other companies. Data corruptions, losses, inconsistencies, meaningless key relationships, subpar performance (one place had two columns - an ID and a JSONB and were wondering why their query performance was poor, the DB locked so much and some updates never appeared to go through or "reset" the data). There are KV-stores out there, that sell for a pretty penny, handle all KV conflict scenarios natively without the KV-store client having to worry about it (and doing a halfassed, buggy job about it because they are not DB designers and might not even know the bombs they are planting in their code. Maybe ignorance is bliss?). These systems have conflict resolution algorithms, offer CRDTs for the clients to use and engineers who know what they are doing, use them. > The feature may not even make it to production Feel free to do whatever you want with code that never makes it to production and affect people's livelihood. I would argue you don't even need a DB to give you the warm fuzzy feelings and just do everything in memory. Afterall, memory is cheap and configuring a DB correctly can get too expensive. As an aside - if you're working at a company that regularly pushes features that do not even make it to production, there's a miscommunication issue. Your business is bleeding money. This is not to say overall, your business is not profitable - its just that it's bleeding money in that specific project and other more profitable ones are making up the slack. At the minimum, the deliverable should be broken down into a POC and, when it's clear a production need absolutely exists, that POC is delivered production ready. > Writing solid migrations can end up being more complex than the feature code itself Sure, thats the price you pay for ACID compliance. Nothing is free. There is no magic. > I'm using it because the cost of changing the schema for a feature that may only be experimental is too high. I started off changing the schema every time, and it just got too expensive. Again, you are free to do whatever you want in an experimental setup but we are talking production here. If your proposal is you push the same design to production that you use in your experimental/POC setup, in a serious, well used and depended on production environment without anyone noticing, I would really like to know more! My contact info in the profile. If your claim is you have figured out a way to get something for nothing - hmm. The ROI of the businesses you help run just trended towards infinity!

smoe 6y ago

Thing is, for the early stage development or prototyping, where JSON to SQL is most praised for, to be completely honest, I don't write good migrations either. I change the application code, trigger the cli to generate the sql migration and be done with it. In 99% of cases I don't even look at the generate file, I don't write specific migration tests, etc. If migration runs without error and the application tests pass in development and staging there is a good chance it will as well in production or auto rollback saving my ass. This works until it doesn't and you have to become much more vigilant. But at that point I much rather build upon the relative robustness and consistency of the sql I got so far, than JSON that is all over the place. Maybe it's my tooling, but almost every time I made a JSON column in Postgres for the sake of saving time in the past, it slowed me down almost immediately after adding it.

subhobroto 6y ago
> I much rather build upon the relative robustness and consistency of the sql I got so far, than JSON that is all over the place. Yup. > almost every time I made a JSON column in Postgres for the sake of saving time in the past, it slowed me down almost immediately after adding it Backs my experience fixing software from companies that had terrible data loss issues after these JSON hacks reared their ugly head. I am not saying JSON does not belong in a DB - I have many well tested and audited code that does do that. What I am saying is I have seen things go wrong far too much, for me to trust that someone who proposes that approach to actually know what they are getting themselves into. Plus, it's way cheaper to use a custom built KeyValDB that actually supports object semantics if one does not mean to use RDBMS semantics. Anyways, I am being severely downvoted and cant respond until a few hours between posts so I wont be responding any longer on this thread as I have a lot of work to do the next few days and cant wait around. See my other comments. Contact info in my profile, would love to be in touch!
- aabbcc1241 6y ago
  I'm not sure what "custom built KeyValDB" means here.
  - 6y ago