Since I wrote my previous blog entry on index-only scans, quite a bit of additional work has been done. Tom Lane cleaned up the code and improved the costing model, but possibly the most interesting thing he did was to allow index-only scans to be used for queries that don’t involve an indexable condition at all. The classic example is SELECT COUNT(*) FROM table. In previous versions of PostgreSQL, there’s just one way to implement this: sequential scan the table and count ‘em up. In PostgreSQL 9.2, that method will still, of course, be available, but now there will be another choice: pick any index you like and do a full index scan, checking whether each tuple is all-visible either using the visibility map or via a heap fetch. So, how well does it work
Read more

If you're new here, you may want to subscribe to our RSS feed or follow us on Twitter for product announcements, event information, and industry news.

As I mentioned in a previous blog post, I spend some of my time working in and with EnterpriseDB’s support department. And what that means is that every customer I talk to has a problem, typically a fairly serious problem, and they want me to help them fix it. Of course, to fix it, you first have to be able to identify the problem, and sometimes that’s not so simple. Database crashes can be among the more difficult cases to debug.
Read more

Last week, someone pinged me on instant messenger to ask about the following message, which their PostgreSQL instance had just produced:

This message is a complaining about a deadlock. But unless you’ve seen and debugged these a few times before, it might not be entirely obvious to you what’s actually going on here. What, exactly, did the offending processes do that caused the problem?

Read more

Tom Lane committed a patch for index-only scans by myself and Ibrar Ahmed, which also incorporated some previous work by Heikki Linnakangas, after hacking on it some more himself. Woohoo!

There is, of course, more work to be done here – performance fine-tuning, cost estimation, extensions to the core functionality – but the core of the feature is now in.  If you get a chance, please test it out and let us know how it works for you.

For those that may not have been following along at home, what we’re essentially doing here is allowing any index to act as a “covering index”.  If all of the columns the query needs are available from the index tuple, we’ll skip fetching the corresponding heap (table) page if every tuple on that page is visible to all running transactions.

Although I know we’re not even really done with this feature yet, I can’t help wondering what’s next.  Index-only scans have so often be cited as “the big performance feature that PostgreSQL is missing” that it’s become something of a clich.  Now that we have them, what will take their place as the next big thing?

I’ve seen a lot of articles lately about the great new features (and removed limitations) in PostgreSQL 9.1.  Unless you’re a regular reader of pgsql-hackers, you could almost forget about the fact that PostgreSQL 9.2 development is in full swing.  In fact, there’s a CommitFest going on right now and we could use a few more reviewers.

Many of the features that were submitted to this CommitFest are small improvements – minor fine-tuning of existing features, like generating better column names for subquery expressions, or fixing things so that LIKE can more reliably make use of indexes when non-English characters are involved.  But some of the big features that will hopefully become part of PostgreSQL 9.2 are also beginning to materialize.

Read more

I’m at Surge, this week, where I just listed to Baron Schwartz give a talk about scalability and performance.  As usual, Baron was careful to distinguish between performance (which is how fast it is) and scalability (which is how much faster you can make it by adding more resources).  One of the things Baron talked about was Neil Guenther’s Universal Scalability Law, which attempts to model the behavior of complex systems (such as database systems) as you add threads, or nodes, or users.

Read more

My last few blog posts have been all about scalability and performance, mostly because that’s what I’ve been spending most of my time on these last few months.  But I’m pleased to have another subject: Postgres Open.  I’ve been going to PostgreSQL conferences for a few years now, but Postgres Open – which is a new conference this year – was my first experience being on the program committee.

Read more

In November of 2010, I blogged about a much-requested PostgreSQL feature: index-only scans. We’ve made some progress! In June of this year, I committed a patch (and then, after Heikki found some bugs, another patch) to make the visibility map crash-safe. In previous releases, it was possible for the visibility map to become incorrect after a system crash, which means that it could not be relied on for anything very critical. That should be fixed now. Last week, I posted a patch for the main feature: index-only scans.

Read more

As some of you probably already know from following the traffic on pgsql-hackers, I’ve been continuing to beat away at the scalability issues around PostgreSQL. Interestingly, the last two problems I found turned out, somewhat unexpectedly, not to be internal bottlenecks in PostgreSQL. Instead, they were bottlenecks with other software with which PostgreSQL was interacting during the test runs.

Read more

In my previous blog post on read scaling out to 32 cores, I wrote a patch I recently committed to improve read scalability in PostgreSQL. It turns out, however, that there’s a downside to that patch, which was uncovered in testing by Stefan Kaltenbrunner. (Fortunately, I have a fix.) And, there’s an opportunity for further performance improvement by applying a similar technique to an additional type of lock. For full details, read on.

Read more