Is there a way to store a PostgreSQL database directly on a block device (not filesystem)?

Question

I am reading about where PostgreSQL stores its data it needs to offer me its service (here: http://www.postgresql.org/docs/8.4/static/storage-file-layout.html )

As it is writen there PostgreSQL's data and storing seems to work on top of the respective filesystem in which I store the PGDATA folder. In this folder again the data is stored in a set of individual files (i.e. also files per database).

Question: While I can get along with such an "in-filesystem-setup",

I wonder if it is possible to setup PostgreSQL to use directly a block device (like /dev/sda) on for POSIX systems for data storage?

I would think that this would offer some advantages:

• the blocksize configuration and optimization to fit database to filesystem needs no longer be undertagen
• overhead in the filesystem can be avoided
• backup can be simpler

If not for PostgreSQL are there database applications known that do not rely on keeping its data as files filesystem, but use block devices directly?

Answer 1

[Is it] possible to setup PostgreSQL to use directly a block device (like /dev/sda) on for POSIX systems for data storage?

Nope.

PostgreSQL would have to implement its own file system to support this. It lets the operating system do that, on the theory that the OS will probably do it better.

The project tries to avoid duplicating functionality the operating system already provides. That's one of the reasons why it performs as well as it does; when the kernel folks make improvements, PostgreSQL gets them for free.

Of course, when the kernel folks add performance regressions and bugs we get those too, but that's true even if you use raw block devices. We're just exposed to more filesystem and memory management level differences too.

There are a lot more smart kernel hackers with a lot more time than there are PostgreSQL hackers. So letting them deal with the complexities of buffer eviction, NUMA scheduling, dirty page write-back, etc seems like a no-brainer and it's worked out well so far. Mostly.

If not for PostgreSQL are there database applications known that do not rely on keeping its data as files filesystem, but use block devices directly?

Oracle. It is virtually its own operating system; the Emacs of the database world.

MySQL with InnoDB can create InnoDB segments on raw disk partitions.

I know it's been looked into for SQLite but I don't know if anyone cared enough to finish the work.

backup can be simpler

I question your assertion that using a raw partition makes backups simpler. I see few advantages and plenty of disadvantages to relying on raw block devices.

Answer 2

Informix can also use "raw" volumes (as well as "cooked" files) for storage. AFAIK the databases that have this functionality do so as they believe they're better at organising data for their own purposes (which is probably true) ie it's generally about eeking a little more performance out. I doubt postgres' reliance on the OS for this makes a lot of difference unless you have very large, high traffic databases.