Our web site lists many Postgres user groups, but perhaps only a dozen are active, having monthly meetings. Why is that? What makes a Postgres user group successful? How can you help create or revitalize a user group?

The answers aren't a great mystery, but it does require hard work. I think a lot of people who start Postgres user groups don't realize the work necessary to make it successful. They probably think, "Oh, I will announce a user group meeting, and people will show up, and it will be fun." Well, the "fun" part might be right, but growing a user group long-term requires lots of work — let me explain.

Continue Reading »

Postgres uses a process-based architecture. Rather than using threads, necessary information is shared among sessions via shared memory (diagram, slide 12). This allows for:

• Simplified backend code
• Efficient scheduling of multiple sessions across multiple CPUs by the operating system
• Improved reliability

Unfortunately, this process architecture makes it difficult for a single session to utilize multiple CPUs and I/O channels. (Multiple sessions use multiple CPUs just fine.) This is becoming more of a limitation because of recent computer architectural improvements:

• More CPU cores rather than faster CPUs
• Increased memory capacity
• Solid-state drives with reduced I/O latency

Continue Reading »

Having attended 138 Postgres-related events in the past twelve years (chart), I have done quite a bit of travel (map). Of course, many business travelers travel a lot more than I do, but the variety, distance, and duration of my trips is somewhat unusual.

I am often asked at conferences about how I manage my heavy travel schedule. There are a few tips that keep me sane and motivated to continue traveling, so I thought I would share them here. I especially hope this is useful for the many volunteers that give of their time to travel as speakers to distant Postgres conferences:

• Control: For me, most travel is voluntary, meaning I can decide to accept or reject a conference invitation. Psychologically, that is an important aspect. If I have booked too much travel, I have only myself to blame.
• Pace: A related issue is pacing. You might remember I recently declined some events to recover from a heavy travel schedule. Pushing travel past the comfort point is an easy way to start hating it and therefore burn out.
• Variety: It is hard to get excited about going to the same distant city repeatedly. Variety is a great way to keep travel exciting.
• Vacation Day: I always try to book an extra day in the travel city for sight-seeing. I often don't remember many details of the conference, but I do remember those sight-seeing days, and that keeps me going and anticipating the excitement of travel. Also, my travels usually allow me to experience the culture as a native, rather than as a tourist, which is also enlightening.
• Sleep: Sleep is an often overlooked aspect of travel. A long flight where you can't sleep is a frustrating flight. A flight where you are asleep by wheels-up and are awoken by landing instructions is a breeze. (A window seat, blow-up travel pillow, ear plugs, eye shades, and travel socks can make all the difference.) Also, sleeping early when on location is important, especially the night before your presentation. You need to be sharp; you spent hours traveling --- don't blow it by not pacing yourself and getting insufficient sleep. Sleep also helps prevent you from getting sick, which is more likely and more inconvenient when traveling.
• Working From Home: Because I work from home when not traveling, I have lots of travel energy. If I had to go to an office every day, I would have a much harder time. Heck, when I have not traveled for a few months, I emit a slight whimper when driving past the airport. (Weird, huh?) I remember waiting for a short flight and hearing a Madrid boarding announcement and thinking, "Hey, I could go over there and be in Madrid by morning". (Yeah, odd, I know. I guess I was made for this.)
• Maximize: Travel is a big time-waster, so make the most of it. I don't worry about community emails and minor things while I am away but focus on making the most of the event by spending time with folks and being available for unscheduled things, like speaking at local companies that use Postgres. (This usually happens after the conference is over when people realize I am around for an extra day.) Combining trips to visit several cities is also always a win --- who wants to fly back the next month?
• Family: My work is not in a vacuum, and my family is certainly affected by my travel. I try to schedule things ahead of time so they have few surprises, and I often take one of my children with me on trips. That's a win in any family's book, and it cuts down on travel resentment. In fact, this is the first year I took all four of my children on at least one trip. (It is easier because they are home-schooled.)

Continue Reading »

You might have heard the term "GUC" before, especially on the Postgres email lists. It is documented as an acronym meaning "Grand Unified Configuration", but the section it references doesn't mention the term "GUC" at all. Pretty obscure, huh?

Our Grand Unified Configuration (GUC) system was created in 2000 as a way to control Postgres at various levels. You are probably most familiar with controlling Postgres via the postgresql.conf file or using the SQL SET command, but it is much more powerful than that.

Continue Reading »

Having given many talks about Postgres administration over the years, I have recently felt convicted that I have not given sufficient coverage to external monitoring tools. I have always discussed how to get information out of Postgres, but not how to efficiently process that information. I have updated my administration presentation, but I would like to go into more detail here to atone for my previous lack of coverage.

• Alerting: For alerting, it is tough to beat check_postgres and tail_n_mail. Check_postgres runs checks on many aspects of Postgres, reporting the results via text output, Nagios, or MTRG. Tail_n_mail scans for important messages in the server logs and emails them.
• Analysis: There are a lot of analysis tools out there. Nagios is great for distributing alerts, while Munin and Zabbix are good for graphing. In fact, some people use Nagios for alerting and Munin for graphing. Cacti and MTRG are also popular. This blog entry from 2009 compares several popular analysis tools.
• Queries: pgFouine ("fouine" is French for weasel) is an often-overlooked tool for analyzing SQL query traffic to find the slowest, most frequent, and queries that took the most cumulative execution time (sample report). It analyzes the queries by reading the server logs.
• Commercial: For commercially-developed tools, there is Circonus (open source version, Reconnoiter), Postgres Enterprise Manager (strong Postgres integration), and Hyperic (both open and closed source versions).

Postgres is good at generating voluminous output suitable for monitoring. External monitoring tools help to make that information useful for administrators.

Recent Postgres major releases have been packed with features (9.1, 9.0), but you might have noticed a lack of major performance improvements in those releases. I was starting to think that we had basically addressed all the performance improvements we could.

Well, I was sure wrong. Postgres 9.2 is going to have a boat-load of performance enhancements. The inclusion in Postgres 9.2 of index-only scans and count(*) optimizations clearly is something that is going to help a lot of work-loads.

Continue Reading »

After attending Postgres events in Chicago, New York City, Baltimore, Amsterdam, and Brazil during the past two months, I returned home this week to recover. Even though I travel frequently, this pace exceeded even my ability to adjust. I will be skipping upcoming Postgres events in Boston and Los Angeles (for my own sanity ) and am staying closer to home during the next few months.

I will be teaching a one-day training class in Philadelphia next week, and hope to do other classes in New York City, Boston, and Washington, DC in the coming months. (That shouldn't be too hard.) I also plan on making some modest code contributions for Postgres 9.2.

Continue Reading »