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

Representing Graphs in PostgreSQL

richard-towers.com

Post reply on HN
Print view View original

41–50 of 80 posts

Re: Representing Graphs in PostgreSQL

#41

Post by eurg »

demaga wrote: post #33
osigurdson wrote: post #22

It is always good to know, at what point does "Postgres as X" break down. For instance, I know from experience that Postgres as timeseries DB (without add-ons) starts to break down in low billions of rows. It would be great to know that for graph DBs as well. I think a lot of people would prefer just to use Postgres if they can get away with it.

I've done almost exactly the kind of thing described in article for a couple millions of rows. It broke down when I needed to find "friends of friends" of depth 6. None of the optimizations I could come up with helped. Neither did Apache Age. Maybe I'm just not skilled enough to handle such a workload with Postgres. But Neo4j handled it easily

I'm not sure if you are serious or joking. "6 degrees of separation" is the famous radius for how many steps are needed such that everyone is connected.

https://en.wikipedia.org/wiki/Six_degrees_of_separation

Re: Representing Graphs in PostgreSQL

#42

Post by SJC_Hacker »

voodooEntity wrote: post #19
SJC_Hacker wrote: post #15

Earlier quoted context omitted.

The only problem with representing graphs on an RDBMS is the queries. So whats the big issue if queries are added which can handle the graph cases?

Thats like saying why do we need json we can represent it also in a textfile but accessing values can be more tricky. Sure you can represent a GraphDB in a RDBMS - but graphdbs are optimized for their specific use case. Therefor for example locking of resources is optimized for said purpose. When going a "Edge" and "Node" table approach, locking is by default rather unoptimized in many RDBMS. Just one simple example.

> Thats like saying why do we need json we can represent it also in a textfile but accessing values can be more tricky.

If your text processing stack works good enough, and JSON only solves edge cases, then I think its a legit argument against adding another tech to the stack.

Its not like everyone immediately switched over to JSON even if they could have.

And the analogy may not be the best - in the case of graph databases, they don't do what RDBMS can do, at least not as efficiently.

So if we need some graph functionality in our RDBMS, do we split the database and use a "real" graph database or just incorporate the graph functionality into the existing DB? The choice seems quite easy for me, because there are whole host of issues in splitting DBs (duplication of information, inconsistency, etc.)

Re: Representing Graphs in PostgreSQL

#43

Post by jascha_eng »

osigurdson wrote: post #22

It is always good to know, at what point does "Postgres as X" break down. For instance, I know from experience that Postgres as timeseries DB (without add-ons) starts to break down in low billions of rows. It would be great to know that for graph DBs as well. I think a lot of people would prefer just to use Postgres if they can get away with it.

I mean there is a lot of extensions and optimizations that you can pull on a postgres instance before you truly have to abandon it.

For timeseries I'd argue with timescale there is very few use cases that ever outgrow postgres. But I might be biased.

Re: Representing Graphs in PostgreSQL

#44

Post by jsumrall »

voodooEntity wrote: post #8

Im a big fan of GraphDatabase's since about 10 years. I even wrote my own "in memory graph storage" in golang for a specific use case that none of the big GraphDatabase's could cover at the time. That said - i WISH people would embrase the existing GraphDatabases more and make the hosters support them as standard, rather than abusing existing relational databases for graph purposes. And to make it clear,i'm not talki…

My personal opinion is that nobody should touch OrientDB with a 10 ft pole.

Re: Representing Graphs in PostgreSQL

#46

Post by sureglymop »

osigurdson wrote: post #22

It is always good to know, at what point does "Postgres as X" break down. For instance, I know from experience that Postgres as timeseries DB (without add-ons) starts to break down in low billions of rows. It would be great to know that for graph DBs as well. I think a lot of people would prefer just to use Postgres if they can get away with it.

I've been running into exactly that problem. Which time series add-on would you recommend looking into?

Re: Representing Graphs in PostgreSQL

#47

Post by redbell »

osigurdson wrote: post #22

It is always good to know, at what point does "Postgres as X" break down. For instance, I know from experience that Postgres as timeseries DB (without add-ons) starts to break down in low billions of rows. It would be great to know that for graph DBs as well. I think a lot of people would prefer just to use Postgres if they can get away with it.

> "Postgres as X"

This reminds me of "Just Use Postgres for Everything": https://news.ycombinator.com/item?id=33934139

Re: Representing Graphs in PostgreSQL

#48

Post by idefine »

hknlof1 wrote: post #24

At DuckCon #6 there was a talk given about implementing SQL/PGQ as an extension to DuckDB https://m.youtube.com/watch?v=QDdTbhSR2Vo https://duckdb.org/community_extensions/extensions/duckpgq.h...

I recently came across https://kuzudb.com/ which takes a lot of inspiration from DuckDB. Seems promising for embedded.

Re: Representing Graphs in PostgreSQL

#50

Post by pella »

mycall wrote: post #39
pella wrote: post #27

If you need some "Routing Graph" - check the pgRouting library https://pgrouting.org/ "pgRouting library contains following features: All Pairs Shortest Path, Johnson’s Algorithm All Pairs Shortest Path, Floyd-Warshall Algorithm Shortest Path A* Bi-directional Dijkstra Shortest Path Bi-directional A\* Shortest Path Shortest Path Dijkstra Driving Distance K-Shortest Path, Multiple Alternative Paths K-Dijkstra, One to…

Is it normally preferred to use some library such as this over a more traditional approach of nested sets, adjacency lists or ltree module?

PG Database + "The Boost Graph Library (BGL)" C++ ( https://www.boost.org/doc/libs/1_87_0/libs/graph/doc/index.h... )+ PostGIS ==> PGRouting

Just check the Workshop : https://workshop.pgrouting.org/3.0/en/index.html

Pedestrian Routing:

https://workshop.pgrouting.org/3.0/en/basic/pedestrian.html#...

Graph views:

https://workshop.pgrouting.org/3.0/en/basic/graph_views.html

Create a Network Topology:

https://workshop.pgrouting.org/3.0/en/advanced/chapter-12.ht...

Post reply on HN