What's new in PostgreSQL 9.0 - a User's Perspective | Hacker News Reader

What's new in PostgreSQL 9.0 - a User's Perspective

wiki.postgresql.org

121 pointsjsrn45 comments

This is a wonderful upgrade that might finally convince some users to leave the mess that is MySQL behind.

It's been a long wait but replication is now finally as easy as it gets. And, by design, it can not silently lose or corrupt data. Unlike a certain other popular RDBMS.

I honestly can't imagine why folks continue to use MySQL over PostreSQL. All technical benefits of PostgreSQL aside, I can't help but wonder if it really comes down to something silly, like MySQL being much easier to pronounce properly.

For my part, I'm stoked about 9.0. It can't get here soon enough.

Very simple: a lot of pre-packaged software doesn't use except for MySQL.

Well, to clarify, a lot of popular pre-packaged software is out there in PHP. And PHP never had a good db abstraction layer + the entire style of the PHP language lent itself to the direct use of queries in the code, and the direct usage of the MySQL connectors.

When users want a forum, a wiki, a helpdesk, a blog, etc. and it's in PHP, chances are it doesn't have a PostgreSQL backend. And if you have to have MySQL, and MySQL works for everything (for some definition of the word "works") while PostgreSQL doesn't... there's your answer.

The real reason is: PostgreSQL doesn't have any must-have apps that only run PGSQL (which is a good thing), and MySQL does (which is a bad thing) so MySQL wins.

This is exactly why there are two DB's at my work.

- Postgres for Django and custom Perl system.

- MySql for all the 3rd party crap WP, forums, Redmine, couple craptistically old Perl help ticket and project management systems.

Just want to note that Redmine runs just fine with PostgreSQL.

We do the same at my work too, but we have less third party apps that require MySQL.

There's ADODB for PHP. And it is a very good DB abstraction layer.

However, only some sites use it and big projects like Wordpress seem to be totally oblivious to it.

There is indeed ADODB, and I have used it. It still more or less requires writing queries, though — and most ORM systems — redbean, Kohana's ORM — still seem to interact directly with MySQL instead of using ADOdb.

Wordpress.

Most widely used blog engine (CMS?) in the world - only MySQL. http://codex.wordpress.org/Using_Alternative_Databases

Drupal is probably one of the few PHP CMS that work with PG - http://geek.joshwaihi.com/content/drupal-7-postgresql-suppor...

And even for PHP apps like MediaWiki that do support PostgreSQL the primary development target is still MySQL.

So it's not worth the trouble to use PostgreSQL in most cases, you'll eventually want to install some extension that only supports MySQL, or get tired of reading documentation that assumes MySQL etc.

It depends, lots of people know mysql and don't want to deal with switching costs if what there doing isn't going to be limited by database performance. So even if PostreSQL would be the better choice it may not be in terms of migration costs.

In my case, I can tell you it's because I don't already know PG, and MySQL hasn't let me down to the point that I've had to consider switching.

I've done the 'apt-get install postgresql' routine a few times, and started to play with it, but due to it being different enough, and without any compelling motivation, I've never been able to learn it beyond scratching the surface.

That said, I'm a developer, and not a DB admin, so the limitation is almost certainly mine.

If MySQL hasn't let you down, then, frankly, you are just lucky. In fact, I'd have trouble to even call MySQL a database as it even lacks the one feature that a database needs to have: The ability to get the data back that you put in it.

If by accident you put Latin 1 (or any other non-utf-8-data) into a database that is configured as UTF8, MySQL will go ahead and cut off your data at the first byte with the 8th bit set. No error. No warning. The data is just gone. The only way to find out this happened is by reading back after every write - something I don't want to have to do.

Or allowing to

    alter table whatever add somecolumn text not null;

that succeeds and consequently sets every row of somecolumn to NULL, violating the constraint.

Or inserting strings longer than allowed by the datatype which MySQL doesn't complain about but just truncates them - another case of having to read and compare the data just stored.

And don't get me started about corrupt on disk data, leading to unreadable tables. But worse - mysqldump at least once exited with an exit code of 0 even though it failed to read one of these corrupt tables. What's worse: It stopped the dumping process and didn't dump any tables and even databases following the corrupt table - yeah. I thought I backed up, but in fact I didn't.

Stuff like this must not happen with a database.

Stuff like this never happened to me with PostgreSQL.

It might be harder to set up. It might feel a bit foreign at first. It might provide features you think you don't need. But at least it doesn't destroy data I entrusted it with.

edit: Also see the rant on my blog I posted when I ran into the UTF8-problem: http://www.gnegg.ch/2008/02/failing-silently-is-bad/

In MySQL, you can set the SQL mode to make it just as strict as Postgres. I'm still using MySQL for two reasons: a) It's already in place and it works great and b) I'm very familiar with it. For the next project (which may never come), I'll definitely be looking into Postgres.

In MySQL, you can set the SQL mode to make it just as strict as Postgres.

How much are you inclined to trust this mode which was bolted on as an afterthought, though?

I'm maintaining a bunch of MySQL installs for customers and the amount of random failures I've seen even with InnoDB and strict mode during backup/restore and upgrades is just not funny.

Obscure second guesses during even a minor version upgrade are the rule rather than the exception. 'mysql_upgrade' hardly ever worked right for me at first try. A straightforward dump/restore is not an option either because they change the schema of the system tables all the time (which, to add insult to injury, are still MyISAM).

Then you also frequently bump into gems like the following:

Incompatible change: As of MySQL 5.5.3, the server includes dtoa, a library for conversion between strings and numbers by David M. Gay. In MySQL, this library provides the basis for improved conversion between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers.

Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision. (From: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previo...)

Oh, and don't get me started on what they call "replication", which will happily, silently desync or corrupt data in various situations.

So well, yeah. Perhaps strict mode indeed works right in all cases. Perhaps.

MySQL has a incomparable better marketing and internet presence (well written documentation and community sites).

Its success was due to illusion of simplicity and easiness + massive community of believers ^_^

PostreSQL sites is rather shy comparing to them.

Have you seriously compared MySQLs documentation to Postgres? It has such mindblowingly retarded gems such as http://dev.mysql.com/doc/refman/5.1/en/connection-access.htm.... I mean people actually sat around and thought this was the best way to do user auth.

Feeling is what matters, especially for newbies.. Documentation should be written is a light, non-boring style. Django's or Android's is a best examples.

Django's or Android's is a best examples

Yes, but how can you put those in the same bucket with MySQL's?

http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html vs http://www.postgresql.org/docs/current/static/app-pgdump.htm...

Note how half the page of the mysqldump docs is spent explaining the braindead semantics and dangerous interaction between --opt* and pretty much everything else.

Moreover the MySQL docs generally read as if they were written by a monkey with ADD:

mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default, so to enable memory buffering, use --skip-quick.

This is frankly just a random example from recent memory. Compare any two pages and you get similar results.

This is a wonderful upgrade that might finally convince some users to leave the mess that is MySQL behind.

I agree. PG is becoming better and better with every release. It can compete with Oracle in some applications. Personally I hope they implement Row Level Security some time. It's my favorite feature in Oracle and I hope they will implement something similar soon.

To be fair some other popular RDBMS had useable replication way before it came to PG. Also it would be interesting to see how much PG is better when used on the scale MySQL is used: the case of benefits on paper vs. real benefits.

how much PG is better when used on the scale MySQL is used

Please do your homework before spreading FUD. PostgreSQL is used at large scale every day, e.g. at Skype: http://highscalability.com/skype-plans-postgresql-scale-1-bi...

More examples can be found here: http://www.postgresql.org/about/users

Where is FUD in my comment? Or am I wrong thinking that MySQL has more and different scenarios production install than PG? MySQL is also used on very large scale every day, so?

Or am I wrong thinking that MySQL has more and different scenarios production install than PG?

Yes, you are wrong.

another year, another release.

Thank you, everyone behind the development of PostgreSQL. I wouldn't be in my life where I am now if it wasn't for the work you are constantly putting into this wonderful project.

And when one thinks "now. this is it. it can't get any better now", you come out with another high-quality release.

Thank you ever so much.

I keep hoping that PostgreSQL will support an embedded version. SQLite only gets you so far, and it'd be nice to be able to ship PostgreSQL statically linked into your applications with zero-configuration at the user's end.

MySQL's working on this (http://mysql.com/oem/), but I've read that PostgreSQL is way too tied to their multi-process network architecture for this to be feasible. But I'd love to be shown to wrong on that.

I don't think it'd be terribly infeasible from an architectural standpoint because of the multi-process network architecture, but there are other things to consider. The multi-process architecture is really in it's favor, as it already uses IPC primitives (shared memory, semaphores) to share cache and control access to shared resources. The biggest barrier (I think) would be the file format. SQLite stores all of it's data in a single file (plus one temporary file for the journal). PostgreSQL's on-disk format would have to be heavily redesigned to support this, which I think is a huge advantage for SQLite.

Then again, there's no reason why an embedded database can't launch multiple subprocesses, or talk to itself via a socket for that matter. The same goes for the storage SQLite uses one file, but an embedded PostgreSQL allocating one opaque directory would be fine.

The important part is that you'd be able to easily statically link to it and handle permissions on a by-file basis, instead of PostgreSQL's current permission model. For all intents and purposes it'd be a larger SQLite replacement then.

This is it. The final release that will put PostgreSQL ahead of MySQL in the performance and scalability realm. They've already exceeded or are at par with MySQL in single-box OLTP performance, now PostgreSQL can scale-out with read slaves. Other than entrenchment, there's very little justifiability in using any other RDBMS system (proprietary or open source) after 9.0 rolls out.

Postgres just keeps getting better. 8.4 was a fantastic release, too.

I don't know how they manage it, but every release adds features and becomes more stable. Pretty awesome job.

And they also get faster with every release.

A very high quality product, indeed.

I've been wanting to use PostgreSQL. Does anyone know any good GUI tools for MacOS X for it?

pgAdmin3 + SQLEditor (http://www.malcolmhardie.com/sqleditor/)

Ah, it looks like pgAdmin3 is what I was after. It's a bit fugly but it will probably do.

Just once I'd like to have a discussion on HN about either MySQL or PostgreSQL without having a long thread about why one is more popular/better than the other. Not going to happen.

I know that cost and cross platform capabilities are big issues but for someone using MS SQL server express (free if database size < 10 GB) in windows, are there any advantages that PostgreSQL has?

datapoint: I've been using PostgreSQL (and it's GIS extension PostGIS) with Rails for over a year now. Some minor issues along the way, but never ever going back to MySQL.

Does this release add any features that would keep you from going for a NoSQL solution? How does it for instance compare to MongoDB in terms of raw performance and scalability? (Not mentioning of course the difference in schema-less and relational design)

I have found Apples to Apples (fsync off in PgSQL) and doing key/value store in PgSQL, PgSQL performed on par with, or slightly faster than MongoDB, using my admittedly not fully baked KVPBench app - http://github.com/gmr/kvpbench.

FUN FACT: (and you probably already know this but many do not): "fsync = off" is postgresql-ese for "please destroy all my data". Don't do it. Ever. The 9.0 docs are finally improving the language here to indicate that you must not set fsync off if you value your job.

Setting "synchronous_commit = off" nets you 99.9% of the performance improvement without the possibility of corrupting your entire database.