Robert Haas

Linux Memory Reporting

2012-01-12T10:01:00.001-05:00

As much as I like Linux (and, really, I do: I ran Linux 0.99.something on my desktop in college, and wrote my class papers using vim and LaTeX), there are certain things about it that drive me crazy, and the way it reports memory usage is definitely on the list. It should be possible for a reasonably intelligent human being (in which category I place myself) to answer simple questions about system memory usage, such as "How much memory is my database using?" or "How much memory is my web server using?" relatively simply.
Read more »

Write Scalability

2011-12-15T11:28:00.000-05:00

Time flies when you're benchmarking. I noticed today that it's been over a month since my last blog post, so it's past time for an update. One of the great things about the PostgreSQL community is that it is full of smart people. One of them is my colleague Pavan Deolasee, who came up with a great idea for reducing contention on one of PostgreSQL's most heavily-trafficked locks: ProcArrayLock. Heikki Linnakangas (another really smart guy, who is also a colleague of mine) did some more work on the patch, and then I cleaned it up further and committed it.
Read more »

Linux lseek scalability

2011-11-14T12:07:00.000-05:00

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

2011-11-10T13:41:00.001-05:00

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.
Read more »

Hint Bits

2011-11-07T09:55:00.001-05:00

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.
Read more »

Fast Counting

2011-10-31T09:17:00.001-04:00

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 »

PostgreSQL Crash Debugging

2011-10-24T08:58:00.000-04:00

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 »

Deadlocks

2011-10-17T10:16:00.002-04:00

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

DETAIL: Process 22986 waits for ShareLock on transaction 939; blocked by process 22959.
Process 22959 waits for ShareLock on transaction 940; blocked by process 22986.

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 »

Index-Only Scans: We've Got 'Em

2011-10-07T21:09:00.001-04:00

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?

CommitFest In Progress

2011-10-04T16:11:00.001-04:00

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 »

Scalability, in Graphical Form, Analyzed

2011-09-30T14:36:00.001-04:00

I'm at Surge, this week, where I just listened 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 »

Postgres Open, and What Makes a Good Talk

2011-09-20T16:14:00.001-04:00

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 »

Index-Only Scans: Now There's a Patch

2011-08-18T16:00:00.001-04:00

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 »

Linux and glibc Scalability

2011-08-04T15:39:00.001-04:00

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 »

Read Scaling Out to 32 Cores

2011-07-21T10:33:00.001-04:00

With the exception of a week's vacation, my last month has been mostly absorbed by PostgreSQL performance work. Specifically, I've been looking at the workload generated by "pgbench -S", which essentially fires off lots and lots of SELECT queries that all do primary key lookups against a single table. Even more specifically, I've been looking at the way this workload performs on systems with many CPU cores where (I found) PostgreSQL was not able to use all of the available CPU time to answer queries. Although the single-core performance was around 4,300 tps, performance with 36 clients (on a 24-core server) was only about 36,000 tps.

Research revealed that performance was being limited mostly by PostgreSQL's lock manager. Each SELECT query needed to lock the table being queried - and its index - against a concurrent DROP operation. Since PostgreSQL 8.2, the lock manager has been partitioned: a lock request against a database object will be assigned to one of 16 "partitions", and lock requests against objects that fall into different partitions can proceed in parallel. Unfortunately, that doesn't help much in this case, because only two objects are being locked: the table, and its index. Most of the traffic therefore targets just two of the sixteen lock manager partitions. Furthermore, because the query itself is so trivial, the rate of lock and unlock requests is extremely high - on a more complex query, the bottleneck wouldn't be as severe.
Read more »

False Contention

2011-06-13T15:15:00.000-04:00

For the last few weeks, I've been buried in PostgreSQL performance analysis, mostly focusing on the workload generated by "pgbench -S" under high concurrency. In other words, lots and lots of very simple SELECT statements on a single table. Such workloads can generate serious internal contention within PostgreSQL on systems within many CPU cores.

But it's interesting to note that most of the contention points I've so far identified are what might be called "false contention". The transactions generated by this workload need to perform operations such as:

- acquire an AccessShareLock on the target relation and its index to guard against a concurrent drop or schema change
- acquire an ExclusiveLock on their VXID in case another transaction wishes to wait for transaction end
- read the list of pending "invalidation" events, which are generally created by DDL
- read the list of in-progress XIDs, to generate a snapshot
- find the root index block for the table's primary key index in the shared buffer pool
- momentarily pin the block containing the root index page into the buffer pool, so that it can't be evicted while we're examining it
- momentarily lock the root index page, so that no new items can be added until we've decided which downlink to follow

Now, in fact, in this workload, there is no concurrent DDL against the relevant table and index, or any other one; no one is attempting to wait for the completion of any VXID; the list of in-progress XIDs can easily be simultaneously read by many backends; and the root index page is in no danger either of being modified or of being evicted. In short, the problem is not that there are resource conflicts, but that verifying that no resource conflicts exist is itself eating up too many resources.

Reducing Lock Contention

2011-06-06T08:46:00.000-04:00

In a recent blog post on Performance Optimization, I mentioned that Noah Misch and I had discussed some methods of reducing the overhead of frequent relation locks. Every transaction that touches a given table or index locks it and, at commit time, unlocks it. This adds up to a lot of locking and unlocking, which ends up being very significant on machines with many CPU cores. I ended up spending a good chunk of last week hacking on this problem, with very promising results: I have a prototype patch that improves throughput on a SELECT-only pgbench test by about 3.5x on a system with 24 cores. Not bad for a couple days work.

Read more »

Open Source Licensing

2011-05-27T13:48:00.000-04:00

I can't resist the opportunity to comment on the FSF's guidelines - apparently just published - for how to choose a license for your work. A story on this was posted on Slashdot this morning. The FSF's guidelines are a little more nuanced than they could be; for example, they recommend contributing code to existing projects under the licenses used by those projects, rather than starting a giant war with the maintainers of those projects. And if you're including trivial test programs in your work, or your work is shorter than the text of the GPL itself, you might just want to put the work in the public domain. Very sensible!

But that's about as far as they're willing to go. For example, the section on contributing to existing projects suggests that, if you're making major enhancements to a non-GPL program, you might want to fork it and release your changes under the GPL. In other words, in the FSF's opinion, sometimes you should start a giant war with the maintainers. In the open source world, forks are a part of life, and people are free to choose the licenses they want, but this seems awfully cavalier to me. Forks are really damaging. Maintaining a large and unified developer community has value; having those people spend their time developing, rather than arguing about licensing, is a good thing.
Read more »

Performance Optimization

2011-05-26T09:55:00.001-04:00

There have been several lively discussions this week on possible performance optimizations for PostgreSQL 9.2. PostgreSQL 9.1 is still in beta, and there's still plenty of bug-fixing going on there, but the number of people who need to and can be involved in that process is not as large as it was a month or two ago. So it's a good time to start thinking about development for PostgreSQL 9.2. Not a lot of code is being written yet, which is probably just right for where we are in the development cycle, but we're kicking the tires of various ideas and trying to figure out what projects are worth spending time on. Here are a few that I'm excited about right now.
Read more »

PostgreSQL 9.2 Development: CommitFest Managers Needed

2011-05-23T12:22:00.000-04:00

There's an old joke that project managers don't know how to do anything; they only know how to do it better. Of course, the project manager is the butt of the joke: how can you know how to do something better, if you don't know how to do it in the first place?
Read more »

PostgreSQL 9.1 In Review

2011-05-11T21:48:00.000-04:00

As the PostgreSQL 9.1 development cycle winds down to a close (we're now in beta), I find myself reflecting on what we got done during this development cycle, and what we didn't get done. Just over a year ago, I published a blog post entitled Big Ideas, basically asking for feedback on what we should attempt to tackle in PostgreSQL 9.1. The feedback was overwhelming, and led to a follow-on blog post summarizing the most-requested features. Here's the short version: Of the 10 or 11 most requested features, we implemented (drum roll, please) one. Credit for granular collation support goes to Peter Eisentraut, with much additional hackery by Tom Lane.

You can find a second list of possible 9.1 features in the minutes of the PGCon 2010 Developer Meeting. Of the 30 features listed there, we got, by my count, 12.5, or more than 40%. While even that may seem like a somewhat small percentage, a dozen or so major enhancements to the development process is nothing to sneeze at. And certainly, if you were a feature longing to be implemented, you'd much prefer to be on the second list than the first.
Read more »

shared_buffers on 32-bit systems

2011-05-05T14:20:00.000-04:00

Every once in a while, I read something that's so obvious after the fact that I wonder why I didn't think of it myself. A recent thread on pgsql-performance had that effect. The PostgreSQL documentation gives some rough guidelines for tuning shared_buffers, which recommends (on UNIX-like systems) 25% of system memory up to a maximum of 8GB. There are similar guidelines on the PostgreSQL wiki page, in the article on Tuning Your PostgreSQL Server.

What became obvious to me in reading the thread - and probably should have been obvious to me all along - is that this advice only makes sense if you're running a 64-bit build of PostgreSQL, because, on a 32-bit build, each process is limited to 4GB of address space, of which (at least on Linux) 1GB is reserved for the kernel. That means that no matter how much physical memory the machine has, each PostgreSQL backend will be able to address at most 3GB of data. That includes (most significantly) shared_buffers, but also the process executable text, stack, and heap, including backend-local memory allocations for sorting and hashing, various internal caches that each backend process uses, local buffers for accessing temporary relations, and so on. And 3GB is really a theoretical upper limit, if everything is packed optimally into the available address space: I'm not sure how close you can get to that limit in practice before things start breaking.

So, if you're running a 32-bit PostgreSQL on UNIX-like operating system, you probably need to limit shared_buffers to at most 2GB or 2.5GB. If that is less than about 25% of your system memory, you should seriously consider an upgrade to a 64-bit PostgreSQL.

A Change of Role

2011-04-26T14:19:00.000-04:00

I'm pleased to report that I have changed roles within EnterpriseDB and am now officially a member of EnterpriseDB's support and professional services team. I have a number of other responsibilities as well, so I will not be spending all of my time on support, but I have been and will continue to spend a significant chunk of time on it each day. When I've talked about this with people, they sometimes look at me as if I have two heads. After all, I am a backend server developer, and developers as a breed are not known for enjoying support.
Read more »

PostgreSQL East, and The MySQL Conference and Expo

2011-04-19T14:17:00.001-04:00

Last month, I attended (and spoke at) PostgreSQL East in New York City, which this year featured a MongoDB track. This past week, I was in Santa Clara at the O'Reilly MySQL Conference & Expo, which had a substantial PostgreSQL track this year, where I also spoke.

Both conferences had some very good talks. The first talk I attended at the MySQL conference turned out to be one of the best - it was entitled Linux and H/W optimizations for MySQL. I had a little difficulty understanding Yoshinori Matsunobu's accent at times, but the slides were excellent, and very detailed. Some of his more interesting findings: (1) SSDs speed things up both on the master and on replication slaves, but the speedup is larger on the slaves; so it's useful to put hard disks on the master and SSDs on the slave to make it possible for single-threaded recovery there to keep up with the master; (2) while SSDs are much faster for random access, they are actually slower for sequential access and fsync, so a RAID array with a battery-backed or flash-backed write cache may still be a better option in those cases, (3) Fusion I/O drives were FAR faster than Intel drives, (4) the Intel Nehalem architecture was much faster than the AMD Opteron architecture when used in combination with SSDs, and (5) HyperThreading helps more in SSD environments than it does otherwise, because the system, overall, becomes more heavily CPU-bound; for the same reasons, mutex contention hurts more.

Another very good talk was Peter Zaitsev's discussion of Innodb and XtraDB Architecture and Performance Optimization, which gave me the feeling of looking into a sort of carnival mirror, where you recognize yourself, but it's all distorted. Two of the problems that give PostgreSQL DBAs heartburn - bloat, and checkpoint I/O spikes (and less frequently, purge not keeping up a la vacuum not keeping up) - are apparently problems for MySQL as well, though with significantly different details. I'm not even going to attempt to summarize the differences, or say which problem is worse or occurs more often, because I honestly have no idea. I was a bit surprised to hear dump-and-reload recommended to recover from certain worst-case scenarios, though.

There were other good talks, too, which helped me understand what's going on in the world of MySQL forks. Apparently, the Drizzle team is busy removing features that they consider half-baked and modularizing the code so that it is easier to understand and improve, while the MariaDB team is busy adding optimizer features, including support for hash joins and persistent statistics. From what I understand, the MySQL optimizer has typically worked by gathering statistics through on-the-fly index probes, which can be a problem in some situations. It's not so easy to categorize the work that Oracle is doing, but it seems to involve a fair amount of filing down of rough edges, and various improvements to replication, including, perhaps most significantly, parallel replication apply.

At PostgreSQL East, I think my favorite talk was Ken Rosensteel's talk, somewhat misleadingly titled Large Customers Want PostgreSQL, Too. This talk turned to be about migrating a large Oracle mainframe application to use PostgreSQL, and the challenges faced during that migration. He, or his team, built an Oracle-to-PostgreSQL converter for stored procedures; it was interesting to see that they got bitten by our bizarre casting rules around the smallint data type. They also ended up doing some very interesting work optimizing the performance of ECPG for small FETCH statements; these are areas of the code that I think don't normally get a lot of attention, and it was great to hear about the optimization work that got done.

I was disappointed that Jon Hoffman's talk on Experiences with Postgres and MongoDB at foursquare.com got cancelled; I think that would have been an interesting talk. I did have an opportunity to attend Jake Luciani's talk Comparing the Apache Cassandra Architecutre to PostgreSQL, which turned out to be more about Cassandra than PostgreSQL, but was nevertheless interesting. I would have been interested to hear a more technical talk, though, about how problems like distributed serialization anomalies and distributed checkpointing are handled.

Next month, I'll be speaking at PGCon 2011 on Using The PostgreSQL System Catalogs and How To Get Your PostgreSQL Patch Accepted. And after that, unlike Bruce, I'm going to stay home for a few months!

One-Way Tuning

2011-04-07T06:00:00.002-04:00

I've been noticing that many of the parameters in postgresql.conf only ever need to be adjusted in one direction from the default.

shared_buffers, temp_buffers, work_mem, maintenance_work_mem, checkpoint_segments, checkpoint_timeout, and checkpoint_completion_target, join_collapse_limit, and from_collapse_limit only ever need to be adjusted upward from the default.

bgwriter_delay, seq_page_cost, and random_page_cost only ever need to be adjusted downward from the default.

Does this mean we should change some of the defaults?

CommitFests and Meritocracy

2011-03-31T22:06:00.000-04:00

Yesterday, I wrote a blog post on whether and to what extend the PostgreSQL community is a welcoming community, in which I quoted some remarks that Selena Deckelmann made, and she responded with her own post on where meritocracy fails. I want to just recap a few things that may not be totally obvious to casual observers of our community; and then I have a few remarks about meritocracy.

Read more »

A Welcoming Community?

2011-03-30T16:38:00.001-04:00

I attended PostgreSQL East last week and, as usual, the most interesting discussions were the ones that nobody planned. Ed Boyajian, the CEO here at EnterpriseDB where I work, threw out a remark to the effect that the PostgreSQL community is a meritocracy. Selena Deckelmann wasn't convinced, "not to say that we don't do try hard, and do pretty well." During the closing session, Josh Drake made some remarks to the effect that, in effect, the process for submitting a patch to PostgreSQL is long, difficult, and fraught with arbitrary and capricious rejection, which caused me to rise to my feet and object that that while it's not perfect, I think we do pretty well, and better than we used to. Josh agreed with some of what I have to say, and I see his point, too. But it got me thinking about the whole way that we operate as a development community, and what is good and bad about it.
Read more »

Working Toward PostgreSQL 9.1beta

2011-03-21T10:36:00.000-04:00

I'm pleased to report that we seem to be making good progress toward PostgreSQL 9.1beta. As we did for PostgreSQL 9.0, we are maintaining a list of open items that we need either to address or to decide that they aren't so important after all (there are also some issues that are being discussed on the mailing list that aren't reflected in that open items list). What we're really doing here is trying to stabilize the code, particularly with respect to the features committed at the very end of the development cycle. So far, the main problem areas appear to be (1) collation support, (2) changes to streaming replication, especially the new synchronous replication feature, and (3) the new serializable isolation level. But we are making rapid progress in sorting out the loose ends, and I feel a lot better about the release now than I did even a week ago.

Barring objections or unforeseen problems, I'm hoping to bundle an alpha5 release a week from today, on Monday, March 28. We haven't discussed the time line for beta yet, other than to hope that we'll be able to get there in April. I still think that's a realistic timeline, although there is quite a bit of work that must be done between now and then for us to hit it. In the meantime, if you're in a position to build from source, please keep testing and sending in bug reports. Thanks!

Query Planner Enhancements in PostgreSQL 9.1

2011-03-16T16:45:00.000-04:00

PostgreSQL has an awesome query planner. And it keeps getting better. postgresql.org has a survey asking people Which PostgreSQL 9.1 Feature Are You Most Excited About? (to vote on it, if it's still open when you read this, go here). For some reason, query planner features tend not to be listed when people talk about what they're most excited about in a new release, but there's ample room for excitement. If a particular query planner feature happens to apply to your use case, it's possible to see multiple-order-of-magnitude speedups, avoiding the need for a major application redesign or even an expensive hardware purchase. This is a big deal.

Read more »

Advice for Google Summer of Code Students (and other prospective contributors)

2011-03-10T13:12:00.004-05:00

Periodically, someone whose name I've ever seen before contacts me -- or posts to pgsql-hackers -- to say that either (1) they've already written a great patch for PostgreSQL and they'd like to know how to get it committed or (2) they're interested in writing a patch to implement some major new feature in PostgreSQL. Some of these people are prospective Google Summer of Code students, while others are researchers or other people who, for whatever reason, are interested in PostgreSQL. I am always thrilled to see more people take an interest in PostgreSQL, but unfortunately I've seen a number of people who seemed very smart and promising crash and burn when in terms of actually making a successful contribution of code. In fact, no matter how promising things seem at the outset, the failure rate - in my experience - is very close to 100%.
Read more »

More Musings on Logical Replication

2011-03-03T13:07:00.001-05:00

There were some interesting comments on my previous blog post on logical replication, which have inspired me to write a bit more about this topic. Exactly how should this actually work? Every time a tuple is inserted, updated, or deleted, we already write to the transaction log the entire contents of any new tuple, and the physical position of any old tuple. It would be nice to piggyback somehow on the work that's already being done there, but it's hard to see exactly how. I see three main problems.
Read more »

Troubleshooting Stuck VACUUMs

2011-03-01T10:04:00.001-05:00

Over the years, I've occasionally encountered situations where VACUUM fails to make progress, and not fully understood why that was happening. Recently, I've come to a better understanding of how lock conflicts can result in VACUUM stalling out either at the beginning of the table, or part-way through. (If you're not already familiar with the types of locks that PostgreSQL uses, you may find it helpful to read through my earlier blog post on locking in PostgreSQL before reading on.)
Read more »

The Case For Logical Replication

2011-02-26T07:30:00.000-05:00

Replication, as it exists in PostgreSQL today, is physical replication. That is, the disk files as they exist on the standby are essentially byte-for-byte identical to the ones on the master (with the exception that the same hint bits may not be set). When a change is made on the master, the write-ahead log is streamed to the standby, which makes the same change to the corresponding disk block on the standby. The alternative is logical replication, in which what is transferred is not an instruction to write a certain sequence of bytes at a certain location, but the information that a certain tuple was inserted, or that a table with a given schema was created.
Read more »

Working in the Community

2011-02-17T12:40:00.002-05:00

The PostgreSQL community makes decisions by consensus. The craziness of this approach should be readily apparent. The United States Senate has come under withering criticism for rules which permit one or a small number of Senators to hold up the business of the chamber to such an extent that it's difficult to get anything done. Forty-one senators can filibuster just about anything, to the frustration of the fifty-nine who want to have a vote and get on with it; and sometimes just one Senator can make himself or herself a near-complete obstacle to progress.

Read more »

Keeping Local Git Branches Up To Date

2011-02-17T09:23:00.002-05:00

Because I spend most of my time working on the master branch of the PostgreSQL git repository, I prefer to work with just a single clone. The PostgreSQL wiki page Committing with Git describes several ways of using multiple clones and/or git-new-workdir, but my personal preference is to just use one clone. Most of the time, I keep the main branch checked out, but every once in a while I check out one of the back-branches to look at something, or to back-patch. (If you're unfamiliar with the PostgreSQL workflow, note that we do not merge into our official branches; we always rebase, so that there are no merge commits in our official repository. You may or may not like this workflow, but it works for us.)

One small annoyance is that "git pull" doesn't leave my clone in the state I want. Say I have the master branch checked out. "git pull" will update all of my remote tracking branches, but it will only update the local branch that I currently have checked out. This is annoying, first of all because if I later type "git log REL9_0_STABLE" I'll only get the commits since the last time I checked out and pulled that branch, rather than as I intended the latest state of the upstream, and secondly because it leads to spurious griping when I later do "git push": it complains that the old branches can't be pushed because it wouldn't be a fast-forward merge. This is of course a little silly: since my branch tip is an ancestor of the tracking branch, it would be more reasonable to conclude that I haven't updated it than to imagine I meant to clobber the origin.
Read more »

MySQL vs. PostgreSQL, Part 2: VACUUM vs. Purge

2011-02-01T10:06:00.002-05:00

Almost two months ago, I wrote part one of what I indicated would be an occasional series of blog posts comparing the architecture of PostgreSQL to that of MySQL . Here's part two. Please note that the caveats set forth in part one apply to this and all future installments as well, so if you haven't read part one already, please click on the link above and read at least the first two paragraphs before reading this post.
Read more »

PostgreSQL 9.1: Neat Stuff is Coming

2011-01-24T09:49:00.003-05:00

Up until the last month or so, the development arc for PostgreSQL 9.1 was looking fairly humdrum. Quite a bit of good refactoring and some good general enhancements, but not a lot of sizzle. That seems to be changing now. Of course, in one sense, that's a bad thing: we're only three weeks from the end of the development cycle, and ideally it would have been nicer to have some of these big patches land just a little bit sooner. Still, I feel pretty good about the quality of what's been committed.
Read more »

Locking in PostgreSQL

2011-01-20T11:45:00.001-05:00

Have you ever had one of those annoying problems where a query, or some kind of maintenance task such as vacuum, seems to hang, without making any discernable foreign progress, basically forever? If it's a foreground task, you typically think to yourself "oh, that's taking longer than normal" - but then you start to become suspicious as to whether anything's happening at all. You fire up top, or strace, or gdb, or iostat, or some combination of those tools, and eventually decide that, indeed, nothing is happening.

Read more »

What Kind of Replication Do You Need?

2011-01-18T09:33:00.001-05:00

As you probably know by now if you're a regular reader of Planet PostgreSQL, or if you've read the PostgreSQL 9.0 release notes, PostgreSQL 9.0 offers a much-improved form of built-in replication. In PostgreSQL 8.4 and prior releases, replication was possible via WAL shipping, but the delay between master and standby could be several minutes, or even longer, depending on configuration parameters. This delay has been essentially eliminated in PostgreSQL 9.0, which allows the write-ahead log to be streamed from master to standby as it's generated.

But it's still asynchronous replication - as opposed synchronous replication, which has been proposed for inclusion in PostgreSQL 9.1. If the master crashes just an instant after processing a COMMIT statement, the client will believe that the transaction has committed, but the slave won't know about it. If the slave is then promoted to become the new master, the transaction is gone forever.
Read more »

Why SQL/MED is Cool

2011-01-14T13:57:00.000-05:00

One of the big patches that is in the works for PostgreSQL 9.1 -- and will hopefully but not for sure make the cut -- is a series of patches that implement basic SQL/MED functionality for PostgreSQL. What is SQL/MED and why should you care?
Read more »

PostgreSQL 9.1: Big Patches

2011-01-04T17:24:00.002-05:00

About three weeks ago, I wrote a blog post about the forthcoming end of the PostgreSQL 9.1 development cycle, and the many large and important features for which we have patches outstanding. Since we now have just 11 days to go before the beginning of the forth and final CommitFest, this seems like a good time to revisit the progress we've made. Here again is the list of features from my previous post:
Read more »

PostgreSQL Performance vs. Microsoft SQL Server

2010-12-20T17:28:00.000-05:00

A recent poster to the pgsql-performance mailing list enquired as to the relative performance of Microsoft SQL Server vs. PostgreSQL. It's a reasonable question. Switching databases can be a major project, and you certainly wouldn't want to do it and then find out at the end that you'd taken a huge performance hit and had to throw all your work away and switch back. The good news is that this scenario is fairly unlikely.

Read more »

Two Hundred Commits

2010-12-16T08:52:00.003-05:00

My first patch (add a separate TRUNCATE privilege) was committed to the PostgreSQL source code repository (at the time, CVS) by Tom Lane on September 8, 2008. I became a committer a little over a year later, and the first commit I did myself was on December 10, 2009 (a patch from Marcin Mank to fix levenshtein_with_costs). Of course, I screwed it up: the release team was in the middle of wrapping a minor release, and I back-patched the fix in the brief window after the release notes were written and before the release went out.
Read more »

SE-Linux for PostgreSQL: Part 3

2010-12-15T18:03:00.000-05:00

Back in September, I wrote two blog posts on SE-Linux integration for PostgreSQL. In part 1, I discussed the work that had already been committed as of that time. In part 2, I discussed what I learned at the meeting, and planned next steps. Since then, a considerable amount of progress has been made, so it seems like a good time to revisit the issue.

Read more »

Crunch Time for PostgreSQL 9.1

2010-12-13T13:43:00.000-05:00

According to the PostgreSQL 9.1 development plan, the final CommitFest for PostgreSQL 9.1 development will begin in 33 days. Approximately 30 days later, we'll stamp our final alpha release and begin preparations for PostgreSQL 9.1 beta. This is exciting news, because I'm really looking forward to PostgreSQL 9.1. It's also scary news, because there is a lot of work left to be done between now and then, and at least in the United States, Christmas is going to take a bite out of that time.

We have a number of very interesting, very significant features which were submitted to the 2010-11 CommitFest. These include SQL/MED, extensions, synchronous replication, writeable CTEs, per-column collation, MERGE, checkpoint improvements, further infrastructure for SE-Linux integration, and my own work on unlogged tables. While we've made significant progress on most of these features during this CommitFest, major work still remains to be done on nearly all of them, and none of them can be considered a sure thing for PostgreSQL 9.1. It's possible - maybe even likely - that even more worthwhile features will be added to the queue between now and mid-January.

So it's crunch time. We have about two months to define what PostgreSQL 9.1 will be. Let's make the most of it.

The Cost of Page Faults

2010-12-06T22:20:00.000-05:00

Over Thanksgiving, I wrote a bit about some work I did profiling PostgreSQL, and about squeezing a bit of overhead out of the backend shutdown process. After making that change, I did some further profiling of connection startup/tearddown, and was dismayed to see that the revised profile looked pretty mundane, with most of the time being taken up by functions like memset() and memcpy() that are typically hard to optimize.

As it turns out, this profile wasn't really showing what I thought it was showing. Andres Freund and Tom Lane theorized that the reason why memset() and memcpy() showed up so high in the profile was not because those operations were intrinsically expensive, but because those functions were triggering page faults. Page faults occur when a process attempts to access a portion of its address space it hasn't previously touched, and the kernel must arrange to map that chunk of address space to an actual chunk of physical memory. As it turns out, it appears that Andres and Tom were right: processing a page fault is 2 or 3 times more expensive than zeroing a page of memory.

I found this a bit surprising, because I'm in the habit of thinking of process startup on UNIX-like systems as being very cheap, but it appears that in this case there's so little actual work going on the page faults actually become the dominant cost. This means that if we want to make a significant further reduction in our connection overhead, we're probably going to have to avoid starting a new process for each new connection. I posted a few ideas on this topic, to which Tom Lane responded. In short, there may be some benefit in making PostgreSQL follow a model more like Apache, where workers are spawned before they are actually needed, rather than on demand. I don't presently have time to follow up on this, but I think it's got potential.

Troubleshooting Database Unresponsiveness

2010-12-03T13:15:00.002-05:00

From time to time, we get complaints on the pgsql-performance mailing list about a PostgreSQL database that - for the most part - performs reasonably well, but every once in a while it becomes unresponsive for some number of seconds or even minutes; and then eventually recovers. What's going on?
Read more »

MySQL vs. PostgreSQL, Part 1: Table Organization

2010-11-29T16:37:00.001-05:00

I'm going to be starting an occasional series of blog postings comparing MySQL's architecture to PostgreSQL's architecture. Regular readers of this blog will already be aware that I know PostgreSQL far better than MySQL, having last used MySQL a very long time ago when both products were far less mature than they are today. So, my discussion of how PostgreSQL works will be based on first-hand knowledge, but discussion of how MySQL works will be based on research and - insofar as I'm can make it happen - discussion with people who know it better than I do. (Note: If you're a person who knows MySQL better than I do and would like to help me avoid making stupid mistakes, drop me an email.)

Read more »

Profiling PostgreSQL

2010-11-25T09:09:00.000-05:00

I did a little bit of work Tuesday night and Wednesday profiling PostgreSQL. I ran two different tests. The first test was designed just to measure the overhead of repeatedly connecting to the database without doing anything, while the second test looked running pgbench with 36 concurrent threads. The best thing that can happen to you when you fire up the profiler is to have something pop up the profile that you never would have expected. At least in my experience, when you see what you expect to see, that typically means it's something you've already thought about optimizing, and is therefore probably reasonably efficient already. When you see something totally unexpected, it's probably something you've never thought about optimizing, and of course the first optimization is always the easiest.

Anyhow, that's what happened to me with the "repeated connections" test. It turns out that a big chunk of the CPU time was actually being spent during backend exit, rather than (as I had anticipated) backend startup. We had a check in there to forcibly release any buffer pins that hadn't been cleaned up properly during normal execution. Originally, this was probably a necessary and valuable check, but we've subsequently added much more thorough and robust cleanup mechanisms which should mean that this code never finds anything to release. If it does find something, gracefully cleaning up the pin is the wrong idea: we want the code to yell and scream, so that we find and fix the underlying bug.

So, after some discussion with Tom Lane, I ripped this code out and replaced it with some code that will run only in assert-enabled builds (which are typically used only for development and debugging) that will check for leftover buffer pins and fail an assertion if any are found, which will hopefully make it easier to find any current or future bugs in this area. In non-assert-enabled builds, we no longer do anything at all here (the best kind of optimization!).

Unfortunately, this was the only really surprising thing that popped up in the profiling results. Further improvements are going to take a bit more work.

Happy Thanksgiving!

Index-Only Scans

2010-11-22T11:21:00.002-05:00

There seems to be a lot of interest in the as-yet-unimplemented performance feature called index-only scans, so I thought it would be useful to explain a little bit more about what this feature is, how it will help PostgreSQL, and where the bodies are buried.

First, the name. What do we mean by an index-only scan? In PostgreSQL today, an index scan always accesses both the index itself and the underlying table. You might think this unnecessary. For example, if you have the query SELECT name FROM table WHERE id = 10, and there is an index on (id, name), you might assume that we could use the index to check for tuples with id = 10, and the if one is found, return the name directly from the index tuple, without consulting the underlying table. Unfortunately, this does not work, because that tuple might not actually be one that the SELECT statement can see. If the tuple was inserted by a transaction which began after the SELECT statement took its MVCC snapshot, or deleted by a transaction which committed before the SELECT statement took its MVCC snapshot, then the SELECT statement must not return it. If it did, we would quickly get very surprising wrong answers out of the database. So PostgreSQL first looks at the index tuple, and then the heap (table) tuple, decides what the right thing to do is, and does it. By an index ONLY scan, we mean one which will look at just the index, and not at the corresponding table; the trick is to figure out how to make that happen without returning wrong answers.

Read more »

Best Patches of 9.1CF3

2010-11-18T09:39:00.003-05:00

Back in July, I wrote a blog post on the best patches submitted for the first CommitFest for PostgreSQL 9.1 development (so far, the first two out of the three have been committed). I didn't end up writing a similar post for the second CommitFest, because there wasn't a lot of stuff that really grabbed my attention, but the third CommitFest is here now, and there are a ton of exciting patches.
Read more »

When Your Data Isn't Made of Gold

2010-11-17T00:14:00.002-05:00

Josh Berkus' recent blog posting on What We Should Be Learning from MySQL, part 2 includes the following quote: "We tend to treat all data in Postgres as if it were made of gold, and not all data is equally valuable." He goes on to wonder what we can do to better provide for the case where your data isn't made of gold.

Read more »

Rob Wultsch's MySQL Talk at PostgreSQL West

2010-11-10T22:04:00.002-05:00

I thought this talk deserved a blog post of its own, so here it is. I have to admit that I approach this topic with some trepidation. The MySQL vs. PostgreSQL debate is one of those things that people get touchy about. Still, I'm pleased that not only Rob, but a number of other MySQL community members who I did not get a chance to meet, came to the conference, and it sounds like it will be our community's turn to visit their conference in April of next year. Rob was kind enough to offer to introduce me to some of the MySQL community members who were there, and I, well, I didn't take him up on it. That's something I'd like to rectify down the road, but unfortunately this was a very compressed trip for me, and the number of people I had time to talk to and meet with was much less than what I would have liked.

Read more »

PostgreSQL West Talks

2010-11-08T14:23:00.000-05:00

As I blogged about before the conference, I gave two talks this year at PostgreSQL West. The first was a talk on the query optimizer, which I've given before, and the second talk was on using the system catalogs, which was new. While the second one was well-attended, the first one was packed. I keep hoping I'll think of something to talk about that people find even more interesting than the query planner, but so far no luck. Slides for both presentations are now posted; I've added two slides to the system catalogs presentation that weren't there when I gave the talk, but probably should have been.

Nearly all the talks I attended were good. Some of the best were Greg Smith's talk on Righting Your Writes (slides), Gabrielle Roth's talk on PostgreSQL monitoring tools, and Joe Conway's talk on Building an Open Geospatial Technology Stack (which was actually given in part by Jeff Hamann, who has a company, and a book). All three of these, and a number of the others, were rich with the sort of anecdotal information that it's hard to get out of the documentation: How exactly do you set this up? How well does it actually work? What are its best and worst points?

Another memorable talk was Rob Wultsch's talk entitled "MySQL: The Elephant in the Room". But that talk really deserves a blog post all of its own. Stay tuned.

Here Comes PostgreSQL West

2010-10-28T13:50:00.001-04:00

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.

Read more »

WAL Reliability

2010-10-25T10:36:00.000-04:00

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?

A. Linux
B. Windows
C. MacOS

Read more »

Choosing a Datastore

2010-10-14T15:30:00.003-04:00

In thinking about which database might be best for any particular job, it's easy to get lost in the PR. Advocates of traditional relational database systems like Oracle and PostgreSQL tend to focus on the fact that systems are feature-rich and provide features such as atomicity, consistency, isolation, and durability (ACID), while advocates of document databases (like MongoDB) and key-value stores (memcached, Dynamo, Riak, and many others) tend to focus on performance, horizontal scalability, and ease of configuration. This is obviously an apples-and-oranges comparison, and a good deal of misunderstanding and finger-pointing can result. Of course, the real situation is a bit more complicated: everyone really wants to have all of these features, and any trade-off between them is bound to be difficult.

Read more »

Down To Six

2010-10-06T23:27:00.001-04:00

From early July until the beginning of this week, the PostgreSQL project has been maintaining eight active branches: 7.4, 8.0, 8.1, 8.2, 8.3, 8.4, 9.0, and the master branch (9.1devel). As a result, a significant number of bug fixes and security updates had to be back-patched into all of those releases. At least for me, the recent switch to git has made back-patching, at least for simple cases, a whole lot simpler. But it's still a fair amount of work - some parts of the code have changed a good deal since 2003, when 7.4 was released.

Read more »

SURGE Recap

2010-10-04T23:34:00.003-04:00

Bruce Momjian and I spent Thursday and Friday of last week in Baltimore, attending Surge. It was a great conference. I think the best speakers were Bryan Cantrill of Joyent (@bcantrill), John Allspaw of Etsy (@allspaw), and Artur Bergman of Wikia (@crucially), but there were many other good talks as well. The theme of the conference was scalability, and a number of speakers discussed how they'd tackled scalability challenges. Most seem to have started out with an infrastructure based on MySQL or PostgreSQL and added other technologies around the core database to improve scalability, especially Lucene and memcached. But there were some interesting exceptions, such as a talk by Mike Malone wherein he described building a system to manage spatial data (along the lines of PostGIS) on top of Apache Cassandra.

Some general themes I took away from the conference:
Read more »

Stupid Git Tricks for PostgreSQL

2010-09-28T13:17:00.000-04:00

Even before PostgreSQL switched to git, we had a git mirror of our old CVS repository. So I suppose I could have hacked up these scripts any time. But I didn't get around to it until we really did the switch. Here's the first one. It's a one-liner. For some definition of "one line".

git log --format='%H' --shortstat `git merge-base REL9_0_STABLE master`..master | perl -ne 'chomp; if (/^[0-9a-f]/) { print $_, " "; } elsif (/files changed/) { s/^\s+//; my @a = split /\s+/; print $a[3] + $a[5], "\n" }' | sort -k2 -n -r | head | cut -d' ' -f1 | while read commit; do git log --shortstat -n 1 $commit | cat; echo ""; done

This will show you the ten "biggest" commits since the REL9_0_STABLE branch was created, according to number of lines of code touched. Of course, this isn't a great proxy for significance, as the output shows. Heavily abbreviated, largest first:

66424a284879b Fix indentation of verbatim block elements (Peter Eisentraut)
9f2e211386931 Remove cvs keywords from all files (Magnus Hagander)
4d355a8336e0f Add a SECURITY LABEL command (Robert Haas)
c10575ff005c3 Rewrite comment code for better modular
ity, and add necessary locking (Robert Haas)
53e757689ce94 Make NestLoop plan nodes pass outer-relation variables into their inner relation using the general PARAM_EXEC executor parameter mechanism, rather than the ad-hoc kluge of passing the outer tuple down through ExecReScan (Tom Lane)
5194b9d04988a Spell and markup checking (Peter Eisentraut)
005e427a22e3b Make an editorial pass over the 9.0 release notes. (Tom Lane)
3186560f46b50 Replace doc references to install-win32 with install-windows (Robert Haas)
debcec7dc31a9 Include the backend ID in the relpath of temporary relations (Robert Haas)

2746e5f21d4dc Introduce latches. A latch is a boolean variable, with the capability to wait until it is set (Heikki Linnakangas)

Of course, some of these are not-very-interesting commits that happen to touch a lot of lines of code, but a number of them represented significant refactoring work that can be expected to lead to good things down the line. In particular, latches are intended to reduce replication latency and eventually facilitate synchronous replication; and Tom's PARAM_EXEC refactoring is one step towards support for the SQL construct LATERAL().

OK, one more.

#!/bin/sh

BP=`git merge-base master REL9_0_STABLE`

git log --format='format:%an' $BP..master | sort -u |
while read author; do
    echo "$author: \c"
    git log --author="$author" --numstat $BP..master |
    awk '/^[0-9]/ { P += $1; M += $2 }
         /^commit/ { C++ }
         END { print C " commits, " P " additions, " M " deletions, " (P+M) " total"}'
done

This one shows you the total number of lines of code committed to 9.1devel, summed up by committer. It has the same problem as the previous script, which is that it sometimes you change a lot of lines of code without actually doing anything terribly important. It has a further problem, too: it only takes into account the committer, rather other important roles, including reporter, authors, and reviewers. Unfortunately, that information can't easily be extracted from the commit logs in a structured way. I would like to see us address that defect in the future, but we're going to need something more clever than git's Author field. Most non-trivial patches, in the form in which they are eventually committed, are the work of more than one person; and, at least IMO, crediting only the main author (if there even is one) would be misleading and unfair in many cases.

I think the most interesting tidbit I learned from playing around with this stuff is that git merge-base can be used to find the branch point for a release. That's definitely handy.

Enjoying Git

2010-09-24T09:35:00.000-04:00

OK, I admit it. This is awesome. I'm still getting used to committing to PostgreSQL with git rather than CVS, but it's sort of like the feeling of being let out of the dungeon. Wow, sunlight, what am I supposed to do about that?

Actually, I've never really been into CVS bashing; it's an OK system for what it does. And compare to RCS, which I actually used once or twice a long time ago, it's positively phenomenal. But git, despite its imperfections, is just a lot better.

There are two major things that caused problems for me when committing to CVS. First, it was painfully slow. Second, since I was doing all of my development work on git, that meant extracting the patch, applying it to CVS, making sure to CVS add/rm any new/deleted files, retyping (or copying) the commit message, and double-checking that I hadn't messed anything up while moving the patch around.

$ git commit

 $ git show

$ git push

Nice! I feel like someone gave me an easy button.

Git Conversion, Take Two

2010-09-18T01:01:00.000-04:00

The PostgreSQL project will be making its second attempt to migrate from CVS to git this coming Monday. In a previous blog post, I talked about some of the difficulties we've had getting a clean conversion of our project history to git. I was surprised that a number of people suggested throwing out our development history and just moving the head of each branch to git; and I agree with some of the later comments that this would be a bad idea. I refer back to our development history fairly frequently, for a variety of reasons: to determine when particular features were introduced, to determine what patch last touched a particular area of the code, to see how old a particular bit of code is, and sometimes even to model a new patch on a previous patch that added a similar feature. So I'd find it very painful to lose convenient access to all of that history. Even a somewhat messed-up conversion would be better than no conversion at all.

Fortunately, it looks like we're going to end up with a pretty darn good conversion. Tom Lane spent most of last weekend cleaning up most of the remaining infelicities. The newest conversions are a huge improvement over both our current, incrementally-updated conversion (which is what I use for day to day development) as well as earlier attempts at a final conversion. Only a handful of minor artifacts remain, mostly because of wacky things that were done in CVS many years ago. Our use of CVS in recent years has been quite disciplined, which is why such a clean conversion is possible.

SE-Linux For PostgreSQL: Part 2

2010-09-14T07:00:00.000-04:00

In part 1 of this blog post, I talked about my recent visit to BWPUG to discuss SE-Linux and PostgreSQL, and reviewed the work that has been done so far, as well as a few upcoming patches. In this second part of the post, I'm going to review what I learned at the meeting and where the SE-Linux folks told me they'd like to see us go with this.

One of the most interesting concepts we discussed was the idea of a type transition. This may be old hat for experienced SE-Linux users, but it was a new idea for me. I'm not sure that I understand this concept in its full generality, but Joshua Brindle explained two specific applications of it to me. First, when objects are created, SE-Linux apparently allows the context of that object to depend not only on the context of the creator, but also on where the object was created. For example, if Apache is running in a context called apache_t and creates a temporary file in /tmp, the context of the new file might be apache_tmp_t. Similarly, if a PostgreSQL client creates a table, the SE-Linux folks would like to be able to determine the security label for the table based on a combination of the client's context and the containing schema's label.

The second application of type transitions which we discussed in the meeting related to what KaiGai Kohei has been calling a trusted procedure. The idea here seems to be that when a certain function is executed, SE-Linux should have the option based on the user's context and the function's context to transition to a new security context for just the period of time during which the function is executing. This doesn't involve a kernel call: it's just internal recordkeeping. I'm imaging that SE-Linux support for PostgreSQL will be provided by a loadable module, so essentially we'd need a bullet-proof way of allowing the SE-Linux module to gain control briefly at function entry and exit time (and that would be certain to be called even if, say, we exit the function due to an error condition).

We also talked about SE-Linux control of operations other than DML, which is what the ExecCheckRTPerms hook I talked about in part 1 of this posting will support. In particular, Joshua Brindle and David Quigley were very concerned about proper control over the LOAD statement. It looks like this can be easily accomplished using the existing ProcessUtility_hook. They were also concerned about DDL, but again it seems like the existing ProcessUtility_hook would be sufficient to impose coarse-grained restrictions. Ultimately, that may not be the best way to go forward, as it may not provide easy access to all the bits they care about - in particular, I think we will need one or more special-purpose hooks in ALTER TABLE - but it may be enough to do something crude.

Another very large hole that will need to be plugged is control over large objects. These will need security labels and appropriate access checks.

Finally, the SE-Linux folks indicated that in the long run they would really like to have row-level access control, but they believe that they can accomplish useful things with an implementation which does not include that capability, as long as they have the "trusted procedure" facility discussed above.

I'm not sure how far we're going to get with this work during the PostgreSQL 9.1 time frame. KaiGai Kohei has poured a tremendous amount of time into this work over the last several years, but progress has been slow. I think one of the big reasons for that is that doing this work in a way that is acceptable to the PostgreSQL community can sometimes require significant refactoring of the existing code. It's not always obvious how to accomplish that, and many of the people who are in the best position to carry it off successfully can't put a lot of time into it unless there is funding attached. So far, no one stepped forward in this area; if that changes, I expect to see much more rapid progress.

So, Why Isn't PostgreSQL Using Git Yet?

2010-09-12T07:00:00.000-04:00

Just over a month ago, I wrote a blog posting entitled Git is Coming to PostgreSQL, in which I stated that we planned to move to git sometime in the next several weeks. But a funny thing happened on the way to the conversion. After we had frozen the CVS repository and while Magnus Hagander was in the process of performing the migration, using cvs2git, I happened to notice - just by coincidence - that the conversion had big problems. cvs2git had interpreted some of the cases where we'd back-patched commits from newer branches into older branches as merges, and generated merge commits. This made the history look really weird: the merge commits pulled in the entire history of the branch behind them, with the result that newer commits appeared in the commit logs of older branches, even we didn't commit them there and the changes were not present there.

Fortunately, Max Bowsher and Michael Haggerty of the cvs2git project were able to jump in and help us out, first by advising us not to panic, and secondly by making it possible to run cvs2git in a way that doesn't generate merge commits. Once this was done, Magnus reran the conversion. The results looked a lot better, but there were still a few things we weren't quite happy with. There were a number of "manufactured commits" in the history, for a variety of reasons. Some of these were the result of spurious revisions in the CVS history of generated files that were removed from CVS many years ago; Max Bowsher figured out how to fix this for us. Others represented cases where a file was deleted from the trunk and then later re-added to a back branch. But because we are running a very old version of CVS (shame on us!), not enough information was recorded in the RCS files that make up the CVS repository to reconstruct the commit history correctly. Tom Lane, again with help from the cvs2git folks, has figured out how to fix this. We also end up with a few spurious branches (which are easily deleted), and there are some other manufactured commits that Tom is still investigating.

In spite of the difficulties, I'm feeling optimistic again. We seem to have gotten past the worst of the issues, and seem to be making progress on the ones that remain. It seems likely that we may decide to postpone the migration until after the upcoming CommitFest is over (get your patches in by September 14!) so it may be a bit longer before we get this done - but we're making headway.

SE-Linux For PostgreSQL: Part 1

2010-09-10T16:21:00.000-04:00

I made the trip down to OmniTI headquarters just south of Baltimore, MD this Wednesday for BWPUG. This month's topic was the ongoing project to integrate SE-Linux with PostgreSQL. Besides myself, Stephen Frost, Greg Smith, Robert Treat were all there from the PostgreSQL community, along with David Quigley and Joshua Brindle from the SE-Linux community. It was a very productive meeting and I learned a lot about what the SE-Linux community is looking for from PostgreSQL.

We first discussed the current status of the project. Following discussions with Stephen Frost, KaiGai Kohei, and Greg Smith at PGCon 2010, I wrote and committed four patches which have, I think, helped to clear the way for an eventual loadable module implementing basic SE-Linux support for PostgreSQL; and I also committed a fifth patch by KaiGai Kohei. These were, in order of commit:

1. Add a hook in ExecCheckRTPerms(). This is just a very simple hook to allow a loadable module to gain control at the time DML relation permissions are checked. Whenever a SELECT, INSERT, UPDATE, or DELETE statement is invoked, this function gets a listed of the relation OIDs and can choose to allow the statement to proceed or throw an error. (It could also potentially do other things, like write to a log file, if that were useful for some reason.)

2. Centralize DML permissions-checking logic. KaiGai Kohei spotted the fact that the previous patch didn't actually work for a couple of important cases. In particular, COPY did not previously go through ExecCheckRTPerms(), and there is some hairy code inside the foreign key stuff that also needed adjustment to work properly with this hook. This patch, by KaiGai Kohei, cleaned all of that up. So as far as I know, we now have a single point for all DML permissions checking, and a hook function at that point. Yeah!

Unfortunately, in order to do label-based security, a simple hook function is not enough. You also need a place to store the labels, and ideally that place should be a PostgreSQL system catalog. I had initially thought that we would add a security label column to the system catalog for each object type, but that would require fairly invasive changes across the whole system and carry some minor performance penalty even for people who did not use it. At PGCon, we came up with the idea of storing all security labels for all objects in a separate catalog. Security labels are, in essence, just strings, which we don't try to interpret but which have some meaning (the details of which we need not understand) to an external security provider such as SE-Linux.

As luck would have it, we already have a model for such a facility: the COMMENT statement already knows how to store arbitrary strings which it does not attempt to interpret for arbitrary database objects, using a catalog (actually two catalogs) dedicated to that purpose. Unfortunately, the comment code is quite large, and, as it turned out, buggy, so it didn't seem like a good idea to copy-and-paste it into a new file and then hack it up from there, as I had initially hoped to do. So that led to three more patches.

3. Standardize get_whatever_oid functions for object types with unqualified names. As it turns out, one of the things that the comment code needed to do over and over again was examine the parse tree representation of an object and convert it to an OID by looking up the name in a system catalog. But there wasn't any standard way to do this, and in some cases the code was quite lengthy and already duplicated in multiple places throughout our source base. This patch cleaned that up, by introducing a standard API and adjusting the existing OID-getter functions, or adding new ones, for tablespaces, databases, roles, schemas, languages, and access methods, to conform to that API.

4. Standardize get_whatever_oid functions for other object types. More of the same, this time for text search parsers, dictionaries, templates, and configs; as well as for conversions, constraints, operator classes, operator families, rules, triggers, and casts.

5. Rewrite comment code for better modularity, and add necessary locking. This patch took the refactoring in the previous two patches one step further. The functions in the previous patches provide a way to translate a named object of a different type to an OID. This patch creates a more general API that can be passed an object type and a parse tree and return an ObjectAddress, which is an internal representation that can point to a database object of any type. The ObjectAddress representation is used for management of dependencies between database objects (e.g. you can't drop a table if there's a view using it, unless you also drop the view) as well as by the comment code, and they will be useful for security label support as well.

This new facility also fixes a longstanding locking bug in the COMMENT code, which still exists (and likely won't be fixed) in 9.0 and all prior releases. An object that is dropped concurrently with a COMMENT operation on that same object could lead to an orphaned comment in the pg_description or pg_shdescription catalog. If another object of the same type is subsequently assigned the same OID, it will inherit the orphaned comment. This is fairly unlikely and, for comments, fairly innocuous, but it would obviously create a potential security hole for security labels.

With these preliminary patches in place, I think we're now well-positioned to introduce the major piece of functionality which we will need to support SE-Linux integration: an in-core security label facility for use by SE-Linux and perhaps other label-based security systems. Stephen Frost, KaiGai Kohei, and I have had extensive discussions about the design of this facility and there are currently two pending patches by KaiGai Kohei which are intended to implement that design: one adds the basic security facility and commands for manually applying labels, and the other adds hooks at table creation time to allow enhanced security providers to automatically set a label on newly created tables. I have not yet reviewed these patches in detail, but I hope to see them committed - likely with some modifications - within the next month.

In the second part of this blog post, I'll go over what I learned from David and Joshua (who were extremely helpful in explaining SE-Linux to me), the additional facilities which they felt would be necessary for a minimally useful SE-Linux integration, and what they'd like to see over the longer term.

Version Numbering

2010-08-24T08:16:00.000-04:00

Over the last few days, there's been a debate raging on pgsql-hackers on the subject of version numbering. There are many thoughtful (and some less-thoughtful) opinions on the thread that you may wish to read, but I thought the most interesting was a link posted by Thom Brown to a blog post called The Golden Rules of Version Naming. If you haven't seen it, it's definitely worth a read.

Why We're Conservative With PostgreSQL Minor Releases

2010-08-16T12:50:00.000-04:00

Last week, a PostgreSQL user filed bug #5611, complaining about a performance regression in PostgreSQL 8.4 as compared with PostgreSQL 8.2. The regression occurred because PostgreSQL 8.4 is capable of inlining SQL functions, while PostgreSQL 8.2 is not. The bug report was also surprising to me, because in my experience, inlining SQL queries has always improved performance, often dramatically. But this user managed unluckily hit a case where the opposite is true: inlining caused a function which had previously been evaluated just once to be evaluated multiple times. Fortunately, there is an easy workaround: writing the function using the "plpgsql" language rather than the "sql" language defeats inlining.

Although the bug itself is interesting (let's face it, I'm a geek), what I found even more interesting was that I totally failed to appreciate the possibility that inlining an SQL function could ever fail to be a performance win. Prior to last week, if someone had asked me whether that was possible, I would have said that I didn't think so, but you never know...

And that is why the PostgreSQL project maintains stable branches for each of our major releases for about five years. Stable branches don't get new features; they don't get performance enhancements; they don't even get tweaks for things we wish we'd done differently or corrections to behavior of doubtful utility. What they do get is fixes for bugs (like: without this fix, your data might get corrupted; or, without this fix, the database might crash), security issues, and a smattering of documentation and translation updates. When we release a new major release (or actually about six months prior to when we actually release), development on that major release is over. Any further changes go into the next release.

On the other hand, we don't abandon our releases once they're out the door, either. We are just now in the process of ceasing to support PostgreSQL 7.4, which was released in November 2003. For nearly seven years, any serious bugs or security vulnerabilities which we have discovered either in that version or any newer version have been addressed by releasing a new version of PostgreSQL 7.4; the current release is 7.4.29. Absent a change in project policy, 7.4.30 will be the last 7.4.x release.

If you're running PostgreSQL 8.3 or older, and particularly if you're running PostgreSQL 8.2 or older, you should consider an upgrade, especially once PostgreSQL 9.0 comes out. Each release of PostgreSQL includes many exciting new features: new SQL constructions, sometimes new data types or built-in functions, and performance and manageability enhancements. Of course, before you upgrade to PostgreSQL 8.4 (or 9.0), you should carefully test your application to make sure that everything still works as you expect. For the most part, things tend to go pretty smoothly, but as bug #5611 demonstrates, not always.

Of course, this upgrade path is not for everyone. Application retesting can be difficult and time-consuming, especially for large installations. There is nothing wrong with staying on the major release of PostgreSQL that you are currently using. But it is very wise to upgrade regularly to the latest minor version available for that release. The upgrade process is generally as simple as installing the new binaries and restarting the server (but see the release notes for your version for details), and the PostgreSQL community is firmly committed to making sure that each of these releases represents an improvement to performance and stability rather than a step backwards.

How I Hack on PostgreSQL

2010-08-13T09:40:00.000-04:00

Today's post by Dimitri Fontaine gave me the idea of writing a blog posting about the tools I use for PostgreSQL development. I'm not saying that what I do is the best way of doing it (and it's certainly not the only way of doing it), but it's one way of doing it, and I've had good luck with it.

What commands do I use? The following list shows the ten commands that occur most frequently in my shell history.

[rhaas pgsql]$ history  | awk '{print $2}' | sort | uniq -c | sort -rn | head
 250 git
  57 vi
  31 %%
  25 cd
  24 less
  20 up
  18 make
  13 pg_ctl
  10 ls
   8 psql

Wow, that's a lot of git. I didn't realize that approximately half of all the commands I type are git commands. Let's see some more details.

[rhaas pgsql]$ history  | awk '$2 == "git" { print $3}' | sort | uniq -c | sort -rn | head
  93 diff
  91 grep
  15 log
  10 commit
   8 checkout
   7 add
   6 reset
   5 clean
   4 pull
   3 branch

As you can see, I use git diff and git grep far more often than any other commands. The most common things I do with git diff are just plain git diff, which displays the unstaged changes in my working tree (so I can see what I've changed, or what a patch I've just applied has changed) and git diff master (which shows all the differences between my working tree and the master branch; this is because I frequently use git branches to hack on a patch I'm working on). A great deal of the work of writing a good patch - or reviewing one - consists in looking at the code over and over again and thinking about whether every change can be justified and proven correct.

git grep does a recursive grep starting at the current directory, but only examines files checked into git (not build products, for example). I use this as a way to find where a certain function is defined (by grepping for the name of the function at the start of a line) and as a way to find all occurrences of an identifier in the code (which is an absolutely essential step in verifying the correctness of your own patch, or someone else's).

As you can also see, my preferred editor is vi (really vim). This might not be the best choice for everyone, but I've been using it for close to 20 years, so it's probably too late to learn something else now. I think Dimitri Fontaine said it well in the post linked above: the best editor you can find is the one you master. Having said that, if you do even a small amount of programming, you're likely to spend a lot of time in whatever editor you pick, so it's probably worth the time it takes to learn a reasonably powerful one.

Git is Coming to PostgreSQL

2010-08-07T22:08:00.000-04:00

As discussed at the PGCon 2010 Developer Meeting, PostgreSQL is scheduled to adopt git as its version control system some time in the next few weeks. Andrew Dunstan, who maintains the PostgreSQL build farm, has adapted the build farm code to work with either CVS or git; meanwhile, Magnus Hagander has done a trial conversion so that we can all see what the new repository will look like. My small contribution was to write some documentation for the PostgreSQL committers, which has subsequently been further edited by Heikki Linnakangas (the link here is to his personal web page, whose one complete sentence is one of the funnier things I've read on the Internet).

I don't think the move to git is going to be radical change; indeed, we're taking some pains to make sure that it isn't. But it will make my life easier in several small ways. First, the existing git clone of the PostgreSQL CVS repository is flaky and unreliable. The back-branches have had severe problems in this area for some time (some don't build), and the master branch (aka CVS HEAD) has periodic issues as well. At present, for example, the regression tests for contrib/dblink fail on a build from git, but pass on a build from CVS. While we might be able to fix (or minimize) these issues by fixing bugs in the conversion code, switching to git should eliminate them. Also, since I do my day-to-day PostgreSQL work using git, it will be nice to be able to commit that way also - it should be both faster (CVS is very slow by comparison) and less error-prone (no cutting and pasting the commit message, no forgetting to add a file in CVS that you already added in git).

Multi-Tenancy and Virtualization

2010-07-29T11:02:00.000-04:00

In a recent blog post on Gigaom, Simeon Simeonov argues that virtualization is on the way out, and discusses VMware's move toward platform-as-a-service computing. In a nutshell, his argument is that virtualization is inefficient, and is essentially a last resort when legacy applications can't play nicely together in the same sandbox. In other words, the real goal for IT shops and service providers is not virtualization per se, but multi-tenancy, cost-effective use of hardware, and high availability. Find any two servers in the average corporate data center and ask why they're not running on the same machine. It's a good bet you'll get one of the following four answers: (1) machine A is running a piece of software that misbehaves if run on the same machine as some piece of software running on machine B, (2) a single server couldn't handle the load, (3) one of those servers provides redundancy for the other, or (4) no particular reason, but we haven't gotten around to consolidating them yet. In my experience, the first answer is probably the most common. But as Simeonov points out, the ideal solution is not virtualization, but better software - specifically, platforms that can transparently service multiple customers.

PostgreSQL is very strong in this area. Hosting providers such as hub.org provision databases for multiple customers onto a single PostgreSQL instance; and here at EnterpriseDB, we support several customers who do much the same thing. Databases in PostgreSQL provide a high degree of isolation: many configuration parameters can be set on a per-database basis, extensions can be installed into a single database without affecting other databases that are part of the same instance, and each database can in turn contain multiple schemas. The ability to have multiple databases, each containing multiple schemas, makes the PostgreSQL model more flexible than Oracle or MySQL, which have only a single tier system. In the upcoming PostgreSQL 9.0 release, the new grant on all in schema and default privileges features will further simplify user administration in multi-user and multi-tenant environments. Behind the scenes, a PostgreSQL instance uses a single buffer pool which can be efficiently shared among any number of databases without excessive lock contention. This is critical. Fragmenting memory into many small buffer pools prevents databases from scaling up (using more memory) when under heavy load, and at the same time prevents databases from scaling down (using less memory) when not in use. By managing all databases out of a single pool, PostgreSQL can allow a single database to use every block in the buffer pool - if no other databases are in use - or no blocks at all - if the database is completely idle.

Simeonov seems to feel that virtualization has already nearly run its course, and predicts that the market will hit its peak within three years. That doesn't seem likely to me. I think there is an awful lot of crufty hardware and software out there that could benefit from virtualization, but it's working right now, so no one is eager to make changes that might break something. As the physical equipment starts to fail, IT administrators will think about virtualization, but hardware that isn't touched can sometimes run for a surprisingly long time, so I don't expect server consolidation projects to disappear any time soon. More importantly, Simeonov seems to assume that all new applications will be developed using platform-as-a-service architectures such as Google App Engine, Bungee, Engine Yard, and Heroku. While some certainly will be, it seems unlikely that the traditional model of application development, using a dedicated web server and a dedicated database running on a physical or virtual machine will disappear overnight. For one thing, choosing one of those vendors means being locked into that vendor's API - and choice of programming language. Bungee and Heroku are Ruby environments, for example, while Google App Engine offers Java and Python. Good luck making the switch!

So, if plain old virtual machines are going to be around for a while, how does PostgreSQL stack up in that environment? Not too bad. Of course, write-intensive workloads will suffer from the generalized slowness of virtualized I/O. But PostgreSQL is designed to run well even in a very small memory footprint, to take good advantage of the OS buffer cache and process scheduler, and to be portable across a wide variety of platforms. If your database is small enough to fit in memory, performance should be good. And if your database isn't small enough to fit in memory, there's not much point in virtualizing it: you're going to need a dedicated machine either way.

Google and our Documentation

2010-07-25T20:58:00.000-04:00

As I mentioned in a previous blog post, trying to find pages in the PostgreSQL documentation using Google doesn't work very well: most often, one gets links to older versions.

A recent thread on pgsql-performance (somewhat off-topic for that mailing list, but that's where it was) suggested that perhaps we could use Google's canonical URL feature to work around this problem.

Another suggestion was that we ask people who link to our docs to link to http://postgresql.org/docs/current/ (or some sub-page) rather than linking to a specific version (e.g. the same URL with 8.4 in place of current). That way, as new versions come out, everyone's links will still be pointing at the latest version of the docs, helping the new versions accumulate "Google karma" more quickly than they would otherwise. Or at least, that's the idea: I have no idea whether it would actually work.

Best Patches of 9.1CF1

2010-07-22T15:18:00.000-04:00

Although PostgreSQL 9.0 isn't out yet, we began the first CommitFest for PostgreSQL 9.1 development on July 15, 2010. Our goal is to review every patch submitted by then before August 15. While we're only a week into the CommitFest, I already have some favorite patches: none of which are committed yet, so they might die, get withdrawn, changed, etc. But here they my top picks.

1. Simon Riggs wrote a very nice patch to reduce the lock level required for various DDL statements. We haven't yet come up with clearly workable ideas for allowing multiple DDL statements to execute on the same table at the same time, but what this patch will do is allow certain DDL commands to run in parallel with DML. Some versions of ALTER TABLE will lock out everything (as they all do, presently), some will lock out INSERT/UPDATE/DELETE/VACUUM statements but allow SELECT to run in parallel, and some will only lock out concurrent DDL and VACUUM operations (like ALTER TABLE ... SET WITHOUT CLUSTER). This should be really nice for anyone administering a busy database.

2. My employer, EnterpriseDB, asked me to write a patch that reduces the size of most numeric values on disk. This was based on a design proposal from Tom Lane a few years ago, and turned out to be pretty simple to code up. Currently, our numeric data type always has a 4-byte header specifying the weight of the first digit and display scale. For the values people typically do store, that's overkill. This patch allows a 2-byte header to be used opportunistically, when we can cram everything in; but the old format can still be understood, so it doesn't break pg_upgrade. It'll be interesting to see whether people can see a noticeable change in the size of their disk footprint when this patch is used. And maybe we could even get by with a 1-byte header sometimes... but that's a thought for another patch.

3. Kevin Grittner posted a patch to implement true serializability. I haven't studied the code in detail, and I'm not sure how soon we can hope to see this committed, but it's pretty cool. Our current serialization techniques are pretty good, but this should be a whole lot better whose application logic relies heavily on the absence of serialization anomalies.

Distributed Serialization Anomalies

2010-07-07T10:51:00.000-04:00

One of the more difficult responsibilities of a database is to provide you with the illusion that transactions on the system are executed sequentially, one after another, while in fact allowing as much parallelism as possible. PostgreSQL's MVCC implementation does this using "snapshots": each statement (or, if you choose the serializable isolation level, each transaction), upon first access to the database, records which transactions have committed as of that moment, and everything it does afterwards will see the effect of those transactions, but not any transactions committed later. (There are some exceptions to this rule when using READ COMMITTED mode with INSERT, UPDATE, or DELETE statements.)

This produces, more or less, the illusion that SQL statements execute sequentially, with each one completing its work before the next one begins. This illusion is extremely important and valuable in many real-world applications. For example, if you transfer money from your checking account to your savings account, a banking application might insert two new rows into the "banking_transactions" table: one to show the debit from checking, and another to show the credit to savings. It wouldn't be good if some other query saw just one of these two new rows: it would look as if the money disappeared from checking but did not appear in savings, or perhaps as if it had appeared in savings without disappearing from checking. You'd be unhappy about the first scenario, and the bank would be unhappy about the second one. This type of scenario is called a serialization anomaly, and databases are responsible for preventing them. In this case, it's pretty easy to make sure this problem can't happen: just do both inserts within a single transaction, and then commit it.

Things get a little trickier when there's more than one database involved. Suppose that I'm moving money from my account (at one branch) to my friend Magnus's account (at a different branch of the same bank). As before, we must make two transaction entries: one showing the debit to my account, and the other showing the credit to his account. We can start transactions on both nodes and do the inserts, but it's not possible to commit both transactions at the very same instant: there could always be a crash after one transaction commits, but before the other one commits.

We can work around this problem to some extent using a protocol called two-phase commit: we'll issue a "PREPARE TRANSACTION" command in both transactions, which should be enough to guarantee that a subsequent "COMMIT PREPARED" command, even after an intervening crash, has no chance of failure. So, we start a transaction on each database, do an insert on each database, prepare both transactions, and then commit both transactions. If there's a crash (or loss of connectivity) after either transaction is prepared but before both transactions are committed, we can still get things back to a consistent state once things are back up again. How? We look to see if either transaction committed; if so, we commit the other one. If not, we see whether both transactions were succesfully prepared; if so, we can commit or abort both; if not, we must abort both.

This solves the problem of making sure that no money can be permanently lost (or created), but there will still be a period of time during which we can see inconsistent views of the system as a whole. Imagine that the bank auditor comes along and runs a report across all bank branches adding up the bank's assets and liabilities. It's possible that he'll query one of the two databases involved in our hypothetical funds transfer before the transaction commits on that node, but by the time he visits the other one, it's committed - therefore he'll see the transferred funds either in both accounts, or in neither one, depending on the order in which he hits the different branches. This is a distributed serialization anomaly.

Distributed serialization anomalies are much harder to avoid than regular serialization anomalies (which are a hard problem all by themselves). One method - which is used by Postgres-XC - is to have a single authority (which Postgres-XC calls a global transaction manager) which hands out snapshots and transaction IDs across all nodes in the cluster; regrettably, there is a potential for this to become a bottleneck, or a single point of failure (see Postgres-XC_Write-Scalable_Cluster.pptx, slides 10 and following).

Unfortunately, there may not be many good alternatives. There is a technology called commitment ordering which seems to have a long paper trail[1] in the academic literature, and which has been studied in relation to MVCC. The good news is that commitment ordering does not require a global coordinator of any kind; each node operates independently and does not even need to know the identities of the other nodes, or even how many exist. It requires no additional communication of any kind. The bad news is that it operates by aborting potentially problematic transactions, and it might end up aborting quite a lot of them. The rule is simply that the serialization order must match the commit order; so if transaction A reads x and writes y, transaction B reads y; and then transaction A commits, the system will abort B (because there could be a read-write dependency cycle between A and B involving another database).

Another alternative is to build up a commit-order dependency graph that spans all the databases involved in the transaction. That is, we imagine a graph with each unaborted transaction as a vertex. If A reads or updates a row and B subsequently updates it, we add an edge from A to B. If A updates a row and B subsequently reads the updated version (or a later version), we also add an edge from A to B. If, at any time, adding an edge to the graph would create a cycle, we abort one of the constituent transactions. Kevin Grittner and Emmanuel Cecchet pointed out a paper by Michael Cahill on this topic[2]; one of the advantages of this approach is that it is possible to prevent all serialization anomalies, which our current approach does not. Kevin and Dan Ports have proposed a patch for 9.1 which would implement true serializability for a single PostgreSQL database, but it's not clear that this would scale well to a distributed system.

[1] e.g. The Principle of Commitment Ordering, or Guaranteeing Serializability in a Heterogeneous Environment of Multiple Autonomous Resource-Managers, Yoav Raz, 1990 [PDF].
[2] Serializable Isolation for Snapshot Databases, Michael J. Cahill, Uwe Röhm, Alan D. Fekete, 2006 [PDF].

Concurrent Development

2010-07-05T12:10:00.000-04:00

PostgreSQL 9.0 beta 3 will be wrapped in the next few days, and at the same time, we'll be branching the tree to begin 9.1 development. This is a new thing for us. In the past, we've waited until the previous release was shipped before opening the tree to new development. However, at the PGCon 2010 development meeting, we decided to try something different this time.

I believe that the primary motivation for this change was that, as we get closer to release, there are fewer and fewer issues to work on, and fewer and fewer people who can be involved in fixing them. So, waiting until release to branch the tree leaves a substantial portion of the developer community sitting idle. A second advantage is that it shortens the time between releases - our tentative plan is to use the same release schedule for 9.1 that we did for 9.0. The first CommitFest for 9.0 began on July 15, 2009, and the first CommitFest for 9.1 will begin on July 15, 2010; the last CommitFest for 9.0 began on January 15, 2010, and the last CommitFest for 9.1 will begin on January 15, 2011. Of course, the actual release date will almost certainly be different, but the plan is for feature freeze to happen about the same time next year that it did this year, so that we can continue to have releases about a year apart.

Of course, the danger of concurrent development is that the work people are doing for 9.1 may distract us from finishing 9.0. Hopefully that won't happen, because I think there is a lot to like about the new process.

PostgreSQL as an In-Memory Only Database

2010-06-24T11:00:00.000-04:00

There's been some recent, interesting discussion on the pgsql-performance mailing list on using PostgreSQL as an in-memory only database. In other words, you basically want to use it as a cache, similar to the way that you would use memcached or a NoSQL solution, but with a lot more features.

If you're interested in doing this, you'll need to configure the system so that you have a convenient, automatic way erase the database cluster and reinitialize it (using initdb) after an operating system crash. Per discussion on the mailing list, for best performance, it seems best to set up the data directory on a tmpfs and configure the following parameters in postgresql.conf:

fsync=off
synchronous_commit=off
full_page_writes=off
bgwriter_lru_maxpages=0

With fsync=off, and most likely also with full_page_writes=off, your database will not be crash-safe - but you don't care, because you're planning to start from scratch after a crash anyway. If you're familiar with postgresql.conf parameters, setting synchronous_commit=off might seem redundant if you've already set fsync=off, but testing reveals that it still boosts performance. Turning off full_page_writes and bgwriter_lru_maxpages eliminates I/O that isn't needed for this use case.

On a related note, Gavin Roy gave a talk at PGCon comparing the performance of PostgreSQL with fsync=off with a number of NoSQL databases. The results were pretty good, but there might even be room for improvement with some additional tuning.

If you end up testing a configuration along these lines, please post a comment here or on the pgsql-performance mailing list with your experiences.

Working Toward PostgreSQL 9.0 Beta3

2010-06-23T16:47:00.000-04:00

We are gradually creeping toward the release of PostgreSQL 9.0, but there's still a ways to go. We're continuing to get several bug reports per week about problems in 9.0 beta 2 - many thanks to all those who have tested and reported bugs. Here are some of the issues we've resolved in CVS since beta2:

- Fix a problem that could cause checkpoints to happen too infrequently when using streaming replication, with certain combinations of settings (Fujii Masao).
- Fix quoting problems in EXPLAIN (FORMAT YAML) output (Dean Rasheed).
- Fix a problem that could cause a "cache lookup failed for type %d" error when using PL/python (Tom Lane).
- Change pg_last_xlog_receive_location() and pg_last_xlog_replay_location() to return NULL instead of 0/0 when they do not apply (Heikki Linnakangas).
- Rename restartpoint_command to archive_cleanup_command, to clarify what it's for (Itagaki Takahiro).
- Allow replication connections to use a "replication" entry in .pgpass (Fujii Masao).
- Fix the newly added vacuumdb -Z option (Josh Berkus).
- Have pg_upgrade create its output files in a less surprising location (Bruce Momjian).
- Fix ALTER LARGE OBJECT and GRANT ... ON LARGE OBJECT to not break when an OID too large to be represented by a signed integer is used (Robert Haas).
- Avoid entering a tight loop when streaming replication hits a corrupt WAL record (Heikki Linnakangas).
- New contrib module for use as an archive_cleanup_command (Simon Riggs).
- Adjust GUC categories to match the docs (Itagaki Takahiro).
- Deprecate the use of => as an operator name, and remove or rename the new => operators in 9.0, so we can eventually use this for the purpose the SQL standards committee has in mind (Robert Haas).
- Revert apparently-useless code to add symbol table entries for PL/perl functions (Andrew Dunstan).
- Avoid calling malloc(0) in pg_upgrade (Bruce Momjian).
- Don't allow WAL to be streamed to the standby until it has been fsync'd on the master - otherwise, a master crash can effectively corrupt the standby database (Fujii Masao).
- Fix pg_upgrade problems on Win32 (Bruce Momjian).
- Various documentation improvements.

If you haven't yet beta-tested PostgreSQL 9.0, there's no time like the present! Ideally, load up your production data, run your production application against it, and let us know whether anything breaks. Or, pull the plug a few times and see if anything goes wrong; or try to break Streaming Replication or Hot Standby. This is shaping up to be a great release - but it's not done yet.

Making PostgreSQL Faster

2010-06-13T22:20:00.000-04:00

Although we've made great progress in speeding up PostgreSQL over the last few years, there's always more to be done. Performance, with PostgreSQL as with any other database, is largely determined by the availability of three resources: CPU, memory, and disk. What could we do to use each of these resources most efficiently?

PostgreSQL is already pretty efficient at using the CPU. For high-concurrency databases, I don't anticipate that things will get much better than they already are. For low-concurrency databases, we need parallel query - that is, the ability to use more than one CPU to process the same query.

Memory is a little bit more of a problem. We do a good job keeping our memory footprint small, but we don't manage it terribly well. work_mem limits the maximum size of a sort or hash, but takes no account of current conditions: if the system is swapping due to memory pressure, you get the same plan as if the system has 40GB of free memory. And all the memory allocated to shared_buffers remains allocated even when it isn't truly needed.

I/O is perhaps the biggest problem. I don't think this problem is unique to PostgreSQL - I believe all databases probably share this pain point to some degree. Disks are slow. With respect to PostgreSQL specifically, there are a number of things we need to do to minimize our I/O bandwidth, including index-only scans and further improvements to VACUUM. Partial vacuum (implemented in 8.4) is a pretty big deal, but there's more that needs to be done.

We also need to put more effort into minimizing our on-disk format and WAL volume. The actual disk space is cheap, but the time needed to read and write a larger volume of data hurts performance.

Why Join Removal Is Cool

2010-06-08T13:12:00.000-04:00

When people talk to me about the (limited implementation of) join removal that will be part of PostgreSQL 9.0, the conversation usually goes in two ways. Some people ask how the feature works and then say something like "oh, I guess that could be useful every once in a while". Other people already know exactly how the feature works and usually say some variant of "this is an amazingly wonderful feature that I am looking forward to with great enthusiasm".

The difference between these two groups of people (I think) is not so much their level of technical knowledge or how closely they've been following pgsql-hackers, but their use case. If your database is primarily a data warehouse, my guess is that you won't have many occasions to benefit from join removal. Where this feature really comes in handy is in OLTP workloads with highly normalized data, in situations where users are generating queries against views (perhaps through some sort of reporting interface) and expecting to get results back immediately.

Let's take an example. Suppose you're writing a bug-tracking system. Each bug has a number of properties associated with it: who reported it, who's working on it, current status, priority, date opened, release for which it's slated to be fixed, date of last status change, date resolved, people who want to get an email when it's updated, comments, etc. If like me you're a big fan of database normalization, you'll not want to store all of these as text fields. So you might end up with a table like this:

CREATE TABLE bug (
id serial,
reporter_id integer not null references person (id),
assigned_to_id integer references person (id),
status_id integer not null references bug_status (id),
priority_id integer not null references priority (id),
target_release_id integer references release (id),
open_date date not null,
...
primary key (id)
);

You'll probably also end up with some supplementary tables for the items that can exist multiple times, like bug_comment and bug_watchers. Now, to make reporting easier, you'll probably want to define a view over the bug table that joins to all the other tables, so that it's easy to get the text values for the reporter, status, etc.

CREATE VIEW bug_view AS
SELECT
b.id,
b.reporter_id, r.name AS reporter,
b.assigned_to_id, at.name AS assigned_to,
b.status_id, s.name AS status,
b.priority_id, p.name AS priority,
b.target_release_id, tr.name AS target_release,
b.open_date,
...
FROM
bug b
JOIN person r ON b.reporter_id = r.id
JOIN bug_status s ON b.status_id = s.id
JOIN priority p ON b.priority_id = p.id
LEFT JOIN person at ON b.assigned_to_id = at.id
LEFT JOIN release tr ON b.target_release_id = tr.id;

And now you can pretty easily write an engine that will let users select the columns they'd like to see from bug_view and the filter conditions they'd like to apply (only open bugs, only bugs slated to be resolved in release X, etc.) via a spiffy web interface. Note that the reporter, bug status, and priority fields can't be null, so we can use a plain old JOIN, but the bug might be assigned to no one or have no target release, so we use LEFT JOIN in those cases. (Otherwise, rows where those fields were NULL would not appear in the output.)

Over time, you'll tend to add more fields. Scalar fields like open_date don't cause much heartache, but as you add more fields that require joins, your view will tend to slow down. Some people might say that the answer is simply to denormalize - use natural keys in the bug table, and don't join. While that solution may be appropriate for some people, it is not without its downsides: database normalization was invented for a reason. The good news is that PostgreSQL is fast and has an excellent query planner, so even fairly complex queries run quite quickly. The bad news is that every query against the view is going to hit every table that's part of the view definition, so if you add enough of them, it's eventually going to be slow.

And, realistically, most of the time, users aren't going to want all the columns anyway. In a web application, 8-12 columns of output in an HTML table is typically about as much as you can squeeze in without starting to have a lot of line wrapping issues. This leads pretty naturally to the following question: if you don't need all of the columns, can you skip some of those joins and speed up the query?

Yes. In PostgreSQL 9.0, we can drop a join against a base table if (1) it's a left join, (2) there is a unique index on all or a subset of the join columns, and (3) none of the attributes from the nullable side of the join are used elsewhere in the query. So, in the above example, we could skip the joins to person at or release tr if the assigned_to or target_release columns, respectively, are not selected, assuming those tables have unique indexes on their id columns (if they don't, the join might change the number of rows in the output, so we must perform it).

We can't skip joining to any of the other tables, because those are inner joins. That's an implementation restriction which I hope will be lifted in PostgreSQL 9.1, but some more logic is needed to make that safe. In the meantime, a useful workaround may be to write those joins as LEFT JOINs rather the INNER JOINs, in cases where either join type will produce the same results.

An Excellent Developer Meeting

2010-05-24T17:00:00.000-04:00

I'm really pretty fired up about the results of our PGCon 2010 Developer Meeting. Of course, the list of what everyone plans to work on is pretty interesting, and if we got even a fraction of those features we'd have a pretty awesome release. But that's not really what got me fired up. What I'm excited about is some of the new and innovative thinking on replication and clustering - or, at any rate, it was new to me.

Two concepts in particular stand out for me. First, we discussed the ability to give replication solutions a crash-recoverable view into transaction commit order, a point which Jan Wieck has since expanded into a full-blown design proposal. Jan believes that this infrastructure will be useful not only for his own project, Slony, but also for other replication solutions such as Londiste which also operate by replaying transactions in commit order. As I understand it, one of the major advantages of this approach is that it eliminates the need for a global shared counter to track the order of writes (which becomes a bottleneck). Instead, they can be tagged with their order within the top-level transaction, and then the transactions as a whole can be ordered using the transaction commit ordering information.

Second, I was very interested in our discussion of a global transaction manager, for which I unfortunately do not have a good link for further reading. One possible way of avoiding cross-node serialization anomalies in a distributed database environment is to have a single node which knows about all in-flight transactions and hands out snapshots that are coherent across the entire cluster. Postgres-XC takes this approach, but there might be value in integrating something like this into core PostgreSQL. We might imagine allowing one PostgreSQL instance to be configured as a "snapshot provider" and another instance to subscribe to it. Right now, it's not clear that there's enough benefit to core PostgreSQL from accepting a patch along these lines, but there are several ways that might change as our distributed computing capabilities improve. For example, if we had a significant SQL/MED implementation, we'd need to think about how to do serialization correctly across multiple nodes; there might also be applications as we work to expand the capabilities of Hot Standby.

If your eyes are glazing over at this point, you're probably not alone. These features are fairly esoteric. Still, I think the fact that we're starting to seriously talk about this topics and consider integrating some of them into core shows that we're starting to understand better what the real needs are for replication and clustering. As our understanding of those needs continues to improve, I expect to see more capabilities in core PostgreSQL, but perhaps even more importantly, an even stronger set of tools around core PostgreSQL that will make it progressively easier to scale horizontally. I don't expect this to happen overnight, but I feel like we're moving in the right direction.

Global Temporary and Unlogged Tables

2010-05-20T09:10:00.001-04:00

From a technical standpoint, temporary tables in PostgreSQL have three properties that distinguish them from ordinary tables:

1. They're stored in a special schema, so that they are normally visible only to the creating backend.
2. They are managed by the local buffer manager rather than the shared buffer manager.
3. They are not WAL-logged.

It makes sense to think about removing these properties one by one, in the order listed above. Removing just the first property, without doing anything else, doesn't quite make sense, because a table which is managed by the local buffer manager can't be simultaneously accessed by multiple backends. We could work around this by having each backend access a separate set of files. This would give us a global temporary table - that is, a table which is visible to everyone, but each backend sees its own contents. (There is some debate about whether this is the right name, or what the right name for this concept might be - but that's what I'm calling it for now.)

Removing both of the first two properties also makes sense. It gives us an unlogged table - that is, a basically ordinary table for which no WAL is written. (Again, the naming is debatable.) Such tables are not crash-safe: an unexpected system crash could leave the table hopelessly corrupted. The only obvious workaround for this problem is to truncate the table on every system restart.

Why might someone want these new table types? Global temporary tables are appealing for users who need temporary tables with a relatively fixed structure, and don't want to recreate them in every new session. In addition to administrative convenience, this avoids the overhead of repeatedly creating and vacuuming the system catalog entries associated with the temporary tables, which may be a performance benefit for some users.

Unlogged tables are appealing for data that needs to be shared across backends, but which we're willing to lose in the case of a server restart. For example, consider a web application maintaining a table of active user sessions. If the server restarts, we may be willing to lose this data. Everyone will need to log in again, but considering that database crashes are rare, that may not be such a big deal. Unlogged tables also won't be replicated to standby servers, since replication relies on WAL. But, on the plus side, skipping WAL-logging should hopefully yield a significant performance benefit.

I'm going to be working on implementing both of these table types for PostgreSQL 9.1. In each case, the hardest part seems to be making sure that we clean up properly after a crash or server restart.

Lots and Lots of PostgreSQL Feature Requests

2010-05-10T21:20:00.000-04:00

I was surprised and pleased to see that my last blog post, concerning possible directions for future PostgreSQL development, got about five times as many page views as my previous five posts put together, and a total of 70 comments (to date). This may be partly because it got syndicated on LWN, where a few more comments were also posted. I've gone through the comments posted on the blog itself and on the LWN article and counted up the number of times each feature was mentioned. Of course, this is totally unscientific, but it matches up fairly well to the results of previous surveys and gives me an excuse to talk about a bunch of interesting features.

1. Materialized Views (12). See my previous post on Materialized Views in PostgreSQL. Long story short, we may quite possibly get a simple version of this in PostgreSQL 9.1, but I suspect a lot more work will be needed to meet some of the use cases people have in mind.

2. Multi-master replication (6). This is more or less the holy grail of database geeks; it's a pretty hard problem. As it turns out, there is a project in the works called Postgres-XC which does just this. I hope to learn more about this project next week at PGcon. My understanding is that it currently supports only a subset of the SQL query types supported by PostgreSQL, but that work is underway to remove these limitations. Currently, none of the Postgres-XC code can be considered for including in core PostgreSQL because it uses a different license (LGPL), but it's still very interesting as an independent project.

3. Index-organized tables and/or index-only scans and/or automatic maintenance of CLUSTER order (6). I've grouped all of these features together because they're really driving toward the same underlying goal: reducing the I/O cost of an index scan. PostgreSQL will most likely not implement index-organized tables in the sense that Oracle has them, wherein, as I understand it, the table data is stored in the leaf pages of an index. However, we probably will implement index-only scans, which will allow us to gain some of the same performance benefits. Automatic maintenance of CLUSTER order would help, too, but I am not aware that anyone is currently working on that project.

4. MERGE (6). There is a (very ambitious) Google Summer of Code project to implement this feature. Stay tuned. If you're curious about what this feature actually does, I think Simon Riggs has written the best description of MERGE I've seen so far, together with some discussion of the implementation issues. A few weeks later he discussed he followed up with some further notes on the design of the feature.

5. Partitioning syntax (5). Itagaki Takahiro proposed a patch to implement this for PostgreSQL 9.0, but we simply ran out of time. I am hopeful that this will come back to life and be improved and eventually committed for PostgreSQL 9.1. The patch as proposed would have allowed the use of dedicated syntax to specify partitions and would have automatically created appropriate CHECK constraints for each partition, but would not have automatically routed INSERTs to the proper partition, which I think is necessary to make this really useful. Of course, others may have different opinions. :-)

6. Parallel query execution (5). This is a very good idea and yet also a very hard problem; I am not aware that anyone has even proposed a possible design for this, yet alone attempted to implement it. If we implement better SQL/MED support, it might be possible to get some of the benefits of this feature by spreading out data across multiple nodes but making it all appear local by creating remote tables. Or, it might be possible to leverage some of the I/O bandwidth of remote nodes by adding a feature to support non-local tablespaces (with some kind of specialized daemon process reading and writing remote pages on request). But neither of these are spot-on: what we really want is the ability to parallelize a query on a single node.

7. Procedures that can BEGIN, COMMIT, or ABORT transactions (4). This is another feature that would be great to have, but I am not aware that anyone is currently working on it.

8. Granular collation support (4). There are really two halves to this project. The SQL standard specifies a set of complex rules for determining which collation should be used for a particular comparison or ORDER BY operation. In PostgreSQL, you could imagine setting the collation for a particular setting using the "SET" command; associating a collation with a particular column; or overriding the collation for a particular instance of ORDER BY or a particular use of the < operator. So, one half of this problem is simply being able to recognize which collation applies in a particular context and doing the sort or comparison under that collation. The other half of the problem is extending our indexing system to handle multiple collations - either the ability to create an index with a particular collation (which can then be used to satisfy queries that pertain to that collation) or even the ability to create a single index which can somehow answer queries pertaining to multiple collations.

9. Better drivers (4). JDBC was mentioned twice, and asynchronous drivers were mentioned twice. It was also suggested that we should have an "official" Python driver. As a project, we've generally been wary about endorsing other projects, perhaps to our detriment. But good things seem to be happening with the psycopg2 project, especially the recent license change.

10. Graph database features (3). At least one person observed that you can do some of what is wanted here using common table expressions, also known as WITH queries, which are supported beginning in PostgreSQL 8.4. But several people seem to feel that we should have more graph support; one poster mentioned algorithms such as all-pairs shortest paths. I don't have a clear idea of what is needed here, but I suspect that some of the things people are looking for here could be implemented as PostgreSQL extensions. It would be interesting to see some more detailed requirements.

11. Documentation (3). I was surprised to see several requests for documentation among the comments, since I have generally found the PostgreSQL documentation to be superb and one of the great strengths of the project. But one poster did hit on an issue which I think is entirely legitimate: if you Google something like "PostgreSQL documentation", you get a link to our main documentation. But if you Google "PostgreSQL ALTER TABLE", you get a link to the documentation for ALTER TABLE in PostgreSQL 8.1, whereas you might hope to get a link to the 8.4 version of the documentation for that command. And if you Google "PostgreSQL setting", well, let's just say you don't get a link to a page that tells you how to change PostgreSQL settings. If you actually go to the documentation page and navigate through it manually, it's quite easy to find what you're looking for, but there must be something about our site that make Google fail to grok it properly.

Still another poster was looking for documentation in Chinese. Currently, I believe that we maintain documentation only in English due to the rather large translation effort that would be involved in keeping documentation up to date in multiple languages. In fact, we currently don't even ship Chinese translations of our error messages, due to the fact that our existing set of translations is too incomplete. If you would like to help localize PostgreSQL for your native language, please see our wiki page on NLS. Volunteers are needed!

Big Ideas

2010-05-05T12:05:00.000-04:00

Each release of PostgreSQL features dozens, if not hundreds, of small improvements over the previous release; and a few really major new features. Of course, not everyone will agree on what the best new features are. In the forthcoming 9.0 release, pretty much everyone seems to agree that Streaming Replication (standby servers can receive WAL incrementally rather than in 16MB chunks) and Hot Standby (standby servers can execute read-only queries) are the big new features. In 8.4, I think the biggest improvements were the addition of window functions and common table expressions and the self-tuning free space map, which eliminated a major potential source of hassle for database administrators. In 8.3, we got several dramatic performance improvements - HOT, checkpoint spreading, and improvements to the background writer.

So, what's next? What should our community be focusing on for PostgreSQL 9.1? If you've been following developments on pgsql-hackers, you might be tempted to pick out some of the big patches that weren't completed in time for 9.1, like KNNGIST (use indices to accelerate queries that do ORDER BY , as when you want to find, say, the nearest points to a given circle), improved partitioning support (built-in syntax to reduce manual setup), and index-only scans (reduce I/O for index scans by opportunistically skipping or postponing tuple visibility checks). Another possible source of ideas is to look at the features that will be part of 9.0 and think about ways in which they could be further improved: streaming replication that is synchronous rather than asynchronous, or allowing Hot Standby to pause and resume WAL replay, or narrowing the set of circumstances under which Hot Standby needs to cancel a query in order to proceed with WAL replay. You could also look at our TODO list, or previous surveys and blog postings on this topic.

But I think it might be good to step back and look at the problem a bit more broadly. Ignoring for a minute what people actually are working on, what should they be working on? Where is PostgreSQL strong as a product and where does it need improvement? Here are a few things to think about - please leave a comment with your thoughts.

1. Performance and Scalability. When I first started using PostgreSQL, the product had a reputation for being slow, principally because of issues relating to VACUUM. That reputation wasn't entirely justified even back then, and I think we've made enormous progress here in 8.3 and 8.4, but there might be more improvements we can make. Where are the remaining bottlenecks? What features can we provide to make better use of machines with lots and lots of RAM, CPU, and/or I/O bandwidth? Or what if you have more than one machine available? Are there classes of queries that we don't optimize well, and could do better with?

2. SQL Features. We're pretty close to having all of the SQL features required by the standard - and many of the ones that are left are not terribly interesting, which may be why no one has gone to the trouble of implementing them. Still, there are still a few more things that would be nice to have, including updateable views, materialized views, LATERAL(), and global temporary tables Do you need these features? Are there other things we're missing?

3. Indexes and Data Types. We currently support btree, hash, gin, and gist indices; hash indices are somewhat limited at present because they are not WAL-logged, and therefore can become corrupted after a system crash. We also support a broad array of datatypes, including all of the usual base types plus user-defined record and enum types, an XML datatype, and the ability to create new datatypes using PostgreSQL's powerful extensibility model. What else do we need, or what that we already have needs improvement?

4. Procedural Languages. Our core distribution includes support for SQL functions and four procedural languages: PL/pgsql, PL/perl, PL/python, and PL/tcl. Additional languages such as PL/R and the ever-popular PL/LOLCODE are available as extensions. It seems unlikely to me that we need more procedural lanaguages, but the existing ones might need improvement.

5. Security. Security can be further subdivided into connection security (preventing the bad guys from connecting to your database) and database privileges (allowing access to some of the data in the database, but not all of it). Two major features that we don't have in this are row-level security and SE-Linux integration, but there may be other things as well. What are they and which ones are important?

6. Administration. I think that the simplicity of administering PostgreSQL is one of its greatest strengths: installing PostgreSQL doesn't mean that you need to hire a dedicated DBA. Still, there's always something that's hard to do. What is it?

7. Replication and High Availability. I alluded to some possible projects in this area near the top of this post, and of course some of our needs in this area will continue to be met by third-party projects, such as Slony, Bucardo, and Londiste, but there may be other enhancements needed in the core product, also.

8. Other Stuff. What else is there?

Write-Ahead Logging in PostgreSQL 9.0

2010-04-29T16:12:00.000-04:00

PostgreSQL 9.0 will feature a new setting, currently called wal_level (we still have a ways to go before the final release, so that could change, but probably not), which will control the amount of information written to the system's write-ahead log. There are three levels:

- "minimal" will write just enough information to the log to ensure proper crash recovery.

- "archive" will write enough extra information to support a standby server.

- "hot_standby" will write the same information as "archive", plus enough additional information to allow the standby server to run queries (the feature popularly known as "Hot Standby").

If you're familiar with setting up a standby server in releases prior to 9.0, you may know that this behavior was controlled in 8.4 and prior by setting the configuration variable called "archive_mode". In 8.4 and prior, archive_mode controls not only the amount of information written to the write-ahead log (WAL), but also whether the archiver process is started and whether unarchived segments are retained on the master. While this design worked OK for earlier releases, we outgrew it in 9.0 due to the addition of two new features: streaming replication and hot standby.

In 8.4, archive_mode controls three separate behaviors: what information is written to WAL, whether or not to start the archiver process, and whether or not to retain unarchived WAL segments indefinitely. In 9.0, it's possible to set up a system where streaming replication is enabled but archiving is not, and streaming replication has the same WAL requirements as archiving. Rather than writing the necessary information to WAL if either archiving or streaming replication is enabled, it seemed cleaner to have a separate setting to explicitly control what got written to WAL.

While streaming replication and archiving have essentially the same WAL requirements, Hot Standby requires all of those same things to be logged plus a few more. Initially, we just had a setting for whether or not to log those few extra things, and it simply didn't do anything unless either archiving or streaming replication was also enabled. But that led to some confusing error conditions. Streaming replication was enabled by setting a variable called max_wal_senders to a value greater than 0, so the WAL needed for Hot Standby would get written if:

(archive_mode = 'on' OR max_wal_senders>0) AND recovery_connections='on'

Again, it seemed cleaner to have a separate setting. When Hot Standby failed to start on the standby because of incorrect settings on the master, the error message needed to say something like this:

We can't start Hot Standby because the master server either has max_wal_senders=0 and archive_mode=off, or else it has recovery_connections=off. So please either go enable recovery_connections, or if it's already enabled, then either increase max_wal_senders or turn on archive_mode.

With the new wal_level setting, we can just say (in effect) this:

We can't start Hot Standby because you didn't set wal_level='hot_standby' on the master.

That's not the exact text of the error message. We actually refer to the thing that we can't start on the standby as "recovery_connections", and there is a "recovery_connections" setting on the standby that controls whether or not we try to start it. I'm not sure whether this is all good terminology - these are all new features so we're hammering it out as we go along! Hopefully we'll get some further feedback as we move into beta. Beta1 should be wrapped later today!

Materialized Views in PostgreSQL

2010-04-22T09:24:00.000-04:00

About 9 months ago, Peter Eisentraut reported on the results of a UserVoice survey he had set up. The purpose of the survey was to gather information from PostgreSQL users on which features they were most interested in seeing added to PostgreSQL. Of course, to what extent the features that got high marks in that survey represent the real interests of the community is questionable: certainly, not every PostgreSQL user (or potential user) reads Peter's blog. Still, the results seem to indicate a lot of interest in materialized views.

Recently, Pavel Baros made a proposed a possible implementation of materialized views on the pgsql-hackers mailing list as a Google Summer of Code project. What he's proposing to do is just about the simplest possible implementation of materialized views I can imagine, comprising just two commands:

CREATE MATERIALIZED VIEW name AS SELECT ...
ALTER MATERIALIZED VIEW name REFRESH;

Of course, right away this opens a can of worms. Basically, there are two things that you might mean when you talk about a "materialized view":

1. A materialized view that is always up to date. In other words, whenever the underlying tables are updated, there is some incremental-update process that concurrently modifies the materialized view data so that it remains up to date also. The tricky part is that it's not easy to update a materialized view of an arbitrary query for a reasonable cost. If the query is complex, the only feasible way to keep the materialized view up to date may be to re-execute the entire query whenever any of the underlying tables are modified (or when any of the functions or operators are redefined), which is probably not feasible for most people. But the plus side is that, when a cheap incremental update IS possible, you don't really need to know that you're working with a materialized view at all. It's indistinguishable from a regular view, up to performance.

2. A materialized view that isn't always up to date. This would include the proposed implementation, which is manual-refresh-only, as well as, for example, views that are refreshed on a regular schedule. (It's pretty easy to convert manual-refresh-only to refreshed-on-a-regular-schedule if you have access to cron!) Materialized views of this type are possibly useful in cases where incremental refresh isn't easily done, such as aggregates over a huge data set that produce a much smaller output table. Of course, you can do this kind of thing today using triggers, but maybe it would be nice to have something in the database that would do it for you automatically.

In the end, I suspect we'll want to support both of these features in PostgreSQL, but I'm curious which one people think is more useful. Our initial implementation of either feature is likely to be a bit rough around the edges, so don't assume we're going to provide all the optimizations we'd ever like to have on day one!

Materialization in PostgreSQL 9.0

2010-04-18T22:07:00.000-04:00

Some plan types, such as nested loops and, to a lesser degree, merge joins, rely on the ability to "rescan" their inner input. Rescanning basically means backing up: the executor asks the target node to produce the same output tuples it has already spit out all over again - either all of them, or just a specified portion. This can be expensive: if the plan that produced those tuples has a high cost associated with it, we'd very much prefer not to do it more than once.

Enter Materialize. When considering a Nested Loop or Merge Join, the planner considers inserting a "Materialize" node atop the inner plan. This node stores a copy of the tuples generated by the inner plan in an in-memory buffer or in a temporary file, making it very cheap to return them over again if needed.

In versions 8.4 and prior, the logic for inserting Materialize nodes when considering Nested Loop plans is thoroughly bogus. The planner had no idea that rescanning a Materialize node would be any cheaper than scanning it the first time - which is pretty horrible when you consider that this is precisely the point of having Materialize nodes. In 9.0, the logic has been extensively rewritten. Based on the limited testing I've done so far, 9.0 will materialize the inner side of a nested loop a great deal more frequently than 8.4, and most of those cases seem to be wins.

It's probably not too surprising that if the node being rescanned is, say, a join, it's faster to reread the tuples from a buffer than it is to redo the whole join. Similarly, if you're rescanning a base table with a filter condition - e.g. find all the rows in table foo where x = 1 - it's faster to save the rows that meet the condition than it is to rescan the whole table and test the condition over again for each row. Amazingly, however, at least in some cases, it seems to be faster to use materialization even when rescanning a base table WITHOUT a filter condition. In other words, if we need to read all the rows in table foo three times, it's at least sometimes faster to read the table once, dump all the tuples into a tuplestore, and then reread the tuplestore twice more than it is to read the base table three times.

How is that possible, you ask? I haven't played with this enough to be sure, but I suspect the win comes from eliminating tuple visibility checks and other overhead.

Even though this isn't the sort of feature that tends to make the headlines, it's a fairly significant adjustment to the planner, so I expect to find some lurking problems (a few have been found and fixed already). It may be that the planner is now too aggressive at materializing, whereas before it seems it wasn't aggressive enough. Or it may turn out that materializing increases query memory usage too much, or just isn't faster in some as-yet-unknown set of cirumstances. On the whole, though, the new logic is far more sensible than the old logic, so I'm hopeful that we'll seem some modest performance improvements out of this change once the dust settles.

Finding Our Way to LATERAL

2010-04-14T20:29:00.000-04:00

One of the few types of SQL query that we don't yet implement in PostgreSQL is LATERAL, and we seem to get fairly regular requests for it. The typical use case for this feature is where you have a table called, say, foo, and a set-returning function called, say, func, and you'd like to do this:

SELECT foo.x, bar.x FROM foo, func(foo.x) AS bar;

But that doesn't work:

ERROR: function expression in FROM cannot refer to other relations of same query level

In fact, the SQL standard apparently specifies that this is not legal syntax. If you want to reference a variable at the same query level, you need to wrap it using LATERAL():

SELECT foo.x, bar.x FROM foo, LATERAL(func(foo.x)) AS bar;

Currently, PostgreSQL does not support LATERAL(), so this query fails with a complaint that there's no function called LATERAL available. But wouldn't it be neat if it worked? We get requests for this feature pretty regularly. Right now, there's a pretty limited number of ways to call SRFs, and it's hard to get the equivalent of the above query without major gymnastics.

I wanted to see how much work it would be to implement LATERAL() in PostgreSQL, so, just for fun, I beat the parser with a large enough sledgehammer to make it accept the first of the two queries listed above. This effort was doomed to failure, however. When the planner goes to join foo to func(foo.x), it doesn't understand the implicit dependency between those two items. By jiggering the costs, you can get the planner to decide on a nonsensical plan like this: for each row in the output of func(foo.x), scan all the rows in foo. Of course, to us human beings, it's obvious that we have to do those two steps in the opposite order, but the planner doesn't know that. Its only knowledge about the connection between different tables comes from the join clauses, and there are no join clauses in this query.

As it turns out, fixing this turns out to be closely related to solving a problem with nested-loop-with-inner-indexscan plans, which could more generally be called "partial index scan" plans. Currently, we find these plans using some special case crocks that cover most, but not quite all, of the interesting cases. Andrew Gierth supplied this example:

SELECT ... FROM t1 LEFT JOIN (t2 JOIN t3 ON (t2.a=t3.a)) on (t1.a=t2.a);

If t1 is small and t2 and t3 are large, we might like to get a plan that looks something like this:


Nested Loop Left Join
Join Filter: (t1.a = t2.a)
-> Seq Scan on t1
-> Nested Loop
 Join Filter: (t2.a = t3.a)
   -> Index Scan using t2_a on t2
      Index Qual: (t2.a = t1.a)
   -> Index Scan using t3_a on t3
      Index Qual: (t3.a = t1.a)

But we won't. Instead, we'll spend a lot of energy joining all of t2 to all of t3, and then throw away those results when we do the join to t1. This happens because planner will only consider a partial index scan on t2 or t3 if the index scan is the inner side of a nested loop and the table providing the value to look for is on the outer side of the same nested loop. Here, the index quals are referencing t1, which isn't part of the join: it's one level up. In many cases, the planner can work around the inability to form these types of plans by reordering the joins, but swapping the order of the LEFT and INNER joins in this query would change it's meaning, so that doesn't work in this case.

Tom Lane had an idea about how to generalize the inner-indexscan machinery to handle this. Instead of considering partial index scans using special-case logic at the time we form a nested loop, Tom proposed, essentially, promoting them to first-class citizens, to be considered along with all the other paths we examine when forming a join. To make that work, a little bit of tracking data must be added: if we choose a partial index scan path for t2, we don't need to join it directly to t1, but we do need to *eventually* put whichever portion of the plan contains it on the inner side of a nested loop that has t1 on the outer side, so that t1 can supply the values for the index lookup. As it happens, set-returning functions called via LATERAL need exactly the same thing: a way to make sure that the portion of the query tree where they end up being called is somewhere under the inner side of a nested loop whose outer side supplies the necessary variables.

There's one more fly in the ointment: this is not just a planning problem. We also need to make sure that whatever plan the planner creates can be executed by the executor. It turns out that the executor has an almost identical limitation. Nested loops pass down an "outer tuple" to their inner plan, and if that plan happens to be an index scan then the outer tuple can be used to constrain the index lookup. Unfortunately, again, this only works if the nested loop immediate parent of the index scan. Tom Lane has an idea for fixing this, too: replace the existing mechanism with executor parameters, which are more general and can pass values between far-distant parts of the query plan. Again, we'll use this same design for LATERAL, when a set-returning function has input parameters that must be supplied by a table scan elsewhere in the query.

So, when might we see actual LATERAL support in PostgreSQL? Tom Lane has indicated that he plans to fix the executor limitation described above for 9.1; I'm not sure whether he's planning to fix the planner limitation as well. Once those are fixed, I think we'll be within striking distance of the core feature. So, I'm hopeful that we'll get LATERAL in either 9.1 or 9.2. Stay tuned!

PostgreSQL East 2010 Slides

2010-04-12T22:33:00.000-04:00

Quite a few people seemed to like my talk on the PostgreSQL Query Planner at PostgresSQL East, and I got a few requests for copies of my slides. I'll be giving a shortened version of that talk at PGcon 2010. I didn't get as many positive comments on other talk, an Introduction to Triggers, though I had a pretty full room and the people who came seemed to find the material interesting. Anyhow, I've put both presentations on line.