jsonb is limited in it's querying capabilities. you're essentially casting the json as a string when storing and querying, it's not truly json. therein lies the advantage of document model, native secondary indexes, $<queries> for everry nested layer, etc.
Postgres's jsonb format is definitely not "essentially" a string. You can get native indexes on json fields in Postgres, and you can query fields however you like.
This is among the most shallow comment I ever saw on HN. Just because `TreeSet a = [1,2,3]` is surrounded by [ and ] does that mean `a` is a list? When you do `jsonb j = '{"a":"c"}` postgres builds a separate data structure for you from that json, which allows you to use a btree index.
I don't believe that is true, as far as I understand jsonb is a binary representation, not text. And there are certainly ways to index jsonb columns, either a function index on a particular jsonb expression or a GIN index so that you can perform contains (@>) queries against the whole jsonb contents.
I use postgres for pretty much everything these days, sometimes with Hasura if I need GraphQL.
There was a short period of time (1-2 years or so?) where I did my projects with Mongo, but right now I don't see what it can offer over something extremely stable like postgres, which also handles JSON amazingly well.
No, I had a sour experience in 2009 where it ate my data, the devs were rather cavalier with "there's a warning on the download page" (I got it through apt), it ate my data again when the OOM killer killed its process.
I didn't like the project attitude of a database being so lax with persistence, so I never used it again.
I feel like MongoDB now is actually a pretty stable product simply through time and investment, however I will never trust the company for using our data to beta test for a decade.
That's my attitude as well. RethinkDB, in comparison, had a much better attitude of "reliable first, fast later". Unfortunately, it turned out that when you're a database, it doesn't matter how much data you lose, only how fast you are while losing it.
Listening to the community and using Postgres is my biggest regret. In hindight, given our scale, any database would have worked. There is no built in solution for high availability with multiple VPS, and having one server alone isn't enough availability for me.
I love RethinkDB. I used it for 5 years at my previous company. It was document oriented, relational, and stable.
I use Postgres JSON blobs right now, but it's odd having a different syntax for keys/values depending on whether they're in a named row or a JSON blob.
> MySQL is slow as a dog. MongoDB will run circles around MySQL because MongoDB is web scale.
> "MongoDB does have some impressive benchmarks, but they do some interesting things to get those numbers. For example, when you write to MongoDB, you don't actually write anything. You stage your data to be written at a later time. If there's a problem writing your data, you're fucked. Does that sound like a good design to you?"
> If that's what they need to do to get those kickass benchmarks, then it's a great design.
> "..... If you were stupid enough to totally ignore durability just to get benchmarks, I suggest you pipe your data to /dev/null. It will be very fast."
> If /dev/null is fast and web scale I will use it. Is it web scale?
> "You are kidding me, right? I was making a joke. I mean, if you're happy writing to a database that doesn't give you any idea that your data is actually written just because you want high performance numbers, why not write to /dev/null? It's fast as hell."
There's no reasonable way to compare performance between PostgreSQL and MongoDB because they're not the same kind of product and they don't have a similar query interface.
You can take a particular application and compare performance when using two different databases on the back-end, but then the database itself might not necessarily be your bottleneck, it might also be the way the application is written. Because the database is only a part of the equation, the comparison also doesn't tell you anything about the performance of any other application.
You can get amazing performance results if you don't care about data persistence. Anyway when postgresql introduced JSONB there was a benchmark for CRUD operations, can't find it now, because it was long time ago, but I remeber postgresql was doing circles around mongodb.
MongoDB is a pretty good database IMO. I've used it at several companies in the past and wouldn't mind using it again.
My favorite DB is RethinkDB. It's a shame that the company behind it fizzled out and was absorbed by Stripe. I still cannot wrap my mind around why it's not more popular. It's similar to MongoDB but much better. It's the perfect database. It adds constraints which improve the quality of your code. Also RethinkDB scales very well and the control panel that comes with it is mind-bendingly powerful, I'm not kidding; you can click a button to shard or replicate a table over multiple hosts! WTF! I can't say the same about Postgres unfortunately. There is nothing truly remarkable about it.
I use Postgres for one of my projects today but purely because of compatibility reasons with an existing system. I don't understand what all the hype is with Postgres.
IMO I've come to see DB transactions as a hack because they limit scalability.
It's possible to use two-phase commits instead. Two-phase commits can scale without limit but you have to be more careful when designing your tables and specifying your indexes.
For tables which need atomicity, you can add a 'state' column/field which is either 'pending' or 'settled'. You can have a separate parallel subroutine (or process) in your code to handle settlement. You have to design it in such a way that if the system fails at any point, it will re-process all 'pending' entries in an idempotent way.
It's a bit more work, but it scales without limit. You can add a shard key (or use account IDs) so that you can have multiple processes/hosts working in parallel to insert pending transactions and also multiple processes to settle them in parallel.
In financial transactions, I would separate it into two parts: A debit entry and a credit entry which are initially inserted into the DB as 'pending'. The settlement routine will match them up together - Making sure to process/settle the debit side of the transaction first and only once the debit is in the settled state, it will process/settle the credit side.
If the settlement engine sees any conflict (e.g. user tries to spend more money than they have by submitting many transactions in parallel), it will accept all the transactions as 'pending' but mark the later ones as canceled (they will not settle) so the credit entry will never be created on the other account.
Exactly it's a bit more of work does make you prone to mistakes. This is what happens when you rewrite transaction on your application. See what happens with to a btc exchange using mongo who got hacked.
I'm a bit outdated with mongo since 2.4-2.6 . It's a bit traumatic and i'm never coming back to it.
If in case I'll need a high atomicity and consistency for financial transaction i'll just use postgres with SERIALIZABLE transaction isolation. This solves everything.
Checkout the redbook.io it's a bit outdated but you can see from stonebreaker's discussion that nosql + sql will merge, which is what happening or happened right now. Mongo having transaction, and SQL having json datatypes.
much of the anti-mongo sentiment is around people who used it in 2009-ish.
it's useful to point out that when using the early version of any software, there will be bugs. that's a trade-off early adopters always need to contend with. unfortunately, it hasn't sat well over time despite every one of the concerns being addressed since.
> much of the anti-mongo sentiment is around people who used it in 2009-ish.
So? I think this is a very plausible way of measuring how much you should trust something. There had to be a downside to the "move fast and break things" that mongodb subscribes to (i.e., add features now, think about reliability later).
As it turns out, they moved fast and broke their reputation.
i agree, there's reputational impact but again it's the trade off that early adopters make and are fully aware of.
When you're the first user of a brand new companies' database, you better bet there will still be kinks they're working through and let's face it you're the guinea pig.
i'm sure mongo is thankful for all these guinea pigs because they helped to define the roadmap and iron out the bugs. however, in the end of the day 2009 version of a product is entirely different than 2020 version. there are endless analogies (facebook, google, etc.) the only difference with an open source technology is users can analyze it under a microscope.
You seem to think this is about tech. It's not. It's about company culture, trust, and reputation.
Code can change reasonably fast, in some cases 10 years is enough to replace an entire codebase. But company culture has or can have an expected life time way longer than 10 years. In this case, my heuristic is justified.
I’m saying it’s completely illogical to base technical decisions on information that is 10 years old when just like you can see the modern specs of the iPhone and compare to the first iPod, you can also see the modern specs of Mongo, read third party assessments, and even look at the source code to see if it meets your needs.
> You make technical decisions based on what happened a decade ago? Do you make all decisions based on ten year old information?
Yes?
The problem was not that MongoDB was young, the problem was that authors of it had no idea about databases when they started their work. They stored data in RAM and get fastest benchmarks, because they didn't worry about persisting the data on disk. They outright lied in their documentation about the guarantees.
It wasn't the being early product part, it was the misrepresenting the truth part that hurt their credibility.
DynamoDB for example is much younger than MongoDB, and doesn't suffer from this.
yep I heard of it and had similar period of excitement I had with Mongo. Although when they were postponing addition of distributed functionality I lost interest. You can't design a good distributed system as an addon, you need to think about it from day 1.
At that point it was webscale ready. The problem was that MongoDB lied in their documentation what guarantees it provided. It took them a decade to improve, and now that data is safer (still not completely) it no longer offers performance edge over its competition.
Originally it was super fast, because it kept everything in RAM and didn't worry about writing data to disk, which caused many people to lose data.
So what's the status of RethinkDB? Is it still being developed? Who's working on it? Is it stable and feature-complete or are there any kinds of features missing that only a dedicated (paid) team would be able to pull off? Genuinely curious!
the hype about postgres is that there is no hype. it's one of those systems that have been working for decades. they have their flaws but they are well known and as long as you aware of them it does exactly what it is supposed to do.
I am still using it for few legacy apps. I don't really love it but it it is rock solid for that payload: mostly just storing simple events. For new projects I just use Postgres, especially after they've introduced json datatype I don't have need for noSQL database for type of projects I am doing.
Yes, still using it for storing relatively unstructured blobs of JSON which I only lookup via key but update with various operators ($addToSet, $set, $incr). Also using it as a persistent session store and lately for storing rate-limiting information. I've come to like the MongoDB update operators and features such as the Change Events. However I will eventually be moving off MongoDB as I need horizontal scaling via shards, and the sharding setup of MongoDB is needlessly complex and brittle. Currently planning to go with ScyllaDB for my large-volume key-value blob datastore and some flavour of SQL for low-volume things which need more complex query behavior (user database, subscriptions). ScyllaDB will present challenges since I still want to have the ability to update single keys in big JSON blobs, even if my only lookup is by key.
If MongoDB came up with a better sharding experience where every box is equal and you don't need the dance with shards on top of replica sets plus mongos plus config servers plus arbiters I might consider it again.
No offense, but if I wanted to have managed MongoDB I might as well use AWS DocumentDB.
I honestly believe that this is a key differentiating feature for many databases. ElasticSearch, ScyllaDB, others too work the same way: Every node is equal and in order to scale you just keep adding more boxes, end of story. Compare that with what you have to do with MongoDB.
DocumentDB is a great DB, but in the end of the day it's a forked version of 3.6-ish so it's missing a ton of new features like multi-collection ACID guaratneees.
Plus, whenever you use DocumentDB you're accessing it via the MongoDB official drivers, so you'll be handling that compatibility mess yourself.
It is just shocking that you would be on mongoDB and move away from it for horizontal sharding. As some one who has never used mongo that seems like the only reason to use it
I have never used MongoDB directly, but I can tell you since their license shenanigans I consider it toxic.
Their "we will try some different form of Open Source (which really isn't open source at all, but we still want you to think so, because we know open source is popular) also AWS did something evil by using our software accoding to the license that we used for our software" thing really didn't inspire any trust.
Yes, still use it, together with GridFS. We mostly use it as a persistence store for SOAP message dumps from XML to JSON in Java; everything else usually resides in a Postgres or Aurora RDS database on AWS or some legacy Oracle/MSSQL stuff elsewhere (which we try to get rid of).
The issue with most of this stuff is that a lot of projects just need some persistence and basic querying, and almost any database can do that equally fine. At that point, maintenance, ops workload in general or reliability are the differentiators and most of those go away when you run them as a service with your cloud provider of choice. Which specific one you use is practically selected for you: take all the ones that are compatible with your application or framework and sort by price.
Yes, it's our main DB. I still like it quite a lot, we use Mongoose as ODM, it makes adding new stuff so much easier without having to do things like alter table etc. But for our big data stuff we use BigQuery, simple because of cost.
I do like how easy it is to get a mongo instance up and running locally. I found maintenance tasks for mongo are much easier than postgres.
One thing you still need to do is manage indexes for performance, I've had to spend many a days tuning these.
I have come across some rather frustrating issues, for example a count documents call is executed as an aggregate call, but it doesn't do projection using your filters. e.g you want to count how many times the name 'hacker' appears. It will do the search against name, then do the $count, but because it doesn't do a projection, it will read the whole document in to do this. Which is not good when the property you're searching against has an index, so it shouldn't have to read in the document at all.
Yes, use it with Atlas for every one of my companies' projects.
- The document model is a no-brainer when working with JS on the front-end. I have JSON from the client, and Dictionaries on the backend (Flask), so it's as easy as dumping into the DB via the pymongo driver. No object relational mapping.
- Can scale up/down physical hardware as needed so we only pay for what we use
I'd echo most of those sentiments, but not all:
+ Support on Atlas is good
+ Set up process is very streamlined and smooth
+ Modifications (scaling IOS etc) are all very easy, but ...
- The Atlas web client had a bug that swapped data types (fixed, but still)
- The database starts off fast, but seemed to get quite slow considering how small it was (fitted entirely in RAM)
- The latest Jepsen report suggests t is still very cavalier with data integrity (http://jepsen.io/analyses/mongodb-4.2.6)
My experience has been the same, for I only used it for a couple weeks now.
I love their web interface - anyone knows if there are any cloud offerings for PostgresSQL which are similar?
No, I inherited a project that was using it a number of years back. After 6 months we migrated away to postgres. The data was really relational so it was just the wrong tool for the job. I can see that it might have value as a document store but with postgres' json facilities these days it's hard for me to see a scenario where I'd choose it.
No. Every time I've used mongodb we've ended up regretting it for one reason or another. And migrating to a different database after launch is a huge hassle.
I've done a couple projects where we kicked off with postgres using JSONB columns for early iteration. Then we gradually migrated to normal SQL columns as the product matured and our design decisions crystallized. That gave us basically all the benefits of mongodb but with a very smooth journey toward classical database semantics as we locked down features and scaled.
Back in 2010 when the MongoDB hype was high, the well-known company where I was working at the time decided to build the next version of the product using MongoDB. I was on the analytics team and had to code a whole bunch of intricate map-reduce jobs to extract summary data out of Mongo. I'd repeatedly head to the product team and ask them to explain the edge cases I was seeing in the data and they would not be able to give me an answer because the data was on the third or fourth version of their mentally-stored schema and no one knew anymore. All in all, misery.
DynamoDB is a completely different beast and would use no other data store unless I had to. It can pretty much handle any transactional workload I need.
It's cheap, its fast and it scales super high. Don't need much more
DynamoDB is consistent and scalable, not fast and cheap.
DDB is great for storing data that is required to be scalable and never needs to be joined. Add in DAX, developer time necessary to orchestrate transactions, calculate the scaling costs and...that's how AWS gets you.
Plus, local development requires half-complete emulators or a hosted database you're charged for.
No, maybe people should think twice about DynanoDB.
This came up in a thread a few days back, but people considering it should note that Dynamo’s transactions are limited to 25 rows. That may be enough for most operations, but I definitely wouldn’t say it can handle “any transactional workload”. I ran into this limit pretty quickly when trying it out.
DynamoDB is a lot more explicit about its tradeoffs. Much of the backlash against Mongo was because it basically claimed to be well-suited to any use case, when its sweet spot was really far narrower. To be successful with Mongo, you need to design the entire app around its limitations, but those limitations were initially downplayed and obscured.
People were convinced that Mongo was a good choice as a default, general purpose db, when it clearly wasn’t for about a million reasons.
I don’t think DynamoDB is marketed or viewed in the same way. The docs are pretty clear about needing to design your data model to specifically work well with Dynamo. People using it seem to generally be aware of its limitations, and deliberately choose to accept them for the sake of performance and scalability. At least that’s my perception.
Basically when speed and horizontal scalability are very important, and consistency/durability are less important. It’s also pretty good for unstructured or irregularly structured data that’s hard to write a schema for.
Web scraping or data ingestion from apis might be a reasonable use case. Or maybe consumer apps/games where occasional data loss or inconsistency isn’t a big deal.
It can also be used effectively as a kind of durable cache (with a nice query language) in place of redis/memcached if you give it plenty of ram. While its guarantees or lack thereof aren’t great for a database, they’re pretty good for a cache.
> I don’t think DynamoDB is marketed or viewed in the same way. The docs are pretty clear about needing to design your data model to specifically work well with Dynamo.
More importantly, AWS is very explicit in letting newcomers be perfectly aware that DynamoDB's usecases are, that they are very specific niche use cases, and that if users require schemas and joins then they should just stick with either relational databases or graph databases.
I decided to check out the hype back then, and started writing tutorials on using PHP with MongoDB. After the 3rd posting, I realized that they were all about being anti relational, and though you you could have keys to other records in your record. This lead to bringing back records, querying more records, then manually filtering records,rinse and repeat.
Iirc, they've turned around on their anti relational views and now allow for joins.
I looked at OrientDB awhile back ago, but it fell flat with the lack of features and oddness.
If I had more time, I would really dig into ArangoDB.
I never understood the appeal of the JSON to SQL columns workflow.
At least with the ORM(-ish) tools I worked with, it always felt much more straightforward to just change classes within the application code and automatically generate the respective migrations files to be run on the relational database, than having to interact as a human with json (for the app as well as for business intelligence and reporting/monitoring).
I feel I have handwritten significantly more schema and data migration code for non-sql databases and JSONB in Postgres than for relational databases in the last 10 years.
Sure once the database growths bigger, those automatically generate migrations files don't work as seamlessly anymore and can be dangerous, but no tool or database magically solves all the problems at scale.
> 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.
>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.
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.
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
> 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!
Sorry, I can't wade through that much bullshit. I got about halfway through a carefully-written refutation of everything you wrote, but you're not going to listen to me (since you didn't the first time either).
I know what I'm doing. I've been doing this for literally decades. Please stop assuming that people who don't agree with you are doing so from ignorance.
> but you're not going to listen to me (since you didn't the first time either)
I listened to what you had to say the first time (which is why I responded to it) and will continue to listen to what you have to say. My contact info is on my profile.
> Please stop assuming that people who don't agree with you are doing so from ignorance
I make no such assumptions.
I shared why I think the JSON blob on a RDBMS causes more pain than it solves and I look forward to your refutation of everything I wrote.
My contact info is on my profile and I keep it there because I like to know where I am wrong.
Have a great weekend and look forward to connecting with you
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.
> 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!
> At least with the ORM(-ish) tools I worked with, it always felt much more straightforward to just change classes within the application code and automatically generate the respective migrations...
There's your answer, no? You've got specific tools and workflows you've designed to work with your database in a specific way. You can't just take one workflow and substitute a piece of another workflow and expect it to always work. Your tools and processes would be as useless for me as mine would be for you.
Sure, but almost all the times (I'm not specifically referring to person in the comment I'm responding to and should have been more clear on that) people were making it fundamentally an sql vs. non-sql argument.
As in you need to switch your entire database system or make a bet on this completely new player instead of battle proven tech, because with relational databases schema migrations are hard. Among a bunch of other questionable claims.
Sure if you take the vanilla databases without their ecosystem, there is some merit to that, but I don't find it a very practical argument to ignore all the tooling and workflows that do exists and are used by people.
When MongoDB first came out, I was eager to check it out. I was using mainly Django with Postgres at the time, but had my career start on ZODB, an object-oriented non-sql database created in the late 90s. So I was hopeful that MongoDB could give me the best of both worlds. One of the reasons I backed of Mongo very quickly was the lack of tooling as soon as you have something resembling a schema or relationship and you'd want to change it.
I've recently adopted this practice. All new features use a JSONB (or hstore - I'm experimenting with both) field instead of a "real" one until they're bedded in and stop changing. Then I convert the field + data to a real field with a NOT NULL constraint in one easy migration.
So far so good. Being able to join on JSONB fields right in the SQL is awesome
The JSON-to-columns approach is a best practice for analytic applications. ClickHouse has a feature called materialized columns that allows you to do this cheaply. You can add new columns that are computed from JSON on existing rows and materialized for new rows.
I haven't used it for a while and I've been planning on learning Postgres after much praise from a friend (and the internet at large). I find DB design and migrations quite difficult though, any book/course recommendation?
I don't have a specific book for you, I myself learned about relational databases at school, not sure why is not being taught everywhere.
The things that you have problems with aren't really specific to PostgreSQL so if you are looking for a book while one based on PostgreSQL might be easier, there might be better books that talk about relational databases and what you learn will still apply.
About migrations, from PostgreSQL point of view you just use ALTER TABLE, CREATE INDEX, DROP INDEX etc. it's fairly trivial.
The problem comes that developers want to have it version controlled. So a framework around it is often built. Typically that functionality comes with whatever framework you are planning to use. For example if you use Python and Django framework it comes with migrations functionality, so you just learn what the framework provides.
I studied a different Engineering, not CS, so never really had any course (besides basic C). I've read quite a bit and understand the normalization forms, but it's more from a practical sense. What data should we include, how to structure it so that it's not too complex, but doesn't come back biting me, etc.
A good example (but not only!) is login. You can have just user session, or session+token in DB, or session + devices (1 active token/device), etc. So maybe seeing business problems and how the DB was structured to help would be the best here.
> The problem comes that developers want to have it version controlled.
Oh maybe that's the reason I found those so tricky. I don't explicitly care about VCS, but I definitely care about migrations going wrong, and hopefully being able to revert if it goes wrong. Maybe if I get myself comfortable enough with manual migrations this is not such a huge topic though.
When structuring data you generally want it to be in 3rd normal form or higher. The normal forms the higher you go the less duplication you have in your data, but then the more joins you will do and that could reduce performance.
> A good example (but not only!) is login. You can have just user session, or session+token in DB, or session + devices (1 active token/device), etc. So maybe seeing business problems and how the DB was structured to help would be the best here.
I think you're overthinking it a bit, besides I don't know if you're saying it but in case you are, you don't want to mix login information (like user account, name, login password etc) with a session. Those things are separate and have different requirements.
For example let's say your site became so popular that you have scaling problems. It is perfectly fine to take the session and put it in nosql store, or (maybe even better) in a cache. This data is accessed on key/value basis and also while not great it won't kill you if the data disappears due to some outage (users just get log out).
As to what you store there, session + device or session + token in DB is purely based on your need. Frankly I don't know what you mean by token in DB, in fact I'm not sure what do you mean session + device either. Sessions generally is a randomly generated ID that tied to a session, if user connects from a different device they will have another session anyway.
I don't think this particular thing is something that relational database dictates. But you generally wouldn't want to place sessions in user table. Those two things are completely different even when they seem to be related. You should have separate users table and separate session table (most frameworks handle sessions for you, so you might not even need to think too much about it)
> Oh maybe that's the reason I found those so tricky. I don't explicitly care about VCS, but I definitely care about migrations going wrong, and hopefully being able to revert if it goes wrong. Maybe if I get myself comfortable enough with manual migrations this is not such a huge topic though.
In PostgreSQL (it might not be true in other databases) DDL (Data Modification Language) can be inside of a transaction. So you can start a migration with "BEGIN" and then check if what you did worked fine before you say "COMMIT". Note though, when I said developers want to have it version controlled. They do that to avoid any mistakes. Such as forgetting the BEGIN, or accidentally pasting wrong thing to the shell. So if you have a production data you absolutely should use them. But if I were you I would first try to learn it by doing it by hand on a test database. If you understand what is actually done the migrations don't seem that magical.
No, we used MongoDB in 2009-2010 but it was a disaster as performance and integrity fell apart as our data-set grew. Maybe it’s OK now, but I see no reason to go back.
Yes, we do at WaystoCap. It's our main DB. I like it mostly for the flexibility. I think it's one of the best DBs when you're in pre-product market fit stage and making a lot of changes to the data model.
One of the main cons I've experienced with it, is it's beginner friendly nature and docs leads you to have a non-optimal data schema for No-SQL. Like even the way it does pagination with Skip, is not the performant way to do it.
As areas in our business mature and scale we suffer bottlenecks and have pretty big changes to optimize those areas of the data model.
I have not used it for years. I simply prefer relational databases after all.
When a document database makes sense, it happens, I go with CouchDB. Its multi-primary architecture is attractive compared to MongoDB with a single primary node. But I'm thinking about using PostgreSQL and jsonb next time.
242 comments
[ 5.4 ms ] story [ 258 ms ] threadthey are surrounded by quotes, is that not a string ?
vs
db.api.find({tags:"qui"}, {guid:1, name:1})
There was a short period of time (1-2 years or so?) where I did my projects with Mongo, but right now I don't see what it can offer over something extremely stable like postgres, which also handles JSON amazingly well.
I didn't like the project attitude of a database being so lax with persistence, so I never used it again.
My answer contains a history of my experiences with MongoDB that is pretty similar to yours:
https://stackoverflow.com/a/18269939/123671
I feel like MongoDB now is actually a pretty stable product simply through time and investment, however I will never trust the company for using our data to beta test for a decade.
https://aws.amazon.com/rds/ha/
https://github.com/zalando/patroni
https://repmgr.org/
I use Postgres JSON blobs right now, but it's odd having a different syntax for keys/values depending on whether they're in a named row or a JSON blob.
Ahh yes, just like the dialogue of the never-stale "Mongodb is Webscale" meme video:
https://www.youtube.com/watch?v=b2F-DItXtZs
http://www.mongodb-is-web-scale.com/
> MySQL is slow as a dog. MongoDB will run circles around MySQL because MongoDB is web scale.
> "MongoDB does have some impressive benchmarks, but they do some interesting things to get those numbers. For example, when you write to MongoDB, you don't actually write anything. You stage your data to be written at a later time. If there's a problem writing your data, you're fucked. Does that sound like a good design to you?"
> If that's what they need to do to get those kickass benchmarks, then it's a great design.
> "..... If you were stupid enough to totally ignore durability just to get benchmarks, I suggest you pipe your data to /dev/null. It will be very fast."
> If /dev/null is fast and web scale I will use it. Is it web scale?
> "You are kidding me, right? I was making a joke. I mean, if you're happy writing to a database that doesn't give you any idea that your data is actually written just because you want high performance numbers, why not write to /dev/null? It's fast as hell."
> Does /dev/null support sharding?
I watch the video every time I see it just for this shit.
https://www.youtube.com/watch?v=bzkRVzciAZg
Because from my experience MongoDB was at least 10x faster and all of the benchmarks I've seen were using pre-WiredTiger storage engine.
You can take a particular application and compare performance when using two different databases on the back-end, but then the database itself might not necessarily be your bottleneck, it might also be the way the application is written. Because the database is only a part of the equation, the comparison also doesn't tell you anything about the performance of any other application.
In our last 3 years of usage, we never faced any issues from DB. It's improved a lot in last 2-3 years and I can say it's rock solid right now.
I am basically finding no reason to switch to any other DB.
My favorite DB is RethinkDB. It's a shame that the company behind it fizzled out and was absorbed by Stripe. I still cannot wrap my mind around why it's not more popular. It's similar to MongoDB but much better. It's the perfect database. It adds constraints which improve the quality of your code. Also RethinkDB scales very well and the control panel that comes with it is mind-bendingly powerful, I'm not kidding; you can click a button to shard or replicate a table over multiple hosts! WTF! I can't say the same about Postgres unfortunately. There is nothing truly remarkable about it.
I use Postgres for one of my projects today but purely because of compatibility reasons with an existing system. I don't understand what all the hype is with Postgres.
Also, you'll encounter more problems with mongodb if in case you need transaction. You should check out more discussion here on db systems.
http://www.redbook.io/
If you use MongoDB today then (a) there is no global write lock and (b) there are transactions.
It's possible to use two-phase commits instead. Two-phase commits can scale without limit but you have to be more careful when designing your tables and specifying your indexes.
For tables which need atomicity, you can add a 'state' column/field which is either 'pending' or 'settled'. You can have a separate parallel subroutine (or process) in your code to handle settlement. You have to design it in such a way that if the system fails at any point, it will re-process all 'pending' entries in an idempotent way.
It's a bit more work, but it scales without limit. You can add a shard key (or use account IDs) so that you can have multiple processes/hosts working in parallel to insert pending transactions and also multiple processes to settle them in parallel.
In financial transactions, I would separate it into two parts: A debit entry and a credit entry which are initially inserted into the DB as 'pending'. The settlement routine will match them up together - Making sure to process/settle the debit side of the transaction first and only once the debit is in the settled state, it will process/settle the credit side.
If the settlement engine sees any conflict (e.g. user tries to spend more money than they have by submitting many transactions in parallel), it will accept all the transactions as 'pending' but mark the later ones as canceled (they will not settle) so the credit entry will never be created on the other account.
https://dzone.com/articles/mongodb-bitcoin-how-nosql (article is 2014)
I'm a bit outdated with mongo since 2.4-2.6 . It's a bit traumatic and i'm never coming back to it.
If in case I'll need a high atomicity and consistency for financial transaction i'll just use postgres with SERIALIZABLE transaction isolation. This solves everything.
Checkout the redbook.io it's a bit outdated but you can see from stonebreaker's discussion that nosql + sql will merge, which is what happening or happened right now. Mongo having transaction, and SQL having json datatypes.
it's useful to point out that when using the early version of any software, there will be bugs. that's a trade-off early adopters always need to contend with. unfortunately, it hasn't sat well over time despite every one of the concerns being addressed since.
So? I think this is a very plausible way of measuring how much you should trust something. There had to be a downside to the "move fast and break things" that mongodb subscribes to (i.e., add features now, think about reliability later).
As it turns out, they moved fast and broke their reputation.
When you're the first user of a brand new companies' database, you better bet there will still be kinks they're working through and let's face it you're the guinea pig.
i'm sure mongo is thankful for all these guinea pigs because they helped to define the roadmap and iron out the bugs. however, in the end of the day 2009 version of a product is entirely different than 2020 version. there are endless analogies (facebook, google, etc.) the only difference with an open source technology is users can analyze it under a microscope.
You seem to think this is about tech. It's not. It's about company culture, trust, and reputation.
Code can change reasonably fast, in some cases 10 years is enough to replace an entire codebase. But company culture has or can have an expected life time way longer than 10 years. In this case, my heuristic is justified.
(For the younger people https://slashdot.org/story/01/10/23/1816257/apple-releases-i...)
I think you're making an active effort to misunderstand me.
I’ve only developed software from everything from 8 bit computers with 128K of RAM, to DEC mainframes to Android tablets....
What do I know?
Yes?
The problem was not that MongoDB was young, the problem was that authors of it had no idea about databases when they started their work. They stored data in RAM and get fastest benchmarks, because they didn't worry about persisting the data on disk. They outright lied in their documentation about the guarantees.
It wasn't the being early product part, it was the misrepresenting the truth part that hurt their credibility.
DynamoDB for example is much younger than MongoDB, and doesn't suffer from this.
> It wasn't the being early product part, it was the misrepresenting the truth part that hurt their credibility.
That's why I replied to the person you're responding to that his view of software development is cartoonish, but I don't think he got it.
> the problem was that authors of it had no idea about databases when they started their work
Speaking of which, have you heard of influxdb? It's going down the exact same route.
Originally it was super fast, because it kept everything in RAM and didn't worry about writing data to disk, which caused many people to lose data.
the hype about postgres is that there is no hype. it's one of those systems that have been working for decades. they have their flaws but they are well known and as long as you aware of them it does exactly what it is supposed to do.
If MongoDB came up with a better sharding experience where every box is equal and you don't need the dance with shards on top of replica sets plus mongos plus config servers plus arbiters I might consider it again.
I honestly believe that this is a key differentiating feature for many databases. ElasticSearch, ScyllaDB, others too work the same way: Every node is equal and in order to scale you just keep adding more boxes, end of story. Compare that with what you have to do with MongoDB.
DocumentDB is a great DB, but in the end of the day it's a forked version of 3.6-ish so it's missing a ton of new features like multi-collection ACID guaratneees. Plus, whenever you use DocumentDB you're accessing it via the MongoDB official drivers, so you'll be handling that compatibility mess yourself.
Their "we will try some different form of Open Source (which really isn't open source at all, but we still want you to think so, because we know open source is popular) also AWS did something evil by using our software accoding to the license that we used for our software" thing really didn't inspire any trust.
The issue with most of this stuff is that a lot of projects just need some persistence and basic querying, and almost any database can do that equally fine. At that point, maintenance, ops workload in general or reliability are the differentiators and most of those go away when you run them as a service with your cloud provider of choice. Which specific one you use is practically selected for you: take all the ones that are compatible with your application or framework and sort by price.
I do like how easy it is to get a mongo instance up and running locally. I found maintenance tasks for mongo are much easier than postgres.
One thing you still need to do is manage indexes for performance, I've had to spend many a days tuning these.
I have come across some rather frustrating issues, for example a count documents call is executed as an aggregate call, but it doesn't do projection using your filters. e.g you want to count how many times the name 'hacker' appears. It will do the search against name, then do the $count, but because it doesn't do a projection, it will read the whole document in to do this. Which is not good when the property you're searching against has an index, so it shouldn't have to read in the document at all.
- The document model is a no-brainer when working with JS on the front-end. I have JSON from the client, and Dictionaries on the backend (Flask), so it's as easy as dumping into the DB via the pymongo driver. No object relational mapping.
- Can scale up/down physical hardware as needed so we only pay for what we use
- Sharding is painfully easily, with one click
- Support has been incredible
I've done a couple projects where we kicked off with postgres using JSONB columns for early iteration. Then we gradually migrated to normal SQL columns as the product matured and our design decisions crystallized. That gave us basically all the benefits of mongodb but with a very smooth journey toward classical database semantics as we locked down features and scaled.
Back in 2010 when the MongoDB hype was high, the well-known company where I was working at the time decided to build the next version of the product using MongoDB. I was on the analytics team and had to code a whole bunch of intricate map-reduce jobs to extract summary data out of Mongo. I'd repeatedly head to the product team and ask them to explain the edge cases I was seeing in the data and they would not be able to give me an answer because the data was on the third or fourth version of their mentally-stored schema and no one knew anymore. All in all, misery.
Since they are both of the NoSQL family.
It's cheap, its fast and it scales super high. Don't need much more
DDB is great for storing data that is required to be scalable and never needs to be joined. Add in DAX, developer time necessary to orchestrate transactions, calculate the scaling costs and...that's how AWS gets you.
Plus, local development requires half-complete emulators or a hosted database you're charged for.
No, maybe people should think twice about DynanoDB.
People were convinced that Mongo was a good choice as a default, general purpose db, when it clearly wasn’t for about a million reasons.
I don’t think DynamoDB is marketed or viewed in the same way. The docs are pretty clear about needing to design your data model to specifically work well with Dynamo. People using it seem to generally be aware of its limitations, and deliberately choose to accept them for the sake of performance and scalability. At least that’s my perception.
Web scraping or data ingestion from apis might be a reasonable use case. Or maybe consumer apps/games where occasional data loss or inconsistency isn’t a big deal.
It can also be used effectively as a kind of durable cache (with a nice query language) in place of redis/memcached if you give it plenty of ram. While its guarantees or lack thereof aren’t great for a database, they’re pretty good for a cache.
I went on to work at mongodb
More importantly, AWS is very explicit in letting newcomers be perfectly aware that DynamoDB's usecases are, that they are very specific niche use cases, and that if users require schemas and joins then they should just stick with either relational databases or graph databases.
Those who overpromise will always disappoint.
Iirc, they've turned around on their anti relational views and now allow for joins.
I looked at OrientDB awhile back ago, but it fell flat with the lack of features and oddness.
If I had more time, I would really dig into ArangoDB.
At least with the ORM(-ish) tools I worked with, it always felt much more straightforward to just change classes within the application code and automatically generate the respective migrations files to be run on the relational database, than having to interact as a human with json (for the app as well as for business intelligence and reporting/monitoring).
I feel I have handwritten significantly more schema and data migration code for non-sql databases and JSONB in Postgres than for relational databases in the last 10 years.
Sure once the database growths bigger, those automatically generate migrations files don't work as seamlessly anymore and can be dangerous, but no tool or database magically solves all the problems at scale.
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.
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.
> 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.
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
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!
I know what I'm doing. I've been doing this for literally decades. Please stop assuming that people who don't agree with you are doing so from ignorance.
I listened to what you had to say the first time (which is why I responded to it) and will continue to listen to what you have to say. My contact info is on my profile.
> Please stop assuming that people who don't agree with you are doing so from ignorance
I make no such assumptions.
I shared why I think the JSON blob on a RDBMS causes more pain than it solves and I look forward to your refutation of everything I wrote.
My contact info is on my profile and I keep it there because I like to know where I am wrong.
Have a great weekend and look forward to connecting with you
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.
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!
There's your answer, no? You've got specific tools and workflows you've designed to work with your database in a specific way. You can't just take one workflow and substitute a piece of another workflow and expect it to always work. Your tools and processes would be as useless for me as mine would be for you.
As in you need to switch your entire database system or make a bet on this completely new player instead of battle proven tech, because with relational databases schema migrations are hard. Among a bunch of other questionable claims.
Sure if you take the vanilla databases without their ecosystem, there is some merit to that, but I don't find it a very practical argument to ignore all the tooling and workflows that do exists and are used by people.
When MongoDB first came out, I was eager to check it out. I was using mainly Django with Postgres at the time, but had my career start on ZODB, an object-oriented non-sql database created in the late 90s. So I was hopeful that MongoDB could give me the best of both worlds. One of the reasons I backed of Mongo very quickly was the lack of tooling as soon as you have something resembling a schema or relationship and you'd want to change it.
So far so good. Being able to join on JSONB fields right in the SQL is awesome
The things that you have problems with aren't really specific to PostgreSQL so if you are looking for a book while one based on PostgreSQL might be easier, there might be better books that talk about relational databases and what you learn will still apply.
About migrations, from PostgreSQL point of view you just use ALTER TABLE, CREATE INDEX, DROP INDEX etc. it's fairly trivial.
The problem comes that developers want to have it version controlled. So a framework around it is often built. Typically that functionality comes with whatever framework you are planning to use. For example if you use Python and Django framework it comes with migrations functionality, so you just learn what the framework provides.
A good example (but not only!) is login. You can have just user session, or session+token in DB, or session + devices (1 active token/device), etc. So maybe seeing business problems and how the DB was structured to help would be the best here.
> The problem comes that developers want to have it version controlled.
Oh maybe that's the reason I found those so tricky. I don't explicitly care about VCS, but I definitely care about migrations going wrong, and hopefully being able to revert if it goes wrong. Maybe if I get myself comfortable enough with manual migrations this is not such a huge topic though.
> A good example (but not only!) is login. You can have just user session, or session+token in DB, or session + devices (1 active token/device), etc. So maybe seeing business problems and how the DB was structured to help would be the best here.
I think you're overthinking it a bit, besides I don't know if you're saying it but in case you are, you don't want to mix login information (like user account, name, login password etc) with a session. Those things are separate and have different requirements.
For example let's say your site became so popular that you have scaling problems. It is perfectly fine to take the session and put it in nosql store, or (maybe even better) in a cache. This data is accessed on key/value basis and also while not great it won't kill you if the data disappears due to some outage (users just get log out). As to what you store there, session + device or session + token in DB is purely based on your need. Frankly I don't know what you mean by token in DB, in fact I'm not sure what do you mean session + device either. Sessions generally is a randomly generated ID that tied to a session, if user connects from a different device they will have another session anyway.
I don't think this particular thing is something that relational database dictates. But you generally wouldn't want to place sessions in user table. Those two things are completely different even when they seem to be related. You should have separate users table and separate session table (most frameworks handle sessions for you, so you might not even need to think too much about it)
> Oh maybe that's the reason I found those so tricky. I don't explicitly care about VCS, but I definitely care about migrations going wrong, and hopefully being able to revert if it goes wrong. Maybe if I get myself comfortable enough with manual migrations this is not such a huge topic though.
In PostgreSQL (it might not be true in other databases) DDL (Data Modification Language) can be inside of a transaction. So you can start a migration with "BEGIN" and then check if what you did worked fine before you say "COMMIT". Note though, when I said developers want to have it version controlled. They do that to avoid any mistakes. Such as forgetting the BEGIN, or accidentally pasting wrong thing to the shell. So if you have a production data you absolutely should use them. But if I were you I would first try to learn it by doing it by hand on a test database. If you understand what is actually done the migrations don't seem that magical.
There was a time when JSON wasn't that integrated into databases, it is now. That was really MongoDB's killer feature.
PostgreSQL does most of this better now and more robust/reliable.
One of the main cons I've experienced with it, is it's beginner friendly nature and docs leads you to have a non-optimal data schema for No-SQL. Like even the way it does pagination with Skip, is not the performant way to do it.
As areas in our business mature and scale we suffer bottlenecks and have pretty big changes to optimize those areas of the data model.
When a document database makes sense, it happens, I go with CouchDB. Its multi-primary architecture is attractive compared to MongoDB with a single primary node. But I'm thinking about using PostgreSQL and jsonb next time.