Postgres as a Graph Database: (Ab)Using PgRouting

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 &lt;<a href="https:&#x2F;&#x2F;github.com&#x2F;unum-cloud&#x2F;NetworkXum">https:&#x2F;&#x2F;github.com&#x2F;unum-cloud&#x2F;NetworkXum</a>&gt;.<p>When benchmarked, Neo4J crashed on every graph I’ve tried &lt;<a href="https:&#x2F;&#x2F;www.unum.cloud&#x2F;blog&#x2F;2020-11-12-graphs" rel="nofollow">https:&#x2F;&#x2F;www.unum.cloud&#x2F;blog&#x2F;2020-11-12-graphs</a>&gt;, 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.<p>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.

My original goal in this article was to figure out if pgrouting would be a good tool to build a memory-layer (for AI&#x2F;agents) but the article got a bit long. Early results are promising - I’ll follow up with another article soon<p>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<p>[0] <a href="https:&#x2F;&#x2F;onesparse.com&#x2F;docs.html" rel="nofollow">https:&#x2F;&#x2F;onesparse.com&#x2F;docs.html</a>

Supabase consistently puts out such fantastic bite-sized gems - and for me some of my favorites have been related to PostGIS - whether it’s about serving tiles directly, or this (ab)use of functionality typically used in a PG geospatial context. Nothing revolutionary or massive and complex - not like reading DDIA of course, but just fun and mentally activating, making me want to jump into something new. I really applaud them for frequently posting actually engaging content that just gets you excited to work with databases… it sounds silly to say it like that, but it does feel like I get regularly struck with the feeling of sadness when I realize how vanilla all of my daily development related interactions with dbs are so vanilla.

I’ve always wondered why there isn’t a “SQLite for graphs,” so to speak. Is there something about how they have to be stored that precludes an in-process solution with disk-based storage?

I&#x27;m working on a little Poatgres graph db project. The querying and table structure is much simpler for the same task:<p><a href="https:&#x2F;&#x2F;memelang.net&#x2F;03&#x2F;" rel="nofollow">https:&#x2F;&#x2F;memelang.net&#x2F;03&#x2F;</a> <a href="https:&#x2F;&#x2F;github.com&#x2F;memelang-net&#x2F;memesql3">https:&#x2F;&#x2F;github.com&#x2F;memelang-net&#x2F;memesql3</a>

Interesting in hearing some thoughts about using roaring bitmaps stored in a bytea postgres column to represent adjacency matrixes.<p>I was thinking that given RDS has support for plrust and PostgreSQL&#x27;s SPI I could use the fact they support croaring-rs there as a crate and build upon that.<p>I figure I can use that to represent many graph&#x27;s with say 100s to ~100m nodes and many relations between these things. But each graph would be tenanted to a tenant (company&#x2F;b2b saas use case).<p>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.<p>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.<p>Basically something like <a href="https:&#x2F;&#x2F;jazco.dev&#x2F;2024&#x2F;04&#x2F;20&#x2F;roaring-bitmaps&#x2F;" rel="nofollow">https:&#x2F;&#x2F;jazco.dev&#x2F;2024&#x2F;04&#x2F;20&#x2F;roaring-bitmaps&#x2F;</a> but on postgres. Given I need to support many tenanted graphs &amp; we&#x27;re already using citus this seems like something that is feasible at a larger scale too.<p>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).<p>I am aware of <a href="https:&#x2F;&#x2F;github.com&#x2F;ChenHuajun&#x2F;pg_roaringbitmap">https:&#x2F;&#x2F;github.com&#x2F;ChenHuajun&#x2F;pg_roaringbitmap</a> 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&#x2F;s.<p>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.<p>I don&#x27;t like the idea of neo4j when we&#x27;re already deep into PostgreSQL for a bunch of workloads (~20+ TB table workloads etc so we have some reasonable inhouse PG experience).<p>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.

Any comments on &quot;Apache AGE&quot;?<p>Apache AGE™ is a PostgreSQL that provides graph database functionality.<p><a href="https:&#x2F;&#x2F;age.apache.org" rel="nofollow">https:&#x2F;&#x2F;age.apache.org</a>

Maybe a stupid question. When just looking at the data model (and not e.g. the query language) ... but is there a real difference between a &quot;graph&quot; database and a &quot;normal SQL&quot; database when the SQL database is able to directly point to rows (ROWID?) without a separate index?

Anybody have any experience creating isocrhones using PgRouting? I have a use case that involves generating isochrone maps for walking, biking, etc. but I&#x27;d like to just use Postgres if possible and avoid another piece of infrastructure like Valhalla, OpenTripPlanner, OpenRouteService, etc.

You can always trust Postgres to have another extension that opens up great new data modelling opportunities. This is great. Wonder how this stacks up to the CedarDB (Postgres-compatible) graph capabilities.

Great article! Although it seems that the last section (about YouTube recommendations) is incomplete, as there&#x27;s no query to actually calculate the recommendations.

Has anyone done something like this with a very large dataset? Hundreds of millions of rows.

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

Supabase such a gem