4 ms·

My advice would be to avoid attempting to solve the problem by introducing a cache layer or any auxiliary query cache, and instead remove the problem at storage

My advice would be to avoid attempting to solve the problem by introducing a cache layer or any auxiliary query cache, and instead remove the problem at storage level by leveraging a database solution that 1) stores relationships as links 2) allows queries to resolve relationships to an arbitrary level without specifying join conditions. These two things make sure your entity resolution can happen with direct lookups instead of index lookups (or pog forbid, table scans). If you absolutely cannot do that, cache at entity resolution level. Although, it really doesn’t solve the actual problem, just makes it more bearable by offloading primary DB workload to another DB, so why not run a read replica instead?

xwowsersx 4y ago
Thanks for the reply. Regarding the type of DB you referenced, what are some examples of DBs that allow relationships as links and querying to an arbitrary level? With regards to using a read replica, we do have one that we use for some queries, but that doesn't really resolve the problem with having to do many queries for certain endpoints. We can only eliminate so many queries before the backend code becomes unmanageable, because we effectively forgo any of Django's ORM in favor of our own handcrafted queries that are hard to reuse.
- eurasiantiger 4y ago
  Potentially anything which supports Cypher or has Apache TinkerPop integration, Neo4j with GraphQL, PostgreSQL with Apache AGE, EdgeDB, SurrealDB (very fresh) and Dgraph (almost but not quite production grade).
  - xwowsersx 4y ago
    Got it, thank you. We are currently using PostgreSQL so I will look into AGE, Edge and Surreal. Any opinions on one vs the other? I guess some of that depends on which one has the best packages for Python.. Thank you!
    - eurasiantiger 4y ago
      No real opinion there, my usual stack is different — those are just some of the players that came to mind. AGE has a Python driver, so maybe that’s a good place to start. Note that depending on your data model, you may not need ”complex” edges, just ”simple” links directly from vertex to vertex. Not all databases support this. Then again, you already might have N:M tables which exactly correspond to edges in a graph model. This distinction is something to keep in mind.
      - xwowsersx 4y ago
        Thanks, I will keep that in mind. EdgeDB looks really nice. It would be a rather large project to migrate our PG over to it, but maybe not so bad. I hopped on their Discord to ask if there any tools to aid with migration. They said: >>> Tools to make migrations from Django (and other ORMs) are possible but we haven't yet started working on them. So you'd have to write the migration script manually.
        1st1 4y ago
        Your comment on our Discord actually prompted an internal discussion on how we can implement this and we now have a pretty good idea. :) Not ETA or anything at this point, but we'll be looking into this soon.
        xwowsersx 4y ago
        Haha great. Would be hugely helpful to anyone thinking about migrating, but I'm sure you have your work cut out for you already and there's lots of competing things to prioritize.

xwowsersx 4y ago

Thanks for the reply. Regarding the type of DB you referenced, what are some examples of DBs that allow relationships as links and querying to an arbitrary level? With regards to using a read replica, we do have one that we use for some queries, but that doesn't really resolve the problem with having to do many queries for certain endpoints. We can only eliminate so many queries before the backend code becomes unmanageable, because we effectively forgo any of Django's ORM in favor of our own handcrafted queries that are hard to reuse.

eurasiantiger 4y ago
Potentially anything which supports Cypher or has Apache TinkerPop integration, Neo4j with GraphQL, PostgreSQL with Apache AGE, EdgeDB, SurrealDB (very fresh) and Dgraph (almost but not quite production grade).
- xwowsersx 4y ago
  Got it, thank you. We are currently using PostgreSQL so I will look into AGE, Edge and Surreal. Any opinions on one vs the other? I guess some of that depends on which one has the best packages for Python.. Thank you!
  - eurasiantiger 4y ago
    No real opinion there, my usual stack is different — those are just some of the players that came to mind. AGE has a Python driver, so maybe that’s a good place to start. Note that depending on your data model, you may not need ”complex” edges, just ”simple” links directly from vertex to vertex. Not all databases support this. Then again, you already might have N:M tables which exactly correspond to edges in a graph model. This distinction is something to keep in mind.
    - xwowsersx 4y ago
      Thanks, I will keep that in mind. EdgeDB looks really nice. It would be a rather large project to migrate our PG over to it, but maybe not so bad. I hopped on their Discord to ask if there any tools to aid with migration. They said: >>> Tools to make migrations from Django (and other ORMs) are possible but we haven't yet started working on them. So you'd have to write the migration script manually.
      - 1st1 4y ago
        Your comment on our Discord actually prompted an internal discussion on how we can implement this and we now have a pretty good idea. :) Not ETA or anything at this point, but we'll be looking into this soon.
        xwowsersx 4y ago
        Haha great. Would be hugely helpful to anyone thinking about migrating, but I'm sure you have your work cut out for you already and there's lots of competing things to prioritize.