Slashdot Mirror
PostgreSQL Wins LJ Editor's Choice Award
Quickfoot writes "PostgreSQL has won the LJ Editor's choice award for database servers the second year in a row and three times total (2000, 2003 and 2004). With the upcoming features in version 8.0 PostgreSQL is posed to do even better in 2005."
It looks like they are ( have been? ) quite big fans of MySQL in the past. Netcraft shows that a couple of years ago ( 4th Aug 2002 ) that their webserver was running with Mod Auth MySQL.
Apache/1.3.26 (Unix) PHP/4.1.2 mod_ssl/2.8.10 OpenSSL/0.9.6e AuthMySQL/2.20
Get your own free personal location tracker
Well, just start a few chapters into the excellent postgres manual, skipping the bits that say "introduction to sql" and paying close attention to bits like "advanced features" and "extending sql"...
What a difference a few years makes! Today, PostgreSQL is fast, extremely robust, and incredibly capable. It scales better than MySQL, preserves data integrity better, and makes it possible to do things that you can't do even in Oracle (for instance, just about all DDL in PostgreSQL is transactional: table creation/deletion, index creation/deletion, user creation/deletion, etc. This means, for instance, that you don't have to have an operator to alter a column's datatype: you just create the new column, copy the data into it, and then drop the old column, all within a single transaction, and if you screw up you can roll the whole thing back). It supports a number of different languages in which one can write stored procedures. The planner is quite good and yet is constantly improving.
About the only thing that PostgreSQL is not is auto-adaptive. That is, one still has to configure it to get optimal performance, same as with any database I've ever seen. The default settings provided in the raw distribution are, well, quite conservative: they're set up so that you can successfully start PostgreSQL even on a small, old system, which means you almost certainly have to tweak the configuration file in order to get truly good performance out of it.
In short, PostgreSQL has gotten very, very good in a relatively short period of time. It's so good compared with the other freely-available databases out there that I can't really think of a compelling reason to use anything else -- it's so good that if you need something more capable then you're going to have to pay big, big money.
And 8.0 will get you native Win32 support (with a point-n-click installer and everything, if I'm not mistaken). With its feature set (especially if you include what's going to be delivered in 8.0), that makes PostgreSQL-win32 a real SQL Server killer, as long as it performs well on that platform.
In short, PostgreSQL deserves very much to win this award, and the PostgreSQL development crew deserves a ton of kudos for producing such a kickass database system. I'm very much hoping that third-party software support for PostgreSQL gets as good as it is for MySQL, because the database engine is certainly deserving of it.
Use 'slashdot stuff' in the subject line in any email you send me if you want to get past the spam filter.
> The PGAdmin3 tool
PLUG: Another good tool - PQA, a SQL query analysis tool. Here's a sample report.
The Army reading list
What's wrong with Rekall, or perhaps OpenOffice's DB interface (it works very well too) or maybe even using Microsoft Access. There's a commercial one from the UK too but I can't for the life of me remember the right incantation to bring it up in Google.
IIRC they are all programmable. Rekall's programmable in Python, OO in Java, Python and whatever else you can interface to it and Access in VB.
So what's this point-in-time recovery and what's it do better?
As I understand it, prior Point-in-Time recovery the WAL files would replay ALL of the transactions they contained, you could not pick were to end them.
With PIT you can tell the system to replay just to a certain point or all the way.
With the new utilities included with PostgreSQL 8.0(now beta) you can also use this as a backup system (it was not easy to do prior to this). Create a backup dump and load it into your backup server. Copy (rsync would work here) the WAL files over to the backup server and replay them as they compleate. When you need the backup, you can (using an included util) replay the last partial WAL file and bring the system up. If I were do this though I would most likely shrink the size of the WAL files from the stock 16MB to something a little smaller (unless your DB was VERY busy...).
BWP
Prior to 8.0 you just about had to do that, but with the ability to use gzip to compress your archives, it was not too bad unless you had lots of bytea or blobs in the DB.
But with the advent of Point-in-Time recovery in 8.0 thats changed. With the new utils you can make a dump of the system and just copy it and the WAL files around. Database crash (that is not handled automaticly)? Just load the backup and replay the WAL files to whatever point in time you want them. You can even use partial WAL files.
BWP
Continuous VACUUM actually is the solution. It just should be automatic instead of manual.
Databases inevitably have some point in a transaction where they require 2 versions of the same row to be present in persistent storage (on disk). That obviously means that the old version (or the new version in case of a rollback) has to be removed at some point in time. Some databases choose to do this on transaction commit, adding a little bit of overhead to each transaction. Some databases choose to do this in a separate process at scheduled intervals, reducing the commit overhead but adding the overhead of having more versions on disk. PostgreSQL has choosen the second path, and VACUUM is the cleanup process.
Which solution is the best depends on the requirements. As you have discovered, tables with a high turnover get easily bloated when the cleanup is not done frequently enough. The solution for that is to cleanup more often, with cleanup at commit of each transaction as the higher limit. But quite likely it is sufficient if cleanup occurs every X transactions or every Y seconds.
The intention was to have the pg_autovacuum utility integrated in the backend to manage the vacuum process for all databases in a cluster. If enabled, it would allow for automatic vacuum and analyze on tables, with some logic to learn if tables are high-turnover or fairly static. Unfortunately, the patch for that didn't make it into the 8.0 beta.
You can always dismiss any heated debate as simply a result of nit-picking or personal preferences.
However, the differences here are quite substantial. And it isn't really 'mysql vs postgresql' - it's more like 'mysql vs inexpensive standards-compliant database solutions'. What really irks most experience database developers about mysql is that mysql abandoned decades of standards and standard features - while insisting that 90% of the users didn't need transactions, triggers, views, etc. That's disingenuous misinformation.
mysql still has a role out there - since it has such a wide host base and so much 'mind share'. But this is all marketing. In almost any technical comparison, Postgresql now comes out on top.
Furthermore, since postgresql is very similar to other relational databases - migration between it and oracle, db2, sql server, etc is relatively painless (unless you went overboard on stored procs, etc). This means that your investment in postgresql is fairly 'future-proof'. If on the other hand, you've gone with mysql, you will always have a more difficult migration - and may fail to get anticipated performance benefits since you are probably using the target database is a way not recommended by its vendor (joining inside application rather than inside sql, etc). This is especially true of the many mysql applications out there that believed MySQL AB when they told people that 90% of the applications didn't need transactions (!)
Of course, you can wait for mysql to catch up to everyone else in the database features area, and perhaps they'll try to become more standards compliant along the way. But that's going to be a tough slog for them, probably involving a complete rewrite. Could take quite a while, and there may be no easy transition for today's mysql apps.
Sibling rivalry? hardly