5 ms·
I've said it as a joke many times that PostgreSQL can be a better NoSQL database than many actual NoSQL databases just by creating a (text, JSONB) table. You c
by theshrike79 2y ago
I've said it as a joke many times that PostgreSQL can be a better NoSQL database than many actual NoSQL databases just by creating a (text, JSONB) table.
You can do actual searches inside the JSON, index the table with JSONB contents etc. Things that became available in MongoDB very very late.
- Thaxll 2y agoIt's very painful to use JSON with regular SQL drivers, MongoDB driver is much better overall than regular SQL from a programmer perspective.
- javaunsafe2019 2y agoWait what? Writing mongo queries vs SQL is a pain in my experience…
- dtech 2y agoWhich language? In Java (/the JVM) it's perfectly fine while Mongo is a PITA
- nonethewiser 2y agoworking with JSONB in Postgres is painful. It's powerful, I love that it's there, but actually using it is always such a chore.
- krashidov 2y agoGenuinely curious, what do you find painful about it? Is it the syntax? Performance? Or does it just encourage bad practices?
- javaunsafe2019 2y agoI also don't get it - it's super easy to write queries with it. Please elaborate...
- nonethewiser 2y agostuff like this SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}'; or SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")'; which find documents in which the key "tags" contains array element "qui" https://www.postgresql.org/docs/current/datatype-json.html https://www.postgresql.org/docs/current/datatype-json.html
- Tostino 2y agoI had to ask Claude because I've never worked with Mongo, but apparently the equivalent query for MongoDB is: db.api.find( { "tags": "qui" }, { "guid": 1, "name": 1, "_id": 0 } )
- nonethewiser 2y agoIt's fair to compare alternatives but to be clear I'm not saying postgres JSONB is worse than mongo. I'm not very familiar with mongo either. Im saying it can be tedious is absolute terms. I did not assume this to be universal to document dbs but perhaps it could be. Although your example does seem simpler.
- zulban 2y agoTo your point, I replaced MongoDB with postgresql and a naive wrapper to still use JSON docs the same, and to my surprise my services got way faster and more stable. I write about it here: https://blog.stuartspence.ca/2023-05-goodbye-mongo.html https://blog.stuartspence.ca/2023-05-goodbye-mongo.html
- Thaxll 2y agoAnd many told you that you did not tell the whole story or did not understand mongodb but apparently you fail to adress those. Going from 150ms to 8ms and 80% CPU reduction does not make any sense perf wise. I stand my point it's missing a lot of details in your post and probably miss usage.
- smt88 2y agoIn GP’s shoes, why bother to learn Mongo then? What’s the benefit? Postgres has a good-enough (if not better) document DB inside it, but it also has a billion other features and widespread adoption. Mongo needs a huge benefit somewhere to justify using it.
- troupo 2y agoIf that reduction can be achieved by simply replacing Mongo with Postgres... what is the point of using Mongo? Edit: on top of that, very few things that you store in the DB are non-relational. So you will always end up recreating a relational database on top of any NoSQL database. So why bother when you can just go for a RDBMS from the start?
- Thews 2y agoWhile data can be used in a relational way, it doesn't mean that's the best for performance or storage. Important systems usually require compliance (auditing) and need things like soft deletion and versioning. Relational databases come to a crawl with that need. Sure you can implement things to make it better, but it's layers added that balloon the complexity. Most robust systems end up requiring more than one type of database. It is nice to work on projects with a limited scope where RDBMS is good enough.
- koolba 2y ago> I've said it as a joke many times that PostgreSQL can be a better NoSQL database than many actual NoSQL databases just by creating a (text, JSONB) table. Indeed, the tag line for one of the releases, I think 9.4 or 9.5, was “NoSQL on ACID”.
- sangnoir 2y agoI'd like to buy whoever came up with that tagline a beer.
- deleted 2y ago[deleted]
- mplanchard 2y agoOften true, but updates of JSON documents in postgres or inserts into nested arrays or objects are much, much slower, and involve locking the entire row. I think if your use case is insert/read only, it works well, though at scale even that can become an issue due to the GIN fastupdate index logic, which can lead to the occasional 20-30 second insert as the buffer gets flushed and the index updated.
- GordonS 2y ago> it works well, though at scale even that can become an issue due to the GIN fastupdate index logic, which can lead to the occasional 20-30 second insert as the buffer gets flushed and the index updated. Hmm, interesting! I've been having some intermittent issues recently with a Postgres table that uses JSONB, where everything seems to lock up for several minutes at a time, with no useful logs as to why. Do you have any more info about the GIN issue, and how it can be verified as being a problem please?
- joaohaas 2y agoA quick search brought this up: https://www.louisemeta.com/blog/indexes-gin-algorithms/ https://www.louisemeta.com/blog/indexes-gin-algorithms/ I think it illustrates the issue he's mentioning well enough.
- mplanchard 2y agoThis gitlab issue where they disabled it on one of their tables helped me a lot with understanding and debugging the problem: https://gitlab.com/gitlab-com/gl-infra/production/-/issues/7840 https://gitlab.com/gitlab-com/gl-infra/production/-/issues/7... It’s not a super well-documented feature in postgres, so I also wound up doing some digging in the mailing lists, but I was really appreciative of all the detail in that GL issue and the associated links.
- GordonS 2y agoThis is absolute gold, thanks!
- pjmlp 2y agoNoSQL is one of those cycles where I was quite happy to keep working with RDMS, while ignoring all the hype around it.
- tracker1 2y agoThere are lessons you can take with you all the same to relational rdbms. Especially with decent json support in the box.
- ak217 2y agoIt's not a joke. JSONB blows Mongo out of the water. I've run large production workloads on both. > You can do actual searches inside the JSON, index the table To be clear, you can do those things with Mongo (and the features have been available for a very long time), they just don't work as well, and are less reliable at scale.
- zX41ZdbW 2y agoThere is still a long road to improve database usability on semistructured data. My team has compared PostgreSQL and MongoDB on analytical queries on JSON, and the results so far have been unimpressive: https://github.com/ClickHouse/JSONBench/ https://github.com/ClickHouse/JSONBench/
- boruto 2y agoWe use postgres jsonb and have search built 30 million/~100-500 kb records. Only GIN/GIST indexes are possible, if we want to anything special(Search/send events if that is edited) at a deeper node of jsonb, the team prefers to extract it out of jsonb into a new column, which creates it own set of problems. Probably if someone looking to use jsonb to replace NoSQL DB would need to look deeper.
- tracker1 2y agoThe past few years that is often the first table I create in a database/schema for b configurations to store in the database.