7

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?

  • 2
    It doesn't really matter, but: why are you reading a manual from an outdated (and unsupported) Postgres version? Oracle is able to store its data on "raw" devices. But I don't see any advantage in that. Plus Postgres relies heavily on the cache of the underlying file-system. Using "raw" devices might have made a difference 20 years ago, but I don't think the filesystem is really a problem nowadays (especially with the number of alternatives to choose from) – a_horse_with_no_name Oct 13 '14 at 7:36
  • @a_horse_with_no_name sorry for the outdate version I was reading the highest rated answer on stackoverflow.com/questions/1137060/… . – humanityANDpeace Oct 13 '14 at 7:49
  • @a_horse_with_no_name I was led into thinking that especially the number of alternatives that makes it just as hard to pick the right one + to configure it in accordance with best performance for PostgreSQL. thanks tof the comment. maybe it can serve as an answer too? if you can maybe give a reference that states that if needs to be storing its data as files only? – humanityANDpeace Oct 13 '14 at 7:55
12

[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.

|improve this answer|||||
1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.