6 ms·

What are some reasons I might consider Postgres over MySQL when choosing a DB platform?

petre 8y ago
JSON support, ability to query it, JSONB support, real spatial extensions, timescale extensions, consistent behaviour, online backups. We now have to migrate from MySQL to Postgres because of these features. Instead of MySQL 8 we will be probably usng Pg 11. Now we are using MariaDB.
- evanelias 8y ago
  > JSON support, ability to query it MySQL 5.7 has this, and MySQL 8 improves on it further. Note that MariaDB's implementation is substantially different than MySQL's here. > consistent behaviour This one's a bit vague :) But generally using strict sql_mode and innodb_strict_mode go a long way towards alleviating this common complaint. > online backups Online physical backups can be created with Percona's xtrabackup, which is open source. Or for online logical backups, there's always mysqldump, or third-party concurrent options like mydumper. > real spatial extensions, timescale extensions Yeah these ones I definitely see Postgres having a substantial edge, if they're important to your application or workload.
sv12l 8y ago
If data is central to your business, in all likelihood it will be, and you care.
- calcifer 8y ago
  This is not a useful or constructive comment with its unstated argument of "only idiots use mysql". It's sarcastic flamewar bait.
  - guiriduro 8y ago
    No, its just the TL;DR version of a longer argument demonstrating MySQL's ability to lose, truncate and misinterpret data and ignore commands and thereby create false expectations (like, your data is validated and safe). PostgreSQL takes far greater care over data.
jpalomaki 8y ago
Postgres has much better SQL support and has features which will make you smile if you end writing complex queries for some purposes (like reporting).
- pmontra 8y ago
  Yesterday a friend developer told me he was trying to code a query on MySQL to get the latest message of every conversation stored in a table like conversation id, timestamp, user id, message. I did that years ago so I told him he'll find a lot of solutions on Stackoverflow, ugly ones with MySQL and much better ones for PostgreSQL which has a nrow() function. A MySQL DBA solved that problem in that project I worked on years ago without queries. He used triggers to store the id of the most recent message of every conversation in another table. That is probably the most efficient solution in any database. My friend told me he wants to code that query, no triggers, not even an INSERT OR UPDATE from his PHP code. I replied to go on, he'll learn a lot of things and I hoped this will make him pick PostgreSQL next time. Developers tend not to like triggers or stored procedures IMHO because it's code outside their reach in a language most of them don't know, but that's another story.
  - Avamander 8y ago
    I've had to debug a stored procedure, they're horrible when you have to do that. Just a big headache. I literally rewrote every single one of those procedures in the software's primary language, the code can now be easily debugged, logged, replaced and read.
    - pmontra 8y ago
      I'm not a big fan of PL/SQL. Almost nobody is, so your approach is standard. It's ok if all accesses to the database go through the primary application. Triggers and stored procedures make more sense when a database serves multiple applications possibly in multiple languages. Think of the db as the microservice in front of the data store. Simple stored procedures are no that bad.
  - blattimwind 8y ago
gaius 8y ago
With Postgres you don't need MongoDB, InfluxDB, or any other trendy thing, Postgres does it all, and better than all the wannabes.
- sz4kerto 8y ago
  Is that really true? Postgres is great, but it doesn't cater for _all_ use cases. For example, horizontal scalability is -- understandably for an ACID RDBMS -- not as trivial as it is for some document stores.
  - gaius 8y ago
    Check out what Citus are doing with PG https://www.citusdata.com https://www.citusdata.com
    - threeseed 8y ago
      That's a fork of PostgreSQL. So it doesn't help you when it comes to using cloud hosted services e.g. AWS or Azure. It's also a commercial product that isn't exactly cheap ($890/node).
      - stareatgoats 8y ago
        It's not a fork (according to their promotion), it's an 'extension', but not sure I can explain the difference. Their solution looks interesting but pricing wasn't very visible on their website, what you quote is prohibitive for me (where does it say?). But I at least assumed the 'open source' would be free.
        threeseed 8y ago
        Pricing is available here: https://www.citusdata.com/pricing https://www.citusdata.com/pricing And the point still stands that this isn't core PostgreSQL. So you can't use managed services like AWS RDS with this. And you're reliant entirely on the vendor for support. So from the user perspective it is basically a fork.

JSON support, ability to query it, JSONB support, real spatial extensions, timescale extensions, consistent behaviour, online backups. We now have to migrate from MySQL to Postgres because of these features. Instead of MySQL 8 we will be probably usng Pg 11. Now we are using MariaDB.

evanelias 8y ago
> JSON support, ability to query it MySQL 5.7 has this, and MySQL 8 improves on it further. Note that MariaDB's implementation is substantially different than MySQL's here. > consistent behaviour This one's a bit vague :) But generally using strict sql_mode and innodb_strict_mode go a long way towards alleviating this common complaint. > online backups Online physical backups can be created with Percona's xtrabackup, which is open source. Or for online logical backups, there's always mysqldump, or third-party concurrent options like mydumper. > real spatial extensions, timescale extensions Yeah these ones I definitely see Postgres having a substantial edge, if they're important to your application or workload.

sv12l 8y ago

If data is central to your business, in all likelihood it will be, and you care.

calcifer 8y ago
This is not a useful or constructive comment with its unstated argument of "only idiots use mysql". It's sarcastic flamewar bait.
- guiriduro 8y ago
  No, its just the TL;DR version of a longer argument demonstrating MySQL's ability to lose, truncate and misinterpret data and ignore commands and thereby create false expectations (like, your data is validated and safe). PostgreSQL takes far greater care over data.

jpalomaki 8y ago

Postgres has much better SQL support and has features which will make you smile if you end writing complex queries for some purposes (like reporting).

pmontra 8y ago
Yesterday a friend developer told me he was trying to code a query on MySQL to get the latest message of every conversation stored in a table like conversation id, timestamp, user id, message. I did that years ago so I told him he'll find a lot of solutions on Stackoverflow, ugly ones with MySQL and much better ones for PostgreSQL which has a nrow() function. A MySQL DBA solved that problem in that project I worked on years ago without queries. He used triggers to store the id of the most recent message of every conversation in another table. That is probably the most efficient solution in any database. My friend told me he wants to code that query, no triggers, not even an INSERT OR UPDATE from his PHP code. I replied to go on, he'll learn a lot of things and I hoped this will make him pick PostgreSQL next time. Developers tend not to like triggers or stored procedures IMHO because it's code outside their reach in a language most of them don't know, but that's another story.
- Avamander 8y ago
  I've had to debug a stored procedure, they're horrible when you have to do that. Just a big headache. I literally rewrote every single one of those procedures in the software's primary language, the code can now be easily debugged, logged, replaced and read.
  - pmontra 8y ago
    I'm not a big fan of PL/SQL. Almost nobody is, so your approach is standard. It's ok if all accesses to the database go through the primary application. Triggers and stored procedures make more sense when a database serves multiple applications possibly in multiple languages. Think of the db as the microservice in front of the data store. Simple stored procedures are no that bad.
- blattimwind 8y ago

gaius 8y ago

With Postgres you don't need MongoDB, InfluxDB, or any other trendy thing, Postgres does it all, and better than all the wannabes.

sz4kerto 8y ago
Is that really true? Postgres is great, but it doesn't cater for _all_ use cases. For example, horizontal scalability is -- understandably for an ACID RDBMS -- not as trivial as it is for some document stores.
- gaius 8y ago
  Check out what Citus are doing with PG https://www.citusdata.com https://www.citusdata.com
  - threeseed 8y ago
    That's a fork of PostgreSQL. So it doesn't help you when it comes to using cloud hosted services e.g. AWS or Azure. It's also a commercial product that isn't exactly cheap ($890/node).
    - stareatgoats 8y ago
      It's not a fork (according to their promotion), it's an 'extension', but not sure I can explain the difference. Their solution looks interesting but pricing wasn't very visible on their website, what you quote is prohibitive for me (where does it say?). But I at least assumed the 'open source' would be free.
      - threeseed 8y ago
        Pricing is available here: https://www.citusdata.com/pricing https://www.citusdata.com/pricing And the point still stands that this isn't core PostgreSQL. So you can't use managed services like AWS RDS with this. And you're reliant entirely on the vendor for support. So from the user perspective it is basically a fork.