PostgreSQL benchmarking over a RAMdisk?

Question

I have been considering the idea of moving to a RAMdisk for a while. I know its risks, but just wanted to do a little benchmark. I just had two questions: (a) when reading the query plan, will it still differentiate between disk and buffers hits? If so, should I assume that both are equally expensive or should I assume that there is a difference between them? (b) a RAM disk is not persistent, but if I want to export some results to persistent storage, are there some precautions I would need to take? Is it the same as usual e.g. COPY command?

Answer 1

I do not recommend using RAM disks in PostgreSQL for persistent storage. With careful tuning, you can get PostgreSQL not to use more disk I/O than what is required to make your data persistent.

I recommend doing this:

• Have more RAM in your machine than the size of the database.

• Define shared_buffers big enough to contain the database (on Linux, define memory hugepages to contain them).

• Increase checkpoint_timeout and max_wal_size to get fewer checkpoints.

• Set synchronous_commit = off to keep PostgreSQL from syncing WAL to disk on every commit.

• If you are happy to lose all your data in the case of a crash, define your tables UNLOGGED. The data will survive a normal shutdown.

Anyway, to answer your questions:

(a) You should set seq_page_cost and random_page_cost way lower to tell PostgreSQL how fast your storage is.

(b) You could run backups with either pg_dump or pg_basebackup, they don't care what kind of storage you have got.