Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 16 '13 at 17:18

This question came from our site for professional and enthusiast programmers.

    
Why would you ever store tables/indexes on ramdisk? Why not give the memory directly to postgres via configuration? –  Igor Romanchenko Nov 15 '13 at 15:48
    
Postgresql uses the OS caching mechanisms, so there is no way to not have the indexes and relations in cache if they are being used to service queries. (at least, no way that I have ever heard of) –  bma Nov 15 '13 at 15:48
    
@IgorRomanchenko: it can make some sense when running a cache with unlogged tables in a ramdisk — though admittedly, one might as well use memcached in that case. :-) –  Denis Nov 15 '13 at 16:15
1  
How exactly did you "store tables on a ramdisk"? I really, really hope you didn't use a tablespace for that, unless you don't care about any of that database's contents at all. –  Craig Ringer Dec 23 '13 at 4:45

2 Answers 2

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

share|improve this answer

You should be able to do that via general memory settings. If you put them to a very low level, postgres will use disc, which is your ramdisc instead of RAM. But somehow I dislike the idea.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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