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.
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.
> 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.
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.