Postgres: The Graph Database You Didn't Know You Had

Re:Postgres: The Graph Database You Didn't Know You Had by cpa on Friday March 31, 2023 @11:51AM (#35388945)

It's a good tool as long as your queries are simple, and your graph isn't too large (which is probably the case 99% of the time). And if that allows you to have one less tool to deal with, go for it!

But I've tried to push it to its limits (using PostGIS, my nodes being land plots that were connected to each others — the biggest query used joins, lateral, recCTE, windows… you name it) and ran into many issues: can't branch out early, hard to optimize, no support for complex queries (writing anything beyond BFS and DFS is a pita). Yet, in the end I accepted the struggle (and the long queries) because it was so nice to work with my data directly where it was kept :)

[ Parent | on HN ]

Re:Postgres: The Graph Database You Didn't Know You Had by amyres on Friday March 31, 2023 @01:28PM (#35390236)

Using the built-in PostGIS topology tools? Or something more custom? I'm curious as I just started digging into and using PostGIS for a land parcel use case. I've wondered about the graph structure of parcels adjacent to others, rather than just a big unordered table of geom objects.

[ Parent | on HN ]

Re:Postgres: The Graph Database You Didn't Know You Had by cpa on Friday March 31, 2023 @01:35PM (#35390347)

Just using the geography stuff and doing joins on ST_Intersects. I couldn't guarantee that my data formed a partition of the plane, which is necessary for topology iirc.

Fun was had and headaches too. The biggest speedup I got was computing the graph online (creating a node/vertices tables from geometries) and then doing the recursive CTE on that table.

[ Parent | on HN ]

Re:Postgres: The Graph Database You Didn't Know You Had by amyres on Friday March 31, 2023 @01:49PM (#35390575)

Cool thanks! I'm amazed at how much can be done in PostGIS (if my SQL queries are good enough...)

[ Parent | on HN ]