Comment by kdamica | Hacker News Reader

Comment by kdamica | Hacker News Reader

I would not recommend this approach to anyone, for these reasons:

- If your data is small enough that Postgres works as an analytics warehouse, then the cost of running a warehouse like BigQuery are going to be small too, and without the complexity of managing the infra yourself. The company here specializes in Postgres, so it makes sense for them to do this, but for everyone else it makes much more sense to use a warehouse so you can focus on building your product and not worry about scaling your analytics infra.

- Because Postgres doesn't scale for analytics, they only keep 30 days of metrics. I don't think this would be acceptable for most businesses. BigQuery costs 2 cents per GiB per month, so storage is generally a minimal expense until you get to pretty large scale.

- Tools like Fivetran and Airbyte are much simpler for maintaining these pipelines than setting up FDWs, and each support a huge number of data sources. If you're worried about cost, you can self-host Airbyte or use Stitch (which is kind of a budget player in the space). It's true that Fivetran can get very expensive as you scale, but there is enough competition now that you have options to reduce cost.

Disclaimer: I run a data consulting company that specializes in zero to one data projects for startups, including setting up analytics warehouses.

Counterpoint:

- The cost of running a warehouse like BigQuery is pulling in Google Cloud, or whatever 3rd parties like the ones you mentioned, and integrating with it somehow.

How you gonna get the data in there and back out? What should the ACL be? Sure might be a trivial amount of data, heck, could be 1GB. Depending on the type of data (and company) there's potentially a lot more busywork involved here than just shuttling files around and trivial scripting.

"Postgres doesn't scale for analytics" and "you should not spend money on an internal guy/team to run a database, you should blow it on Google and data consulting companies instead" is a bit of a blanket statement.

Seems like teams with great DBAs will go with what they are familiar and teams that are cloud-all-the-things will go with what they are familiar. But the real choice here isn't even technical in the narrow sense, it is strategic.

You wouldn't recommend anybody use Postgres, really? Man, so many teams are doing it wrong I guess.

I can qualify "Postgres doesn't scale for analytics" a bit: You cannot run large scale aggregations in a row-based database like Postgres the way you can in a columnular data warehouse.

If all you need is daily aggregations of metrics like counts of certain events, then Postgres will be fine for a long time. But as soon as you want to ask more complex questions like 'What is the average time between a user signup to the first time they do a certain event?' or 'How many distinct users did X in the past month?', Postgres is going to struggle. It's not impossible to get Postgres to do these things, but every additional question like this will require figuring out how to get Postgres to do it in a reasonable amount of time, whereas a data warehouse is designed to do these kinds of aggregations very efficiently.

In terms of the integration, I think you'd be pleasantly surprised by how easy these tools make it to pipe data into your warehouse. Outside of setting up permissions, you don't really even need engineering. A strong data analyst could do the job.

For startups, engineering time is often the most scarce resource. I strongly believe that startups should not spend time building custom analytics solutions when so many great tools exist off the shelf, and instead focus 100% on building the core product.

> In terms of the integration, I think you'd be pleasantly surprised by how easy these tools make it to pipe data into your warehouse. Outside of setting up permissions, you don't really even need engineering. A strong data analyst could do the job.

Sorry I tried to say as much myself, maybe I wasn't clear. More succinctly put the busywork tradeoff is between keeping your data internally and paying down the required DBA legwork or plunking it into a third party which often has implications beyond the technical and creates work for lawyers and managerial types (if its HIPAA type stuff, sensitive or involves multiple jurisdictions) plus:

"you don't really even need engineering" - one wonders what I need the data consultant for. Another cost. Another trade off. Another point of possible failure.

Somewhere on that continuum is an infliction point and clearly this does not hold: "I would not recommend this approach to anyone" since "If all you need is.."

P.S. - I used to work in AdTech as a consultant myself and was shown early previews of things like Lambda and BigQuery. ;)

> will require figuring out how to get Postgres to do it in a reasonable amount of time, whereas a data warehouse is designed to do these kinds of aggregations very efficiently.

"reasonable amount of time" is speculative term, it could be good enough for some usecases.

Also, you can do indexes in Postgres, but can't in say BigQuery, meaning it is more likely I can build low latency analytics in Postgres but will have issues in BigQuery.

Totally agree with the problem, and need for a columnar store. But isn't BigQuery too slow for powering an interactive website? My goal building a dashboard type tool is to have all the queries return in <1 second. I didn't specifically try BigQuery, but after reading about it I went with Clickhouse mirroring a postgres instance. This seems to scale perfectly pleasantly up to tens of millions of rows of timeseries data.

Yeah I agree with you wouldn't want to point a dashboard directly at BigQuery. There are two cases here:

- You don't need realtime data, but you want to serve the data quickly. If you wanted to use Bigquery in this case, you'd have the query running on some schedule, and then store and serve the results from whatever data store you like. This is what most dashboarding tools are doing under the hood (e.g. Mode, Metabase, etc.)

- If you need real or near real-time data, then an OLAP system like Clickhouse makes a lot of sense!

I'm definitely not trying to say that BigQuery is right for every situation. It's more that there are very few situations that I can see Postgres being the best workhorse for your analytics.

Redshift: columnar database with a Postgres query interface.

There are definitely ways to cleanly make Postgres scale for analytics. We didn't discuss in this blog, but we will be writing about them in the future. For example, check out what the folks at ParadeDB are doing. https://github.com/paradedb/paradedb. Neon is doing an awesome job separating compute from storage. Supabase contributed foreign data wrappers make it super easy to read from S3 into Postgres. Lots of great work going out there :)

I totally agree that there are great solutions out there to use Postgres for specific analytics tasks, especially realtime ones, but if you want something that can handle arbitrary aggregations and do ad hoc analytics, nothing comes close to a standard data warehouse. Lots of exciting things happening in the space so that might change!

One of the authors of ParadeDB here. While ad hoc aggregations in vanilla Postgres are slow due to lack of column-oriented storage, extensions like pg_analytics are addressing that problem. In our view, one of the main use cases of a data warehouse is when you need separation of storage/compute, which enables distributed analytics and scalable storage. With Postgres, query processing + storage are all happening on the same node. That being said we're looking at ways to separate out storage to an external data lake as part of pg_analytics.

Very cool! I’ll be super interested to see how this develops.

Very good response, can I ask you for a way to find your company? I am working in the same space.

Reply on news.ycombinator.com