Our Blog

Greg’s THREE talks at PostgreSQL Conference East this weekend

By Selena Deckelmann
April 2, 2009

(Cross posted from my personal/postgres blog)

Greg Sabino Mullane will be presenting three talks at PostgreSQL Conference East this weekend in Philadelphia, at Drexel University. The talks are listed on the site, and here’s what he’ll be speaking about:

Bucardo
April 5, Sunday, 10am

Bucardo is a replication system for Postgres that uses triggers to asynchronously copy data from one server to many others (master-slave) or to exchange data between two servers (master-master). We’ll look at replication in general and where Bucardo fits in among other solutions, we’ll take a look at some of its features and use-cases, and discuss where it is going next. We’ll setup a running system along the way to demonstrate how it all works.

Monitoring Postgres with check_postgres.pl
April 4, Saturday, 2:30pm

What should you monitor? And how? We’ll look at the sort of things you should care about when watching over your Postgres databases, as well as ways to graph and analyze metadata about about your database, with a focus on the check_postgres.pl script.

The Power of psql
April 4, Saturday 10:30am

All about everyone’s favorite Postgres utility, psql, the best command-line database interface, …

Google Base and Spree

By Steph Skardal
March 31, 2009

Several clients I’ve worked with have integrated Google Base. I’ve always wondered how much Google Base actually impacts traffic without having access to the data.

After the recent development of a google base spree extension, we’ve been able to measure the significant impact of search engine traffic directly related to Google Base. The extension was installed for one of our Spree clients. See the data below:

Search engine traffic (y-axis is hits where the referring site was a search engine). Google Base was installed on March 24th.

Other referral traffic during the same time period (y-axis is hits where the referring site was not a search engine and traffic was not direct).

Although the extension is relatively simple and has room for improvement (missing product brands, product quantity), you can see how much traffic was impacted. Hopefully with future extension improvements, traffic will increase even more.

Rails and SEO advantages

By Ryan Masters
March 27, 2009

In today’s climate, search engine optimization is a must to be competitive. Rails routing provides this advantage and much more.

Descriptive, content packed URLs afford your website better search rankings because they provide a clear context as to what the page is about. Using keywords in the filename goes even further. Under normal circumstances, without advanced configuration, a web page filename is rigid and fixed. This isn’t a problem in itself, except for that it doesn’t help with SEO one bit.

Having multiple URLs linking to the same page opens more doors to search engine crawlers. Generally, once indexed correctly, this means more access paths in to your site which in turn a result in a greater variety and volume of traffic.

Normally in most other programming languages, you would need to use an Apache rewrite rule to accomplish this. This rule will detect a digit in a file name and pass it along as a parameter to another dynamically generated page.

RewriteRule ^/.*([0-9]+).*$ /index.php?i=$1 [R=301,L]

This rule is definitely probably too greedy of a match, however, it serves to illustrate the point. With that rule in place, any request containing at least one number will be …

Inside PostgreSQL — Multi-Batch Hash Join Improvements

By Josh Tolley
March 26, 2009

A few days ago a patch was committed to improve PostgreSQL’s performance when hash joining tables too large to fit into memory. I found this particularly interesting, as I was a minor participant in the patch review.

A hash join is a way of joining two tables where the database partitions each table, starting with the smaller one, using a hash algorithm on the values in the join columns. It then goes through each partition in turn, joining the rows from the first table with those from the second that fell in the same partition.

Things get more interesting when the set of partitions from the first table is too big to fit into memory. As the database partitions a table, if it runs out of memory it has to flush one or more partitions to disk. Then when it’s done partitioning everything, it reads each partition back from the disk and joins the rows inside it. That’s where the “Multi-Batch” in the title of those post comes in—​each partition is a batch. The database chooses the smaller of the two tables to partition first to help guard against having to flush to disk, but it still needs to use the disk for sufficiently large tables.

In practice, there’s one important optimization: after …

End Point: Search Engine Bot Parsing

By Steph Skardal
March 25, 2009

I’ve talked to several coworkers before about bot parsing, but I’ve never gone into too much detail of why I support search engine bot parsing. When I say bot parsing, I mean applying regular expressions to access log files to record distinct visits by the bot. Data such as the url visited, exact date-time, http response, bot, and ip address is collected. Here is a visual representation of bot visits (y-axis is hits).

And here are the top ten reasons why search engine bot parsing should be included in search engine optimization efforts:

#10: It gives you the ability to study search engine bot behavior. What is bot behavior after 500 error responses to a url? What IP addresses are the bots coming from? Do bots visit certain pages on certain days? Do bots really visit js and css pages?

#9: It can be used as a teaching tool. Already, I have discussed certain issues from data generated by this tool and am happy to teach others about some search engine behavior. After reading this post, you will be much more educated in bot crawling!!

#8: It gives you the ability to compare search engine bot behavior across different search engines. From some of the sites I’ve examined, the Yahoo bot …

Generating sample text automatically

By JT Justman
March 25, 2009

It’s a classic problem: you have a template to test, or a field constraint to test, and you need a large block of text. Designers and developers have come up with many ways to generate this sample data. My favorite is the classic ‘Lorem Ipsum’ Latin text used by typesetters for hundreds of years. For a long time I’ve just copy-and-pasted it, but I just happened to find a really cool Lorem Ipsum generator, complete with the ability to specify character length, paragraph number, word count, or even make a bulleted list. Simple and stylish, and easy for less technical collaborators to use. Hit the link for some fascinating history.

I’m sure many of you have your own methods. Share yours in the comments! Extra points for creative shell one-liners.

Ack, grep for Developers

By Brian J. Miller
March 19, 2009

A relatively new tool in my kit that I’ve come to use very frequently over the last 6 months or so is Ack. Notwithstanding that it is written in my preferred development language, and is maintained by a developer active in the Perl community working on some important projects, like TAP, it really does just save typing while producing Real Purdy output. I won’t go so far as to say it completely replaces grep, at least not without a learning curve and especially for people doing more “adminesque” tasks, but as a plain old developer I find its default set of configuration and output settings incredibly efficient for my common tasks. I’d go into the benefits myself, but I think the “Top 10 reasons to use ack instead of grep.” from the ack site pretty much covers it. To highlight a couple here,

1. It’s blazingly fast because it only searches the stuff you want searched.

2. Searches recursively through directories by default, while ignoring .svn, CVS and other VCS directories. Which would you rather type?

   • $ grep pattern $(find . -type f | grep -v '\.svn')
   • $ ack pattern

3. ack ignores most of the crap you don’t want to search

   • VCS directories
   • *blib*, the Perl build directory …

Git commits per contributor one-liner

By Jon Jensen
March 18, 2009

Just for fun, in the Spree Git repository:

git log | grep ^Author: | sed 's/ <.*//; s/^Author: //' | sort | uniq -c | sort -nr
    813 Sean Schofield
     97 Brian Quinn
     81 Steph (Powell) Skardal
     42 Jorge Calás Lozano
     37 paulcc
     27 Edmundo Valle Neto
     16 Dale Hofkens
     13 Gregg Pollack
     12 Sonny Cook
     11 Bobby Santiago
      8 Paul Saieg
      7 Robert Kuhr
      6 pierre
      6 mjwall
      6 Eric Budd
      5 Fabio Akita
      5 Ben Marini
      4 Tor Hovland
      4 Jason Seifer
      2 Wynn Netherland
      2 Will Emerson
      2 spariev
      2 ron
      2 Ricardo Shiota Yasuda
      1 Yves Dufour
      1 yitzhakbg
      1 unknown
      1 Tomasz Mazur
      1 tom
      1 Peter Berkenbosch
      1 Nate Murray
      1 mwestover
      1 Manuel Stuefer
      1 Joshua Nussbaum
      1 Jon Jensen
      1 Chris Gaskett
      1 Caius Durling
      1 Bernd Ahlers