4 ms·

Reasons I like PostgreSQL compared to MySQL: - Timestamp with time zone - More robust, fewer crashes, less corruption of data - More features (JSON data type

by gdulli 13y ago

Reasons I like PostgreSQL compared to MySQL: - Timestamp with time zone - More robust, fewer crashes, less corruption of data - More features (JSON data type, partial indexes, function/expression indexes, window functions, CTEs, hstore, ranges/sequences/sets, materialized views, too many to list) - More disciplined (doesn't do things like auto-truncate input to get it to fit into a column) - Not owned by Oracle, it's actively developed, regular major release schedule, developers/maintainers are talented and trustworthy, etc. - Better Python driver (don't know about other languages) - Choice of languages for database functions/procedures (Python, JS, etc.) - Better partitioning support - Better explain output, explain analyze, buffers - Multiple indexes allowed per table in a query (I hear MySQL has made a little progress here since I last used it)

antjanus 13y ago
Add "built-in fulltext search" and the fact that you've just replaced Redis, Sphinx/Solr/Lucene, and Mongo with a single application. THAT, I think, is the biggest sell, at least to me. The fact that PgSQL does so much and so well. I used Pg for full-text search in the past and the fact that I did not have to bother with setting up Solr or an interface was a wonderful feature. The search index lived right alongside my data.
- jlogsdon 13y ago
  Technically mysql has "built-in fulltext search" as well, but the implementation leaves something to be desired.
  - antjanus 13y ago
    I couldn't find anything nearly as powerful as Pg so I would discount it fully.
- mrweasel 13y ago
  How good is the fulltext search? Comparable with Solr, better or does it depend on your needs?
  - jeltz 13y ago
    It has less features and worse performance than Solr and Sphinx. The worse performance is being worked on and will probably be fixed in 9.4 (the index guys claim that they can beat Sphinx in benchmarks). The advantage of having your fulltext search inside your database is huge though and can outweigh the current disadvantages. For example you do not have to manage another database, indexing can be transactional and instant, and you can can have both fulltext search and normal SQL stuff in the same query (often reducing latency).
Doctor_Fegg 13y ago
I'll add one more: PostGIS.
- nobleach 13y ago
  Three. Freaking. Cheers for this one. Anyone who's had to suffer through MySQL's abysmal attempt at doing "spatial" should give PostGIS a well deserved look. I've used Oracle Spatial, ESRI's SDE (which rides on top of MSSQL/Oracle/DB2/SyBase) MySQL 'spatial' and PostGIS. I have to say, hands down, PostGIS has been my favorite. We had to implement MySQL spatial as all of our tabular data was already there and the IT folks felt "comfortable" with it as they knew they could dump it to a plain text file full of CREATE statements any time they wanted to. The problem we have seen is that dumping some of our ancient MySQL spatial tables, we could not import them into other versions. And vise-versa appeared to be true as well. (I'd need to go back and look)
  - ZenoArrow 13y ago
    You and your IT folks may be interested in Spatialite, which sounds like a good use case for what you've described. Spatialite is spatial extensions to SQLite, means you can store spatial data in standalone DB files. Has good level of support in GIS software, plus it's the first spatial database format to support the upcoming GeoPackage standard (due to replace shapefiles): http://www.gaia-gis.it/gaia-sins/ http://www.gaia-gis.it/gaia-sins/ http://www.ogcnetwork.net/geopackage http://www.ogcnetwork.net/geopackage
ucho 13y ago
> - Better partitioning support Yeah, right. Partitioning in PostgreSQL is as braindead as setting sequence value in Oracle. IMHO the devs made wrong choice: https://wiki.postgresql.org/wiki/Table_partitioning#Possible_Directions https://wiki.postgresql.org/wiki/Table_partitioning#Possible... . You want a partition for every month of stored data? Prepare to copy & paste a lot as you need a set of triggers for all child tables. Oh, the table You are spiting was referenced as foreign keys in other tables? Drop the constraint, it isn't supported even if is the same column you are partitioning over.
- dllthomas 13y ago
  Granting that there are problems with PostgreSQL partitioning, that doesn't invalidate the claim unless MySQL partitioning does these things better and doesn't have other, worse problems. I'm not making any claim as to whether this is the case, but you didn't address it.
- bsg75 13y ago
  In what partition design do you need a trigger per child table? You only need to add partitions to the trigger on the parent table, or at the branches if you have multiple hierarchy levels. I run a Postgres instance that partitions daily (for reasons of space management). When creating the partitions in advance, it is easy enough to alter the trigger (or a function called by the trigger) in the same script that creates the tables. DDL via copy-edit-paste is just asking for problems anyway. Those operations should be automated.
nobleach 13y ago
I've had a problem selling the reliability angle as our instances of MySQL have never crashed. We have drawn the line in the sand that ALL new apps get Postgres by default, but we've been turned down on funding to migrate all old apps. The belief is that they're "doing just fine". I'd love to see every single one of them moved. I know we'd hear squawking from the few folks that know how to use MySQL Workbench as it is a very pretty interface, but it'd be worth it.
- Lewisham 13y ago
  Well, if they aren't crashing, and the apps are already built, then aren't they really running just fine? What value do you buy by switching legacy apps that aren't being actively developed to Postgres?
  - smacktoward 13y ago
    You can cut down on support expenses by not having to have MySQL expertise on staff. Of course, if your DBAs (or whatever passes for a DBA in your outfit) are conversant in both already -- or if they are equally clueless with both -- this doesn't save you much.
DrJ 13y ago
I would contest 'better python driver' with SQLAlchemy.
- gdulli 13y ago
  I'm not a fan of SQLAlchemy, I was referring to psycopg2. I don't think of SQLAlchemy as a driver, just an optional layer on top of the driver that one may prefer to use, but I don't.

by gdulli 13y ago

antjanus 13y ago

Add "built-in fulltext search" and the fact that you've just replaced Redis, Sphinx/Solr/Lucene, and Mongo with a single application. THAT, I think, is the biggest sell, at least to me. The fact that PgSQL does so much and so well. I used Pg for full-text search in the past and the fact that I did not have to bother with setting up Solr or an interface was a wonderful feature. The search index lived right alongside my data.

jlogsdon 13y ago
Technically mysql has "built-in fulltext search" as well, but the implementation leaves something to be desired.
- antjanus 13y ago
  I couldn't find anything nearly as powerful as Pg so I would discount it fully.
mrweasel 13y ago
How good is the fulltext search? Comparable with Solr, better or does it depend on your needs?
- jeltz 13y ago
  It has less features and worse performance than Solr and Sphinx. The worse performance is being worked on and will probably be fixed in 9.4 (the index guys claim that they can beat Sphinx in benchmarks). The advantage of having your fulltext search inside your database is huge though and can outweigh the current disadvantages. For example you do not have to manage another database, indexing can be transactional and instant, and you can can have both fulltext search and normal SQL stuff in the same query (often reducing latency).

Doctor_Fegg 13y ago

I'll add one more: PostGIS.

nobleach 13y ago
Three. Freaking. Cheers for this one. Anyone who's had to suffer through MySQL's abysmal attempt at doing "spatial" should give PostGIS a well deserved look. I've used Oracle Spatial, ESRI's SDE (which rides on top of MSSQL/Oracle/DB2/SyBase) MySQL 'spatial' and PostGIS. I have to say, hands down, PostGIS has been my favorite. We had to implement MySQL spatial as all of our tabular data was already there and the IT folks felt "comfortable" with it as they knew they could dump it to a plain text file full of CREATE statements any time they wanted to. The problem we have seen is that dumping some of our ancient MySQL spatial tables, we could not import them into other versions. And vise-versa appeared to be true as well. (I'd need to go back and look)
- ZenoArrow 13y ago
  You and your IT folks may be interested in Spatialite, which sounds like a good use case for what you've described. Spatialite is spatial extensions to SQLite, means you can store spatial data in standalone DB files. Has good level of support in GIS software, plus it's the first spatial database format to support the upcoming GeoPackage standard (due to replace shapefiles): http://www.gaia-gis.it/gaia-sins/ http://www.gaia-gis.it/gaia-sins/ http://www.ogcnetwork.net/geopackage http://www.ogcnetwork.net/geopackage

ucho 13y ago

> - Better partitioning support Yeah, right. Partitioning in PostgreSQL is as braindead as setting sequence value in Oracle. IMHO the devs made wrong choice: https://wiki.postgresql.org/wiki/Table_partitioning#Possible_Directions https://wiki.postgresql.org/wiki/Table_partitioning#Possible... . You want a partition for every month of stored data? Prepare to copy & paste a lot as you need a set of triggers for all child tables. Oh, the table You are spiting was referenced as foreign keys in other tables? Drop the constraint, it isn't supported even if is the same column you are partitioning over.

dllthomas 13y ago
Granting that there are problems with PostgreSQL partitioning, that doesn't invalidate the claim unless MySQL partitioning does these things better and doesn't have other, worse problems. I'm not making any claim as to whether this is the case, but you didn't address it.
bsg75 13y ago
In what partition design do you need a trigger per child table? You only need to add partitions to the trigger on the parent table, or at the branches if you have multiple hierarchy levels. I run a Postgres instance that partitions daily (for reasons of space management). When creating the partitions in advance, it is easy enough to alter the trigger (or a function called by the trigger) in the same script that creates the tables. DDL via copy-edit-paste is just asking for problems anyway. Those operations should be automated.

nobleach 13y ago

I've had a problem selling the reliability angle as our instances of MySQL have never crashed. We have drawn the line in the sand that ALL new apps get Postgres by default, but we've been turned down on funding to migrate all old apps. The belief is that they're "doing just fine". I'd love to see every single one of them moved. I know we'd hear squawking from the few folks that know how to use MySQL Workbench as it is a very pretty interface, but it'd be worth it.

Lewisham 13y ago
Well, if they aren't crashing, and the apps are already built, then aren't they really running just fine? What value do you buy by switching legacy apps that aren't being actively developed to Postgres?
- smacktoward 13y ago
  You can cut down on support expenses by not having to have MySQL expertise on staff. Of course, if your DBAs (or whatever passes for a DBA in your outfit) are conversant in both already -- or if they are equally clueless with both -- this doesn't save you much.

DrJ 13y ago

I would contest 'better python driver' with SQLAlchemy.

gdulli 13y ago
I'm not a fan of SQLAlchemy, I was referring to psycopg2. I don't think of SQLAlchemy as a driver, just an optional layer on top of the driver that one may prefer to use, but I don't.