1

I wanted to use Postgres as an in memory store. After I create a materialized view, I do not want its result to be written to the disk but kept stored in memory. I have a lot of memory (>100GB) and do not need to use the disk. I was wondering if it would be possible to do so.

Found Solution: Keeping postgres entirely in memory

Improve this question

1 Answer 1

Reset to default
1

It's possible with a ramdisk, but it's really inefficient. You'll have at least two, usually three, copies of data in RAM - the ramdisk, the OS buffers/cache, and PostgreSQL's shared_buffers.

What you should do instead is allocate disk space for it anyway, but set Linux's dirty writeback thresholds very high, turn fsync off in PostgreSQL, use unlogged tables, and basically let it run in non-crashsafe mode almost entirely from RAM.

Let the OS be smart about it - it can still write and flush data lazily, making more room in RAM.

If you feel you truly need an in-RAM database, you'd be better off finding one that's designed to work that way. But it's pretty rare to really need it.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I don't disagree with your answer, but as a matter of interest - AFAIK if you create a tmpfs the kernel does not separately cache it (unlike traditional RAMDISK), and if you turned shared_buffers right down, it might minimize the duplication in RAM?
I'd be interested in references for that - if tmpfs pages don't get kept in kernel buffers longer than necessary for buffered I/O ops, or are zero-copy, that'd certainly be interesting. OTOH, tmpfs can swap pretty easily, and that can result in horrible random I/O patterns.
Its zero copy. tmpfs is based on the earlier ramfs - there is an interesting article here that describes the mechanism. kernel.org/doc/Documentation/filesystems/…. It is a filesystem without a backing store. The extra features added by tmpfs vs. ramfs are the ability to control the max. size and to use swap. See also kernel.org/doc/Documentation/filesystems/tmpfs.txt
OK, good to know. Thanks for the reference. It might be worth adding an answer with more info, especially if you're able to do some testing. I've never had good results running Pg on tmpfs, but I freely admit I didn't dig into tuning it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.