Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Reducing BigQuery Costs

blog.peerdb.io

Post reply on HN
Print view View original

21–30 of 40 posts

Re: Reducing BigQuery Costs

#21

Post by dharmab »

stairlane wrote: post #2

We've recently been struggling with BigQuery and various other GCP services (i.e. CloudRun and Pub sub) as it feels like utilizing these services can feel like a minefield of gotcha's. With their documentation, and limits/quotas being spread all over the place. It's given us more problems than solutions thus far; albeit that could very well be our fault. Has anybody else had this experience? Or are we just doing it w…

Yes, I've found that you need to scrutinize the documentation, quotas, SKUs and billing statements quite closely, and you need to test everything before you run production at scale. I've seen unexpected billing due to an operation or resource using a different SKU than expected which didn't qualify for an account's discount, for example.

Re: Reducing BigQuery Costs

#22

Post by dharmab »

boredemployee wrote: post #4
stairlane wrote: post #2

We've recently been struggling with BigQuery and various other GCP services (i.e. CloudRun and Pub sub) as it feels like utilizing these services can feel like a minefield of gotcha's. With their documentation, and limits/quotas being spread all over the place. It's given us more problems than solutions thus far; albeit that could very well be our fault. Has anybody else had this experience? Or are we just doing it w…

At the beginning of my career, I incurred some hundreds of dollars in losses with BigQuery and Google Cloud Function. The problem with these services is that they are easy and intuitive enough for a beginner to use, but a nightmare to maintain.

I'm glad you learned that lesson for less than $1k. I think everyone who's ever worked with large amounts of data in BigQuery has a story like that, and sometimes the number is six or seven digits.

Re: Reducing BigQuery Costs

#23

Post by SOLAR_FIELDS »

rdli wrote: post #5
stairlane wrote: post #2

We've recently been struggling with BigQuery and various other GCP services (i.e. CloudRun and Pub sub) as it feels like utilizing these services can feel like a minefield of gotcha's. With their documentation, and limits/quotas being spread all over the place. It's given us more problems than solutions thus far; albeit that could very well be our fault. Has anybody else had this experience? Or are we just doing it w…

I agree that some GCP services are better than others. I’ve never used Pub/Sub or Cloud Run, but have been quite happy with BigQuery and GKE.

BigQuery has more footguns than GKE in my experience, but that’s perhaps because I have a lot more experience with GKE and know how to avoid those footguns. To me at least it’s understandable enough to say More Nodes is More Money but completely non-straightforward to say that this query I wrote is going to scan the data in a new and expensive way. Am I doing it wrong?

Re: Reducing BigQuery Costs

#24

Post by fifilura »

dharmab wrote: post #22
boredemployee wrote: post #4

Earlier quoted context omitted.

At the beginning of my career, I incurred some hundreds of dollars in losses with BigQuery and Google Cloud Function. The problem with these services is that they are easy and intuitive enough for a beginner to use, but a nightmare to maintain.

I'm glad you learned that lesson for less than $1k. I think everyone who's ever worked with large amounts of data in BigQuery has a story like that, and sometimes the number is six or seven digits.

Most of the time, it feels a little bit embarrassing, but the cost is just a very small part of your regular salary and overall operating cost. If your boss hits you with this, they don't have the correct perspective and priorities.

My record is $20k and it raised some eyebrows. But it was not really a mistake, just a sub-optimal backfill.

The data was filling a need for making appropriate business decisions, and compared to all the money lost by business developers making investments on a hunch, this was a very small bump in the road.

Re: Reducing BigQuery Costs

#25

Post by hilti »

boredemployee wrote: post #4
stairlane wrote: post #2

We've recently been struggling with BigQuery and various other GCP services (i.e. CloudRun and Pub sub) as it feels like utilizing these services can feel like a minefield of gotcha's. With their documentation, and limits/quotas being spread all over the place. It's given us more problems than solutions thus far; albeit that could very well be our fault. Has anybody else had this experience? Or are we just doing it w…

At the beginning of my career, I incurred some hundreds of dollars in losses with BigQuery and Google Cloud Function. The problem with these services is that they are easy and intuitive enough for a beginner to use, but a nightmare to maintain.

I‘ve had almost the same experience. First I was super impressed how easy it is to get data into BigQuery and retrieve it using their IDE.

But really soon I noticed the slow startup … simple queries took too long (1.2 sec vs milliseconds in a traditional database)

Then I learned a lot about BigQuery views. That helped a little.

At some point I simply wanted to export data. New Google tools needed to be learned: Cloud Storage, Data Flow.

After 18 months of using BigQuery on roughly 850 million rows, I switched back to a traditional database.

Re: Reducing BigQuery Costs

#26

Post by antoniojtorres »

stairlane wrote: post #2

We've recently been struggling with BigQuery and various other GCP services (i.e. CloudRun and Pub sub) as it feels like utilizing these services can feel like a minefield of gotcha's. With their documentation, and limits/quotas being spread all over the place. It's given us more problems than solutions thus far; albeit that could very well be our fault. Has anybody else had this experience? Or are we just doing it w…

Very much. We ended up writing our own queries to try and figure out where the costs were coming from in BQ. Ultimately we decided to offload as much as possible to a self-managed ClickHouse cluster.

Re: Reducing BigQuery Costs

#27

Post by varshithr »

coreyp_1 wrote: post #6

Rule #1. BQ is not a standard database. If you use it like one, it will cost a fortune. Rule #2. BQ is amazing for being able to churn through and analyze massive amounts of data, and can very well be the best option in some use cases. Rule #3. Letting "just anyone" run queries is as dangerous as casually handing a credit card to your drug-addicted cousin. Just wait until you get the bill! Rule #4: Partition and clus…

Thanks for these rules, as a budding engineer, this is very insightful. Will look forward to your video.

Re: Reducing BigQuery Costs

#28

Post by michaelt »

SOLAR_FIELDS wrote: post #23
rdli wrote: post #5

Earlier quoted context omitted.

I agree that some GCP services are better than others. I’ve never used Pub/Sub or Cloud Run, but have been quite happy with BigQuery and GKE.

BigQuery has more footguns than GKE in my experience, but that’s perhaps because I have a lot more experience with GKE and know how to avoid those footguns. To me at least it’s understandable enough to say More Nodes is More Money but completely non-straightforward to say that this query I wrote is going to scan the data in a new and expensive way. Am I doing it wrong?

> To me at least it’s [...] non-straightforward to say that this query I wrote is going to scan the data in a new and expensive way. Am I doing it wrong?

When you put a query in the BigQuery console, it'll tell you "This query will process ??? MB when run" at the top right.

So if you code all your queries interactively in production (which is what everyone else is doing anyway) it's not too hard to keep an eye on.

Re: Reducing BigQuery Costs

#29

Post by danpalmer »

stairlane wrote: post #2

We've recently been struggling with BigQuery and various other GCP services (i.e. CloudRun and Pub sub) as it feels like utilizing these services can feel like a minefield of gotcha's. With their documentation, and limits/quotas being spread all over the place. It's given us more problems than solutions thus far; albeit that could very well be our fault. Has anybody else had this experience? Or are we just doing it w…

At my previous workplace we had a mix of bare metal (most services), AWS (one service), Digital Ocean (misc), and GCP (BigQuery), and eventually moved almost entirely onto GCP, retaining just a bit of Digital Ocean stuff.

We found that all of these had significant caveats that required careful planning. We had a few instances of runaway AWS costs due to basically not knowing enough about AWS and we had to be careful to only use the "good" AWS products, Digital Ocean never had runaway costs but they did keep turning off production services because our use-case was not one they were familiar with (dev machines, off-site backups). Bare metal was a minefield, we found we couldn't reliably run Prometheus because it ate SSDs. As for GCP, it did require understanding the pricing and it was possible to shoot yourself in the foot with things, but no more than anything else.

There are going to be gotchas everywhere. Overall we had a great experience with GCP, to the point that the company has remained on GCP post-acquisition by another company who were mostly on Azure.

Re: Reducing BigQuery Costs

#30

Post by ople »

jojobas wrote: post #13

So far the best solution I've seen to save on BigQuery costs is to deploy your own ClickHouse. It has its own gotchas and quirks, but cost-wise it's not even comparable.

I have had the same experience. I was constantly bumping into unexpected limitations. Moving to CH felt like the opposite, with many more ”Wow, I didn’t expect this to be possible but it is” experiences.

There is a place for BQ but it is good to set expectations correctly and also look at the constraints. They are sometimes not obvious. The docs do helpfully outline the limitations, for example:

- Materialized views: https://cloud.google.com/bigquery/docs/materialized-views-in... - Indexes: https://cloud.google.com/bigquery/docs/search-intro#limitati...

Post reply on HN