Welcome to End Point’s blog

I spent last week in Santa Clara attending the MySQL User Conference. Friends had clued me in that the conference was going to be a riot - with developers from the many forks of MySQL in attendance, all vying for spotlight, and to differentiate themselves from the MySQL core code.

The Oracle announcement of acquiring Sun cast an uncertain and uncomfortable light over the talks about forks, community and the future of MySQL. Many people wondered aloud what development on the core of MySQL’s code would be like now, and what would become of the remaining MySQL engineers.

Would the engineers defect to Monty’s new company? Will Oracle end support of MySQL development? How would MySQL end users feel about the changes? Would there be a surge in interest in Postgres, my favorite open source database?

Of course, it’s a bit early to tell. So, I’ve really got two posts about the trip, and this first one is about PostgreSQL, aka Postgres.

There’s a huge opportunity right now for Postgres to tell its story. Not because of a specific failure on the part of MySQL, but because the Oracle acquisition has raised the consciousness of all of mainstream tech. Developers and IT managers are taking a serious look at Postgres for new development projects, and evaluating their database technology choices with an eye toward whatever Oracle decides to do.

In this window of uncertainty is an opportunity for Postgres advocates to explain what it is that draws us to the project.

As a developer and a sysadmin, my enthusiasm for Postgres comes directly from the people that work on the code. The love of their craft - developing beautiful, purpose-built code - is reflected in the product, the mailing lists and the individuals who make up our community.

When someone asks me why I choose Postgres, I have to first answer that it is because of the people I know who are involved in the project. I trust them, and believe that they make the best technology decisions when it comes to the core of the code.

I believe that there’s room for improvement in extending Postgres’ reach, and speaking to people who don’t already believe the same things that we believe: that conforming to the SQL standard is fundamentally a useful and important goal, that vertical scaling is an important design objective, and that consistency is just as important to excellent user experience as are verbose command names and syntactic sugar extensions.

All of those issues are debated when discussing (typically by people outside of the Postgres community) how the Postgres development is prioritized and how this community works. It is inarguable that in the web space, Postgres lost the race. But the initial goal of the project, I’d argue, wasn’t necessarily to be the most popular end-user database. Now, that may have changed... :)

Meantime, the Postgres community continues to mature. There are clear constraints we need to overcome on the people side. Two that I think about frequently are the need for more code reviewers for patch review and testing, and smoothing over our prickly mailing-list reputation by getting more volunteers responding to requests for information the lists.

During a particularly raucous panel session at the Percona Performance Conference, a friend in the Postgres community commented that he was so happy that our community didn’t have the issues that the MySQL community has. And I said to him that it’s just a matter of time before we experience those issues if Postgres grows as MySQL has.

We will have issues with forks, conflicts and deep-cutting (founded, or unfounded) criticism. So, my advice to all the people I know in the Postgres community is to pay attention to what is happening with MySQL right now, because we can only benefit from being prepared.

PGCon is the annual conference for PostgreSQL users and developers, and PGCon 2009 in Ottawa, Canada, is now only about 3 weeks away. The schedule of presentations looks excellent, and I'm excited to have three of my co-workers presenting talks there. Here's a quick rundown of those talks.

Power psql by Greg Sabino Mullane: The psql command-line interface to PostgreSQL is extremely powerful and versatile. While it's easy to get started with, investing a little time in learning its many features really pays off in improved productivity. Greg will explore some corners and features you might not have known about, and also delve a little into its history and, more importantly, its future.

VACUUM Strategy by Selena Deckelmann: VACUUM is an important topic for both new and seasoned users of Postgres. Selena's talk will focus on changes in Postgres from version 8.0 on, tuning configuration parameters related to VACUUM for best performance, autovacuum, the updated Free Space Map in 8.4, and the brand new Visibility Map.

Writing a Procedural Language by Josh Tolley: Stored procedures and user-defined functions offer a lot of power, and PostgreSQL already allows developing such code in many different programming languages. Josh will show how to write a new PostgreSQL procedural language, which offers many practical lessons in PostgreSQL internals. Using the thoroughly impractical language PL/LOLCODE makes it fun, to boot.

Two separateposts taken from two separate mailing lists I'm on have gotten me thinking about PostgreSQL data types and operator classes today. The first spoke of a table where the poster had noticed that there was no entry in the pg_stats table for a particular column using the point data type. The second talks about Bucardo failing when trying to select DISTINCT values from a polygon type column. I'll only talk about the first, here, but both of these behaviors stem from the fact that the data types in question lack a few things more common types always have.

The first stems from the point type's lack of a default b-tree operator class and lack of an explicitly-declared analyze function. What are those, you ask? In the pg_type table, the column typanalyze contains the OID of a function that will analyze the data type in question, so when you call ANALYZE on a table containing that data type, that function will be run. In a default installation of PostgreSQL, all rows contain 0 in this column, meaning use the default analyze function.

This default analyze function tries, among other things, to build a histogram of the data in the column. Histograms depend on the values in a table having a defined one-dimensional ordering (e.g. X <> Y, like numbers on a number line or words in alphabetical order). Now it gets a bit more complex. Index access methods define "strategies", which are numbers that correspond to the function of a particular index. Per this page, the b-tree access method defines the following:

Operation

Strategy Number

less than

1

less than or equal

2

equal

3

greater than or equal

4

greater than

5

To build a histogram we might use strategies 1, 3, and 5, to determine whether two given values are equal, or which is greater. So having found that there's an appropriate operator class for this data type, the analyze function would finally look in the pg_amop table to get the operators it needs to build its histogram. pg_amop matches these strategy numbers with actual function OIDs to find the functions it should actually call.

This whole line of thought stemmed from the point data type not having these functions. B-tree indexes try to sort their data in some order, as determined by the functions talked about above. But point types don't have an obvious one-dimensional ordering, so the b-tree index isn't really appropriate for them. So there's no b-tree operator class, and thus no statistics from columns of point type.

All that said, if you can think of a nice set of statistics ANALYZE might get from point data that would be useful for later query planning, you might implement a custom analyze function to fill the pg_stats table, and selectivity estimation functions to consume the data you generate, to make queries on point data that much better...

UPDATE: Those interested in the guts of a type-specific analyze function might take a look at ts_typanalyze, which is in 8.4. Note that on its own, the typanalyze function doesn't do any good -- it needs selectivity functions, defined in this file, which also were committed in 8.4. Both patches courtesy of Jan Urbanski, and various reviewers.

Once you've understood what OpenAFS is, you might ask "Why use OpenAFS?" There are several very good reasons to consider OpenAFS.

First, if you need a cross-platform network filesystem, OpenAFS is a
solid choice. While CIFS is the natural choice on Windows, and NFS is
a natural choice on Unix, OpenAFS gives a hetergeneous choice (and it
works on Mac OS X, too).

Setting aside which filesystem is natural for a given platform, though,
OpenAFS has a strong advantage with respect to remote access. While it's
common to access systems remotely via a Virtual Private Network (VPN),
Secure Shell (SSH), or Remote Desktop, OpenAFS allows the actual files
themselves to be shared across a WAN, a dialup link, or a mobile device
(and since OpenAFS is cross platform, the issue of which remote sets
of remote access software to support is lessened). Having files appear
to be local to the device reduces the need for remote access systems and
simplifies access. The big win, though, is that OpenAFS' file caching
helps performance and lessens bandwidth requirements.

Another reason to use OpenAFS is if you need your network filesystem to
be secure. While both CIFS and NFS have secure versions, in practice,
they are often configured to be backwards compatible to a least common
denominator and are relatively insecure. Typically, either they trust the
client to be secure (NFS), or the backwards compatibility significantly
lessens security (CIFS). While for an isolated or trusted network,
their security mechanisms may be acceptable, OpenAFS can relied on over an
untrusted network. Common practice for allowing CIFS and/or NFS accesses
over an untrusted network is to leverage a VPN, which introduces yet
another piece of software to manage. On the other hand, OpenAFS 'just
works' over an untrusted network and it makes no assumptions about the
trustworthiness of the client.

Business growth often drives opening new offices. Sharing data across
those offices can be a challenge, and OpenAFS, because it was designed
to be a wide area filesystem, not just a local area filesystem, shines.
By creating a global namespace and linking the offices together, all
data in all offices can be accessed seamlessly. This can be as simple
as two offices, one central with OpenAFS servers and the other remote,
with only OpenAFS clients, or it can scale up a step to where each remote
office holds file and meta-data servers so that commonly shared local
files can be accessed more quickly. It can even scale up globally with a
more complex environment. Morgan Stanley's environment as of Spring 2008
had around 500 servers globally, providing OpenAFS file services to tens
of thousands of Unix and Windows clients in approximately 100 offices.
No other network filesystem offers such amazing scalability.

Business challenges often mean closing offices, and OpenAFS'
flexibility works well here, too. Since data can be moved while
on-line, servers in an office can be migrated to a different location,
and OpenAFS clients will automatically get data from the new location,
making removal of the infrastructure in an office straightforward.

OpenAFS's ability to scale down to a single office and up to a complex
global environment sets it apart from all other network filesystems.
If you need a network filesystem, why not choose OpenAFS? It will let
you grow without having to go through a filesystem switch when you find
that your current choice limits your ability to accomplish your goals.

I recently read an article that discusses Magento SEO problems and solutions. This got me to think about common search engine optimization issues that I've seen in e-commerce. Below are some highlighted e-commerce search engine optimization issues. The Spree Demo, Interchange Demo, and Magento Demo are used as references.

External links positively influence search engine performance more if they are pointing to one index page rather than being divided between two or three home pages. Since the homepage most likely receives the most external links, this issue can be more problematic than other generated duplicate content. I've also seen this happen in several content management systems.

This article provides directions on mod_rewrite use to apply a 301 redirect from the www.domain.com/index.php homepage to www.domain.com. This solution or other redirect solutions can be applied to Spree, Interchange, and other ecommerce platforms.

Irrelevant Product URLs

A search engine optimization best practice is to provide relevant and indicative text in the product urls. In the Interchange demo, the default catalog uses the product sku in the product url (http://demo.icdevgroup.org/i/demo1/os28073.html). In Magento and Spree, product permalinks with relevant text are used in the product url. In wordpress, the author has the ability to set permalinks for articles. I am unsure if Magento gives you the ability to customize product urls. Spree does not currently give you the ability to manage custom product permalinks. However, for all of these ecommerce platforms, these fixes may all be in the works since it is important for ecommerce platforms to implement search engine optimization best practices.

Another example of this can be found in the Interchange demo. The left navigation taxonomy tree provides links to any product url with "?open=X,Y,Z" appended to the url. The "open" query string indicates how the DHTML tree should be displayed. For example, the "Digger Hand Trencher" has a base url of http://demo.icdevgroup.org/i/demo1/os28076.html. Depending on which tree nodes are exploded, the product can be reached at http://demo.icdevgroup.org/i/demo1/os28076.html?open=0,11,13,19, http://demo.icdevgroup.org/i/demo1/os28076.html?open=0,11,13, etc. This standard demo functionality yields a lot of duplicate content.

In Magento, products are the in the form of www.domain.com/product-name, although the article I mentioned above mentions that www.domain.com/category/product.html product urls were generated. Perhaps this was a recent fix, or perhaps the demo is configured to avoid generating this type of duplicate content.

Duplicate product page content is often used to indicate which breadcrumb should display or to track user click-through behavior (for example, did a user click on a "featured product"? a "best seller"? a specific "product advertisement"?). In Interchange, session ids are appended to urls which is another source of duplicate content. Instead of using the url to track user navigation or behavior, several other solutions such as using cookies, using a '#' (hash), or using session data can be used to avoid duplicate content generation.

Keeping a high content to text ratio, consolidation, minification, and gzipping css and javascript, and minimizing the use of javascript based suckerfish can all improve search engine performance.

The Interchange default catalog has a simple template with minimal css and javascript includes, so the developer is responsible for sticking to best performance practices. The Magento demo appears to have decent content to text ratio, but still requires 5 css files that should be consolidated and minified if they are included on every page. Finally, Spree has undergone some changes in the last month and is moving in the direction of including one consolidated javascript file plus any javascript required for extensions on every page, and the upcoming release of Spree 0.8.0 will have considerable frontend view improvements.

Lacking basic CMS management

Basic CMS management such as the ability to manage and update page titles and page meta data is something that has been overlooked by ecommerce platforms in the past, but appears to have been given more attention recently. An ecommerce solution should also have functionality to create and manage static pages.

The Interchange demo does not have meta description and keyword functionality, however, page titles are equal to product names which is an acceptable default. It's also very simple to add a static content page (as a developer) and would require just a bit more effort to have this content managed by a database in Interchange. The Spree core is missing some basic CMS management such as page title and meta data management, but this functionality is currently in development. One Spree contributer developed a Spree extension that provides management of simple static pages using a WYSIWYG editor. At the moment, Magento appears to have the most traditional content management system functionality out of the box.

Another missed opportunity in ecommerce platforms is finding a solution to elegantly blend content and product listings to target specific keywords. A "landing page" can have a page title, meta data, and content targeted towards a specific terms. http://www.backcountry.com/store/gear/arcteryx-vests.html and http://www.backcountry.com/store/gear/cargo-pant.html are examples of targeted terms with corresponding products. Going one step farther, search pages themselves can have managed content to attract keywords, such as a page title, and meta data for specific high traffic keywords with the related products. For example, http://www.domain.com/s/ruby_shirt could be a search page for "Ruby Shirt" which contains meaningful content and relevant products.

Mishandled Product Pagination

Finding a search engine optimization solution for pagination can be a difficult problem in ecommerce. When there are less than 100 products for a site, this shouldn't be an issue because a simple taxonomy can appropriately group the products with low crawl depth. A website with 10,000 products must balance between keeping a low taxonomy depth to minimize crawl depth and ensure that all products are listed and indexable.

A few problems can arise from the pagination solution. First, by web 2.0 standards, the content should be generated via ajax. An SEO friendly ajax solution must be implemented - where the onclick event refreshes the content, but the links are still crawlable via search engine bots. Second, page 1 with no product offset will have 1 level less of crawl depth, therefore it will receive the most link juice from it's parent page (subcategory). As a result, there must be thoughtful analysis of which products to present on that page: should high traffic pages get the traffic? should popular items be listed on the first page? should low traffic products be listed to try to bump the traffic on those pages? should products with the most "user interaction" (reviews, qna, ratings) be shown on that page? Another problem that comes up is that the page meta data and title will most likely be very similar since the content is a list of similar products. These two pages can essentially be competing for traffic and may be counted as duplicate content if the page titles and meta data are equal.

It is difficult to determine which of the above problems is the most problematic. From personal experience, I have been involved in tackling all duplicate content issues, and then moving on to "optimization" opportunities such as enhancing the content management system. At the very least, developers and users of any ecommerce platform should be aware of common search engine optimization issues.

Interchange provides tags that allow error trapping and handling within ITL--[try] and [catch]--that can be thought of as analogous to perl's eval {} followed by if ($@) {}. However, as I discovered the hard way, the analogy is not perfect.

I set up a block of ITL within [try] that had two major actions, with the 2nd depending on the success of the first. In particular, these two actions were a credit card authorization, followed by a capture of that auth as long as (a) the authorization succeeded, and (b) the merchant's internal rules for analyzing order content compared to AVS results "passed". (b) was necessary as a fraud-protection measure, tightening up the impact of AVS results based on the historic tendency of certain products to be targeted by crooks. In the event that the auth succeeded, but the tests from (b) failed, it is very important that the capture never be attempted because, to the gateway, the auth is entirely valid and the catpure attempt would succeed.

The code that assesses whether AVS passes is done in its own [calc]. From within the code, if the assessment does not pass, the code issues a die(), which in fact does trigger [try] to log the error that becomes accessible in [catch] via the $ERROR$ token, and thus does trigger [catch] to execute its body contents. In that way, the [try] did trap the error, and the error was handled in [catch], but of course that's not the end of the story or this post wouldn't exist.

After the code had been in production for some time, David Christensen brought to my attention that he noticed in development a test order attempt, where the order attempt failed, but both the auth and the capture succeeded. I was highly dubious of this claim and went over in great detail just what he had done. We narrowed down the condition that produced the problem to (b) above: a successful auth, but abort the order attempt anyway because a high-value product in the cart was coupled with a questionable AVS. When I went to the logs, I could see the result spelled out, but the result made no sense to my understanding:

The 0 indicated the [calc] had failed (died), yet the capture later in the [try] was still executing. The only conclusion was that, unlike eval {}, when [try] trapped an error, it just kept right on processing the continuing ITL. [try] always went forward and processed all its ITL to the end, and whatever happened to be the last die() called within that batch of ITL would be the thing that [catch] caught and displayed.

To resolve the problem, I introduced [goto] into the block, which stops the instance of interpolate_html() running at the point of encounter and returns. Continuing to use the die() call to populate the error code from [try], immediately after the [calc] test block I called [goto] conditionally on whether the [calc] block, in fact, died. The [goto] call then terminated the instance of interpolate_html() that [try] had invoked on its body, which had the effect of stopping ITL execution at the point of the die().

This approach to emulating eval {}/if ($@) {} has the significant flaw of developers needing to know ahead of time exactly where in the [try] block such failures are expected. If such is unknowable, it leaves developers in the unenviable position of having to follow each tag call with a conditional [goto] that has to know when the previous tag "failed" (i.e., triggered a die() somewhere).

In the last few weeks, a few of us have been working on a project for Puppet involving several lines of concurrent development. We've relied extensively on the distributed nature of Git and the low cost of branching to facilitate this work. Throughout the process, I occasionally find myself pondering a few things:

How do teams ever coordinate work effectively when their version control system lacks decent branching support?

The ease with which commits can be sliced and diced and tossed about (merge, rebase, cherry-pick, and so on) is truly delightful

It is not unreasonable to describe Git as "liberating" in this process: here is a tool with which the the logical layer (your commit histories) largely reflect reality, with which the engineer is unencumbered in his/her ability accomplish the task at hand, and from which the results' cleanliness or messiness is the product of the engineering team's cleanliness or messiness rather than a by-product of the tool's deficiencies

The current process, in accordance with practices in use within the Puppet project itself, basically involves:

One "canonical" branch in a particular repository, into which all work is merged by a single individual

Engineers do work in their own branches/repositories, which they "publish" (in this case, on Github) through occasional pushes

Different lines of development take place on different branches, keeping the logical threads of development separate until any given piece progresses sufficiently to warrant merging back into the canonical branch

Seemingly-speculative development efforts are worth more in this approach, because the most seemingly-speculative work can go out on an independent branch, starting from the common history, to be used later (or not) according to need. The ease of sharing the work, of keeping it cleanly isolated but generally low-cost to integrate later, all reduce the "speculative" part of speculation.

Much of the public discussion of distributed development in practice, using Git, revolves around Linux kernel development. That's of course a massive project with many contributors and a great many lines of development. It's easy to look at distributed version control and the related development practices and say "this is not necessary; my project isn't that complex and doesn't need all this fanciness." Such a conclusion, while understandable, ignores the most important factor in all software development work: human beings do the work.

Human beings can mentally envision complex structures, relationships, processes with instantaneous ease. While our thought processes on a given thread may move along serially, our general approach to problems often involves a graph or web rather than a single line. Furthermore, concurrent processing is second-nature to all of us, depending on the situation:

The car driver guides the steering wheel such that over the course of traveling forty feet, the car smoothly achieves a ninety-degree change of direction, while coordinating the changing of gears and acceleration through manipulation of clutch, accelerator, and gear shift, all while chatting with the child in the back seat

The singer performing a Bach aria manipulates diaphragm, jaw, tongue, lips, etc., to achieve the ideal resonance for the current vowel across a intricate repeated sequence of pitch relationships, while focusing on the sound of the organ for tuning and ensemble, and while envisioning the expansive overarching shape of the phrase to ensure the large-scale dynamic fits the musical expression needed

The child in the outfield hums quietly, thinking about the cartoons he watched yesterday, while intently watching to see if the tee-ball will ever be coming his way

In my experience, when speaking about development tasks with my peers, the most common situation is for the conversation to be muddied by an excess of ideas and possibilities. Too many topics and ways forward bubble about in our collective head, and development forces us to shed these until we arrive at the stripped-bare essentials. Furthermore, it is similarly common that certain questions cannot be answered in the abstract, and require the rolling-up of sleeves to arrive at a solution. Along the way to that solution, how often does one come upon implementation choices that were not previously considered, the implications of which requiring further assessment?

We often think, individually or collectively, in webs of relationships. A tool that requires us to develop serially defies our basic humanity. This is the true liberation Git brings: concurrent development -- by a team of many, a few, or one -- can be sanely achieved. Put the new thing in a branch and move on. Merging it later will very possibly be easy, but even if it's not, it is always possible.

To quote a special fella, "freedom's untidy". Development tools that facilitate multiple lines of concurrent development mean that one ends up in the situation of dealing with, well, multiple lines of development. The technical problem (no branching!) becomes a meatspace problem (aagh! branches!). There's no magical elixir for that problem, as it requires social solutions, such as email or a wiki. The meatspace problems exist in any case, Git simply forces you to recognize them and plan for them.

We've done a fair amount of investigation in recent years of the free, open messaging field to try to identify the "best" free/open messaging solution. "Best" in quotes because, in software, the belief that one has found the "best" solution for given problem X often says a lot more about the person holding the belief than it says about the solution itself or problem X.

In a survey of messaging options done for a client last year, we determined (at that time) that ActiveMQ was likely a good solution for the client's needs. For simple deployments/usage, setup is quite straightforward. There's good cross-language client support (particularly thanks to native STOMP support). There's positive feedback out in the community about ActiveMQ. It's an active project that's been making decent progress over the years. Etc.

But then the little horror stories pop up. You hear/read that ActiveMQ falls down in various situations. Without getting any visibility into the specifics, it's impossible to know what the problem is, or effectively use the information to intelligently inform any decisions regarding messaging solution selection. You're left with creeping fear and its wonderful offspring: indecision. Yay.

This is what I find so interesting about the blog article (and subsequent comments) mentioned above: the author reads about Twitter moving away from Ruby and towards Scala, and embracing another custom-made messaging solution, and the author has the audacity/courage/hubris/expertise to offer an at-times withering critique of the reasons presented by Twitter, the design decisions evident in Starling, and so on. All done without visibility into the organization itself, but with expert knowledge of many of the tools involved in the discussion. It's rather awe-inspiring to see somebody publicly rip into an organization in this way in such circumstances ("awe" is what it is, neither good nor bad). I personally cannot imagine going after somebody else's technical decisions in a public forum like this, given only the results of their work (the source code to Starling, for instance) and a few out-of-context quotes from an interview. I suppose it's reasonably sane to criticize Microsoft publicly for their absurd decisions (Windows 95/98/XP/Vista? Are you serious?), but that's about as satisfying and informative at this point as mocking Britney Spears.

Anyway, back to the original point: the author offers this intelligent, partially-informed critique, and the Twitter guys jump in and offer feedback and more information in the comments. Beyond that, a guy from the the RabbitMQ community joins in the fun to defend the Twitter folks and to offer some additional background. The end result is a satisfying 30 minutes' read that was far more informative and enlightening to me on the subject of messaging in particular than any of the research I had done previously.

Nothing escapes unscathed, except perhaps Scala and Kestrel. ActiveMQ, RabbitMQ, Starling, etc. all have problems within the context of Twitter. So, going back to the creeping fear: uh oh, all of these fell down, does that mean they're all lame?

No. It means they're software. Perfect isn't an option. You only need stuff that's good enough, and the definition of "good enough" varies by business need.

When you search around for information on message brokers and client support in Ruby, you get a lot of seemingly-helpful-yet-ultimately-near-useless blog articles. To generically paraphrase:

Messaging is really important these days, and I searched around for the right messaging solution for my app. I took these steps to get ActiveMQ working, and it Just Works. Now the 6 people worldwide who use my really awesome Rails app will get really, really awesome results.

"Technology Solution X: It 'Just Works' for Apps with No Users."

That's a little harsh, since the ease of deployment/integration of any given software component is a relevant consideration for any project with a budget or timeline. People solving problems for modest applications still contribute meaningfully to the community overall by sharing their experiences with setup, configuration, etc. But this kind of information is not very helpful when making decisions for systems that really matter to lots of people. Which is why the cited blog article and its comment thread is so terrific; no breathless endorsements or cheerleaderisms, just technically-informed discussion that tells you a lot more about the products discussed than does a "look how easy it was to use this" article.

Ultimately, it boils down to a truth that ought to be self-evident at all times. The awesomeness of a given solution depends primarily upon the need to which it is applied. If I need to add 3 to 7, my calculator Just Works. So does my network of brain cells. Which solution is better? It depends on what you need to do with the answer.

So, is ActiveMQ a great solution? Is, perhaps, RabbitMQ a better choice? Should I perhaps put on my hot pantz and get cracking with Kestrel? At present, my response is: better for what? "Messaging" alone isn't an answer. The data available in the public sphere does not give a compelling answer, from what I can see, and my own (limited) experience does not give a clear answer either. Perhaps the more relevant question is "which can be made to work in my environment more easily"? And the most important consideration of all remains: how good are your people?

A query I worked with the other day gave me a nice example of a useful PostgreSQL query planner trick. This query originally selected a few fields from a set of inner-joined tables, sorting by one particular field in descending order and limiting the results, like this:

The resulting query plan involved a bunch of index scans on the various tables, joined with nested loops, all based on a backward index scan of an index on the table_a.field2 column, looking for rows that matched the condition in the WHERE clause. PostgreSQL likes to choose backward index scans when there's a LIMIT clause and it needs result sorted in reverse order, because although backward index scans can be fairly slow, they're easy to interrupt when it finds enough rows to satisfy the LIMIT. In this case, it figured it could search backward through the index on table_a.field2 and quickly find 20 rows where table_a.field1 = 'value' is true. The problem was that it didn't find enough rows as quickly as it thought it would.

One way of dealing with this is to improve your statistics, which is what PostgreSQL uses to estimate how long the backward index scan will take in the first place. But sometimes that method still doesn't pan out, and it takes a lot of experimentation to be sure it works. That level of experimenting didn't seem appropriate in this case, so I used another trick. I guessed that maybe if I could get PostgreSQL to first pull out all the rows matching the WHERE clause, it could join them to the other tables involved and then do a separate sorting step, and come out faster than the plan that it was using currently. Step one is to separate out the part that filters table_a:

The problem is that this doesn't change the query plan at all. PostgreSQL tries to "flatten" nested subqueries -- that is, it fiddles with join orders and query ordering to avoid subquery operations. In order to convince it not to flatten the new subquery, I added "OFFSET 0" to the subquery. This new query gives me the plan I want:

This selects all rows from table_a where field1 = 'value', and uses them as a distinct relation for the rest of the query. This led to a distinct sorting step, and made the resulting query much faster than it had been previously.

CAVEAT: The query planner is pretty much always smarter than whoever is sending it queries. This trick just happened to work, but can be a really bad idea in some cases. It tells PostgreSQL to pull all matching rows out of the table and keep them all in memory (or worse, temporary disk files), and renders useless any indexes on the original table. If there were lots of rows matching the condition, this would be Very Bad. If one day my table changes and suddenly has lots of rows matching that condition, it will be Very Bad. It's because of potential problems like this that PostgreSQL doesn't support planner hints -- such things are a potent foot gun. Use with great care.

One of our clients recently discovered a bug in a little-used but vital portion of the admin functionality of their site. (Stay with me here...) After traditional debugging techniques failed on the code in question, it was time to look to the VCS for identifying the regression.

We fortunately had the code for their site in version control, which is obviously a big win. Unfortunately (for me, at least), the repository was stored in Subversion, which means that my bag o' tricks was significantly diminished compared to my favorite VCS, git. After attempting to use 'svn log/svn diff -c' to help identify the culprit based on what I *thought* the issue might be, I realized that svn was just not up to the task.

Enter git-svn. Using git svn clone file://path/to/repository/trunk, I was able to acquire a git-ized version of the application's repository. For this client, we use DevCamps exclusively, so the entire application stack is stored in the local directory and run locally, including apache instance and postgres cluster. These pieces are necessarily unversioned, and are ignored in the repository setup. I was able to stop all camp services in the old camp directory (svn-based), rsync over all unversioned files to the new git repository (excluding the .svn metadata), replace the svn-based camp with the new git-svn based one, and fire up the camp services again. Started up immediately and worked like a charm. I now had git installed and working in what had previously only been svn-capable before.

Now that I had a git installation, I was able to pull one of my favorite tools from my toolbox when fighting regressions: git-bisect. In my previous svn contortions, I had located a previous revision several hundred commits back which did not exhibit the regression, so I was able to start the bisect with the following command: git bisect start badgood. In this case, bad was master and good was the revision I had found previously. Using git svn find-rev rnumber, I found the SHA1 commit for the good ref as git saw it.

From this point, I was able to quickly identify the commit which introduced the regression. In reviewing the diff, there was nothing that I would have expected to cause the issue at hand; the code did not touch any of the affected area of the admin. But git had never lied to me before. I compared the code currently in master with that introduced in the implicated commit and saw that most of it was still in place. I began selectively commenting out pieces of the code the commit introduced, and was able to enable/disable the bug with increasingly fine granularity. Finally, I was able to identify the single line which when removed caused the issue to evaporate. This was a line in an innocuous template which had a simple variable interpolation (inside an HTML comment, nonetheless); however, this line (which was in a file which was included with every document, added in the implicated commit) revealed a bug in the parser of the app-server which was causing the symptoms in the unrelated admin area.

It's certain that I would never have been able to find the source of this issue without git-bisect, as manual bisection with svn would have been too tedious to even consider. I am able to happily interact with the rest of the development team with git being my secret weapon; git svn dcommit enables me to push my commits upstream, and git svn fetch/git svn rebase enable me to pull in the upstream changes. I'll never need to tell my subversive secret (except, you know, on the company blog), and my own happiness and productivity has increased. Profit!!11 all around.

I just read an interesting article on the various downsides to URL shorteners. Let's not break the Internet that way. I found the article on Hacker News, which I'd forgotten about -- this is the Hacker News at Y Combinator, not the good ol' but long-dead Hacker News Network formerly at hackernews.com.

In other news, our very own Selena Deckelmann appears in moving pictures at FLOSS Weekly 64! I haven't even had time to watch it all yet.

BucardoApril 5, Sunday, 10amBucardo 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.plApril 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 psqlApril 4, Saturday 10:30am
All about everyone's favorite Postgres utility, psql, the best command-line database interface, period. We'll cover basic and advanced usage.

I've seen a few of Greg's talks -- The Magic of MVCC, Cloning an elephant and a few others. He's a great speaker and cool guy. And he's my boss. But I'm not just saying that because he's my boss! Really!

He doesn't like to brag about himself, so I'm gonna help him out. He maintains DBD::Pg, check_postgres.pl, Bucardo and has had MANY patches committed to PostgreSQL. He's also a volunteer for the PostgreSQL sysadmins team, and specifically helps maintain the git repo box. He's a contributor to the MediaWiki project. He's on the board of the United States PostgreSQL Association. He's basically awesome.

If you're gonna be there, you should check out his talks. And if you can't make it, here's hoping Josh Drake records the talks and shares them with us all! :)