PostgreSQL query and data caching

Question

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

Why are you concerned about caching? How long does it take the query to run? — Gordon Linoff
– Gordon Linoff, Commented Nov 21, 2018 at 12:02
@GordonLinoff 2.752 ms in average, what if the data is too large to count in every hour? — Kris MP
– Kris MP, Commented Nov 21, 2018 at 12:04
What if new data is inserted into the table? what if the value of todaychanges overnight? — wildplasser
– wildplasser, Commented Nov 21, 2018 at 12:05
PostgreSQL caches data automaticlly based on LRU algorithm (check this link: madusudanan.com/blog/understanding-postgres-caching-in-depth). What you're doing is premature optimization here ;) — JustMe
– JustMe, Commented Nov 21, 2018 at 12:44

KibGzr · Accepted Answer · 2018-11-21 14:00:57Z

5

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.

answered Nov 21, 2018 at 14:00

KibGzr

2,09316 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL query and data caching

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