PostgreSQL is goodness

A recent post by my old friend Sandy reminded me that I haven't posted much of anything about PostgreSQL on my blog. About 10 months ago I did some extensive research on open source databases and quickly zeroed in on PostgreSQL. Here in summary form are what's to like about PostgreSQL (PG).
  • It's true open sources and free. MySQL is sorta, kinda open source but not to same level as PG.
  • PG is very flexible and agnostic. There is nothing special about the function SUM as used in PG versus writing your own SUM function in C and hooking it in. This is also true of datatypes and nearly everything else (like indexes). You can create new datatypes, functions, index types etc. with absolutely no speed penalty and without requiring a recompile of PG.
  • PG is very close to the features of Oracle. I use Oracle every day at work and have learned a lot of the ins and outs of that product. No doubt about it, Oracle is very quick, especially when you've got more than 4 processors, but PG is even faster in most cases if you have fewer processors. It's also close because they both use a write-ahead log (thereby supporting MVCC unlikes most other databases which use row or table locking), not to mention stored procedures, updatable views, triggers, basically all types of referential integrity constraints, sub-selects and so on. If ever you needed to migrate your database from PostgreSQL to Oracle you'd be far better off with PG than you would with MySQL, in my opinion.
  • Excellent documentation. I once saw a review of databases and the reviewer rated the 'community' by doing a google search and seeing how many hits he got. He got a lot more for Oracle than he did for PG, therefore he said that Oracle is 'better'. The way I read it, is that Oracle's documentation is so poor that you need to access dozens of sites to understand anything, and with PG the source documentation is so good you don't have to go very far. MySQL's docs aren't too bad as well, but PG's is better. I especially enjoyed (!) reading about the internals of PG. You can see the obvious pride that the developers have with PG and they show it through their detailed documentation of how it all works. Understanding the actually algorithms and data structures used can really help when writing programs. When you write a query you can pretty much imagine in your mind's eye how PG is going to handle it. When learning about Oracle, it was all about rules-of-thumb and best practices, you had no idea why you needed to do things a certain way.
  • Support for your favorite back-end language. There are certain benefits of having some business rules performed in stored procedures instead of in you client code. It's perhaps even more true in larger companies where one group writes the web pages, another the sales force program, and another the telephone interface. Each is
    written in a different language and the business rules are replicated all over the place. With PG you can write these rules in your favorite language (PL/pgSQL, PL/Perl, plPHP, PL/Python,
    PL/Ruby, PL/sh, PL/Tcl, PL/R, PL/Java or in C and C++) and store it in the database. An added bonus
    is that PL/pgSQL looks a lot like Oracle's PL/SQL, if you're used to
    that.
  • PG really has no limits. I follow the pgsql-performance@postgresql.org mailing list and am regularly blown away with the size of the machines these people run PG on and how many rows they add every day, like this post:
    There are some additional tables that have a few million rows / day of activity, so call it 60M rows/day added and removed.
  • PG closely follows the ANSI SQL standard. It does this far better than older versions of MySQL or even Oracle.
  • PG has only one type of storage engine. This is in comparison to MySQL which has InnoDB and MyISAM and some others. With a simple test I did in Python I found MySQL and MyISAM to be quite a bit faster than PostgreSQL. But when I tried MySQL with InnoDB it was slower. Basically, I think if you need the features that are included in the InnoDB you'd be better off going with PostgreSQL directly. Some may say that MySQL's multiple storage engines an important feature, but I think it just complicates things and makes you make difficult decisions earlier.
  • PG scales well with lots of users. This has to do with the fact that it hardly ever needs to lock a row because of MVCC.
Some PG 'problems':
  • PG used to be hard to install in Windows, not anymore. I think this is one big reason that MySQL surged ahead of PG historically it was a little tricky to install.
  • Select count(*) can be slow. It's still true, but there are ways around it (basically using triggers).
  • Some drivers (like the PHP driver) aren't as fast as they could be, but it's getting better all the time.
  • PostgreSQL has a funny name.
My advice is to use PostgreSQL, it's powerful, free and open source. Stuff you learn with it will help you if you get a job using Oracle. The power and flexibility of PG means you can use it for a wide variety of purposes.

Comments

Post a Comment

Popular posts from this blog

Shortest Sudoku solver in Python

Mark Byer's site has some more Sudoku solvers here's a short one in Python that's only 178 bytes long: def r(a):i=a.find('0');~i or exit(a);[m in[(i-j)%9*(i/9^j/9)*(i/27^j/27|i%9/3^j%9/3)or a[j]for j in range(81)]or r(a[:i]+m+a[i+1:])for m in'%d'%5**18] from sys import*;r(argv[1]) On the site is also shown a longer Perl version at 185 bytes: use integer;sub R{for$i(grep!$A[$_],@x=0..80){%t=map{$_/27-$i/27|$_%9/3-$i%9/3&&amp;amp;amp;$_ /9-$i/9&&($_-$i)%9?0:$A[$_]=>1}@x;R($A[$i]=$_)for grep!$t{$_},1..9;return$A[$i]=0} die@A}@A=split//,<>;R I think the Python version is slightly easier to understand, although they are both quite cryptic. I'm not sure why I find these programs fascinating. I think it reminds me that any program can be written in such a way that it's cryptic, even in Python. In addition, the code is small enough that you should be able to figure out how it works, and may learn something the process. Related is
Read more

Seven Segment Display in Inkscape

Image
Today I got a little sidetracked while working on my key-train (keyboard training) project.  I thought I might spruce it up a little by using a seven segment display for the digits (say for the words per minutes), similar to what kTouch has.  I found an open source font, but it appears to be difficult to just use a ttf font in GTK. So I figured, it's only 10 images, I'll just make them in svg. I vaguely remembered a heathenx inkscape podcast  describing how to make an LCD display and decided to use that at my base. Unfortunately, they didn't provide any source files in the show-notes (as far as I could see) so I had to watch and redo the drawings myself! By naming each of the seven segments I could create the 10 digits simply by deleting a few elements in one template svg file .  I can create the template as beautiful as I like and quickly create the 10 derived images with these programs . Unfortunately, both ImageMagick and GraphicsMagick don't handle
Read more

Dot to Png

Image
For fun I wasted some time creating diagrams using the dot language. It's pretty fast to get a diagram out, except that there are a lot of things you can't do, like make shadows or rounded and filled rectangles. With this dot code: digraph "User Clicks" { node [ shape="rectangle" style="filled" ] server [label="Server"] java [shape="ellipse" label="Java\nCode"] provider [label="Service\nProvider"] browser [label="Firefox"] browser->server[label=<&#9312; AJAX Sends Session ID>]; server->java[label=<&#9313; Confirms Session ID>]; java->provider[label=<&#9314; Makes HTTPS call>]; provider->java[label=<&#9315; Results>]; java->server[label=<&#9316; Results>]; server->browser[label=<&#9317; Results>]; } You can click on the image to see the SVG rendered in Firefox, if you use that. I wrote a little python program to call t
Read more