News for hackers. Stuff that matters.
Re:Amazon RDS for PostgreSQL by yumraj on Thursday November 14, 2013 @12:26PM (#6733653)
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.

Re:Amazon RDS for PostgreSQL by adrianh on Thursday November 14, 2013 @12:39PM (#6733771)
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-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.

Re:Amazon RDS for PostgreSQL by eli on Thursday November 14, 2013 @01:13PM (#6734134)
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.

Re:Amazon RDS for PostgreSQL by jacques_chester on Friday November 15, 2013 @12:34AM (#6737923)
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.
Re:Amazon RDS for PostgreSQL by e12e on Friday November 15, 2013 @11:59AM (#6740506)
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-ta...

I'm not sure what the status of TEXT-fields are in mariadb:

https://mariadb.com/kb/en/optimizing-string-and-character-fi...

Re:Amazon RDS for PostgreSQL by jacques_chester on Friday November 15, 2013 @08:59PM (#6743245)
> Thanks for the explicit heads-up.

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.

Re:Amazon RDS for PostgreSQL by eli on Friday November 15, 2013 @05:47PM (#6742670)
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.
Re:Amazon RDS for PostgreSQL by user9903 on Friday November 15, 2013 @10:43AM (#6740016)
I don't think it's fair to say Mysql doesn't care about your data. For example,

> 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#...

Re:Amazon RDS for PostgreSQL by Daishiman on Thursday November 14, 2013 @04:54PM (#6735915)
Best open source geospatial? It handily beats Oracle and MSSQL!
Re:Amazon RDS for PostgreSQL by ChikkaChiChi on Thursday November 14, 2013 @02:19PM (#6734766)
I've rarely seen a "Why not X?" so concise in getting its point across. Thanks!
Re:Amazon RDS for PostgreSQL by Roboprog on Saturday November 16, 2013 @02:11PM (#6746033)
Thanks for the updated list. It's been a LOOOOONG time since I compared MySQL vs PostGreSQL in detail (around 2001), but what I found at the time made me never want to look at MySQL again.

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?)

Re:Amazon RDS for PostgreSQL by lhaussknecht on Friday November 15, 2013 @04:25AM (#6738493)
Are add-ons such as PostGIS supported on RDS?
Re:Amazon RDS for PostgreSQL by lhaussknecht on Friday November 15, 2013 @04:34AM (#6738525)
Hehe, never mind. It's the first of the offered features...
Re:Amazon RDS for PostgreSQL by eLod on Thursday November 14, 2013 @01:34PM (#6734341)
i myself heavily favor pg over my also, but just for the record mysql has some kind of support for that, see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#...
Re:Amazon RDS for PostgreSQL by jeffdavis on Thursday November 14, 2013 @12:53PM (#6733917)
(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.

Re:Amazon RDS for PostgreSQL by gdulli on Thursday November 14, 2013 @12:51PM (#6733892)
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

Re:Amazon RDS for PostgreSQL by elob36 on Thursday November 14, 2013 @02:53PM (#6735041)
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.

Re:Amazon RDS for PostgreSQL by unclebucknasty on Thursday November 14, 2013 @10:58PM (#6737650)
The single index in a query issue seems to have been addressed as of 5.0:

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimizat...

Re:Amazon RDS for PostgreSQL by will_work4tears on Thursday November 14, 2013 @09:24PM (#6737295)
I'm on mobile and cant quickly and easily search this but is there a good GUI for postgre similar or superior to phpmyadmin?
Re:Amazon RDS for PostgreSQL by epo on Friday November 15, 2013 @05:18AM (#6738648)
Dbvisualizer (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.
Re:Amazon RDS for PostgreSQL by aftabh on Thursday November 14, 2013 @09:45PM (#6737373)
Try pgAdmin at http://www.pgadmin.org/

Edit: You can also check phppgadmin at http://phppgadmin.sourceforge.net/doku.php

Re:Amazon RDS for PostgreSQL by johnyzee on Friday November 15, 2013 @08:50AM (#6739358)
TeamPostgreSQL is a very good web GUI for PostgreSQL: http://www.teampostgresql.com
Re:Amazon RDS for PostgreSQL by billmalarky on Saturday November 16, 2013 @04:24AM (#6744254)
Check out Navicat and you will never touch phpMyAdmin again.
Re:Amazon RDS for PostgreSQL by pnathan on Thursday November 14, 2013 @12:50PM (#6733888)
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.

Re:Amazon RDS for PostgreSQL by the_mitsuhiko on Thursday November 14, 2013 @12:36PM (#6733746)
> 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.)

Re:Amazon RDS for PostgreSQL by jeffdavis on Thursday November 14, 2013 @12:56PM (#6733955)
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.

Re:Amazon RDS for PostgreSQL by gtaylor on Thursday November 14, 2013 @03:35PM (#6735347)
> 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.

Re:Amazon RDS for PostgreSQL by rachbelaid on Thursday November 14, 2013 @07:25PM (#6736857)
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.

Re:Amazon RDS for PostgreSQL by Sanddancer on Thursday November 14, 2013 @07:43PM (#6736941)
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.
Re:Amazon RDS for PostgreSQL by rektide on Thursday November 14, 2013 @01:44PM (#6734440)
If you didn't care yesterday, I HIGHLY ENCOURAGE AND RECOMMEND YOU NOT CARE TODAY.
Re:Amazon RDS for PostgreSQL by elob36 on Thursday November 14, 2013 @02:55PM (#6735050)
He cares today because he now has an option.