Cloud Architecture Framework:
3 min readJan 15, 2023
Optimize cost: Databases and smart analytics: Cost-optimization controls for BigQuery, Cloud SQL.
BigQuery — a serverless, highly scalable, and cost-effective data warehouse designed for business agility
Data and query organization
- To reduce the processing cost and improve performance, partition and cluster your tables when possible.
- Set up dataset-level, table-level, or partition-level expirations for data, based on your compliance strategy.
- Query only the data that you need. Avoid full query scans. To explore and understand data semantics, use the no-charge data preview options.
- Limit query costs by restricting the number of bytes billed
- For non-clustered tables, do not use a LIMIT clause as a method of cost control. With a clustered table, a LIMIT clause can reduce the number of bytes scanned, because scanning stops when enough blocks are scanned to get the result, another way you will get a full scan
- Materialize query results in stages — if you query a big table and want to use the data later — you can save and aggregate the data in a stage table. Partition expiration in the table can help you remove old, not relevant data automatically
- Assess whether you can use batch-loading instead of streaming inserts for your data workflows. Use streaming inserts if the data loaded to BigQuery is consumed immediately.
On-demand pricing or Flat-rate pricing — Big Query use two price models for data analytics
- On-demand pricing — You pay for the data scanned by your queries. If your data is relatively not big or you have not run too many queries — it’s for you
- Flat-rate analysis pricing — You purchase dedicated query processing capacity, measured in BigQuery slots.
- 100-slot minimum — Usually, you start to check the flat-rate plan when the cost of your BQ Analytics is more than 2000$, it’s the approximate price of annual flat-rate 100 slots and is purchased in increments of 100 slots.
- This does not apply to streaming inserts and using the BigQuery Storage API.
- Is purchased as a regional resource. Slot commitments purchased in one region or multi-region cannot be used in another region or multi-region and cannot be moved.
- It is available in per-second, monthly, and annual commitments.
- It’s usually a very effective price reduction instrument for big spends, but you have to analyze your standard slots spend. An on-demand plan gives you 2000 slots, using monitoring to check your slot utilization. If you have some picks and want to use a fewer slots plan — this query will run slowly. You can check if it is not critical for you or transfer the query to another project (without data transfer)
- Flex slots: short-term commitments — can be very effective, you buy slots for operation time and stop it after, but it asks for some implementation to your code. GCP tests an autoscale mechanism, but it’s not GA now
- The commitment duration is only 60 seconds.
- You can cancel Flex slots at any time thereafter.
- You are charged only for the seconds your commitment was deployed.
Cloud SQL — a fully managed relational database for MySQL, PostgreSQL, and SQL Server.
Optimize resources
- Design a high availability and disaster recovery strategy that aligns with your recovery time objective (RTO) and recovery point objective (RPO). Depending on your workload, we recommend the following:
- For workloads that need a short RTO and RPO, use the high-availability configuration and replicas for regional failover.
- For workloads that can withstand a longer RTO and RPO, use automated and on-demand backups, which can take a little longer to restore after a failure.
- To scale storage capacity automatically as your data grows, enable the automatic storage increase feature.
- Choose a storage type, solid-state drives (SSD) or hard disk drives (HDD). HDD might be appropriate for large datasets (>10 TB) that aren’t latency-sensitive or are accessed infrequently.
Optimize rates - Consider purchasing committed use discounts for workloads with predictable resource needs. You can save 25% of on-demand pricing for a 1-year commitment and 52% for a 3-year commitment.
