For large datasets are JOINS better than using JSONB or vice versa, in Postgres?
Here is a simple use case;
I have a posts entity and a tags entity in my app.
There are two types of queries that i want to run here;
- fetch posts for a tag
- fetch tags for a post
The two options That I can go with is
- have separate tables for posts and tags. Then do a join
- add tags as a jsonb column in my posts table. when trying to fetch posts for a tag (nature), I can do
@> "nature"and order by post created date.
When I want to fetch tags for a post, well I dont even need to do anything.
To me JSONB feels much more easier to use and reason with (coming from the JS world).
But I am not able to paint a picture of the performance tradeoff if I were to go for this approach. I understand that JSOB will be much faster when I dont have to filter/search through the objects themselves.
But what if I have to search for a JSONB attribute in a posts table containing 1M records, each with say 20 or so tags? Will going for joins be a far more performant option in that case?
postgresql join jsonb
asked Nov 22 '18 at 6:39
Rishav SharanRishav Sharan
83152237
add a comment |
Here is a simple use case;
I have a posts entity and a tags entity in my app.
There are two types of queries that i want to run here;
- fetch posts for a tag
- fetch tags for a post
The two options That I can go with is
- have separate tables for posts and tags. Then do a join
- add tags as a jsonb column in my posts table. when trying to fetch posts for a tag (nature), I can do
@> "nature"and order by post created date.
When I want to fetch tags for a post, well I dont even need to do anything.
To me JSONB feels much more easier to use and reason with (coming from the JS world).
But I am not able to paint a picture of the performance tradeoff if I were to go for this approach. I understand that JSOB will be much faster when I dont have to filter/search through the objects themselves.
But what if I have to search for a JSONB attribute in a posts table containing 1M records, each with say 20 or so tags? Will going for joins be a far more performant option in that case?
postgresql join jsonb
asked Nov 22 '18 at 6:39
Rishav SharanRishav Sharan
83152237
blog.2ndquadrant.com/…
– a_horse_with_no_name
Nov 22 '18 at 6:42
@a_horse_with_no_name Thanks for looking into my question. I have read that blog, but I didn't feel like the 3 major points raised against JSONB - baroque queries, no fixed typing and no constraints affect me much. I am used to having my apps enforce the schemas anyway. My major worry is around any perf implications.
– Rishav Sharan
Nov 22 '18 at 6:48
add a comment |
Here is a simple use case;
I have a posts entity and a tags entity in my app.
There are two types of queries that i want to run here;
- fetch posts for a tag
- fetch tags for a post
The two options That I can go with is
- have separate tables for posts and tags. Then do a join
- add tags as a jsonb column in my posts table. when trying to fetch posts for a tag (nature), I can do
@> "nature"and order by post created date.
When I want to fetch tags for a post, well I dont even need to do anything.
To me JSONB feels much more easier to use and reason with (coming from the JS world).
But I am not able to paint a picture of the performance tradeoff if I were to go for this approach. I understand that JSOB will be much faster when I dont have to filter/search through the objects themselves.
But what if I have to search for a JSONB attribute in a posts table containing 1M records, each with say 20 or so tags? Will going for joins be a far more performant option in that case?
postgresql join jsonb
asked Nov 22 '18 at 6:39
Rishav SharanRishav Sharan
83152237
Here is a simple use case;
I have a posts entity and a tags entity in my app.
There are two types of queries that i want to run here;
- fetch posts for a tag
- fetch tags for a post
The two options That I can go with is
- have separate tables for posts and tags. Then do a join
- add tags as a jsonb column in my posts table. when trying to fetch posts for a tag (nature), I can do
@> "nature"and order by post created date.
When I want to fetch tags for a post, well I dont even need to do anything.
To me JSONB feels much more easier to use and reason with (coming from the JS world).
But I am not able to paint a picture of the performance tradeoff if I were to go for this approach. I understand that JSOB will be much faster when I dont have to filter/search through the objects themselves.
But what if I have to search for a JSONB attribute in a posts table containing 1M records, each with say 20 or so tags? Will going for joins be a far more performant option in that case?
postgresql join jsonb
postgresql join jsonb
asked Nov 22 '18 at 6:39
Rishav SharanRishav Sharan
83152237
asked Nov 22 '18 at 6:39
Rishav SharanRishav Sharan
83152237
asked Nov 22 '18 at 6:39
Rishav SharanRishav Sharan
83152237
asked Nov 22 '18 at 6:39
Rishav SharanRishav Sharan
83152237
asked Nov 22 '18 at 6:39
Rishav SharanRishav Sharan
83152237
83152237
blog.2ndquadrant.com/…
– a_horse_with_no_name
Nov 22 '18 at 6:42
@a_horse_with_no_name Thanks for looking into my question. I have read that blog, but I didn't feel like the 3 major points raised against JSONB - baroque queries, no fixed typing and no constraints affect me much. I am used to having my apps enforce the schemas anyway. My major worry is around any perf implications.
– Rishav Sharan
Nov 22 '18 at 6:48
add a comment |
blog.2ndquadrant.com/…
– a_horse_with_no_name
Nov 22 '18 at 6:42
@a_horse_with_no_name Thanks for looking into my question. I have read that blog, but I didn't feel like the 3 major points raised against JSONB - baroque queries, no fixed typing and no constraints affect me much. I am used to having my apps enforce the schemas anyway. My major worry is around any perf implications.
– Rishav Sharan
Nov 22 '18 at 6:48
blog.2ndquadrant.com/…
– a_horse_with_no_name
Nov 22 '18 at 6:42
blog.2ndquadrant.com/…
– a_horse_with_no_name
Nov 22 '18 at 6:42
@a_horse_with_no_name Thanks for looking into my question. I have read that blog, but I didn't feel like the 3 major points raised against JSONB - baroque queries, no fixed typing and no constraints affect me much. I am used to having my apps enforce the schemas anyway. My major worry is around any perf implications.
– Rishav Sharan
Nov 22 '18 at 6:48
@a_horse_with_no_name Thanks for looking into my question. I have read that blog, but I didn't feel like the 3 major points raised against JSONB - baroque queries, no fixed typing and no constraints affect me much. I am used to having my apps enforce the schemas anyway. My major worry is around any perf implications.
– Rishav Sharan
Nov 22 '18 at 6:48
add a comment |
0
active
oldest
votes
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%2f53425173%2ffor-large-datasets-are-joins-better-than-using-jsonb-or-vice-versa-in-postgres%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53425173%2ffor-large-datasets-are-joins-better-than-using-jsonb-or-vice-versa-in-postgres%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
blog.2ndquadrant.com/…
– a_horse_with_no_name
Nov 22 '18 at 6:42
@a_horse_with_no_name Thanks for looking into my question. I have read that blog, but I didn't feel like the 3 major points raised against JSONB - baroque queries, no fixed typing and no constraints affect me much. I am used to having my apps enforce the schemas anyway. My major worry is around any perf implications.
– Rishav Sharan
Nov 22 '18 at 6:48