3 ms·

I would recommend against modeling too many relations in a document DB. It can work, but it gets bogged down very quickly for the reasons you've stated. My off

I would recommend against modeling too many relations in a document DB. It can work, but it gets bogged down very quickly for the reasons you've stated. My off-the-cuff suggestion is that document databases are not built to model normalized relational data. If you try to do so, you bring a whole new level of pain upon yourself. It is do-able, but it is hard and annoying. I know this from extensive personal experience dealing with a large, highly relational Mongo database. I am very glad you found a solution within Postgres that works for you, and your mapping of document to row and collection to table is very apt! If possible, would you care to tell me what the size of said documents (rows) and collections (tables) are in your solution? I am curious if in another life, we might have built our tech stack on Postgres instead of MongoDB and been much happier for doing so.

tasubotadas 3y ago
Joins only make sense in analytical context as a tool to get some additional data into your report and almost never as domain modeling concept. People should pretty much hardly ever use RDBMS for their domain data... but since everyone learns RDBMS as their first DB, we have these horrible ORM frameworks on every corner.
- tracker1 3y ago
  Yeah, I've kind of railed against ORMs for a while now... I'd just assume a simple data mapper (like Dapper for C#) or be really explicit in a scripted language with template queries.
- unusualmonkey 3y ago
  Curious why you think this? I've found Relational databases pretty powerful, and learned mongo first.
  - tasubotadas 3y ago
    They are powerful. But often their Power is unnecessary and counter productive for domain modeling. Where are they really good is reporting. Analysts love rdbms. When I am on an analyst role, I love them them too. As an engineer I find them redundant. More about domain modeling https://dev.tasubo.com/2022/07/crash-course-domain-driven-design.html#aggregates--aggregate-root https://dev.tasubo.com/2022/07/crash-course-domain-driven-de...
    - unusualmonkey 3y ago
      1) That article says nothing against using rdms in my quick perusal, in fact it suggests MySQL! 2) In practice having the ability to run reports on your data can be super important and useful. I'm still confused at the point you're trying to make.
deepsun 3y ago
We had everything de-normalized, as you pointed out. However, every now and then we had some one-off questions that required using Mongo for something it's not designed for. However, migrating to postgres+JSONB made it easy to do both.
- phamilton 3y ago
  We've been on a big "Use postgres for everything" path. Mostly migrating dynamodb, elasticsearch, and redis to postgres. The number of times we've had to do some one-off query that was orthogonal to production usage is higher than I predicted. There were so many times in the past where we just didn't fix something because it was too difficult to backfill a document store. We just hacked around it. Now with postgres we fix the root of the problem instead of hacking around it. And we do so with a level of confidence we've never had with document stores.
dfragnito 3y ago
This is one of the reasons we created https://schemafreesql.com https://schemafreesql.com. We wanted SQL with some of the document store (NOSQL) features.

tasubotadas 3y ago

Joins only make sense in analytical context as a tool to get some additional data into your report and almost never as domain modeling concept. People should pretty much hardly ever use RDBMS for their domain data... but since everyone learns RDBMS as their first DB, we have these horrible ORM frameworks on every corner.

tracker1 3y ago
Yeah, I've kind of railed against ORMs for a while now... I'd just assume a simple data mapper (like Dapper for C#) or be really explicit in a scripted language with template queries.
unusualmonkey 3y ago
Curious why you think this? I've found Relational databases pretty powerful, and learned mongo first.
- tasubotadas 3y ago
  They are powerful. But often their Power is unnecessary and counter productive for domain modeling. Where are they really good is reporting. Analysts love rdbms. When I am on an analyst role, I love them them too. As an engineer I find them redundant. More about domain modeling https://dev.tasubo.com/2022/07/crash-course-domain-driven-design.html#aggregates--aggregate-root https://dev.tasubo.com/2022/07/crash-course-domain-driven-de...
  - unusualmonkey 3y ago
    1) That article says nothing against using rdms in my quick perusal, in fact it suggests MySQL! 2) In practice having the ability to run reports on your data can be super important and useful. I'm still confused at the point you're trying to make.

deepsun 3y ago

We had everything de-normalized, as you pointed out. However, every now and then we had some one-off questions that required using Mongo for something it's not designed for. However, migrating to postgres+JSONB made it easy to do both.

phamilton 3y ago
We've been on a big "Use postgres for everything" path. Mostly migrating dynamodb, elasticsearch, and redis to postgres. The number of times we've had to do some one-off query that was orthogonal to production usage is higher than I predicted. There were so many times in the past where we just didn't fix something because it was too difficult to backfill a document store. We just hacked around it. Now with postgres we fix the root of the problem instead of hacking around it. And we do so with a level of confidence we've never had with document stores.

dfragnito 3y ago

This is one of the reasons we created https://schemafreesql.com https://schemafreesql.com. We wanted SQL with some of the document store (NOSQL) features.