2

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

David
  • 4,786
  • 11
  • 52
  • 80

2 Answers2

1

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.

chmullig
  • 13,006
  • 5
  • 35
  • 52
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.

Joshua D. Drake
  • 1,026
  • 5
  • 6
  • So, could I interpret your answer as that I could set work_mem to 0, because hitting the ramdisk does not matter. By data integrity, do you mean that I could loose all my data on a restart or that the data on the ramdisk might actually be wrong under some conditions, the last interpretation would be a concern to me. – David Feb 08 '11 at 08:51
  • I understand that this is an old question, but wouldn't some processes crash with too little work_mem? – Zeruno Mar 23 '20 at 01:35