我们可以要求 postgres 对特定表使用缓存吗？
我们可以要求 postgres 对特定表使用缓存，而对少数表完全不使用它吗？
Can we ask postgres to use caching for specific tables and don't use it for few tables at all?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
不，您无法控制要缓存的内容。
但如果这是一个经常访问的表，它无论如何都会被缓存。如果不经常访问，则不会被缓存。
所以我不认为有理由试图控制它。
No, you cannot control what is going to be cached.
But if that is a frequently accessed table, it will be cached anyway. If it's not accessed frequently it will not be cached.
So I don't see a reason for trying to control this.
为了读取数据并对其进行操作，PostgreSQL 必须将其放入缓存中。这样做也会暂时将其放入操作系统缓存中。您可以在我的里面找到有关一旦进程停止使用该数据最终如何从数据库缓存中逐出的准确描述。 PostgreSQL 缓冲区缓存 谈话。
您可以通过减小 shared_buffers 参数的大小来更快地从缓存中逐出，但这可能会降低性能。 PostgreSQL 总是使用操作系统缓存进行读取，当您访问大量数据时，您无法避免其受到污染。
对顺序扫描进行了优化，可以防止数据库自己的 shared_buffers 缓存被该数据接管。大于 (shared_buffers / 4) 的表仅限于在数据库端使用少量内存。尽管这些仍然会影响操作系统缓存，但它们保留不常用数据的时间往往比数据库要短。
In order to read data and operate on it, PostgreSQL has to bring it into its cache. And doing that will temporarily put it in the operating system cache too. You can find an exact description of how that data will eventually be evicted from the database's cache once processes stop using it in my Inside the PostgreSQL Buffer Cache talk.
You can get faster eviction from cache by reducing the size of the shared_buffers parameter, which may have performance downsides. PostgreSQL always reads using the OS cache, and you can't keep that from being polluted when you access large amounts of data.
There is an optimization for sequential scans that keeps the database's own shared_buffers cache from being taken over by that data. Tables that are larger than (shared_buffers / 4) are limited to only using a small amount of memory on the database's side. Those will still hammer the operating system cache though, but those tend to hold onto infrequently used data for less time than the database will.
你不能那样做。 PostgreSQL 本身的维护做得非常好。事实上，我想不出您想要修改此行为的情况。
如果您发现问题（例如表使用了太多缓存），则可能意味着该表使用效率低下（例如存在使用 seq 扫描的查询）。如果某些表使用很少的缓存，要么是因为缓存太少是最佳的，要么是因为总缓存大小太小。不管怎样，PG已经尽力了。
到底是什么让你问这个问题？
You can't do that. PostgreSQL does a really good job maintaining it itself. In fact, I can't think of a case where you'd like to modify this behavior.
If you see an issue (such as a table using too much cache), it probably means that table is used ineffectively (e.g. there are queries with seq scan). If some tables use little cache, it's either because so little is optimal, or because total cache size is too small. Either way, PG is doing the best it can.
What exactly made you ask this question?
PostgreSQL和操作系统会更好地进行缓存。您不使用的数据不会在缓存中，您使用的数据则在缓存中。你不想与你的数据库和操作系统作斗争，没有必要。
PostgreSQL and the operating system will do the caching much better. Data you don't use, won't be in the cache, data you do use, is in the cache. You don't want to fight your database and the operating system, there is no need for.
为什么不使用 pgfincore 将表存储在操作系统缓存中？
流动的博客展示了如何使用它。
http://francs3.blog.163.com/blog/static/4057672720107541611270/
Why not using pgfincore to store the tables in OS cache?
The flowing blog show how to use it.
http://francs3.blog.163.com/blog/static/4057672720107541611270/