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

41–47 of 47 posts

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

#41

Post by tucnak »

bubblyworld wrote: post #34
tucnak wrote: post #31

Earlier quoted context omitted.

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.

[flagged]

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

#42

Post by Nelkins »

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

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

#43

Post by bubblyworld »

tucnak wrote: post #41
bubblyworld wrote: post #34

Earlier quoted context omitted.

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.

[flagged]

No need for the snark. If you want specific details of how postgres differs from graph databases I have nothing for you. I just find your position that btrees are optimised for every query structure... obviously false on general grounds? Like a thing to do to make recursive queries faster is to store relations as direct pointers of some kind, rather than doing index scans for every level of join.

Perhaps we're talking past each other about the word "optimised".

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

#44

Post by karussell »

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 "graph" database and a "normal SQL" database when the SQL database is able to directly point to rows (ROWID?) without a separate index?

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

#46

Post by tucnak »

bubblyworld wrote: post #43
tucnak wrote: post #41

Earlier quoted context omitted.

[flagged]

No need for the snark. If you want specific details of how postgres differs from graph databases I have nothing for you. I just find your position that btrees are optimised for every query structure... obviously false on general grounds? Like a thing to do to make recursive queries faster is to store relations as direct pointers of some kind, rather than doing index scans for every level of join. Perhaps we're talkin…

> I just find your position that btrees are optimised for every query structure

But that is not my position! Postgres has many index access methods: hash, btree, brin, gin, gist, and there are extensions for rum, bloom, skipscans, geospatial indexes such as sp-gist, & vector indexes like ivf/hnws (see pgvector.) I mean, as far as graph databases are concerned, besides pgRouting, there's also Apache AGE which is a graph-"optimised" Postgres.

You should learn more about Postgres and databases in general. See comment above. https://news.ycombinator.com/item?id=43203833 which is closely related to the argument I am actually making.

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

#47

Post by bubblyworld »

tucnak wrote: post #46
bubblyworld wrote: post #43

Earlier quoted context omitted.

No need for the snark. If you want specific details of how postgres differs from graph databases I have nothing for you. I just find your position that btrees are optimised for every query structure... obviously false on general grounds? Like a thing to do to make recursive queries faster is to store relations as direct pointers of some kind, rather than doing index scans for every level of join. Perhaps we're talkin…

> I just find your position that btrees are optimised for every query structure But that is not my position! Postgres has many index access methods: hash, btree, brin, gin, gist, and there are extensions for rum, bloom, skipscans, geospatial indexes such as sp-gist, & vector indexes like ivf/hnws (see pgvector.) I mean, as far as graph databases are concerned, besides pgRouting, there's also Apache AGE which is a gra…

Fair, and I apologise for misrepresenting it. I should definitely learn more about databases in general!
Post reply on HN