PostgreSQL on RamDisk: Size of work_mem etc.?

Question

I am experimenting with running PostgreSQL on a ramdisk on windows. The way I did it was to simply place the data directory on the ramdisk.

Without having done any specific benchmarks, the performance seems to be magnificent and only CPU bound. My question is what the optimal values for things like work_mem, shared_buffers etc. would be?

Even when the database is in ram it take more than half a minute to run many of my queries. Therefore, I wonder whether it makes sence to create indexes on the table. The indexes would, of course, need to stay in ram. I should mention that I am using PostgreSQL for a data warehouse (small one, though).

Edit: I should mention that I am using the RamDisk utility from DataRam.com. It only gives me a bluescreen once in a while, when I configure the ramdisk, never when it is established. I think of this as nostalgic eyecandy. ;)

Answer 1

Yes, use indexes but work_mem still depends on the size of the machine itself. Of course one of the reasons you want a high work_mem is so you don't hit disk to do the tape sort. On a ramdisk this isn't nearly as dangerous. Just remember, you have no data integrity on a ram disk.

Answer 2

I would definitely create indexes. The engine can use the info contained for all sorts of optimizations, and it should improve your performance quite a bit. RamDisk solves the worst case table scan type cases, but it doesn't necessarily mean that that tablescan is faster than doing a correct lookup.