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

Community
  • 1
  • 1
user1150989
  • 493
  • 1
  • 4
  • 9

1 Answers1

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 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? – harmic Apr 09 '14 at 06:58
  • 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. – Craig Ringer Apr 09 '14 at 11:30
  • Its zero copy. tmpfs is based on the earlier ramfs - there is an interesting article here that describes the mechanism. https://www.kernel.org/doc/Documentation/filesystems/ramfs-rootfs-initramfs.txt. 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 https://www.kernel.org/doc/Documentation/filesystems/tmpfs.txt – harmic Apr 09 '14 at 11:57
  • 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. – Craig Ringer Apr 09 '14 at 12:01