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
sql postgresql
asked Nov 21 '18 at 12:00
Kris MPKris MP
56811024
add a comment |
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
sql postgresql
asked Nov 21 '18 at 12:00
Kris MPKris MP
56811024
Why are you concerned about caching? How long does it take the query to run?
– Gordon Linoff
Nov 21 '18 at 12:02
@GordonLinoff 2.752 ms in average, what if the data is too large to count in every hour?
– Kris MP
Nov 21 '18 at 12:04
1
What if new data is inserted into the table? what if the value oftodaychanges overnight?
– wildplasser
Nov 21 '18 at 12:05
1
2.752 ms is really, really fast.
– Gordon Linoff
Nov 21 '18 at 12:42
3
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
Nov 21 '18 at 12:44
add a comment |
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
sql postgresql
asked Nov 21 '18 at 12:00
Kris MPKris MP
56811024
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
sql postgresql
sql postgresql
asked Nov 21 '18 at 12:00
Kris MPKris MP
56811024
asked Nov 21 '18 at 12:00
Kris MPKris MP
56811024
edited Nov 21 '18 at 12:17
Kris MP
asked Nov 21 '18 at 12:00
Kris MPKris MP
56811024
asked Nov 21 '18 at 12:00
Kris MPKris MP
56811024
asked Nov 21 '18 at 12:00
Kris MPKris MP
56811024
56811024
Why are you concerned about caching? How long does it take the query to run?
– Gordon Linoff
Nov 21 '18 at 12:02
@GordonLinoff 2.752 ms in average, what if the data is too large to count in every hour?
– Kris MP
Nov 21 '18 at 12:04
1
What if new data is inserted into the table? what if the value oftodaychanges overnight?
– wildplasser
Nov 21 '18 at 12:05
1
2.752 ms is really, really fast.
– Gordon Linoff
Nov 21 '18 at 12:42
3
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
Nov 21 '18 at 12:44
add a comment |
Why are you concerned about caching? How long does it take the query to run?
– Gordon Linoff
Nov 21 '18 at 12:02
@GordonLinoff 2.752 ms in average, what if the data is too large to count in every hour?
– Kris MP
Nov 21 '18 at 12:04
1
What if new data is inserted into the table? what if the value oftodaychanges overnight?
– wildplasser
Nov 21 '18 at 12:05
1
2.752 ms is really, really fast.
– Gordon Linoff
Nov 21 '18 at 12:42
3
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
Nov 21 '18 at 12:44
Why are you concerned about caching? How long does it take the query to run?
– Gordon Linoff
Nov 21 '18 at 12:02
Why are you concerned about caching? How long does it take the query to run?
– Gordon Linoff
Nov 21 '18 at 12:02
@GordonLinoff 2.752 ms in average, what if the data is too large to count in every hour?
– Kris MP
Nov 21 '18 at 12:04
@GordonLinoff 2.752 ms in average, what if the data is too large to count in every hour?
– Kris MP
Nov 21 '18 at 12:04
1
1
What if new data is inserted into the table? what if the value of
todaychanges overnight?– wildplasser
Nov 21 '18 at 12:05
What if new data is inserted into the table? what if the value of
todaychanges overnight?– wildplasser
Nov 21 '18 at 12:05
1
1
2.752 ms is really, really fast.
– Gordon Linoff
Nov 21 '18 at 12:42
2.752 ms is really, really fast.
– Gordon Linoff
Nov 21 '18 at 12:42
3
3
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
Nov 21 '18 at 12:44
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
Nov 21 '18 at 12:44
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 14:00
KibGzrKibGzr
1,496611
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53411590%2fpostgresql-query-and-data-caching%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 14:00
KibGzrKibGzr
1,496611
add a comment |
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 '18 at 14:00
KibGzrKibGzr
1,496611
add a comment |
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 '18 at 14:00
KibGzrKibGzr
1,496611
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 '18 at 14:00
KibGzrKibGzr
1,496611
answered Nov 21 '18 at 14:00
KibGzrKibGzr
1,496611
answered Nov 21 '18 at 14:00
KibGzrKibGzr
1,496611
answered Nov 21 '18 at 14:00
KibGzrKibGzr
1,496611
1,496611
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53411590%2fpostgresql-query-and-data-caching%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why are you concerned about caching? How long does it take the query to run?
– Gordon Linoff
Nov 21 '18 at 12:02
@GordonLinoff 2.752 ms in average, what if the data is too large to count in every hour?
– Kris MP
Nov 21 '18 at 12:04
1
What if new data is inserted into the table? what if the value of
todaychanges overnight?– wildplasser
Nov 21 '18 at 12:05
1
2.752 ms is really, really fast.
– Gordon Linoff
Nov 21 '18 at 12:42
3
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
Nov 21 '18 at 12:44