9 ms·

I've heard about PostgreSQL and know that HN community raves about it, but am currently using RDS with MySQL. Does it make sense to migrate to PostgreSQL, I do

by yumraj 13y ago

I've heard about PostgreSQL and know that HN community raves about it, but am currently using RDS with MySQL. Does it make sense to migrate to PostgreSQL, I don't have a lot of data as I'm in the early stage? What are the primary advantages that PostgreSQL provides over MySQL? Any advise/pointers is appreciated.

the_mitsuhiko 13y ago
> Does it make sense to migrate to PostgreSQL, I don't have a lot of data as I'm in the early stage? What are the primary advantages that PostgreSQL provides over MySQL? Any advise/pointers is appreciated. If MySQL works for you, there is no reason to change. Some people have quite a dependency on Postgres (hstore, JSON, transactional DDL, pubsub, PLPGSQL etc.)
- jeffdavis 13y ago
  Ordinarily I would agree, but in this case, the poster said that there's not a lot of data yet so it would be easier to migrate. Choosing a database is a pretty major decision with long-term impact that might not be obvious now, so it's worth exploring a few options if you have that luxury. I think there are two times when it makes sense to consider a DB migration: (1) early on, when it's easy; (2) if you are in major trouble with your existing DB.
- gtaylor 13y ago
  > Some people have quite a dependency on Postgres Our dependency on Postgres is more to do with it being a lot safer with our data. It doesn't silently fail, the transactions have a better failure mode, and as previously pointed out it handles ALTER in production a LOT better than MySQL. The combination of safety, speed/efficiency in schema/index alteration, and its increasingly good performance are why we depend on it. The rest is just gravy.
adrianh 13y ago
Postgres actually cares about your data. Perhaps you mistakenly insert "2013-10-32" into a date column. MySQL will silently convert this to "0000-00-00" (!!). Postgres will raise an error. Perhaps you make an error in a transaction. MySQL lets you keep doing subsequent things in the transaction. Postgres treats the transaction as invalid and forces you to start over. Perhaps you want to add a column to a table that has millions of rows. With MySQL, you'll be waiting a looooong time (see http://stackoverflow.com/questions/463677/alter-table-without-locking-the-table http://stackoverflow.com/questions/463677/alter-table-withou...). With Postgres, it takes about a second. Of course, there are things you can do to make MySQL less horrible, and this is a generalization. But Postgres is just more respectful and more solid. Oh, and PostGIS (Postgres' geo add-on) is by far the best open-source geospatial database. If you're doing anything with geographic queries, you need to be using it. MySQL's stuff is laughable in comparison. Context: I've dealt extensively with both databases, both from the perspective of a framework author (Django) and a developer making products. I've used both databases on and off since 2001.
- eli 13y ago
  Hey at least it throws a warning on the bogus date conversion (MySQL warnings should almost always be treated as fatal errors). The thing that kills me with MySQL (technically it's with InnoDB-based storage enginges in MySQL) are the subtle quirks. Like the thing where it insists on writing temporary tables to disk if you do a query that selects TEXT or BLOB fields. Even if they could have easily fit in memory, it's not smart enough to be able to determine that with variable length fields. A very non-obvious performance killer unless you're specifically looking for it.
  - deleted 13y ago
    [deleted]
  - jacques_chester 13y ago
    I run a small Wordpress network. MySQL's insistence on going to disk for joins on tables with a TEXT field (even if the query doesn't touch those fields) is probably the major performance bottleneck.
    - e12e 13y ago
      Ouch. Thanks for the explicit heads-up. I generally stay with PostgreSql, but I honestly thought most of these things were "fixed" in recent versions of MySQL (and assumed my subconcious dislike of MySql was at least partly irrational/rooted in Ancient and Outdated Lore). I guess not: http://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html http://dev.mysql.com/doc/refman/5.7/en/internal-temporary-ta... I'm not sure what the status of TEXT-fields are in mariadb: https://mariadb.com/kb/en/optimizing-string-and-character-fields/ https://mariadb.com/kb/en/optimizing-string-and-character-fi...
      - eli 13y ago
        I tried MariaDB about a year ago and it had the same problem. It's possible it's been fixed since. I personally prefer the Percona fork of MySQL, which has some performance tweaks yet is basically a 100% drop-in replacement.
pnathan 13y ago
Great question! The basic answer is (from what I can tell) that postgres ships with sane defaults. Lots of little gotchas exist in mysql that experienced mysql dbas know to deal with and avoid. On the more sophisticated side, pgSQL seems to have a focus on being "Really Awesome For Experts" where MySQL seems to be focusing on "Being Easy to Get Started". I've been using pgSQL for my side project and for my "personal tooling" at work, and I can honestly say that it's just as easy for me as MySQL was for the same sort of things.
gdulli 13y ago
edited to add: - 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, 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, 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
- elob36 13y ago
  To me, the MySQL query optimizer (single index in a query) was the proverbial straw that broke the camel's back. My group is developing all new applications in PostgreSQL and we would like to migrate our legacy apps away from MySQL.
  - will_work4tears 13y ago
    I'm on mobile and cant quickly and easily search this but is there a good GUI for postgre similar or superior to phpmyadmin?
    - aftabh 13y ago
      Try pgAdmin at http://www.pgadmin.org/ http://www.pgadmin.org/ Edit: You can also check phppgadmin at http://phppgadmin.sourceforge.net/doku.php http://phppgadmin.sourceforge.net/doku.php
    - epo 13y ago
      Dbvisualizer (http://www.dbvis.com/ http://www.dbvis.com/) works with lots of databases and is really pretty cheap. Disclaimer: no connection other being a long term customer, I use it with Oracle, MySQL and Postgresql.
    - johnyzee 13y ago
      TeamPostgreSQL is a very good web GUI for PostgreSQL: http://www.teampostgresql.com http://www.teampostgresql.com
jeffdavis 13y ago
(Disclaimer: I'm a postgres community member.) I think the biggest advantages are trust and flexibility. Trust, because PostgreSQL language semantics are cleaner, closer to the SQL standard, and less likely to surprise you. And postgresql just has a good reputation for traditional engineering quality. Flexibility, because it offers a lot of APIs and features that can be very useful to adapt your application as needs change. You don't have to go crazy with features, but even simple apps can benefit a lot from prudent use of them -- a trigger here, a foreign table there, or LISTEN/NOTIFY (for cache invalidation) can just save you a huge amount of work and make the system more robust overall. The extension mechanism is very powerful. Before making any big decisions, do a trial migration and see what you think.
deleted 13y ago
[deleted]
rektide 13y ago
If you didn't care yesterday, I HIGHLY ENCOURAGE AND RECOMMEND YOU NOT CARE TODAY.
- elob36 13y ago
  He cares today because he now has an option.
rachbelaid 13y ago
Just DDL transaction make life so much easier but there is much more like Materialized Views, Advanced Data type, Efficient JOIN, pg_stat_statements, PL, full text search, GIS features (pretty useful to do near by query), fuzzy search, ISO SQL standard compliant, Piece of cake replication. Do some search and give it a try one weekend.. Trying it is loving it.
Sanddancer 13y ago
Hating to pile on, but doing so anyways, one of the overlooked things I'd argue is that postgresql tries much harder to let you do neat things with your data without much hassle. Needing to write a custom function to process data? Not only does PostgreSQL have support for PL/PgSQL, but it's made in a way that has let people write plugins to python, perl, v8, tcl, etc, so you can do that data transformation on the database instead of shipping it back and forth to another server with the latency that brings. The recent version also brings read/write support to redis, so you have have your updates trigger redis to store new values, etc. Plus, being that it's not controlled by Oracle, there's much less a feeling of the community being held back; MySQL feels very much like Oracle's tying one hand back, so that when you get to the size to need a "real" database, Oracle will gladly step in, at their standard consulting fees, of course.

by yumraj 13y ago

the_mitsuhiko 13y ago

> Does it make sense to migrate to PostgreSQL, I don't have a lot of data as I'm in the early stage? What are the primary advantages that PostgreSQL provides over MySQL? Any advise/pointers is appreciated. If MySQL works for you, there is no reason to change. Some people have quite a dependency on Postgres (hstore, JSON, transactional DDL, pubsub, PLPGSQL etc.)

jeffdavis 13y ago
Ordinarily I would agree, but in this case, the poster said that there's not a lot of data yet so it would be easier to migrate. Choosing a database is a pretty major decision with long-term impact that might not be obvious now, so it's worth exploring a few options if you have that luxury. I think there are two times when it makes sense to consider a DB migration: (1) early on, when it's easy; (2) if you are in major trouble with your existing DB.
gtaylor 13y ago
> Some people have quite a dependency on Postgres Our dependency on Postgres is more to do with it being a lot safer with our data. It doesn't silently fail, the transactions have a better failure mode, and as previously pointed out it handles ALTER in production a LOT better than MySQL. The combination of safety, speed/efficiency in schema/index alteration, and its increasingly good performance are why we depend on it. The rest is just gravy.

adrianh 13y ago

Postgres actually cares about your data. Perhaps you mistakenly insert "2013-10-32" into a date column. MySQL will silently convert this to "0000-00-00" (!!). Postgres will raise an error. Perhaps you make an error in a transaction. MySQL lets you keep doing subsequent things in the transaction. Postgres treats the transaction as invalid and forces you to start over. Perhaps you want to add a column to a table that has millions of rows. With MySQL, you'll be waiting a looooong time (see http://stackoverflow.com/questions/463677/alter-table-without-locking-the-table http://stackoverflow.com/questions/463677/alter-table-withou...). With Postgres, it takes about a second. Of course, there are things you can do to make MySQL less horrible, and this is a generalization. But Postgres is just more respectful and more solid. Oh, and PostGIS (Postgres' geo add-on) is by far the best open-source geospatial database. If you're doing anything with geographic queries, you need to be using it. MySQL's stuff is laughable in comparison. Context: I've dealt extensively with both databases, both from the perspective of a framework author (Django) and a developer making products. I've used both databases on and off since 2001.

eli 13y ago
Hey at least it throws a warning on the bogus date conversion (MySQL warnings should almost always be treated as fatal errors). The thing that kills me with MySQL (technically it's with InnoDB-based storage enginges in MySQL) are the subtle quirks. Like the thing where it insists on writing temporary tables to disk if you do a query that selects TEXT or BLOB fields. Even if they could have easily fit in memory, it's not smart enough to be able to determine that with variable length fields. A very non-obvious performance killer unless you're specifically looking for it.
- deleted 13y ago
  [deleted]
- jacques_chester 13y ago
  I run a small Wordpress network. MySQL's insistence on going to disk for joins on tables with a TEXT field (even if the query doesn't touch those fields) is probably the major performance bottleneck.
  - e12e 13y ago
    Ouch. Thanks for the explicit heads-up. I generally stay with PostgreSql, but I honestly thought most of these things were "fixed" in recent versions of MySQL (and assumed my subconcious dislike of MySql was at least partly irrational/rooted in Ancient and Outdated Lore). I guess not: http://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html http://dev.mysql.com/doc/refman/5.7/en/internal-temporary-ta... I'm not sure what the status of TEXT-fields are in mariadb: https://mariadb.com/kb/en/optimizing-string-and-character-fields/ https://mariadb.com/kb/en/optimizing-string-and-character-fi...
    - eli 13y ago
      I tried MariaDB about a year ago and it had the same problem. It's possible it's been fixed since. I personally prefer the Percona fork of MySQL, which has some performance tweaks yet is basically a 100% drop-in replacement.

pnathan 13y ago

Great question! The basic answer is (from what I can tell) that postgres ships with sane defaults. Lots of little gotchas exist in mysql that experienced mysql dbas know to deal with and avoid. On the more sophisticated side, pgSQL seems to have a focus on being "Really Awesome For Experts" where MySQL seems to be focusing on "Being Easy to Get Started". I've been using pgSQL for my side project and for my "personal tooling" at work, and I can honestly say that it's just as easy for me as MySQL was for the same sort of things.

gdulli 13y ago

edited to add: - 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, 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, 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

elob36 13y ago
To me, the MySQL query optimizer (single index in a query) was the proverbial straw that broke the camel's back. My group is developing all new applications in PostgreSQL and we would like to migrate our legacy apps away from MySQL.
- will_work4tears 13y ago
  I'm on mobile and cant quickly and easily search this but is there a good GUI for postgre similar or superior to phpmyadmin?
  - aftabh 13y ago
    Try pgAdmin at http://www.pgadmin.org/ http://www.pgadmin.org/ Edit: You can also check phppgadmin at http://phppgadmin.sourceforge.net/doku.php http://phppgadmin.sourceforge.net/doku.php
  - epo 13y ago
    Dbvisualizer (http://www.dbvis.com/ http://www.dbvis.com/) works with lots of databases and is really pretty cheap. Disclaimer: no connection other being a long term customer, I use it with Oracle, MySQL and Postgresql.
  - johnyzee 13y ago
    TeamPostgreSQL is a very good web GUI for PostgreSQL: http://www.teampostgresql.com http://www.teampostgresql.com

jeffdavis 13y ago

(Disclaimer: I'm a postgres community member.) I think the biggest advantages are trust and flexibility. Trust, because PostgreSQL language semantics are cleaner, closer to the SQL standard, and less likely to surprise you. And postgresql just has a good reputation for traditional engineering quality. Flexibility, because it offers a lot of APIs and features that can be very useful to adapt your application as needs change. You don't have to go crazy with features, but even simple apps can benefit a lot from prudent use of them -- a trigger here, a foreign table there, or LISTEN/NOTIFY (for cache invalidation) can just save you a huge amount of work and make the system more robust overall. The extension mechanism is very powerful. Before making any big decisions, do a trial migration and see what you think.

deleted 13y ago

[deleted]

rektide 13y ago

If you didn't care yesterday, I HIGHLY ENCOURAGE AND RECOMMEND YOU NOT CARE TODAY.

elob36 13y ago
He cares today because he now has an option.

rachbelaid 13y ago

Just DDL transaction make life so much easier but there is much more like Materialized Views, Advanced Data type, Efficient JOIN, pg_stat_statements, PL, full text search, GIS features (pretty useful to do near by query), fuzzy search, ISO SQL standard compliant, Piece of cake replication. Do some search and give it a try one weekend.. Trying it is loving it.

Sanddancer 13y ago

Hating to pile on, but doing so anyways, one of the overlooked things I'd argue is that postgresql tries much harder to let you do neat things with your data without much hassle. Needing to write a custom function to process data? Not only does PostgreSQL have support for PL/PgSQL, but it's made in a way that has let people write plugins to python, perl, v8, tcl, etc, so you can do that data transformation on the database instead of shipping it back and forth to another server with the latency that brings. The recent version also brings read/write support to redis, so you have have your updates trigger redis to store new values, etc. Plus, being that it's not controlled by Oracle, there's much less a feeling of the community being held back; MySQL feels very much like Oracle's tying one hand back, so that when you get to the size to need a "real" database, Oracle will gladly step in, at their standard consulting fees, of course.