PostgreSQL 9.3 beta: Federated databases and more

Perhaps I am mistaken, but I believe that if PG were to keep the mmapped file's file descriptor open, fsync() would still work on the mmapped pages. The advantages of mmap(), here, would include that PG can avoid copy operations and share pages with the kernel's buffer cache, and the kernel can begin writing out dirtied file blocks early, in parallel with PG operations. PostgreSQL, with its never-overwrite storage model, seems uniquely positioned to take advantage of these properties.

You can be sure of that with msync(). The problem with using mmap() for bulk file I/O is that it is very hard for the kernel to predict access patterns, so prefetching and the like don't work as well as they might without special madvise()ment from userspace, which next to nobody gives. Meanwhile, for read() and write() it just works.

Also, mmap() implies page faults, which imply TLB shootdowns, which are slower than straight reads into already-allocated buffers as is generally done by read(). Combine that with the fact that EOF is fairly hard to detect, and appending is harder, and...

I wish mmap() was used for everything: it's a lovely unifying interface. But it's also a bit of a pig.

> The big issue with mmap() for fileIO, as I understand it, is that we can never be sure when the file has been flushed to disk. For a guaranteed-durability database like PostgreSQL, that's not something we can live with.
The problem is that we cannot influence the order in which the pages are flushed to disk. For crash safety we cannot allow any pages to be written out that have a LSN (Log Sequence Number := Address of the write ahead log record covering the last modification) bigger than the last LSN of the corresponding WAL that has been flushed out.
So we would have to be able to reliably prevent writeout on a page (postgres' ones, by default 8kb) granularity in an efficient manner.

There's a number of major disadvantages to threads as well, as anyone who's worked on (or with) MySQL or DB2 can tell you. Possibly chief among them is the inability to isolate resources between backends even when you want to; it's quite common for the unexpected termination of a single connection to shut down the server in threaded databases, and quite rare for that to happen in Postgres. We've debated the threads issue over the years on the mailing lists, and the consensus is that not only would moving to threads stall all other development for at least a year (more likely two), we'd just be exchanging one set of problems for another.

Firebird (the perennial other open source SQL database hiding in the attic) supports both threaded and non-threaded modes. On Linux, where a fork() is cheap, nonthreaded wins. On Microsoft the "super server" (threaded) wins. The one notable difference only happens if you are running a beta version that will have the occasional crash. In that case the non-threaded mode is far more reliable on both platforms.

Memory doesn't have to be shared by all threads living in the same process. There are any number of ways, including explicitly shared and memory mapped files. These boil down to choosing to "not shared by default" instead of the "shared by" default model threads use. Speed of access to the memory is the same. The latter is safer, on multi machines with multi CPU's usually faster because less sharing means less cache thrashing. But there is an extra cost of creating a process which is why it loses on Windows.

> Actually, we're still arguing about which algorithm to use. Expect to see some algo changes in successive betas.

I think that ship has sailed and for the on-disk page checksums we are going with the modified FNV.

Explanations about the algorithm:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;...