Postgresql indexes in memory

indexpostgresql

I use Postgresql 9.2. I have tables stored on a ramdisk, but now indexes on those relations are located on a regular disk. I want to move them into ram too, but for all I know postgresql loads indexes into memory when it's possible. Am I right? Is there any method to tell postgres don't load indexes into memory.

Best Answer

If you force indexes and tables into RAM by using a ramdisk, all you're likely to be doing is wasting RAM.

PostgreSQL uses the operating system's buffer cache. So when a table/index page is first read, it's cached by the OS, and future reads are returned by the OS cache, not fetched from disk. If the database is bigger than the available memory then the OS will throw oldest, least-used data away and it'll get read from disk next time it's needed.

If you create a ramdisk, you are reducing the amount of memory the OS can use for cache. It'll still cache each block read from the ram disk before returning it to PostgreSQL. If there's not enough space left for efficient memory management of the buffer cache the kernel will do a lot of work trying to free pages for buffered reads, and your ramdisk will actually slow things down.

For writes, the main effect the ramdisk has is to make all disk sync operations effectively no-ops. So it's like running PostgreSQL with fsync=off, where it never tries to flush writes to disk at all. It has the same property of being able to eat your data whenever it feels like it, too.

There's generally not much benefit in running PostgreSQL on a ramdisk vs running it with data durability settings turned off, a very high dirty writeback threshold, etc.

(On a side note, if you create a tablespace on a ramdisk and put anything in it, you're making the whole database cluster - i.e. all databases on that server - at risk of severe data loss. Don't do it. Either initdb the whole database on the ramdisk or, preferably, just tune the server so you don't need to mess about with ramdisks.)

Related Question