toofishes.net

PostgreSQL thoughts and experiences

When I moved my blog from DreamHost to Slicehost for toofishes.net, I also took the plunge and switched from MySQL to PostgreSQL as the backing database for this Django web application. DreamHost provided the MySQL databases and didn’t offer Postgres as an option, so I was left without much choice there. Moving to a VPS solution, I had my choice of databases so I decided to give Postgres a try.

Postgres was one of the big-name RDBMSs I had yet to work with. In the past, I have had a decent amount of experience with MySQL, MS SQL Server, Oracle, and even a bit with IBM DB2.

Hopefully this post doesn’t become a “PostgreSQL crushes MySQL” type rant, but I’ve definitely seen some advantages to switching. I’ll try to highlight a few of those and also outline any drawbacks I’ve seen.

Postgres doesn’t have the same concept of multiple storage engines as MySQL, which I find to be an advantage. You don’t have to worry about which database type supports true foreign key constraints and full-text search—Postgres just does.

psql has turned out to be as useful as the mysql command line client. Things are slightly different, but psql definitely gets it right in that Control-C doesn’t dump you out of the client like mysql does.

Memory usage has been great for fitting within the bounds of a 256 MB slice. The virtual memory allocated by Postgres is a bit deceiving at 40 MB; however, right now it appears to be only really using 4 to 5 MB of physical RAM. Of course, I have a lightly-trafficed site and a rather small set of data, but there isn’t a huge tradeoff memory-wise between using Postgres and going the embedded route with sqlite.

Postgres has a couple of types devoted to storing IP and MAC addresses. MySQL leaves much to be desired in this arena. You can either store IP addresses as varchar columns (as Django does by default), or you can create an INT UNSIGNED column and use the inet_aton() and inet_ntoa() functions to translate to and from the dotted-quad representation.

Where does this fall apart for MySQL? One, it can’t handle subnets and other things nearly as well as Postgres, which does it easily with the inet type. It also chokes if you want to mix IPv4 and IPv6 addresses. When it comes to Django, storing IP addresses natively rather than in text form also allows the sort order to be correct, rather than text-based. MySQL will sort ‘128.127.126.125’ before ‘76.77.78.79’; Postgres gets it right.

So where does Postgres lose? I haven’t had to do it yet, but the dump/restore on major upgrades isn’t quite as as seamless as a MySQL upgrade. I’ll wait and see before I pass too much judgment on this—I’m currently running 8.3.7, 8.4 is in beta, and I will probably upgrade soon after the 8.4 final release.

MySQL’s ubiquity means there is a lot more information on the internet when it comes to questions. However, I’ve yet to have a question about Postgres I haven’t found the answer to. It might also be a benefit that Postgres is used a bit more selectively, as it cuts down on the signal-to-noise ratio. PostgreSQL’s online documentation is also excellent.

Tags

See Also