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.
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-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.
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.
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-fi...
It took me a long term to learn this one. I suppose if I'd read the MySQL docs from cover to cover I would've found it earlier.
One other problem that popped up was ignoring indexes on tables with TEXT fields during joins, which was a planner weakness. I understand it was fixed in 5.6; I'm waiting for the Percona version to stabilise before I upgrade.
> Perhaps you mistakenly insert "2013-10-32" into a date column.
Only with ALLOW_INVALID_DATES sql mode set. As of 5.0.2, the server requires by default that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31.[1]
> Perhaps you make an error in a transaction. MySQL lets you keep doing subsequent things in the transaction.
If you care about transactions you should have STRICT_TRANS_TABLES on.[2]
[1] - http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#...
[2] - http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#...
Understand, MySQL-ers, I know that your DB has been patched A LOT over the last decade, but running with something that did not support ROLLBACK (nor isolation), nor foreign key constraints???
That the MySQL team even thought they could call such a thing a database terrified me, and made me quite scared to ever trust their judgement. (viewing the comments for this article suggest to me that playing those odds was the right thing to do, as well, rather than simply "prejudice")
That, and at the time, PostGreSQL was supporting stored functions that fit anywhere in SQL statement syntax that the return type matched the needed expression type (scalar, vector/row, matrix/table), and could be written in a PL/SQL work-alike OR alternate loadable languages, while MySQL had no stored procedures at all.
That, and at the time (already), PostGreSQL supported OOP-ish "extension" tables that extended other tables with extra, specialized, columns. Rows in the specialized, subclass, table would show up in the generalized, superclass, table (sans extra columns), but the subclass table would only show the relevant specialized type rows, with non-null columns where needed. Other DBs required you to join 2 tables and manage joins and a view to do this.
Putting SQL syntax on top of an ISAM engine just makes it dBase with awkward syntax, and that's not an environment I wish to revisit. (I know that InnoDB is constantly twiddled to suck less, but that back-end was extra back in the day, yes?)
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.
- 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
My group is developing all new applications in PostgreSQL and we would like to migrate our legacy apps away from MySQL.
http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimizat...
Edit: You can also check phppgadmin at http://phppgadmin.sourceforge.net/doku.php
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.
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.)
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.
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.
Do some search and give it a try one weekend.. Trying it is loving it.