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

71–80 of 80 posts

Re: Representing Graphs in PostgreSQL

#71

Post by JoelJacobson »

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 tested PostgreSQL vs Neo4j with a friends-of-friends query two years ago, and to my surprise it was Neo4j that crashed, not PostgreSQL.

https://github.com/joelonsql/graph-query-benchmarks

I haven't tried the latest versions of both databases though.

Re: Representing Graphs in PostgreSQL

#72

Post by zaphirplane »

eurg wrote: post #41
demaga wrote: post #33

Earlier quoted context omitted.

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

Too funny, enjoy the vote

Re: Representing Graphs in PostgreSQL

#73

Post by physicles »

osigurdson wrote: post #59
sureglymop wrote: post #46

Earlier quoted context omitted.

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

We ended up using ClickHouse after trying Timescale and InfluxDB. ClickHouse is great but important to spend a day or two understanding the data model to make sure it fits what you are trying to do. I have no affiliation with ClickHouse (or any company mentioned).

We’ve been using InfluxDB 1.x since 2017 and I’m itching to get off of it. Currently we’re at about a trillion rows, and we have to ship our data to Snowflake to do large-scale aggregations (like hourly averages). I put a bunch of effort into building this on top of InfluxDB and it’s not up to the task.

Any sense if ClickHouse can scale to several TB of data, and serve giant queries on the last hour’s data across all sensors without getting OOMKilled? We’re also looking at some hacked together DuckDB abomination, or pg_duck.

Re: Representing Graphs in PostgreSQL

#74

Post by physicles »

efxhoy wrote: post #70
sureglymop wrote: post #46

Earlier quoted context omitted.

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

Timescale is definitely worth a look. Pg_partman gets you part of the way. We ended up going with bigquery for our workload because it solved a bigger bag of problems for our needs (data warehouse). It’s very hard to beat for big… queries.

I never understood the rationale behind TimescaleDB — if you’re building a time series database using row-oriented storage, you’ve already got one hand tied behind your back.

What does your testing strategy look like with bigquery? We use snowflake, but the only way to iterate and develop is using snowflake itself, which is so painful as to impact the set of features that we have the stomach to build.

Re: Representing Graphs in PostgreSQL

#75

Post by osigurdson »

sureglymop wrote: post #63
osigurdson wrote: post #59

Earlier quoted context omitted.

We ended up using ClickHouse after trying Timescale and InfluxDB. ClickHouse is great but important to spend a day or two understanding the data model to make sure it fits what you are trying to do. I have no affiliation with ClickHouse (or any company mentioned).

Interesting.. But clickhouse is an entirely different database engine, no? I will look into it, thank you!

Yes, it is a columnar DB. A lot of things feel pretty familiar as it has a SQL like query language. The data model is different though.

The nice thing is, once you understand the data model it becomes very easy to predict if it will fit your use case or not as there is really no magic to it.

Re: Representing Graphs in PostgreSQL

#76

Post by Frankion »

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.

Do you have more details about it?

My first thought is sharding and materialized views.

Re: Representing Graphs in PostgreSQL

#77

Post by osigurdson »

physicles wrote: post #73
osigurdson wrote: post #59

Earlier quoted context omitted.

We ended up using ClickHouse after trying Timescale and InfluxDB. ClickHouse is great but important to spend a day or two understanding the data model to make sure it fits what you are trying to do. I have no affiliation with ClickHouse (or any company mentioned).

We’ve been using InfluxDB 1.x since 2017 and I’m itching to get off of it. Currently we’re at about a trillion rows, and we have to ship our data to Snowflake to do large-scale aggregations (like hourly averages). I put a bunch of effort into building this on top of InfluxDB and it’s not up to the task. Any sense if ClickHouse can scale to several TB of data, and serve giant queries on the last hour’s data across all…

A trillion rows should be no issue at all for ClickHouse. Your use case sounds a bit more typical for it (our data is simulated so there is no single / monotonically increasing clock). I don't know though, is this ~100KS/s data acquisition type stuff (i.e. sound or vibration data for instance)? If so, it wouldn't be possible to push that into ClickHouse without pre-processing.

Re: Representing Graphs in PostgreSQL

#78

Post by derefr »

ffsm8 wrote: post #62
demaga wrote: post #61

Earlier quoted context omitted.

Except that it wasn't about people at all? I was just referring to a _type_ of query

What kind of data was it, of I may ask? Graph databases have a very narrow usecase, and it's almost always in relation to people - at least ime. Though the data type isn't really important for the performance question, the amount of data selected is. So a 6-level depth graph of connections that only connect 2-3 entities would never get into performance issues. You'd be able to go way beyond that too with such a narro…

Network analysis of financial transactions to detect "layering" (onion-routed money laundering) paths, probably.

Re: Representing Graphs in PostgreSQL

#79

Post by efxhoy »

physicles wrote: post #74
efxhoy wrote: post #70

Earlier quoted context omitted.

Timescale is definitely worth a look. Pg_partman gets you part of the way. We ended up going with bigquery for our workload because it solved a bigger bag of problems for our needs (data warehouse). It’s very hard to beat for big… queries.

I never understood the rationale behind TimescaleDB — if you’re building a time series database using row-oriented storage, you’ve already got one hand tied behind your back. What does your testing strategy look like with bigquery? We use snowflake, but the only way to iterate and develop is using snowflake itself, which is so painful as to impact the set of features that we have the stomach to build.

Testing strategy? What’s that? I kid, but just a bit. Our use case is a data warehouse. We use DBT to build everything. Each commit is built in CI to a CI target project. Each commit gets its own hash prefixed in front of dataset names. Each developer also has their own prefix for local development. The dev and ci datasets expire and are deleted after like a week. We use data tests on the actual data for “foreign keys”, checking for duplicates and allowed values. But that’s pretty much it. It’s very difficult to do TDD for a data warehouse in sql.

My current headache is what to do with an actually big table, 25 billion rows of json, for development. It’s going to be some DBT hacks I think.

God help you if you want to unit test application code that relies on bigquery. I’m sure there are ways but I doubt they don’t hurt a lot.

Re: Representing Graphs in PostgreSQL

#80

Post by physicles »

efxhoy wrote: post #79
physicles wrote: post #74

Earlier quoted context omitted.

I never understood the rationale behind TimescaleDB — if you’re building a time series database using row-oriented storage, you’ve already got one hand tied behind your back. What does your testing strategy look like with bigquery? We use snowflake, but the only way to iterate and develop is using snowflake itself, which is so painful as to impact the set of features that we have the stomach to build.

Testing strategy? What’s that? I kid, but just a bit. Our use case is a data warehouse. We use DBT to build everything. Each commit is built in CI to a CI target project. Each commit gets its own hash prefixed in front of dataset names. Each developer also has their own prefix for local development. The dev and ci datasets expire and are deleted after like a week. We use data tests on the actual data for “foreign key…

Interesting strategy with appending the commit hash to the dataset name. If one of those commits is known to be good and you want to “ship” it, do you then rename it?

What are you doing with that JSON? What’s the reason why you can’t get a representative sample of rows onto your dev machine and hack on that?

Post reply on HN