4 ms·

I feel like a graph database is a solution to an issue I've faced (and, continue to face) and it may just be because that I haven't spun one up and tried or tha

I feel like a graph database is a solution to an issue I've faced (and, continue to face) and it may just be because that I haven't spun one up and tried or that the documentation/examples don't stick out. But could someone confirm my feeling? If my feeling is correct, I'd enjoy verifying it with EdgeDB or the like. My example/requirement: I have a user wanting to find best-matching blog posts. Every post is tagged with a given category. There could be 100+ categories in the blog system and a blog post could be tagged with any number of these system categories. A user wants to see all posts tagged with "angular", "nestjs", "cypress" and "nx". The resulting list should return and be sorted by the best matches, to those of least relevance. So, posts that include all four tags should be up top and as the user browses down the results, there are posts with less matching tags. What I've seen with SQL looks expensive, especially if you search with more and more tags. I may just not know what to search for though, re. SQL. Is there a query against a graph database that could accomplish this?

rmbyrro 5y ago
Thanks for posting this. Kind of comment that adds value to the discussion by illustrating how a piece of tech can or cannot be useful. I just happen to have a very similar requirement to yours and was also wondering.
klohto 5y ago
Is it though? Simple IN with an ORDER BY on the same match will return the correct ranking. More info on ranking here https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING https://www.postgresql.org/docs/current/textsearch-controls....
- d_watt 5y ago
  Am I right in reading this as the parent comment envisioning a “post” table and a “tag” table, and you’re suggest the “post” table just have a “tag” column?
  - klohto 5y ago
    I see just one table > Every post is tagged with a given category. There could be 100+ categories in the blog system and a blog post could be tagged with any number of these system categories. My point is, I don’t see SQL query as expensive for this kind of use case. There are easy and native ways to do it. In case you would like a top notch performance, Redis might be a way to do it. Even a reverse-index would achieve great performance.
    - thaumasiotes 5y ago
      That's a standard many-to-many relationship that would normally be implemented by three tables: +---------+-------+---------+-------+-----------+ | post_id | title | content | other | fields... | +--------+------+ | tag_id | name | +------------+---------+--------+ | tagging_id | post_id | tag_id | But it seems like the core of the request is still something like: SELECT post_id, count(1) AS count FROM taggings WHERE tag_id IN (3, 8, 255) GROUP BY post_id ORDER BY count DESC (off the top of my head; I haven't checked this for any kind of correctness) And I don't see why that query suffers as you add tags...? ------------ EDIT responding to below [HN believes I am a problem user who should only be allowed to make so many comments per day]: < that is pretty much what I meant by “I see just one table” as you don’t need any joins Well, assuming you're doing this because a user is interacting with your site via some kind of web interface, you can set the interface up to deliver you tag_id values directly, but you'll still need to do a join with the posts table so you can present a list of posts back to the user instead of a list of internal post_id values. So I guess SELECT t.post_id, count(1) AS count, p.title, p.url FROM taggings t JOIN posts p ON t.post_id = p.post_id ...
      - klohto 5y ago
        Confusing but that is pretty much what I meant by “I see just one table” as you don’t need any joins (atleast with the same design you outline)
rmbyrro 5y ago
I'm currently investigating whether Redis Bloom [1] could be a good tool for similar requirement. [1] https://github.com/RedisBloom/RedisBloom https://github.com/RedisBloom/RedisBloom
FractalHQ 5y ago
I do these often with standard GraphQL queries, often over Postgres. Now I’m curious about the performance difference compared to an SQL ORDERBY or similar EdgeDB implementation!
contingencies 5y ago
select posts.name as post, count(post_tags.id) as matches from posts,post_tags where post_tags.post=posts.id and post_tags.tag in ("angular","nestjs","cypress","nx") group by post_tags.post order by matches desc; Test data @ http://pratyeka.org/hn.sqlite3 http://pratyeka.org/hn.sqlite3
colinmcd 5y ago
EdgeDB employee here. I couldn't have asked for a better question to demonstrate the power of subqueries! Here's how I'd do this in EdgeQL: with tag_names := {"angular", "nestjs", "cypress", "nx"}, select BlogPost { title, tag_names := .tags.name, match_count := count((select .tags filter .name in tag_names)) } order by .match_count desc; Which would give you a result like this: [ { title: 'All the frameworks!', tag_names: ['angular', 'nestjs', 'cypress', 'nx'], match_count: 4, }, { title: 'Nest + Cypress', tag_names: ['nestjs', 'cypress'], match_count: 2, }, { title: 'NX is cool', tag_names: ['nx'], match_count: 1, }, ];
- thaumasiotes 5y ago
  So as I read TheSpiciestDev's comment, he's complaining that making his query in PostgreSQL is slow. It looks like EdgeDB is a frontend to PostgreSQL; how will it help with TheSpiciestDev's problem?
  - RedCrowbar 5y ago
    The problem sounds like something that could be solved with a GIST index. EdgeDB doesn't yet have a way to specify the index type, though, mostly because we aren't sure what would be the best way to do it without things becoming too Postgres-specific in schemas.
    - eenell 5y ago
      What do you mean by "too Postgres-specific"? Will you be supporting other DBs behind the EdgeDB interface in the future?
      - RedCrowbar 5y ago
        This is not something we plan to do in the near future, but it’s also not outside the realm of possibility. We picked Postgres because of its power, quality and unparalleled extensibility, but we are also very careful to not leak any implementation details into our interfaces.
AtNightWeCode 5y ago
Maybe I misunderstand what you are saying but it sounds pretty straight forward in SQL.

rmbyrro 5y ago

Thanks for posting this. Kind of comment that adds value to the discussion by illustrating how a piece of tech can or cannot be useful. I just happen to have a very similar requirement to yours and was also wondering.

klohto 5y ago

Is it though? Simple IN with an ORDER BY on the same match will return the correct ranking. More info on ranking here https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING https://www.postgresql.org/docs/current/textsearch-controls....

d_watt 5y ago
Am I right in reading this as the parent comment envisioning a “post” table and a “tag” table, and you’re suggest the “post” table just have a “tag” column?
- klohto 5y ago
  I see just one table > Every post is tagged with a given category. There could be 100+ categories in the blog system and a blog post could be tagged with any number of these system categories. My point is, I don’t see SQL query as expensive for this kind of use case. There are easy and native ways to do it. In case you would like a top notch performance, Redis might be a way to do it. Even a reverse-index would achieve great performance.
  - thaumasiotes 5y ago
    That's a standard many-to-many relationship that would normally be implemented by three tables: +---------+-------+---------+-------+-----------+ | post_id | title | content | other | fields... | +--------+------+ | tag_id | name | +------------+---------+--------+ | tagging_id | post_id | tag_id | But it seems like the core of the request is still something like: SELECT post_id, count(1) AS count FROM taggings WHERE tag_id IN (3, 8, 255) GROUP BY post_id ORDER BY count DESC (off the top of my head; I haven't checked this for any kind of correctness) And I don't see why that query suffers as you add tags...? ------------ EDIT responding to below [HN believes I am a problem user who should only be allowed to make so many comments per day]: < that is pretty much what I meant by “I see just one table” as you don’t need any joins Well, assuming you're doing this because a user is interacting with your site via some kind of web interface, you can set the interface up to deliver you tag_id values directly, but you'll still need to do a join with the posts table so you can present a list of posts back to the user instead of a list of internal post_id values. So I guess SELECT t.post_id, count(1) AS count, p.title, p.url FROM taggings t JOIN posts p ON t.post_id = p.post_id ...
    - klohto 5y ago
      Confusing but that is pretty much what I meant by “I see just one table” as you don’t need any joins (atleast with the same design you outline)

rmbyrro 5y ago

I'm currently investigating whether Redis Bloom [1] could be a good tool for similar requirement. [1] https://github.com/RedisBloom/RedisBloom https://github.com/RedisBloom/RedisBloom

FractalHQ 5y ago

I do these often with standard GraphQL queries, often over Postgres. Now I’m curious about the performance difference compared to an SQL ORDERBY or similar EdgeDB implementation!

contingencies 5y ago

select posts.name as post, count(post_tags.id) as matches from posts,post_tags where post_tags.post=posts.id and post_tags.tag in ("angular","nestjs","cypress","nx") group by post_tags.post order by matches desc; Test data @ http://pratyeka.org/hn.sqlite3 http://pratyeka.org/hn.sqlite3

colinmcd 5y ago

EdgeDB employee here. I couldn't have asked for a better question to demonstrate the power of subqueries! Here's how I'd do this in EdgeQL: with tag_names := {"angular", "nestjs", "cypress", "nx"}, select BlogPost { title, tag_names := .tags.name, match_count := count((select .tags filter .name in tag_names)) } order by .match_count desc; Which would give you a result like this: [ { title: 'All the frameworks!', tag_names: ['angular', 'nestjs', 'cypress', 'nx'], match_count: 4, }, { title: 'Nest + Cypress', tag_names: ['nestjs', 'cypress'], match_count: 2, }, { title: 'NX is cool', tag_names: ['nx'], match_count: 1, }, ];

thaumasiotes 5y ago
So as I read TheSpiciestDev's comment, he's complaining that making his query in PostgreSQL is slow. It looks like EdgeDB is a frontend to PostgreSQL; how will it help with TheSpiciestDev's problem?
- RedCrowbar 5y ago
  The problem sounds like something that could be solved with a GIST index. EdgeDB doesn't yet have a way to specify the index type, though, mostly because we aren't sure what would be the best way to do it without things becoming too Postgres-specific in schemas.
  - eenell 5y ago
    What do you mean by "too Postgres-specific"? Will you be supporting other DBs behind the EdgeDB interface in the future?
    - RedCrowbar 5y ago
      This is not something we plan to do in the near future, but it’s also not outside the realm of possibility. We picked Postgres because of its power, quality and unparalleled extensibility, but we are also very careful to not leak any implementation details into our interfaces.

AtNightWeCode 5y ago

Maybe I misunderstand what you are saying but it sounds pretty straight forward in SQL.