CODEWITHSUNDEEP
sqlpostgresql
Kris MP
Kris MP

Reputation: 2415

PostgreSQL query and data caching

I have this SQL query:

SELECT p.timestamp,
      COUNT(*) as total,
      date_part('hour', p.timestamp) as hour
      FROM parties as p
      WHERE p.timestamp >= TIMESTAMP 'today' AND p.timestamp < TIMESTAMP 'tomorrow'
      AND p.member_id = 1
      GROUP BY p.timestamp, hour;

which will grouped how many people by hour:

+-------------------------+-------+------+
|        Timestamp        | Total | Hour |
+-------------------------+-------+------+
| 2018-11-21 12:00:00+07  |    10 |   12 |
| 2018-11-21 13:00:00+07  |     2 |   13 |
| 2018-11-21 14:00:00+07  |     2 |   14 |
| 2018-11-21 16:00:00+07  |     1 |   16 |
| 2018-11-21 17:00:00+07  |    21 |   17 |
| 2018-11-21 19:00:00+07  |    18 |   19 |
| 2018-11-21 20:00:00+07  |     8 |   20 |
| 2018-11-21 21:00:00+07  |     1 |   21 |
+-------------------------+-------+------+

My question is, if I refetch some API end point that will query above statement, would it be the data in the past hour cached automatically? because in my case, if there is a new data, it will update the last hour's row only.

If not how to cache it? Thanks in advance

Upvotes: 3

Views: 7110

Answers (1)

KibGzr
KibGzr

Reputation: 2093

PSQL can not cache result of query itself. The solution is cache the result at API application layer.
I prefer using redis to cache it. Using a hash with fields is year+month+day+hour and value is total online user of each hour. Example: 

 hash: useronline
 field: 2018112112 - value: 10
 field: 2018112113 - value: 2

You also set a timeout on key. After the timeout has expired, the key will automatically be deleted. I will set 1 hour in here.

EXPIRE useronline 3600

When have API request we will get result in redis cache first. If do not exist or expired call query to database layer to get result, save to redis cache again. Reponse result to client.
Here is list of redis clients suitable for programing language.

Upvotes: 5

Related Questions