Linux lseek scalability

I don't normally follow Linux kernel development, but I was pleased to hear (via Andres Freund) that the Linux kernel developers have committed a series of patches by Andi Kleen to reduce locking around the lseek() system call.  As I blogged about back in August, PostgreSQL calls lseek quite frequently (to determine the file length, not to actually move the file pointer), and due to the performance enhancements in 9.2devel, it's now much easier to hit the contention problems that can be caused by frequently acquiring and releasing the inode mutex.  But it looks like this should be fixed in Linux 3.2, which is now at rc1, and therefore on track to be released well before PostgreSQL 9.2.

Meanwhile, we're gearing up for CommitFest #3.  Interesting stuff in this CommitFest includes Álvaro Herrera's work on reducing foreign key lock strength and a PostgreSQL foreign data wrapper (pgsql_fdw) by Hanada Shigeru.  Reviewers are needed, for those and many other patches!

Unsticking VACUUM

Every PostgreSQL release adds new features, but sometimes the key to a release has less to do with what you add than with what you take away.  PostgreSQL 8.4, for example, removed the settings max_fsm_pages and max_fsm_relations, and replaced them with a per-relation free space map that no longer requires manual sizing.  Those parameters are now gone, and more importantly, something that you previously needed to understand and manage was replaced with something that just works.   People who are still running PostgreSQL 8.3, or older versions, want to understand exactly how the free space map works; people who are running PostgreSQL 8.4, or newer, don't care.  It's enough to know that it does work.

Now, about eight months ago, I wrote a blog entry on troubleshooting stuck vacuums.  I would not say that this is an everyday problem, but in ten years of working with PostgreSQL, I've seen it a few times, and it's very unpleasant.  It's easy to miss the fact that you have a problem at all, because in most cases, nothing immediately breaks.  Instead, system performance just slowly degrades, gradually enough that you may not realize what the problem is until things have gotten pretty bad and you need to CLUSTER or VACUUM FULL to recover.

Hint Bits

Heikki Linnakangas was doing some benchmarking last week and discovered something surprising: in some circumstances, unlogged tables were actually slower than permanent tables.  Upon examination, he discovered that the problem was caused by CLOG contention, due to hint bits not being set soon enough.  This leads to a few questions:

1. What is CLOG?
2. What are hint bits?
3. How does setting hint bits prevent CLOG contention?
4. Why weren't hint bits being set sooner?

Let's take those in order.