4 ms·

I love postgres and my business relies on it. However, at the scale the author is talking about (~100m rows), throwing all the data into BigQuery is very likely

I love postgres and my business relies on it. However, at the scale the author is talking about (~100m rows), throwing all the data into BigQuery is very likely to be a better option for many people. If rows are around 1kB, then full-dataset queries over 100m rows will cost less than $0.5 each on BQ -- less if only a few columns have to be scanned. Storage costs will be negligible and, unlike pg, setup time will be pretty much nil.

cedricd 5y ago
Yep. Fully agree. The point of the post wasn't to say that you should use PG as a data warehouse. Just that if it's what you've got available (for various reasons) that you can.
- open0 5y ago
  I've had well over a billion rows in multiple tables with PostgreSQL and it wasn't a problem at all.
predictmktegirl 5y ago
$0.5 queries?
- Keyframe 5y ago
  Full scan. Still absurd to what we've come to to think this is tolerable.
  - phpnode 5y ago
    Reading 100Gb from disk costs $0.50? this is absolutely incredible to me, how has this become acceptable to the industry?
    - Keyframe 5y ago
      Exactly.
    - manigandham 5y ago
      A query reading about 100GB with one of the most advanced data warehouse systems with no operational overhead and integration into a major cloud environment costs $0.50. There's a lot more to value than the price.
    - Justin_K 5y ago
      We find value with this model because we don't pay for the instance when it's idle and queries come back extremely quickly.
    - asdf123wtf 5y ago
      It's not for every use case, but Big Query is often a very stupidly cheap datastore. Query results get cached, and repeats don't incur a charge unless the data has changed. It's not a datastore to power a crud app, or anything requiring frequent queries, but it's a great place to stash gobs of logs that you may need to query at some point. Or it's great for serverless batch workloads and is often cheaper in both time and money than firing up spark clusters or something similar to do the work. Quite frankly, it's awesome. But sure, they do use it as a tool for lock-in, and for some cases it would be prohibitively expensive.
    - npsf3000 5y ago
      Incredible as in 'that's a great deal' or as in 'that seems a ripoff'? I find 50c to read 100GB from disk, do useful work on it (including running javascript code or ML models if you are so inclined) and returning a result in seconds... pretty damn incredible.
    - deleted 5y ago
      [deleted]
  - flakiness 5y ago
    A full scan rarely happens on BQ because of the nature of the columnar store. Try some public dataset like HN archive, and see how a query actually costs. You'll need very advanced (or stupid) query to read 100GB at once on BQ.
  - gnfargbl 5y ago
    The point of BQ is to allow you to perform queries which are ad-hoc and/or touch a significant fraction of the data. If you have a problem of that shape, then full column scans are not merely tolerable, they are optimal.
- wodenokoto 5y ago
  You don't pay for having a server running. You only pay for storage and data read during queries. No server fees and no fees when autoscaling up for heavy computations.
- kevindong 5y ago
  Per the BigQuery docs, GCP charges $5 per TB of data read. Which works out to be $0.50 per query if said query reads 100 GB of data. It feels expensive, but then again running and maintaining a big data platform is inherently very expensive. If you consider the fully loaded cost of a single big data engineer to maintain such a platform to be ~$250,000 (likely an underestimate if you want similar performance characteristics to BigQuery [disclaimer: never used it myself, but I assume its performance is near-unbeatable]), that'd be ~500,000, 100 GB queries. Which makes a $0.50 query feel reasonable relatively speaking. GCP also sells dedicated "slots" (as they call it) which as I understand it is an abstraction over a CPU. If you buy said slots, the marginal cost of queries is $0, but you may be subject to queueing. No idea what a "slot" actually represents however. https://cloud.google.com/bigquery/pricing#on_demand_pricing https://cloud.google.com/bigquery/pricing#on_demand_pricing

cedricd 5y ago

Yep. Fully agree. The point of the post wasn't to say that you should use PG as a data warehouse. Just that if it's what you've got available (for various reasons) that you can.

open0 5y ago
I've had well over a billion rows in multiple tables with PostgreSQL and it wasn't a problem at all.

predictmktegirl 5y ago

$0.5 queries?

Keyframe 5y ago
Full scan. Still absurd to what we've come to to think this is tolerable.
- phpnode 5y ago
  Reading 100Gb from disk costs $0.50? this is absolutely incredible to me, how has this become acceptable to the industry?
  - Keyframe 5y ago
    Exactly.
  - manigandham 5y ago
    A query reading about 100GB with one of the most advanced data warehouse systems with no operational overhead and integration into a major cloud environment costs $0.50. There's a lot more to value than the price.
  - Justin_K 5y ago
    We find value with this model because we don't pay for the instance when it's idle and queries come back extremely quickly.
  - asdf123wtf 5y ago
    It's not for every use case, but Big Query is often a very stupidly cheap datastore. Query results get cached, and repeats don't incur a charge unless the data has changed. It's not a datastore to power a crud app, or anything requiring frequent queries, but it's a great place to stash gobs of logs that you may need to query at some point. Or it's great for serverless batch workloads and is often cheaper in both time and money than firing up spark clusters or something similar to do the work. Quite frankly, it's awesome. But sure, they do use it as a tool for lock-in, and for some cases it would be prohibitively expensive.
  - npsf3000 5y ago
    Incredible as in 'that's a great deal' or as in 'that seems a ripoff'? I find 50c to read 100GB from disk, do useful work on it (including running javascript code or ML models if you are so inclined) and returning a result in seconds... pretty damn incredible.
  - deleted 5y ago
    [deleted]
- flakiness 5y ago
  A full scan rarely happens on BQ because of the nature of the columnar store. Try some public dataset like HN archive, and see how a query actually costs. You'll need very advanced (or stupid) query to read 100GB at once on BQ.
- gnfargbl 5y ago
  The point of BQ is to allow you to perform queries which are ad-hoc and/or touch a significant fraction of the data. If you have a problem of that shape, then full column scans are not merely tolerable, they are optimal.
wodenokoto 5y ago
You don't pay for having a server running. You only pay for storage and data read during queries. No server fees and no fees when autoscaling up for heavy computations.
kevindong 5y ago
Per the BigQuery docs, GCP charges $5 per TB of data read. Which works out to be $0.50 per query if said query reads 100 GB of data. It feels expensive, but then again running and maintaining a big data platform is inherently very expensive. If you consider the fully loaded cost of a single big data engineer to maintain such a platform to be ~$250,000 (likely an underestimate if you want similar performance characteristics to BigQuery [disclaimer: never used it myself, but I assume its performance is near-unbeatable]), that'd be ~500,000, 100 GB queries. Which makes a $0.50 query feel reasonable relatively speaking. GCP also sells dedicated "slots" (as they call it) which as I understand it is an abstraction over a CPU. If you buy said slots, the marginal cost of queries is $0, but you may be subject to queueing. No idea what a "slot" actually represents however. https://cloud.google.com/bigquery/pricing#on_demand_pricing https://cloud.google.com/bigquery/pricing#on_demand_pricing