Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres as a Graph Database: (Ab)Using PgRouting

supabase.com

Post reply on HN
Print view View original

1–10 of 47 posts

Re: Postgres as a Graph Database: (Ab)Using PgRouting

#6

Post by nyrikki »

xnx wrote: post #3

Resourceful, but is there a reason to use this approach over pgvector?

There is an unfortunate overloaded of the terms relational and relationships in relational databases.

Relationships is association between relations/tables, parent-child, node/edge etc, depending on model, extensions etc..

There are three basic models of databases:

      model name.  | basic data structure
      -----------------------------------
      relational   | tables
      hierarchical | trees
      network      | graph
A "relational" in RDBMS and Codd's rules is just a table data structure with some additional rules.

Part of those rules are a named table, with named and typed attributes (columns) with data in the form of rows of tuples.

PgVector is nearest neighbor search for tuple values, often from a single table/relation while PgRouting is graph traversal for relational data.

There is a bit more to that and in the relational model the data is independent of the schema, and no RDBMS is pure.

It is possibly helpful to realize that pgvector is about finding neighbors among tuples, in that relation/table, and that it is very different from graph traversal.

Re: Postgres as a Graph Database: (Ab)Using PgRouting

#7

Post by ashvardanian »

Five years ago I was absolutely frustrated with the state of Graph databases and libraries and tried putting several non-Graph DBMSs behind a NetworkX-like Python interface https://github.com/unum-cloud/NetworkXum>.

When benchmarked, Neo4J crashed on every graph I’ve tried https://www.unum.cloud/blog/2020-11-12-graphs>, making SQLite and Postgres much more viable options even for network-processing workloads. So I wouldn’t be surprised to learn that people actually use pgRouting and Supabase in that setting.

With the rise of Postgres-compatible I’m wondering if it’s worth refreshing the project. Similarly, there are now more Graph DBs like MemGraph compatible with CYPHER, which should probably work much better than Neo4J.

Re: Postgres as a Graph Database: (Ab)Using PgRouting

#8

Post by kiwicopple »

My original goal in this article was to figure out if pgrouting would be a good tool to build a memory-layer (for AI/agents) but the article got a bit long. Early results are promising - I’ll follow up with another article soon

there are some other interesting extensions in this space - onesparse[0] is early in development but pretty exciting as it builds on SuiteSparse which is very mature

[0] https://onesparse.com/docs.html

Re: Postgres as a Graph Database: (Ab)Using PgRouting

#9

Post by henryfjordan »

ashvardanian wrote: post #7

Five years ago I was absolutely frustrated with the state of Graph databases and libraries and tried putting several non-Graph DBMSs behind a NetworkX-like Python interface https://github.com/unum-cloud/NetworkXum >. When benchmarked, Neo4J crashed on every graph I’ve tried https://www.unum.cloud/blog/2020-11-12-graphs >, making SQLite and Postgres much more viable options even for network-processing workloads. So I…

I had almost exactly the opposite experience, although my dataset was pretty small.

We wanted to store a graph in postgres and ended up writing some recursive queries to pull subgraphs then had NetworkX layered over it to do some more complex graph operations. We ended up doing that for a short while but then switched to Neo4j because of how comparatively easy it was to write queries (although the Python support for Neo4j was severely lacking). Never really stressed it out on dataset size though.

I did manage to crash Redis' graph plugin pretty quickly when I was testing that.

Re: Postgres as a Graph Database: (Ab)Using PgRouting

#10

Post by SahAssar »

henryfjordan wrote: post #9
ashvardanian wrote: post #7

Five years ago I was absolutely frustrated with the state of Graph databases and libraries and tried putting several non-Graph DBMSs behind a NetworkX-like Python interface https://github.com/unum-cloud/NetworkXum >. When benchmarked, Neo4J crashed on every graph I’ve tried https://www.unum.cloud/blog/2020-11-12-graphs >, making SQLite and Postgres much more viable options even for network-processing workloads. So I…

I had almost exactly the opposite experience, although my dataset was pretty small. We wanted to store a graph in postgres and ended up writing some recursive queries to pull subgraphs then had NetworkX layered over it to do some more complex graph operations. We ended up doing that for a short while but then switched to Neo4j because of how comparatively easy it was to write queries (although the Python support for…

Not sure what you consider "quite small" and I don't know how NetworkX works, but postgresql recursive queries have worked well for me for small graphs.

Could you share what the data structure and scale was?

Post reply on HN