Postgres + Select query from Cache

Question

I was wondering if Postgres had a feature like SQL_CACHE in mySQL. For example in mySQL I can do a query like "SELECT SQL_CACHE Product_ID, Product_Name, Product_Sku FROM Products" I want to know if I can do something similar in Postgres.

Kenaniah · Accepted Answer · 2011-04-06 19:32:12Z

4

PostgreSQL automatically caches recently accessed data in memory, depending on your shared_buffers configuration parameter. There's really no need for an SQL_CACHE feature, since Postgres already does a great job of managing the tuple cache.

edited Apr 6, 2011 at 19:32

answered Apr 5, 2011 at 19:55

Kenaniah

5,20126 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user330315 Over a year ago

That's not entirely correct. effective_cache_size does not influence the cache that Postgre manages. It only give the planner a hint how like it will be that data is already in the filesystem cache. The bigger that value, the less expensive random seeks are treated.

Frank Heikens Over a year ago

Wrong parameter, the only cache PostgreSQL has, is shared_buffers.

Kenaniah Over a year ago

@Frank. That's what I get for typing too quickly. Thanks :-)

Wrikken · Accepted Answer · 2011-04-05 19:50:56Z

0

In MySQL, SQL_CACHE doesn not select from cache, depending on query_cache_type, this happens:

1 or ON: Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
2 or DEMAND: Cache results only for cacheable queries that begin with SELECT SQL_CACHE.

In essence, using SQL_NO_CACHE with any setting other the 1 or SQL_CACHE with any other setting the 2 is meaningless.

If this is still what you need in PostgreSQL: no idea, but I had to make this very clear.

answered Apr 5, 2011 at 19:50

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

Comments

Max Sherbakov · Accepted Answer · 2018-07-31 12:00:08Z

0

just simple goto /etc/postgresql/YOUREVERSION/main/posgresql.conf (*nix) ( C:\Program Files\PostgreSQL\YOUREVERSION\data\postgresql.conf (win)) find shared_buffers, uncomment itб and specify need cache size.

answered Jul 31, 2018 at 12:00

Max Sherbakov

1,97517 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgres + Select query from Cache

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related