News for hackers. Stuff that matters.
Re:Microsoft open sources PostgreSQL extensions to muscle in... by theshrike79 on Tuesday February 11, 2025 @08:45AM (#43012645)
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.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by zulban on Tuesday February 11, 2025 @09:36AM (#43013139)
To 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

Re:Microsoft open sources PostgreSQL extensions to muscle in... by Thaxll on Tuesday February 11, 2025 @10:28AM (#43013913)
And 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.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by smt88 on Tuesday February 11, 2025 @11:21AM (#43014679)
In 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.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by troupo on Tuesday February 11, 2025 @11:39AM (#43014900)
If 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?

Re:Microsoft open sources PostgreSQL extensions to muscle in... by Thews on Tuesday February 11, 2025 @12:34PM (#43015583)
While 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.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by troupo on Tuesday February 11, 2025 @03:03PM (#43017646)
> and need things like soft deletion and versioning. Relational databases come to a crawl with that need.

Lol. No relational database slows to a crawl on `is_deleted=true` or versioning

In general so far not a single claim by NoSQL databases has been shown to be true. Except KV databases, as they have their own uses

Re:Microsoft open sources PostgreSQL extensions to muscle in... by Thews on Tuesday February 11, 2025 @05:27PM (#43019180)
They slow to a crawl when you have huge tables with lots of versioned data and massive indexes that can't perform maintenance in a reasonable amount of time, even with the fastest vertically scaled hardware. You run into issues partitioning the data and spreading it across processors, and spreading it across servers takes solutions that require engineering teams.

There's a large amount of solutions for different kinds of data for a reason.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by pritambaral on Wednesday February 12, 2025 @02:26AM (#43022785)
I have built "huge tables with lots of versioned data and massive indexes". This is false. I had no issues partitioning the data and spreading it across shards. On Postgres.

> ... takes solutions that require engineering teams.

All it took was an understanding of the data. And just one guy (me), not an "engineering team". Mongo knows only one way of sharding data. That one way may work for some use-cases, but for the vast majority of use-cases it's a Bad Idea. Postgres lets me do things in many different ways, and that's without extensions.

If you don't understand your data, and you buy in to the marketing bullshit of a proprietary "solution", and you're too gullible to see through their lies, well, you're doomed to fail.

This fear-mongering that you're trying to pull in favour of the pretending-to-be-a-DB that is Mongo is not going to work anymore. It's not the early 2010s.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by Thews on Wednesday February 12, 2025 @06:43AM (#43024354)
Where did I ever say anything about Mongo?

I have worked with tables on this scale. It definitely is not a walk in the park with traditional setups. https://www.timescale.com/blog/scaling-postgresql-to-petabyt...

Now data chunked into objects distributed around to be accessed by lots of servers, that's no sweat.

I'd love to see how you handle database maintenance when your active data is over 100TB.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by troupo on Wednesday February 12, 2025 @07:29AM (#43024704)
I'd love to see a NoSQL database handling this easier than a RDBMS
Re:Microsoft open sources PostgreSQL extensions to muscle in... by Thews on Wednesday February 12, 2025 @08:13AM (#43025064)
You mean like scylla?
Re:Microsoft open sources PostgreSQL extensions to muscle in... by troupo on Wednesday February 12, 2025 @07:29AM (#43024698)
> They slow to a crawl when you have huge tables

Define "huge". Define "massive".

For modern RDBMS that starts at volumes that can't really fit on one machine (for some definition of "one machine"). I doubt Mongo would be very happy at that scale, too.

On top of that an analysis of the query plan usually shows trivially fixable bottlenecks.

On top of that it also depends on how you store your versioned data (wikipedia stores gzipped diffs, and runs on PHP and MariaDB).

Again, none of the claims you presented have any solid evidence in real world.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by Thews on Wednesday February 12, 2025 @08:38AM (#43025289)
Wikipedia is tiny data. You don't start to really see cost scaling issues until you have active data a few hundred times larger and your data changes enough that autovacuuming can't keep up.

I'm getting paid to move a database that size this morning.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by troupo on Wednesday February 12, 2025 @10:45AM (#43026481)
English language Wikipedia revision history dump: April 2019: 18 880 938 139 465 bytes (19 TB) uncompressed. 937GB bz2 compressed. 157GB 7z compressed.

I assume since then it's grown at least ten-fold. It's already an amount of data that would cripple most NoSQL solutions on the market.

I honestly feel like talking to functional programming zealots. There's this fictional product that is oh so much better than whatever tool you're talking about. No one has seen it, no one has proven it exists, or works better than the current perfectly adequate and performant tool. But trust us, for some ridiculous vaguely specified constraints it definitely works amazingly well.

This time "RDBMS is bad at soft deletions and versions because 19TBs of revisions on one of the world's most popular websites is tiny"

[1] https://meta.wikimedia.org/wiki/Data_dumps/Dumps_sizes_and_g...

Re:Microsoft open sources PostgreSQL extensions to muscle in... by Thews on Wednesday February 12, 2025 @12:03PM (#43027257)
Wikipedia's active english data is only 24gb compressed. https://dumps.wikimedia.org/enwiki/20250201/

They store revisions in compressed storage mostly read only for archival. https://wikitech.wikimedia.org/wiki/MariaDB#External_storage

They have the layout and backup plans of their servers available.

They've got an efficient layout, and they use caching, and it is by nature very read intensive.

https://wikitech.wikimedia.org/wiki/MariaDB#/media/File:Wiki...

Archival read only servers don't have to worry about any of the maintenance mentioned. Use chatgpt or something to play your devil's advocate, because what you're saying is magical and non existent is quite common.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by ak217 on Tuesday February 11, 2025 @11:58AM (#43015138)
It'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.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by koolba on Tuesday February 11, 2025 @09:44AM (#43013263)
> 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”.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by sangnoir on Tuesday February 11, 2025 @02:27PM (#43017185)
I'd like to buy whoever came up with that tagline a beer.
Re:Microsoft open sources PostgreSQL extensions to muscle in... by koolba on Tuesday February 11, 2025 @07:55PM (#43020453)
There was this talk around that time: https://www.pgcon.org/2015/schedule/events/868.en.html

Though pretty sure Bruce is a teetotaler.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by sangnoir on Friday February 14, 2025 @12:19PM (#43050617)
Root beer, then, or any beverage of their choice.
Re:Microsoft open sources PostgreSQL extensions to muscle in... by mplanchard on Tuesday February 11, 2025 @10:12AM (#43013689)
Often 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.
Re:Microsoft open sources PostgreSQL extensions to muscle in... by GordonS on Tuesday February 11, 2025 @10:28AM (#43013908)
> 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?

Re:Microsoft open sources PostgreSQL extensions to muscle in... by mplanchard on Tuesday February 11, 2025 @11:47AM (#43014987)
This 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/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.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by GordonS on Wednesday February 12, 2025 @10:46AM (#43026491)
This is absolute gold, thanks!
Re:Microsoft open sources PostgreSQL extensions to muscle in... by joaohaas on Tuesday February 11, 2025 @10:37AM (#43014039)
A quick search brought this up: https://www.louisemeta.com/blog/indexes-gin-algorithms/

I think it illustrates the issue he's mentioning well enough.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by pjmlp on Tuesday February 11, 2025 @10:18AM (#43013779)
NoSQL is one of those cycles where I was quite happy to keep working with RDMS, while ignoring all the hype around it.
Re:Microsoft open sources PostgreSQL extensions to muscle in... by tracker1 on Thursday February 13, 2025 @04:21AM (#43034209)
There are lessons you can take with you all the same to relational rdbms. Especially with decent json support in the box.
Re:Microsoft open sources PostgreSQL extensions to muscle in... by Thaxll on Tuesday February 11, 2025 @09:24AM (#43013008)
It's very painful to use JSON with regular SQL drivers, MongoDB driver is much better overall than regular SQL from a programmer perspective.
Re:Microsoft open sources PostgreSQL extensions to muscle in... by dtech on Tuesday February 11, 2025 @10:03AM (#43013552)
Which language? In Java (/the JVM) it's perfectly fine while Mongo is a PITA
Re:Microsoft open sources PostgreSQL extensions to muscle in... by nonethewiser on Tuesday February 11, 2025 @10:13AM (#43013704)
working with JSONB in Postgres is painful.

It's powerful, I love that it's there, but actually using it is always such a chore.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by krashidov on Tuesday February 11, 2025 @12:53PM (#43015814)
Genuinely curious, what do you find painful about it? Is it the syntax? Performance? Or does it just encourage bad practices?
Re:Microsoft open sources PostgreSQL extensions to muscle in... by javaunsafe2019 on Tuesday February 11, 2025 @02:28PM (#43017194)
I also don't get it - it's super easy to write queries with it. Please elaborate...
Re:Microsoft open sources PostgreSQL extensions to muscle in... by nonethewiser on Tuesday February 11, 2025 @06:03PM (#43019511)
stuff 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

Re:Microsoft open sources PostgreSQL extensions to muscle in... by Tostino on Tuesday February 11, 2025 @08:20PM (#43020648)
I 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
      }
    )
Re:Microsoft open sources PostgreSQL extensions to muscle in... by nonethewiser on Tuesday February 11, 2025 @08:31PM (#43020740)
It'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.
Re:Microsoft open sources PostgreSQL extensions to muscle in... by javaunsafe2019 on Tuesday February 11, 2025 @09:27AM (#43013040)
Wait what? Writing mongo queries vs SQL is a pain in my experience…
Re:Microsoft open sources PostgreSQL extensions to muscle in... by zX41ZdbW on Tuesday February 11, 2025 @12:49PM (#43015768)
There 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/

Re:Microsoft open sources PostgreSQL extensions to muscle in... by boruto on Wednesday February 12, 2025 @04:39AM (#43023608)
We 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.

Re:Microsoft open sources PostgreSQL extensions to muscle in... by tracker1 on Thursday February 13, 2025 @04:12AM (#43034163)
The past few years that is often the first table I create in a database/schema for b configurations to store in the database.