3 ms·

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 o

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.

lysecret 3y ago
Very good response, can I ask you for a way to find your company? I am working in the same space.
verticalscaler 3y ago
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.
- kdamica 3y ago
  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.
  - verticalscaler 3y ago
    > 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. ;)
  - 3y ago
chuckhend 3y ago
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 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 :)
- kdamica 3y ago
  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!
  - philippemnoel 3y ago
    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.
    - kdamica 3y ago
      Very cool! I’ll be super interested to see how this develops.

lysecret 3y ago

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

verticalscaler 3y ago

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.

kdamica 3y ago
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.
- verticalscaler 3y ago
  > 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. ;)
- 3y ago

chuckhend 3y ago

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 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 :)

kdamica 3y ago
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!
- philippemnoel 3y ago
  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.
  - kdamica 3y ago
    Very cool! I’ll be super interested to see how this develops.