Hancorp Technologies

I just got word that version 8.3 of PostgreSQL has been released.  Along with the usual amount of improvements there are some new features in 8.3 that should be of interest to PostgreSQL admins and developers such as:

• Integrated TSearch
• ENUM and UUID data types
• Faster sorting technique used for LIMIT operations
• Faster LIKE and ILIKE operations
• Lazy XID assignment which will make many read only operations much faster

Check out the full list of features at the PostgreSQL site or download it from the download section of their site.

My friend and former co-worker Adrian Holovaty and his team just launched their new project EveryBlock.com. EveryBlock takes the term hyperlocal to a whole new level.  They aggregate tons of public data sources by geo location so you can for example find all of the recent crime around a particular address, neighborhood, zip code, etc.  Or maybe you might be interested in the building code violations of where you live or work?

Right now they have San Francisco, Chicago, and New York up and running, but will be adding more cities as time goes on.  Adrian asked me to help performance tune their PostgreSQL database a couple of months ago and so far things seem to be humming along nicely.

Here are some links to other blogs talking about EveryBlock.com:

• News Kids on EveryBlock 
• Launching EveryBlock.com
• EveryBlock, an especially slick interface for discovering local information
• A Review of EveryBlock: How it helps the public

Congrats and the best of luck to EveryBlock! I’m sure we’ll see even more new and interesting things from this team in the future!

Welcome to the 65th edition of Log Buffer, the weekly survey of database related blogs.

First let’s start with some miscellaneous entries that could be of interest to any DBA.  Crazy DBA has an interesting post about how attending conferences helped to grow his professional network, which in turn has made him a better DBA.  And Thomas Kyte has a great post about why it’s the data, not the application itself, that matters.  Brian Aker gives us a great link to a Werner Vogels’ entry on Dynamo, one of the key technologies used behind the scenes at Amazon.

Oracle users will certainly find these two links of interest.  First off, Frederik Visser shows you how to play with Oracle 11g RAC in VMWare. And Alex Gorbachev has a nice write up about Miracle Open World.

SQL Server DBAs might enjoy the following posts.  If you’re thinking about using or upgrading to Idera SQLsafe v4.5, you’ll want to check out Sean McCown’s post about some of that product’s issues. Steve Jones has some thoughts on monitoring and alerting with your SQL Server, but are valid for any database. Need to know when your SQL Server instance was started? Check out Joe Webb’s tip on how to find out.  And Mladen Prajdić has some advice on how to notify a client in a long running process with SQL Server.

MySQL users will find this post on accurately measuring how far behind your slave is lagging. Over at the MySQL Performance Blog there is an opportunity to ask questions of Heikki Tuuri, the creator of InnoDB, and Peter has some thoughts on a few serious bugs in the MySQL 5.0 release.  Kaj Arnö has an interesting post on how MySQL GmbH and MySQL AB help birds of a feather to flock together, quite literally and about how they have opened up the call for papers for the 2008 MySQL Users Conference.

Kevin Burton talks about how to avoid swapping insanity with InnoDB. Want a free MySQL Magazine? Lewis Cunningham has found one for us all. Jan Kneschke introduces us to the Wormhole storage engine for MySQL.  Not really sure how useful it is, but it is definitely interesting.

Hubert Lubaczewski has written a great tool to help you determine the optimal layout of tables, indexes, etc. on your various tablespaces for PostgreSQL.  Robert Treat follows up with some additional thoughts to consider. 

Joshua Drake has announced the speakers and topics for the PostgreSQL Conference Fall 2007, which is October 20th 2007 at Portland State University. Greg Sabino Mullane has a nice explanation of why you can’t used prepared queries when using DBD::Pg and pg_bouncer. And to finish our this week’s  links, Francisco Figueiredo Jr mentions that PostgreSQL will have a UUID data type in version 8.3.

Enjoy!

Someone asked me how to determine which PostgreSQL backend a particular client was connected to.  Everyone’s first thought is to do a ps aux | grep postgres which will show you the IP and user, but if you have different processes connecting from the same IP with the same usernames, how do you know which is which?

One way to tell would be to see which queries are being executed by which backend and match that up to your client side.  But you can quickly get confused, especially if the various connections are all executing the same SQL statements, a web application for example.

The simplest way was suggested by Jacob Kaplan-Moss, which is to use the pg_backend_pid() function like:

SELECT pg_backend_pid();

I love it when the solution is something really simple!

Josh Berkus and Sun have put together the first real PostgreSQL performance benchmark.  I run into people often who are still working under dubious performance comparisons done years ago against against competition like MySQL and Oracle. Hopefully this in depth comparison will put some of these arguments to rest.

If you’re just interested in the conclusions, PostgreSQL is as fast or faster than MySQL and nearly as fast as Oracle.  I know the performance improvements over the last few years have been nothing short of phenomenal, glad to see there is now a report to back up my gut feelings.

In the spirit of my blog post last week, I’ve created a new page that shows a couple of the more common error messages that confuse newer PostgreSQL users. It is my intention to expand this over time as I see people having trouble.

If you have any error messages you feel should be included or you find any technical inaccuracies please post a comment and I’ll include it on the page.   

I see this problem pop up in the #postgresql IRC channel so often I felt it was necessary to blog about it. This problem trips up so many new users it might even be worth changing the default error message to indicate what is going on. The error message happens when the user tries to run psql for the first time:

psql: FATAL: database "root" does not exist

Where "root" is the current Unix username of the operator.  By default PostgreSQL attempts to log you into a database that is the same as your username.  However, it does not setup this database for you because it would be silly to setup 500 databases for all of the Unix users on your system, if only two of them are going to be using PostgreSQL. 

When setting up PostgreSQL for the first time you need to do the following:

1. su ( or otherwise ) become your root user
2. su ( or otherwise ) become your PostgreSQL user, typically ‘postgres’
3. Create your first database

The ultimate goal here is to become your PostgreSQL user, typically this involves becoming root and then switching to user postgres.  Upon setup this is the only user that is allowed to create users and databases.

Your "first" database can be created in one of two ways:

1. Run the command ‘psql template1′ followed by a ‘CREATE DATABASE’ SQL call
2. Run the command ‘createdb <dbname>’

While you’re still the postgres user it is probably best to also create a user with ‘createuser <username>’ or a ‘CREATE USER’ SQL call. See this section of the PostgreSQL documentation for more information on creating users and roles. You’ll also want to read up on managing databases.

NOTE: The programs createdb and createuser may not be, by default, in your PATH so it may be necessary to use locate or type in the full path to your PostgreSQL bin/ directory.

Hope this helps!

Ran across this article shows some Forrester Research data that Open Source databases such as PostgreSQL are 60% cheaper than the commercial alternatives.

I think most geeks are already aware of this. What I found interesting is the quote:

"Eighty per cent of the applications typically use only 30 per cent of the features found in commercial databases," Yuhanna told vnunet.com. "The open source databases deliver those features today."

In my experience working with clients it is more like 95% of applications use only 10% of the features found in commercial databases. I can’t even count the number of times a company absolutely needed Oracle for a 100MB, 3 table, simple CRUD database.

One of my favorite themes that comes up is when a company again needs Oracle/DB2/whatever because it has all of the mission critical features they need such as clustering, fail over, etc.  Then when it comes to implementation time the tune changes to "Oh we don’t really need a cluster.  And now that I think about it, we can handle downtime easily, so fail over isn’t required either."

I think it comes from the fear that they might need those features and so they play a safe bet.  Much like when you buy something like a car and get the luggage rack on the roof, "just in case", but then realize years later that you’ve never used it.

Moral of the story? As with any type of project, even non-technical ones, you should worry the most about what you know you need today and let tomorrow take care of itself. The tomorrow you are worried about may never come.

One of the great things about modern databases is you can let your database automate some of what used to only happen in application logic.  The example I love to show people is automatically updating a "last modified time" timestamp column in a table. 

This is easily accomplished if you always use the same name for those types of columns.  I like to use ‘created’ for the creation timestamp and ‘modified’ for the last modified time.  First we create a simple function:

	CREATE OR REPLACE FUNCTION update_modified_column()	RETURNS TRIGGER AS $$	BEGIN	   NEW.modified = now(); 	   RETURN NEW;	END;	$$ language ‘plpgsql’;

This function simply sets any column named ‘modified’ to the current timestamp for each row passed to it by the trigger. If you use the same column name consitently you only have to do this step once.  Now, you just have to create your trigger like so:

        CREATE TRIGGER update_customer_modtime BEFORE UPDATE        ON customer FOR EACH ROW EXECUTE PROCEDURE         update_modified_column();

This technique is very useful when you don’t want to have to rely on your application developers to always remember to update the time stamps.  You can just let PostgreSQL handle it for you.

You should note that you will have to create a separate trigger for each table, which isn’t a big deal.  Also, the BEFORE UPDATE is very
important.  If you attempt to use AFTER UPDATE you put yourself into an infinite loop!