9 ms·

While I think it's easy to poke fun at this article - relational database better at handling relational data, more at 10 - it does make me want to understand wh

While I think it's easy to poke fun at this article - relational database better at handling relational data, more at 10 - it does make me want to understand why one would choose mongo over postgres?

mixermachine 7y ago
- Faster development speed - Unknown content of the object that should be stored (and run indexes on this content)
- nicoburns 7y ago
  You can do that by using a JSONB column in postgres (including indexes), and that way you can upgrade to typed columns at yout leisure (and on a per-column basis). Personally, having inherited a database that wqs absolutely chock full of bad data due to the lack of schema, I'd be very reluctant to go that route unless I really needed to.
  - mixermachine 7y ago
    This is a side feature for Postgresql and other relational dbs. MongoDB focuses on especially this. I would not mix the two concepts. Split of the relational data and make it fast with relational DBs. Everything else goes in MongoDB and if fields always occure, they move into the relational DB.
    - Ididntdothis 7y ago
      I would mix the two. JSON columns allow you to deal with unstructured data in Postgres so you don't have to manage two databases. I much prefer that.
      - alexbanks 7y ago
        I agree with you on the original premise of "Why would you use Mongo over Postgres?", managing two DBs is terrible. But if you replace Mongo with something like DynamoDB, I think it gets more complicated. You don't really "manage" DynamoDB as much as just define what you're trying to do, as it's a managed service. I quite enjoyed using DynamoDB for some specific tasks at work, much more than I've enjoyed PostgreSQL's JSON columns.
        mixermachine 7y ago
        I would use the best tools even if it requires two DBs. When I have many transcations with many joins but also user documents (maybe from a online editor which is currently still in heave development), I would use two DBs.
      - Beefin 7y ago
        In an ideal world yes, you only manage one database but no such "jack of all trades" DB exists for the same reason the CAP theorem remains true. If your Postgres JSON schema modified for new application requirements, the table is locked until existing data is copied into the new schema, requiring applications to be quiesced during schema migration. Thats a key difference between the two and one area Mongo performs best in.
        Ididntdothis 7y ago
        Sure there are situations where a Postgres/Mongo hybrid is the best but my preferred default is Postgres only. In most cases this will work fine, is simple and straightforward. Why make things more complicated than necessary?
        nicoburns 7y ago
        > If your Postgres JSON schema modified for new application requirements, the table is locked until existing data is copied into the new schema, requiring applications to be quiesced during schema migration. Are you saying that if you INSERT a row with a new JSONB property that wasn't previously included in any of the rows of that table, then the entire table gets locked while all rows are copied. And if so, do you have a source for that? I can't find anything through google.
    - sinkasapa 7y ago
      I've been using jsonb columns in PostgreSQL recently and have used CouchDB extensively in the past. I find the builtin support for JSON superior in PostgreSQL. There are a lot of nice built in operators for querying and creating indexes is much more concise. I wouldn't call it a side feature at all.
baq 7y ago
when you need a geo-replicated and highly available data store in a way that doesn't make you lose sleep. (also, if you don't need to join relations in it. or have relations at all.)
dsun176 7y ago
Replication and scalability I think
Beefin 7y ago
When you don't want to use an ORM, want to scale horizontally, want to use BSON data types and all the indexing capabilities around them, etc.
- jeltz 7y ago
  PostgreSQL has the JSONB data type which is essentially the same as the BSON type except slightly better optimized for reading partial documents. And PostgreSQL also provides good indexing support for them, and did so before MongoDB.
  - Beefin 7y ago
    have you tried using the postgres json data type? it's a joke.
    - LunaSea 7y ago
      Can you list some reasons?
    - eyelidlessness 7y ago
      Not really sure about the `JSON` type, but the `JSONB` type is excellent.
    - thinkx 7y ago
      that’s a throwaway opinion, worth the time you spent writing it
  - zozbot234 7y ago
    Postgres can also be made to shard any table to multiple hosts via the PARTITION BY RANGE/PARTITION OF feature, combined with the FOREIGN DATA WRAPPER (FDW) core extension.
fatbird 7y ago
There's really only one reason to choose mongo over a competent RDBMS, and that's schemalessness at the database level--which most users of mongo then toss away by adding a schema package at the code layer. If your data is relatively freeform with good indices on stable fields, mongo is hugely convenient, and can be easily structured to scale horizontally. For non-join based aggregation (i.e, aggregation within a collection), mongo is very performant, and works fine for a lot of use cases. I'm using it storing large amounts of simple timeseries data and it's great, largely because my use case doesn't require more complex data structures. As soon as you want to deal with more complex structured data, mongo is worse than any RDBMS. The only viable noSQL solution to complex structures is denormalization, which is sometimes a legit choice. But unless you know your primary use is just retrieving a set of documents based on indexed fields (and many are), get an RDBMS.
pritambarhate 7y ago
MongoDB has built-in auto-failover with replica sets. It is possible to set this up for PGSQL with third-party tools but it's hard to get right. MongoDB has built-in multi-instance sharding support. Again this is possible for PostgreSQL with table partitioning and FDW but it is hard to maintain.
- zozbot234 7y ago
  > MongoDB has built-in auto-failover with replica sets. It is possible to set this up for PGSQL with third-party tools but it's hard to get right. PostgreSQL documentation makes it quite clear how to set up HA, I don't think you need any third-party tools other than general OS-level support for failover. https://www.postgresql.org/docs/current/high-availability.html https://www.postgresql.org/docs/current/high-availability.ht...
  - pritambarhate 7y ago
    I don't think PostgreSQL supports out of the box auto-failover as MongoDB does. It either needs custom scripting or tools like repmgr. From the official docs: >> PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby database server. Many such tools exist and are well integrated with the operating system facilities required for successful failover, such as IP address migration. [1] [1]https://www.postgresql.org/docs/current/warm-standby-failover.html https://www.postgresql.org/docs/current/warm-standby-failove...
    - ahachete 7y ago
      Or Patroni (https://github.com/zalando/patroni https://github.com/zalando/patroni), a better option. But it is complex to setup, requires other external tools too (DNS, virtual IPs, Envoy proxy, etc) and none of that is included in Postgres. Postgres HA is far from easy. I gave a talk 2 years ago about this (video and slides: https://aht.es/#talks-modern_postgresql_high_availability https://aht.es/#talks-modern_postgresql_high_availability). This is an operational disadvantage.
jmull 7y ago
I can’t address MongoDB, but nosql vs relational generally, there are two reasons: (1) what’s faster than a fast join? No join. With nosql you have more flexibility to store the data organized in the same way it is accessed. You don’t need to join to another table if the data your app needs is already directly part of the main data the app requests. You do need to understand your access patterns well, and develop migration plans when they change. (2) scalability. nosql databases generally let you scale horizontally more easily/gracefully than relational databases, though there are trade offs. Put it together and you get performance at scale, though you generally need to understand your data access patterns and usually need to be more resilient to inconsistencies.
- zozbot234 7y ago
  > what’s faster than a fast join? No join. With nosql you have more flexibility to store the data organized in the same way it is accessed. You can definitely do this in PostgreSQL via materialized views. They're directly supported in recent releases, and they don't require you to denormalize the underlying datamodel unlike NoSQL.
  - jmull 7y ago
    Well, a materialized view is denormalized data. You distinguish it from the “underlying data model”, but you can do that in nosql as well. Also, materialized views still tend to be oriented to rectangular data, which isn’t always how you want to access data. I agree with the general point that Postgres and other relational databases can do nosql things (and nosql databases can do relational things).
- ahachete 7y ago
  > You do need to understand your access patterns well, and develop migration plans when they change. This sounds like a minor issue, but it's a huge operational pain. Migrating a data structure to accommodate new queries involves operations and application changes, both substantial. Migrating all data in a consistent way is either extremely hard or requires significant downtime. In my opinion, for most cases, this problem is significantly worse than doing joins in first place.

While I think it's easy to poke fun at this article - relational database better at handling relational data, more at 10 - it does make me want to understand why one would choose mongo over postgres?

mixermachine 7y ago

- Faster development speed - Unknown content of the object that should be stored (and run indexes on this content)

nicoburns 7y ago
You can do that by using a JSONB column in postgres (including indexes), and that way you can upgrade to typed columns at yout leisure (and on a per-column basis). Personally, having inherited a database that wqs absolutely chock full of bad data due to the lack of schema, I'd be very reluctant to go that route unless I really needed to.
- mixermachine 7y ago
  This is a side feature for Postgresql and other relational dbs. MongoDB focuses on especially this. I would not mix the two concepts. Split of the relational data and make it fast with relational DBs. Everything else goes in MongoDB and if fields always occure, they move into the relational DB.
  - Ididntdothis 7y ago
    I would mix the two. JSON columns allow you to deal with unstructured data in Postgres so you don't have to manage two databases. I much prefer that.
    - alexbanks 7y ago
      I agree with you on the original premise of "Why would you use Mongo over Postgres?", managing two DBs is terrible. But if you replace Mongo with something like DynamoDB, I think it gets more complicated. You don't really "manage" DynamoDB as much as just define what you're trying to do, as it's a managed service. I quite enjoyed using DynamoDB for some specific tasks at work, much more than I've enjoyed PostgreSQL's JSON columns.
      - mixermachine 7y ago
        I would use the best tools even if it requires two DBs. When I have many transcations with many joins but also user documents (maybe from a online editor which is currently still in heave development), I would use two DBs.
    - Beefin 7y ago
      In an ideal world yes, you only manage one database but no such "jack of all trades" DB exists for the same reason the CAP theorem remains true. If your Postgres JSON schema modified for new application requirements, the table is locked until existing data is copied into the new schema, requiring applications to be quiesced during schema migration. Thats a key difference between the two and one area Mongo performs best in.
      - Ididntdothis 7y ago
        Sure there are situations where a Postgres/Mongo hybrid is the best but my preferred default is Postgres only. In most cases this will work fine, is simple and straightforward. Why make things more complicated than necessary?
      - nicoburns 7y ago
        > If your Postgres JSON schema modified for new application requirements, the table is locked until existing data is copied into the new schema, requiring applications to be quiesced during schema migration. Are you saying that if you INSERT a row with a new JSONB property that wasn't previously included in any of the rows of that table, then the entire table gets locked while all rows are copied. And if so, do you have a source for that? I can't find anything through google.
  - sinkasapa 7y ago
    I've been using jsonb columns in PostgreSQL recently and have used CouchDB extensively in the past. I find the builtin support for JSON superior in PostgreSQL. There are a lot of nice built in operators for querying and creating indexes is much more concise. I wouldn't call it a side feature at all.

baq 7y ago

when you need a geo-replicated and highly available data store in a way that doesn't make you lose sleep. (also, if you don't need to join relations in it. or have relations at all.)

dsun176 7y ago

Replication and scalability I think

Beefin 7y ago

When you don't want to use an ORM, want to scale horizontally, want to use BSON data types and all the indexing capabilities around them, etc.

jeltz 7y ago
PostgreSQL has the JSONB data type which is essentially the same as the BSON type except slightly better optimized for reading partial documents. And PostgreSQL also provides good indexing support for them, and did so before MongoDB.
- Beefin 7y ago
  have you tried using the postgres json data type? it's a joke.
  - LunaSea 7y ago
    Can you list some reasons?
  - eyelidlessness 7y ago
    Not really sure about the `JSON` type, but the `JSONB` type is excellent.
  - thinkx 7y ago
    that’s a throwaway opinion, worth the time you spent writing it
- zozbot234 7y ago
  Postgres can also be made to shard any table to multiple hosts via the PARTITION BY RANGE/PARTITION OF feature, combined with the FOREIGN DATA WRAPPER (FDW) core extension.

fatbird 7y ago

There's really only one reason to choose mongo over a competent RDBMS, and that's schemalessness at the database level--which most users of mongo then toss away by adding a schema package at the code layer. If your data is relatively freeform with good indices on stable fields, mongo is hugely convenient, and can be easily structured to scale horizontally. For non-join based aggregation (i.e, aggregation within a collection), mongo is very performant, and works fine for a lot of use cases. I'm using it storing large amounts of simple timeseries data and it's great, largely because my use case doesn't require more complex data structures. As soon as you want to deal with more complex structured data, mongo is worse than any RDBMS. The only viable noSQL solution to complex structures is denormalization, which is sometimes a legit choice. But unless you know your primary use is just retrieving a set of documents based on indexed fields (and many are), get an RDBMS.

pritambarhate 7y ago

MongoDB has built-in auto-failover with replica sets. It is possible to set this up for PGSQL with third-party tools but it's hard to get right. MongoDB has built-in multi-instance sharding support. Again this is possible for PostgreSQL with table partitioning and FDW but it is hard to maintain.

zozbot234 7y ago
> MongoDB has built-in auto-failover with replica sets. It is possible to set this up for PGSQL with third-party tools but it's hard to get right. PostgreSQL documentation makes it quite clear how to set up HA, I don't think you need any third-party tools other than general OS-level support for failover. https://www.postgresql.org/docs/current/high-availability.html https://www.postgresql.org/docs/current/high-availability.ht...
- pritambarhate 7y ago
  I don't think PostgreSQL supports out of the box auto-failover as MongoDB does. It either needs custom scripting or tools like repmgr. From the official docs: >> PostgreSQL does not provide the system software required to identify a failure on the primary and notify the standby database server. Many such tools exist and are well integrated with the operating system facilities required for successful failover, such as IP address migration. [1] [1]https://www.postgresql.org/docs/current/warm-standby-failover.html https://www.postgresql.org/docs/current/warm-standby-failove...
  - ahachete 7y ago
    Or Patroni (https://github.com/zalando/patroni https://github.com/zalando/patroni), a better option. But it is complex to setup, requires other external tools too (DNS, virtual IPs, Envoy proxy, etc) and none of that is included in Postgres. Postgres HA is far from easy. I gave a talk 2 years ago about this (video and slides: https://aht.es/#talks-modern_postgresql_high_availability https://aht.es/#talks-modern_postgresql_high_availability). This is an operational disadvantage.

jmull 7y ago

I can’t address MongoDB, but nosql vs relational generally, there are two reasons: (1) what’s faster than a fast join? No join. With nosql you have more flexibility to store the data organized in the same way it is accessed. You don’t need to join to another table if the data your app needs is already directly part of the main data the app requests. You do need to understand your access patterns well, and develop migration plans when they change. (2) scalability. nosql databases generally let you scale horizontally more easily/gracefully than relational databases, though there are trade offs. Put it together and you get performance at scale, though you generally need to understand your data access patterns and usually need to be more resilient to inconsistencies.

zozbot234 7y ago
> what’s faster than a fast join? No join. With nosql you have more flexibility to store the data organized in the same way it is accessed. You can definitely do this in PostgreSQL via materialized views. They're directly supported in recent releases, and they don't require you to denormalize the underlying datamodel unlike NoSQL.
- jmull 7y ago
  Well, a materialized view is denormalized data. You distinguish it from the “underlying data model”, but you can do that in nosql as well. Also, materialized views still tend to be oriented to rectangular data, which isn’t always how you want to access data. I agree with the general point that Postgres and other relational databases can do nosql things (and nosql databases can do relational things).
ahachete 7y ago
> You do need to understand your access patterns well, and develop migration plans when they change. This sounds like a minor issue, but it's a huge operational pain. Migrating a data structure to accommodate new queries involves operations and application changes, both substantial. Migrating all data in a consistent way is either extremely hard or requires significant downtime. In my opinion, for most cases, this problem is significantly worse than doing joins in first place.