PostgreSQL Open Source Database for the Enterprise

PGCon 2012 is now over. Surprisingly, all the talks that excited me were from the second/last day. My morning started with WAL Internals Of PostgreSQL (slides) by Amit Kapila. I rarely see this topic covered, and his comparisons to Oracle’s transaction logging were fresh. Also in the morning, Hooks in PostgreSQL by Guillaume Lelarge: I also rarely see this covered. It highlighted the C-language plug-in capabilities of Postgres.

My afternoon highlight was Big Bad “Upgraded” PostgreSQL by Robert Treat. After the occasional maligning of pg_upgrade (usually in jest), it was great to hear a big pg_upgrade success story. (This OmniTI blog entry has the details. This talk was also presented at PG Corridor Days DC.)

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 part of yesterday’s PGCon Developer Meeting, I hosted a discussion about adding resource parallelism to Postgres, which I blogged about previously. Josh Berkus has kindly summarized the results of that discussion. I am hoping to help track and motivate progress in this area in the coming months; it is a multi-year project to complete.

You might have heard of the template1 database before, or seen it in the output of pg_dumpall:

REVOKE ALL ON DATABASE template1 FROM PUBLIC;
…

Continue Reading »

I have completed the Postgres 9.2 release notes I started seven days ago. Writing the release notes is always a taxing experience. I have to basically dedicate an entire week to the process of digesting 1100 commit messages to produce 3000 lines of SGML text. Once I am done though, it is rewarding to see the finished product. It is like working on a painting all year, and spending a frustrating week framing it and hanging it on the all — once it is hung, you stand and admire it, and forget much of the sweat it took to produce. I am sure many community members feel the same.

Curious about how the 9.2 release item count compares to previous major releases? Here are the results:

Continue Reading »

My children have traveled to many conferences with me, and have heard many webcasts and training calls at home. I guess, after hearing about Postgres so often, they pick up certain phrases of interest, and the big one for them is “shared buffers”. Anytime someone uses those words, they start howling and make up some funny sentence using the words.

There are a variety of beer drinking games that trigger drinking when a word is said — perhaps “shared buffers” is enough on its own to make a drinking game.

Having reported the methods for finding the size of the kernel cache on Linux, I wish to highlight the importance of the postgresql.conf setting
effective_cache_size.

Unlike other memory settings that control how memory is allocated, effective_cache_size tells the optimizer how much cache is present in the kernel. This is important for determining how expensive large index scans will be. The optimizer knows the size of shared_buffers, but not the kernel cache size, which affects the probability of expensive disk access.

Continue Reading »

As in previous years, I have started working on major release notes, this time for Postgres 9.2. I start with this command:

$ src/tools/git_changelog --since '2011-06-11 00:00:00 GMT' --master-only \
> --oldest-first --details-after

Continue Reading »

Measuring Linux free memory and kernel cache size can be somewhat tricky. You might look at /proc/meminfo for the answer (commas added):

# cat /proc/meminfo
MemTotal:       24,736,604 kB
MemFree:         3,805,392 kB
Buffers:           743,016 kB
Cached:         18,188,208 kB
...

Continue Reading »

There are several levels of caching used in a typical server — here they are, in order of increasing distance from the cpu:

All of these are a fixed size and set at hardware installation time, i.e. you can’t move cache from one level to another. For Postgres, there is flexibility in how #2, random-access memory, is allocated, and this provides a never-ending opportunity for administrators to optimize their systems. The three RAM allocation possibilities are:

• shared_buffers
• work_mem
• Kernel cache

Continue Reading »

During the next two months I will be attending events in the following cities: New York City, Ottawa, Charlotte (North Carolina), and Boston — the details are on my website. I will also be doing training in many of these locations. And I spoke in Philadelphia this week — I guess this is what the New Postgres Era looks like.