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

31–40 of 47 posts

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

#31

Post by tucnak »

bubblyworld wrote: post #30
tucnak wrote: post #26

Earlier quoted context omitted.

How is it different? Isn't a graph basically two sets of tuples: edges and nodes? I played with Cayley (Google) for a little while, & that was my impression.

I think it's less a matter of "can you represent graphs in a relational DB" (of course you can), and more about what kind of queries the DB is optimised for. Graph databases are intended for complex recursive queries on relatively unstructured data. You could certainly do that in SQL if you wanted to, but you'll pay for it performance-wise. Graph query languages also make those kinds of queries much easier to express…

So the underlying storage is conventional, it's still tuples of some kind, and it's only a matter of how indexes are laid out? Otherwise, I'm struggling to see how it could "optimise" for certain access patterns. How would a typical graph database index be different from a btree access method in Postgres?

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

#32

Post by BlooIt »

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…

Checkout https://github.com/Pometry/Raphtory, it's written in Rust, embedded (the binaries are about 20mb) and you can use the Python APIs as a drop-in replacement for NetworkX. Disclaimer, I am one of the people behind it.

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

#34

Post by bubblyworld »

tucnak wrote: post #31
bubblyworld wrote: post #30

Earlier quoted context omitted.

I think it's less a matter of "can you represent graphs in a relational DB" (of course you can), and more about what kind of queries the DB is optimised for. Graph databases are intended for complex recursive queries on relatively unstructured data. You could certainly do that in SQL if you wanted to, but you'll pay for it performance-wise. Graph query languages also make those kinds of queries much easier to express…

So the underlying storage is conventional, it's still tuples of some kind, and it's only a matter of how indexes are laid out? Otherwise, I'm struggling to see how it could "optimise" for certain access patterns. How would a typical graph database index be different from a btree access method in Postgres?

I don't know much about the internal details of postgres. But there is a ton of detail underlying "it's just tuples of some kind" and there are lots of ways to implement indices, no? Is it so difficult to imagine that different implementations have different performance properties?

There's also the query planner layer to think about too.

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

#35

Post by thesz »

threeseed wrote: post #24
nemo44x wrote: post #12

Earlier quoted context omitted.

Neo4j is pretty bad and very dated. No idea what they’re doing. MemGraph is a much better tool. Really graph is a feature and not a product.

Neo4J is mature not dated which is why it's so popular. And couldn't disagree more that graph is a feature. You really want something optimised for it (query language / storage approach) as the data structure is so different in every way from a relational or document store.

> ...as the data structure is so different in every way from a relational or document store.

No, it is not.

[1] https://en.wikipedia.org/wiki/Worst-case_optimal_join_algori...

Graph processing can create substantial amount of intermediate data if it is done in typical join implementation fashion (nested loops or hash join). So it may appear that graph processing needs a tailored approach.

But what can help graph algorithms can help SQL query execution as well and vice versa, see the link above.

For example, TPC-DS contains queries that (indirectly) joins same tables multiple times (query 4, for example). This is, basically, a kind of centrality metric computation for a graph represented by the tables.

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

#36

Post by zozbot234 »

henryfjordan wrote: post #13
SahAssar wrote: post #10

Earlier quoted context omitted.

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?

We basically had a single table that we wanted to be able to nest on itself arbitrarily. Think categories and subcategories, maybe 100k nodes/rows Postgres worked fine but cypher is so much more expressive and handles stuff like loop detection for you, neo4j was much easier to work with. Performance wasn't ever really an issue with either.

Note that more recent versions of Postgres have added support for the CYCLE keyword, for easier loop detection.

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

#37

Post by demaga »

vincnetas wrote: post #28

Any comments on "Apache AGE"? Apache AGE™ is a PostgreSQL that provides graph database functionality. https://age.apache.org

I think Apache AGE is much more generic, as it can parse Cypher queries and comes with a bunch of utility functions.

OP article is more like a hack, and a good one! It seems like you can achieve a lot of what you might expect from graph database with pgRouting functions and good old SQL.

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

#38

Post by pjd7 »

Interesting in hearing some thoughts about using roaring bitmaps stored in a bytea postgres column to represent adjacency matrixes.

I was thinking that given RDS has support for plrust and PostgreSQL's SPI I could use the fact they support croaring-rs there as a crate and build upon that.

I figure I can use that to represent many graph's with say 100s to ~100m nodes and many relations between these things. But each graph would be tenanted to a tenant (company/b2b saas use case).

I was thinking that by using plrust storing the roaring bitmap on the DB server in a bytea and using SPI, I can benefit from the minimal network overhead to mutate and query against the bitmap with croaring. Using SPI locally in the DB server I eliminate network overhead shipping that back to my application code.

PostgreSQL also gives me transaction safety to updates etc. And a bunch of support for other column base data such as my tenant ID column, some JSONB for relationship metadata to query on etc.

Basically something like https://jazco.dev/2024/04/20/roaring-bitmaps/ but on postgres. Given I need to support many tenanted graphs & we're already using citus this seems like something that is feasible at a larger scale too.

I was wondering though if I am going to need to create some operator classes to allow me to index relations a bit better (probably seems likely I think).

I am aware of https://github.com/ChenHuajun/pg_roaringbitmap but would prefer to use int64s and maybe start out on RDS instead of having to add another workload to our self hosted citus cluster/s.

Happy to be told I am fool and any insights would be nice. I am potentially going to try this out on some of our data sets we have because our product team is basically laying out a vision where they want us to have a graph powering a bunch of things.

I don't like the idea of neo4j when we're already deep into PostgreSQL for a bunch of workloads (~20+ TB table workloads etc so we have some reasonable inhouse PG experience).

Also huge thanks to the author of the blog post. I had been looking at pgRouting and wondering with a tilted head.. hmm seems like we can just use this as a graph DB. So that is also on my list to test out.

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

#39

Post by pjd7 »

BlooIt wrote: post #32
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…

Checkout https://github.com/Pometry/Raphtory , it's written in Rust, embedded (the binaries are about 20mb) and you can use the Python APIs as a drop-in replacement for NetworkX. Disclaimer, I am one of the people behind it.

This looks super interesting.

Just starting to review it but my front of mind questions: 1) How do I handle persistence? Looks like some code is missing. 2) Do you support multi-tenancy (b2b saas graph backend for handling relations scoped to a tenant)

Thanks

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

#40

Post by BlooIt »

pjd7 wrote: post #39
BlooIt wrote: post #32

Earlier quoted context omitted.

Checkout https://github.com/Pometry/Raphtory , it's written in Rust, embedded (the binaries are about 20mb) and you can use the Python APIs as a drop-in replacement for NetworkX. Disclaimer, I am one of the people behind it.

This looks super interesting. Just starting to review it but my front of mind questions: 1) How do I handle persistence? Looks like some code is missing. 2) Do you support multi-tenancy (b2b saas graph backend for handling relations scoped to a tenant) Thanks

Good questions.

1) You can persist a graph to disk. By default, this uses protobuf (`save_to_file`), however we’re migrating to Parquet in next release for better performance because we noticed loading a 100m edge graph from scratch (CSV, Pandas, or raw Parquet) is actually faster (~1M rows/sec) than from persisted proto, which isn’t ideal. There’s also a private version that uses custom memory buffers for on-disk storage, handling updates and compaction automatically.

2) You can run a Raphtory instance either as a GraphQL server or an embedded library. For the server, multiple users can query the persisted graphs, which are stored in a simple folder structure with namespaces (for different graphs). For now, access control needs to be managed externally, however it's on our roadmap!

Post reply on HN