需要将整个postgreSQL数据库加载到RAM中 [英] Need to load the whole postgreSQL database into the RAM

问题描述

如何将我的整个PostgreSql数据库放入RAM中以实现更快的访问？我有8GB的内存，我想为数据库分配2GB的内存。我已经阅读了有关共享缓冲区设置的信息，但它只是缓存数据库中访问量最大的片段。我需要一个解决方案，将整个数据库放入RAM中，然后从RAM DB中进行任何读取，并且任何写入操作都将首先写入RAM DB，然后再写入硬盘驱动器中的DB。（例如默认的fsync =

How do I put my whole PostgreSql database into the RAM for a faster access?? I have 8GB memory and I want to dedicate 2 GB for the DB. I have read about the shared buffers settings but it just caches the most accessed fragment of the database. I needed a solution where the whole DB is put into the RAM and any read would happen from the RAM DB and any write operation would first write into the RAM DB and then the DB on the hard drive.(some thing like the default fsync = on with shared buffers in postgresql configuration settings).

推荐答案

我有一段时间问过同样的问题了。 PostgreSQL的缺点之一是它似乎不像MySQL那样支持IN MEMORY存储引擎。

I have asked myself the same question for a while. One of the disadvantages of PostgreSQL is that it does not seem to support an IN MEMORY storage engines as MySQL does...

无论如何，我遇到了文章几周前描述如何做到这一点；尽管它似乎只能在Linux上运行。我真的不能担保它，因为我自己还没有尝试过，但是由于确实为PostgreSQL表空间分配了一个已装入的存储库，因此这似乎很有意义。

Anyway I ran in to an article couple of weeks ago describing how this could be done; although it only seems to work on Linux. I really can't vouch for it for I have not tried it myself, but it does seem to make sense since a PostgreSQL tablespace is indeed assigned a mounted repository.

但是，即使采用这种方法，我也不确定您是否也可以将索引放入RAM；我不认为MySQL会强制在其IN MEMORY表中强制使用HASH索引...

However, even with this approach, I am not sure you could put your index(s) into RAM as well; I do not think MySQL forces HASH index use with its IN MEMORY table for nothing...

我也想做类似的事情来提高性能，因为我也在工作具有庞大的数据集。我正在使用python;它们具有字典数据类型，这些数据类型基本上是{key：value}对形式的哈希表。使用它们非常有效。基本上，要将我的PostgreSQL表放入RAM，我将其加载到这样的python字典中，进行处理，并偶尔将其保存到db中；如果使用得当，它值得。

I also wanted to do a similar thing to improve performance for I am also working with huge data sets. I am using python; they have dictionary data types which are basically hash tables in the form of {key: value} pairs. Using these is very efficient and effective. Basically, to get my PostgreSQL table into RAM, I load it into such a python dictionary, work with it, and persist it into db once in a while; its worth it if it is used well.

如果您不使用python，我很确定它们是您语言中类似的字典映射数据结构。

If you are not using python, I am pretty sure their is a similar dictionary-mapping data structure in your language.

希望这会有所帮助！

这篇关于需要将整个postgreSQL数据库加载到RAM中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！