I just returned from five days in Moscow. HighLoad++ conference organizers invited Simon Riggs, Robert
Treat, and myself to speak at a three-hour Postgres mini-conference as part of their main conference. Robert did a great job explaining
the challenges of managing high-volume systems, and Simon presented the history of Postgres performance enhancements in a way I had never
seen before. Conference attendance was good, so hopefully we helped make the conference a success.

I was surprised that they flew in three Postgres people (and paid for their expenses) for a non-Postgres-specific conference. This might
have been a first, but probably not the last, and indicates Postgres’s growing popularity. For example, O’Reilly’s
MySQL Conference is looking to create a Postgres track for their conference (new submission
deadline: November 3).

Continue Reading »

In just a few days, I’ll be off to PostgreSQL West.  I’ve attended PostgreSQL East and PGCon both of the last two years, but this will be my first trip out to PG West.  As with past conferences, this will be a good opportunity for me to catch up with people I normally speak with only via the Internet.  But, there’s something that’s a little different about this one.  Take a look at the agenda.

By my count, there are about twice as many talks at West as there were at this year’s East.  The agenda looks to be both broad and deep, covering everything from performance to internals to case studies, and there are not only a lot of talks, but a lot of new talks on topics I haven’t heard covered before, or with a different slant than the talks I’ve heard before.  As for me, I’ve been pressed into service to give my query optimizer talk, which I have already given a number of times (but people keep coming!), and I’ll also be trying out a new talk, on using the PostgreSQL system catalogs.

See you there (hopefully)!

I have written two new presentations: the first is MVCC Unmasked
(description). This talk is unusual because it shows Postgres Multiversion
Concurrency Control (MVCC) internals using an unmodified server. I will post the PDF next week once I deliver the talk at
PgWest 2010. I will also be presenting this at PGDay Europe 2010.
I already mentioned my second new talk, Database Hardware
Selection Guidelines, in a previous blog entry.

You have probably seen many posts about the switch of Postgres to use Git. I thought it would be helpful to
explain why the conversion was done.

Postgres has used CVS as its source code management (SCM) system since Internet development started in
1996. CVS was never perfect — one of the major limitations was that changes to multiple files by a single CVS commit were not
treated as a unit. We found ways to work around that limitation. Subversion was designed as a
replacement for CVS, and fixed the multi-file commit problem. However, the community had already worked around that problem and did not
see switching to Subversion, and the disruption it would caused, as wise.

Continue Reading »

Next week (November 2nd through 4th), San Francisco’s Sir Francis Drake Hotel will host the PG West 2010 PostgreSQL community conference. Below, we continue our series of brief Q&As with PG West presenters to provide a taste of what attendees can expect at the show. Past PG West speaker QAs include VMware engineer Jignesh Shah and Aster Data’s Jeff Davis.

Below, PostgreSQL project core team member (Josh has been using PostgreSQL for 12 years) and CEO of PostgreSQL Experts answers a few questions relating to PG West and of course, PostgreSQL.

Q: What can attendees expect learn about PostgreSQL from your presentation at PG West?

Josh: I’m doing two presentations.  One is a tutorial on our over 220 configuration settings; this is essential knowledge for anyone who aspires to be an expert PostgreSQL administrator.

The second presentation is a short talk to introduce people who are relatively new to our database to the PostgreSQL community. We have a 15-year-old community with its own terminology and a huge array of online and offline activity.

Q: Describe use cases for PostgreSQL in your organization.

Josh: PostgreSQL Experts Inc. supplies hands-on consulting services for application design, scalability, performance and remote DBA services to a variety of clients. These include social networks, telecoms, retail merchants, security and analytics firms.

Q: What benefits do you see from PostgreSQL?

Josh: The PostgreSQL project is dedicated to implementing things “the right way” the first time, so it can be relied upon absolutely. Plus the database includes lots of “neat widgets” which allow me to build application features which would be hard or impossible otherwise.

Q: What did you use before PostgreSQL?

Josh: Microsoft SQL Server. I got tired of having it crash on me.

Q: What are you hoping to get out of your time at PG West?

Josh: I’m hoping that I can introduce a lot of new people to PostgreSQL and make them interested in not just using it, but becoming part of our community.

For more information about the PG West 2010 conference, visit http://www.postgresqlconference.org/. To register, visit https://www.postgresqlconference.org/content/pgwest-2010-registration.

The PG West 2010 PostgreSQL community conference will be taking place next week (November 2nd through 4th) in San Francisco. In anticipation of the event, we’ve put together a series of brief Q&As with PG West presenters to provide a taste of what attendees can expect. Our first QA with VMware engineer Jignesh Shah can be read here.

Jeff Davis, who works for Aster Data, has been involved in the PostgreSQL community since 2000 and has made two significant code contributions: Synchronized Scans in version 8.3, and Exclusion Constraints in 9.0. At PG West 2010, Jeff will be providing an Introduction to Temporal Data Management. A brief QA with Jeff is below….

Q: What can attendees expect learn about PostgreSQL from your preso at PG West?

Davis: Basic business problems like schedule conflicts are nearly impossible to solve effectively in any general-purpose SQL DBMS — except Postgres 9. I’ll show how temporal features in Postgres (and extensions) can help solve a variety of such problems, and why this is important.

And more temporal features are on their way. This is a great opportunity for participants to discuss their temporal data use cases with developers and influence the development of these new features.

Q: What benefits do you see from PostgreSQL?

Davis:

* Community of talented, experienced people from many backgrounds, all driven by excellence. The help available from this community for anything related to data management cannot be overstated.

* Permissive source code license.

* Reliable, robust, predictable database management system.

* Feature-rich and flexible.

Q: What are you hoping to get out of your time at PG West?

Davis: Last year, PG West 2009 was a huge help to the project I was finishing for 9.0 and other projects that I was still designing for 9.1. Users, potential users, and developers had some great use cases, ideas, and insights. I am confident PG West 2010 will be just as successful at advancing new features and knowledge of existing features. Not just the features that I am interested in, but all of the features on the horizon.

For more information about the PG West 2010 conference, visit http://www.postgresqlconference.org/. The cost per attendee is $150. To register, visit https://www.postgresqlconference.org/content/pgwest-2010-registration.

Just a quick note to say I’ll be speaking at the upcoming PG West conference next week in San Francisco. I’ve had the pleasure to give talks at many other database conferences in the past (e.g. Oracle, MySQL, etc.), but this will be my first PostgreSQL conference, so I’m eager to see what Postgres events are like.

My talk will be “The Thinking Person’s Guide to Data Warehouse Design”. Without a doubt, database analytics and warehousing are hot, and literally every company – large and small – is scrambling to ensure they’re using the information in their systems strategically to make decisions about their business. If you’re one of those who have been tasked with building a data warehouse or mart to serve business intelligence systems, then make sure to stop by my talk. We’ll be covering key topics like how to model your systems properly, how to choose between row, column, and NoSQL databases, monitoring your design, and a whole lot more.

Hope to see you there!

I’m pleased to be able to say that the schedule for this year’s PGDay Europe conference in Stuttgart, Germany on the 6th – 8th December 2010 is now available.

We have a bumper crop of talks this year, with 13 sessions in German, 26 in English, 2 keynotes and a key signing party!

We’re pleased to welcome Free and Open Source veteran, Simon Phipps as the presenter of our opening keynote. Simon is well known for his involvement with various organisations including GNOME, OpenSolaris and OpenJDK and will be talking about some of the changes we’ve been seeing in the Open Source world recently as projects evolve and corporate involvement changes. He is perhaps best known for his role as Chief Open Source Officer at Sun Microsystems where he oversaw the companies Open Source activities.

Of course, we also have some great technical talks lined up for developers, DBAs and people considering using or deploying Postgres, on topics such as PostGIS, application development, PostgreSQL internals, clustering and replication and more all in the comfortable surroundings of a top hotel.

For the first time we’ve also added a day of training sessions to the schedule this year (at extra cost). Training will be available on PostgreSQL administration, application development and PostGIS, with some courses in German and some in English.

As always, we reserve the right to adjust the schedule in the event of unforeseen circumstances.

So, if you haven’t done so already, head on over to the website and register your attendance at the conference. You won’t regret it!

See you in Stuttgart!

I recently learned, somewhat to my chagrin, that operating systems are pathological liars, and in particular that they habitually lie about whether data has actually been written to disk.  If you use any database product, you should care about this, because it can result in unfixable, and in some cases undetected, corruption of your database.  First, a question.  On which of the following operating systems do fsync() and related calls behave properly out of the box?

As far as I can tell, the correct answer is “none of these”.  In the case of Linux, the fine manual sums up the problem pretty well:

If the underlying hard disk has write caching enabled, then the data may not       really be on permanent storage when fsync() / fdatasync() return.

Well, that sucks, doesn’t it?  Getting the data into the drive’s write cache doesn’t really help very much, unless perhaps it’s a battery-backed cache.  It seems that you’ll need to fiddle with the hdparm utility, or perhaps some other tool if you have something other than an IDE hard drive, to turn off write caching. Your choice of filesystem and filesystem mount options can also have an impact on behavior, and apparently most Linux file systems don’t handle this very well, though Greg Smith has some good things to say about the newest versions of ext4, at least in this limited regard.

MacOS X has a similar problem.  On the MacBook Pro provided to me by my employer, EnterpriseDB, I get the following pgbench results with an out-of-the-box configuration.

Hmm.  Even if we rather optimistically assume that a single disk flush writes the commit records for all four threads to disk in a single go, that’s still 323 writes per second.  Each commit should require a full revolution of the hard drive, so apparently this puppy has a 20K RPM drive in it?  Doesn’t sound likely. MacOS X is kind enough to provide an system call that bypasses write caching and really writes data to disk.  If I set wal_sync_method=fsync_writethrough in postgresql.conf, performance drops from 1292 tps to 27 tps.  Ouch.  I guess that’s why people don’t run critical production databases on laptop-class machines.

I’m not terribly familiar with Windows, except as an (occasional) user, so I can’t comment on the situation there in detail.   However, the latest PostgreSQL documentation on this topic reads:

On Windows if wal_sync_method is open_datasync (the default), write caching is disabled by unchecking My Computer\Open\{select disk drive}\Properties\Hardware\Properties\Policies\Enable write caching on the disk. Also on Windows, fsync and fsync_writethrough never do write caching.

What that means is that if you’re running the default configuration, you’d better make sure to have unchecked that box; otherwise, you’d better change the default fsync method.  Assuming this documentation reflects the actual behavior of Windows, it sounds like they’re a bit ahead of the curve, actually making fsync() force a flush of the drive’s write cache out of the box.  On the other hand, apparently they only do this for fsync(), and not for similar methods such as opening the file with O_DSYNC, which is supposed to be similar in effect to an fsync() after every write, but more efficient.

I think the reason why there’s so much operating system (and database!) misbehavior in these cases is that everyone is afraid that doing the right thing will lead to massive loss of performance on benchmarks.  You can understand why.  In the above example with MacOS X, performance dropped by 50x when doing it the right way.  If the next release of MacOS X were to enable this behavior by default, or if the next release of PostgreSQL were to use wal_sync_method=fsync_writethrough on MacOS X by default, someone would doubtless complain that there’d been a huge performance regression “for no reason”.  The same is presumably true for Linux or Windows.  This is an unfortunate state of affairs, and it’s not clear whether it will ever get fixed, but unless and until it does, watch out!  Don’t get fooled into believing that your database is safe without very careful verification.

If you need to improve performance (and don’t have the budget for a machine with a battery-backed write cache), consider setting up the system for reliable operation and then configuring synchronous_commit=off.   This should recapture much of the performance you’ve lost through making your write ahead log reliable, without risking database corruption.  You may lose a few transactions in the event of a power failure, but for many applications that’s an acceptable risk, and certainly better than losing the whole database.

We’ve been working on updating the documentation on these topics lately, so that it will be more clear what you need to do on your system to be certain that data is being properly flushed to disk.  If you haven’t read the first section of Chapter 29, on Reliability and the Write-Ahead Log, it’s worth your time to do so.  You may want to read the version in the developer documentation, which has been recently updated with some additional details that aren’t present in the released versions.