Caching inexpensive but frequent DB queries

Question

I know caching is most commonly used to prevent repeated execution of expensive queries. However, in our application, we have a couple of scenarios where we very frequently (say, hundreds of times per second) execute queries that are, theoretically, rather inexpensive. For example,

SELECT push_token FROM devices WHERE id = ?

or

SELECT value FROM some_table_with_configs WHERE key = ?

My question is whether it is worth to cache the results of these rather inexpensive queries, or if I'm better off hitting the DB (PostgreSQL, in case it matters) everytime and let the RDBMS's caches do their work?

In case it matters, I'm using JDBC with proper connection pooling and that those queries return results that change very, very infrequently.

In situations like this it's always better to benchmark. Postgresql query for a single record by primary key is very fast and as a rule of thumb don't need caching. Factors like whether your cache is local (listens on unix socket rather than tcp) can make a difference. What cache you use (redis, memcache) can make a difference. — e4c5
– e4c5, Commented Jun 5, 2016 at 18:03

Nick · Accepted Answer · 2016-06-05 18:18:52Z

1

One way to potentially optimize this would be to use prepared statements.

https://www.postgresql.org/docs/current/static/sql-prepare.html

Like so:

PREPARE prep_one(INT) AS SELECT push_token FROM devices WHERE id = $1;
EXECUTE prep_one(123);

edited Jun 5, 2016 at 18:18

answered Jun 5, 2016 at 18:11

Nick

7,5012 gold badges25 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

Caching inexpensive but frequent DB queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related