Reducing BigQuery Costs
blog.peerdb.io
Reducing BigQuery Costs
1–10 of 40 posts
Re: Reducing BigQuery Costs
#2We'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 wrong?
This is not intended to be a rant, just curious.
Re: Reducing BigQuery Costs
#3We'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've generally found something similar- lots of gotchas, but also some very useful products.
The best way I've found to approach it is to treat GCP as something that has to be evaluated at an individual service level. It's great if you're on one of their expected workflows/golden paths, and you can get lucky with a good fit if you aren't, but they seem to have a lot of unspoken assumptions and limits baked in that might or might not align with your use case.
Disclaimer: My use cases are pretty unusual from talking to our account rep, so this might be over-fitting to weird data.
Re: Reducing BigQuery Costs
#4We'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.
Re: Reducing BigQuery Costs
#5We'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.
Re: Reducing BigQuery Costs
#6Rule #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 cluster your data wisely. You don't have indexes.
Rule #5: Duplicate data. Throw all of the normal forms out the window. Storage is cheap, computation is expensive.
Rule #6: BQ is not meant to be used like MySQL. It's "spin up" time is too slow, but you would be hard-pressed to beat its performance on truly large data sets.
My perspective: One of our customers has a database growing by 17 gigs a day. One of them. There's several on the same scale. Yes, it's necessary. Another instance: One of our customers spent $8k in one month because limits were not properly placed on the account and we didn't catch it until the bill came. We monitor better now. A different instance: We had a dev trying to optimize a query, and they spent $250 in queries to get the cost down from $50/query to $15/query. Most of the time, though, our queries are only pennies.
Now that I've written all of this out, I feel like I need to record a video about it. There's not a lot of BQ info aside from the marketing fluff put out by "teh Google".
Re: Reducing BigQuery Costs
#7Rule #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…
OP here. 100% agreed on your analysis. Thanks for chiming in. Coming from the Postgres world, this was very counter intuitive for me. I am still not convinced if a database should charge 1000s of $s due to lack of an index (cluster). It could either create the index automatically or explicitly (on the face) warn the user that this can be expensive or else slow.
Re: Reducing BigQuery Costs
#8We'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.
That's nothing. Wait until you incur $150k of Lambda costs in a day!
Re: Reducing BigQuery Costs
#9Rule #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…
OP here. 100% agreed on your analysis. Thanks for chiming in. Coming from the Postgres world, this was very counter intuitive for me. I am still not convinced if a database should charge 1000s of $s due to lack of an index (cluster). It could either create the index automatically or explicitly (on the face) warn the user that this can be expensive or else slow.
We use BQ quite extensively there are a number of billing tuning options which are not that well documented.
1. for some it will make sense to move to pricing based on CPU time per query vs billing on scanned TB of data. This can be done through the commits in UI.
2. there is option to have storage billed on logical or physical bytes. If you have data with a lot of duplication (enums, customer ids etc) then physical billing can be a lot better option. Last I looked this was only available through CLI setting in dataset and you may need to ask Google to include you in their beta. We lowered our billing with 30% for storage.
I try to keep an eye on GCP release notes to find things like the physical vs logical billing.
Re: Reducing BigQuery Costs
#10Earlier 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.
That's nothing. Wait until you incur $150k of Lambda costs in a day!
Did it happen to you? sorry to hear!