4 ms·

We moved to PostgreSQL. Our performance problems went away, with almost no work other than the initial migration. We fixed many data integrity problems during

We moved to PostgreSQL. Our performance problems went away, with almost no work other than the initial migration. We fixed many data integrity problems during the migration, and have not noticed any problems since. Our uptime has become near perfect. We now get to use all of the other great features that PostgreSQL offers.

mikegioia 12y ago
The OP is asking for Mongo performance suggestions, not soliciting offers for other database systems. How big is your dataset? That's nice if you can fit everything on one machine, but have you ever set up a Postgres cluster?
- danpalmer 12y ago
  I'd be interested to know more about setting up clusters with Postgres. Could you explain some of the issues, perhaps some general approaches people take? Is the tooling for it any good, or is the tooling the main issue? I generally prefer relational databases, and from a theory point of view they feel much nicer and have the potential to be much faster, however, one thing MongoDB has got right is the simple primitives for scaling, and having them built into the core product, something I don't think Postgres has. I think there's a lot that Postgres (and other relational databases) can learn from MongoDB (ease of sharding perhaps?), Cassandra (consistent hashing maybe?), HyperDex, etc. And I'm sure there's much that those products can learn from the relational world as well.
  - mikegioia 12y ago
    The problem with PostgreSQL clustering, and really all SQL clustering is that the DB engine is just not designed with that in mind. So what you end up having to do is add an additional layer to support the clustering (or sharding). The advantage that something like MongoDB, or CouchDB, or Elasticsearch (etc) is that they're designed with sharding in mind. This makes the sysadmin's life a lot easier! Scaling Mongo up might be a pain with their config servers, routing servers, etc but at least there's a well-documented and well-supported system for scaling out horizontally. MySQL/PostgreSQL are fantastic database systems and exceptional at solving the problems they were designed for. But its wrong to think of it as this blanket solution for every problem, or to suggest it on HN every single time someone asks a question about MongoDB.
    - MichaelGG 12y ago
      >all SQL clustering Uh that's simply not true? VoltDB, for instance, easily and natively supports sharding. SQL Server's AlwaysOn system (despite being failover clustering) is incredibly simple to setup and is transparent to clients. The OP is asking about a problem with only 1M records. Unless they're huge records or something, no one should be discussing such a small number of records as a performance limit.
      - gaius 12y ago
        Hell, for only a million records, I'd "scale" with SQLite...
    - gaius 12y ago
      Sorry but that simply isn't true, and I suspect you've not really done any serious RDBMS at scale - Teradata, DB/2, Oracle et al have had clustering baked in since the 90s, if not before. If you are talking about MySQL then sure.
    - Ronaldo777 12y ago
      Relational databases provide a very solid foundation upon which a wide variety of replication, partitioning and data distribution schemes can be constructed. Just look at the different approaches that PostgreSQL supports: http://www.postgresql.org/docs/9.4/static/different-replication-solutions.html http://www.postgresql.org/docs/9.4/static/different-replicat... Other relational DB systems, from Oracle, to SQL Server, and even MySQL, offer similar functionality. Practice shows that they're very capable of successfully handling many different scenarios, even if some of these scenarios hadn't been considered decades ago when these relational database systems were first implemented. But that's just what happens when the core building blocks they do provide are so solid and fundamentally sound. It's only anecdotal evidence, but of all of the DB professionals I've worked with over the years, the ones who complained the least were always the ones working with some sort of relational DB system. The ones who complained the most have been working with some NoSQL database system. I've heard nothing but praise for the PostgreSQL documentation, while we repeatedly see submissions like this one asking for help with NoSQL scalability problems that just don't happen when using PostgreSQL, or Oracle, or SQL Server, or DB2, or MySQL, or even SQLite!
- Ronaldo777 12y ago
  The most effective and affordable fix for many scaling problems is to migrate to a different product or approach. I'd have to check with our DB folks to get the exact size of our dataset and the number of servers we're using now with PostgreSQL. At the time of the migration, it was around 85 TB, with 6 physical servers and replication being used.
bhouston 12y ago
We have thought about this. We are interfacing via Mongoose to MongoDB. Is there an easy way to still use Mongoose while transitioning to PostgreSQL?
- giaour 12y ago
  Unfortunately, Mongoose cannot query Postgres instead of Mongo. It's a wrapper around the native Node Mongo driver and does not incorporate a database abstraction layer.
- nullspace 12y ago
  Mongoose is such a brilliant library right? If I was writing something in nodejs - the existence of mongoose itself would justify simply going for a mongodb backend.
deleted 12y ago
[deleted]
lkrubner 12y ago
I am sad to see this answer moved so far down the page. I wish others would upvote it. I worked with MongoDB for 2 years, and more lately with PostGres. I feel like PostGres is underestimated. It is the only software in the world that offers such a powerful combination of SQL and NoSql. More people should try it.

mikegioia 12y ago

The OP is asking for Mongo performance suggestions, not soliciting offers for other database systems. How big is your dataset? That's nice if you can fit everything on one machine, but have you ever set up a Postgres cluster?

danpalmer 12y ago
I'd be interested to know more about setting up clusters with Postgres. Could you explain some of the issues, perhaps some general approaches people take? Is the tooling for it any good, or is the tooling the main issue? I generally prefer relational databases, and from a theory point of view they feel much nicer and have the potential to be much faster, however, one thing MongoDB has got right is the simple primitives for scaling, and having them built into the core product, something I don't think Postgres has. I think there's a lot that Postgres (and other relational databases) can learn from MongoDB (ease of sharding perhaps?), Cassandra (consistent hashing maybe?), HyperDex, etc. And I'm sure there's much that those products can learn from the relational world as well.
- mikegioia 12y ago
  The problem with PostgreSQL clustering, and really all SQL clustering is that the DB engine is just not designed with that in mind. So what you end up having to do is add an additional layer to support the clustering (or sharding). The advantage that something like MongoDB, or CouchDB, or Elasticsearch (etc) is that they're designed with sharding in mind. This makes the sysadmin's life a lot easier! Scaling Mongo up might be a pain with their config servers, routing servers, etc but at least there's a well-documented and well-supported system for scaling out horizontally. MySQL/PostgreSQL are fantastic database systems and exceptional at solving the problems they were designed for. But its wrong to think of it as this blanket solution for every problem, or to suggest it on HN every single time someone asks a question about MongoDB.
  - MichaelGG 12y ago
    >all SQL clustering Uh that's simply not true? VoltDB, for instance, easily and natively supports sharding. SQL Server's AlwaysOn system (despite being failover clustering) is incredibly simple to setup and is transparent to clients. The OP is asking about a problem with only 1M records. Unless they're huge records or something, no one should be discussing such a small number of records as a performance limit.
    - gaius 12y ago
      Hell, for only a million records, I'd "scale" with SQLite...
  - gaius 12y ago
    Sorry but that simply isn't true, and I suspect you've not really done any serious RDBMS at scale - Teradata, DB/2, Oracle et al have had clustering baked in since the 90s, if not before. If you are talking about MySQL then sure.
  - Ronaldo777 12y ago
    Relational databases provide a very solid foundation upon which a wide variety of replication, partitioning and data distribution schemes can be constructed. Just look at the different approaches that PostgreSQL supports: http://www.postgresql.org/docs/9.4/static/different-replication-solutions.html http://www.postgresql.org/docs/9.4/static/different-replicat... Other relational DB systems, from Oracle, to SQL Server, and even MySQL, offer similar functionality. Practice shows that they're very capable of successfully handling many different scenarios, even if some of these scenarios hadn't been considered decades ago when these relational database systems were first implemented. But that's just what happens when the core building blocks they do provide are so solid and fundamentally sound. It's only anecdotal evidence, but of all of the DB professionals I've worked with over the years, the ones who complained the least were always the ones working with some sort of relational DB system. The ones who complained the most have been working with some NoSQL database system. I've heard nothing but praise for the PostgreSQL documentation, while we repeatedly see submissions like this one asking for help with NoSQL scalability problems that just don't happen when using PostgreSQL, or Oracle, or SQL Server, or DB2, or MySQL, or even SQLite!
Ronaldo777 12y ago
The most effective and affordable fix for many scaling problems is to migrate to a different product or approach. I'd have to check with our DB folks to get the exact size of our dataset and the number of servers we're using now with PostgreSQL. At the time of the migration, it was around 85 TB, with 6 physical servers and replication being used.

bhouston 12y ago

We have thought about this. We are interfacing via Mongoose to MongoDB. Is there an easy way to still use Mongoose while transitioning to PostgreSQL?

giaour 12y ago
Unfortunately, Mongoose cannot query Postgres instead of Mongo. It's a wrapper around the native Node Mongo driver and does not incorporate a database abstraction layer.
nullspace 12y ago
Mongoose is such a brilliant library right? If I was writing something in nodejs - the existence of mongoose itself would justify simply going for a mongodb backend.

deleted 12y ago

[deleted]

lkrubner 12y ago

I am sad to see this answer moved so far down the page. I wish others would upvote it. I worked with MongoDB for 2 years, and more lately with PostGres. I feel like PostGres is underestimated. It is the only software in the world that offers such a powerful combination of SQL and NoSql. More people should try it.