GCP Quick Wins

GCP

Template

Tutorials

•

Google Cloud Platform offers powerful tools and services, but optimizing your GCP environment doesn't have to be overwhelming. This tutorial covers practical GCP optimization strategies that you can implement quickly to achieve immediate cost savings, security improvements, and operational efficiency.

GCP Safe Deletes Tutorial #

In this tutorial, you will GCP resources that are likely safe to delete. This can be a significant cost savings: one CloudQuery customer saves approximately $30k/month in costs by using queries like this.

Get started #

Install CloudQuery, point it at a Postgres you control (local Docker or Cloud SQL for Postgres), and use a read-only GCP identity with compute.disks.list, compute.addresses.list, compute.instances.list, compute.snapshots.list, and storage.buckets.list. Save the config below as gcp-safe-deletes.yaml, set POSTGRES_URL and your GCP credentials, then run a single cloudquery sync. Paste the SQL into Postgres for a screenshot-ready scorecard.

CloudQuery Config and Sync #

This config will sync AWS metadata for EC2 and related resources to Postgres.

kind: source
spec:
  name: aws
  path: cloudquery/source/aws
  version: 'vX.Y.Z' # pin a recent version
  tables:
    - aws_ec2_ebs_volumes
    - aws_ec2_eips
    - aws_ec2_security_groups
    - aws_ec2_network_interfaces
  destinations: [postgres]

---
kind: destination
spec:
  name: postgres
  path: cloudquery/destination/postgresql
  version: 'vA.B.C' # whatever your postgres version is
  spec:
    connection_string: ${POSTGRES_URL} # e.g. postgres://user:pass@host:5432/db

After you create your config file, all you need to do is run a sync. Make sure to set your environment variables!

cloudquery sync gcp-safe-deletes.yaml

Data structure #

gcp_compute_disks
Rows: ~hundreds (depends on project)
Columns:
 • self_link (utf8)        e.g. "https://www.googleapis.com/compute/v1/projects/my-proj/zones/us-east1-b/disks/data-disk-1"
 • name (utf8)             e.g. "data-disk-1"
 • project_id (utf8)       e.g. "my-proj"
 • zone (utf8)             e.g. "us-east1-b"
 • size_gb (int64)         e.g. 100
 • status (utf8)           e.g. "READY"
 • users (list)      e.g. ["https://.../instances/web-1"]
 • labels (json)           e.g. {"env":"prod","owner":"teamA"}

gcp_compute_addresses
Rows: usually small (<50)
Columns:
 • self_link (utf8)        e.g. "https://.../addresses/ip-1"
 • name (utf8)             e.g. "static-ip-1"
 • project_id (utf8)       e.g. "my-proj"
 • region (utf8)           e.g. "us-central1"
 • address (utf8)          e.g. "35.201.22.44"
 • address_type (utf8)     e.g. "EXTERNAL"
 • status (utf8)           e.g. "RESERVED" or "IN_USE"
 • users (list)      e.g. [] (empty if unattached)
 • network_tier (utf8)     e.g. "PREMIUM"
 • labels (json)           e.g. {"env":"staging"}

gcp_compute_instances
Rows: active fleet size (tens → thousands)
Columns:
 • self_link (utf8)        e.g. "https://.../instances/web-1"
 • name (utf8)             e.g. "web-1"
 • project_id (utf8)       e.g. "my-proj"
 • zone (utf8)             e.g. "us-central1-a"
 • status (utf8)           e.g. "RUNNING", "TERMINATED"
 • last_stop_timestamp (utf8) e.g. "2024-11-10T08:15:00.000-07:00"
 • labels (json)           e.g. {"role":"frontend"}

gcp_compute_snapshots
Rows: varies (depends on backup policy)
Columns:
 • self_link (utf8)        e.g. "https://.../snapshots/snap-1"
 • name (utf8)             e.g. "snap-1"
 • project_id (utf8)       e.g. "my-proj"
 • creation_timestamp (utf8) e.g. "2024-05-01T12:00:00.000-07:00"
 • disk_size_gb (int64)    e.g. 200
 • auto_created (bool)     e.g. false
 • status (utf8)           e.g. "READY"
 • labels (json)           e.g. {"backup":"weekly"}

gcp_storage_buckets
Rows: modest (dozens per project)
Columns:
 • name (utf8)             e.g. "my-data-bucket"
 • project_id (utf8)       e.g. "my-proj"
 • location (utf8)         e.g. "US"
 • public_access_prevention_string_value (utf8) e.g. "enforced", NULL
 • uniform_bucket_level_access (json) e.g. {"enabled": true}
 • acl (json)              e.g. [{"entity":"allUsers","role":"READER"}]
 • labels (json)           e.g. {"team":"analytics"}

Useful queries #

Note: these all use Postgres-style syntax.

Unattached Persistent Disks → storage burn with no usage #

Disks without any 'users' (list is NULL or empty) are not attached to VMs

SELECT
  d.project_id,
  COALESCE(d.zone, d.region) AS location,
  d.name,
  d.self_link,
  d.size_gb,
  d.status,
  d.labels
FROM gcp_compute_disks d
WHERE d.users IS NULL
   OR (jsonb_typeof(d.users) = 'array' AND jsonb_array_length(d.users) = 0)
ORDER BY d.size_gb DESC NULLS LAST, location, d.name;

Idle Static External IPs → you pay when RESERVED and unused #

External IPs billed when RESERVED and not in use (no 'users').

SELECT
  a.project_id,
  a.region,
  a.name,
  a.self_link,
  a.address,
  a.address_type,
  a.status,
  a.network_tier,
  a.labels
FROM gcp_compute_addresses a
WHERE a.address_type = 'EXTERNAL'
  AND a.status = 'RESERVED'
  AND (
        a.users IS NULL OR
        (jsonb_typeof(a.users) = 'array' AND jsonb_array_length(a.users) = 0)
      )
ORDER BY a.region, a.name;

Stopped/Terminated VMs → surface likely cleanup/savings #

VMs not running (TERMINATED / STOPPED) often leave behind disks/IPs.

SELECT
  i.project_id,
  i.zone,
  i.name,
  i.self_link,
  i.status,
  i.last_stop_timestamp,
  i.labels
FROM gcp_compute_instances i
WHERE i.status IN ('TERMINATED', 'STOPPED')
ORDER BY i.zone, i.name;

Stale Snapshots → storage creep #

Snapshots older than 90 days and not auto-created (policy-dependent) are easy review targets.

SELECT
  s.project_id,
  s.name,
  s.self_link,
  s.creation_timestamp,
  s.disk_size_gb,
  s.auto_created,
  s.status,
  s.labels
FROM gcp_compute_snapshots s
WHERE s.auto_created = FALSE
  AND (s.creation_timestamp::timestamp AT TIME ZONE 'UTC') < (NOW() AT TIME ZONE 'UTC') - INTERVAL '90 days'
ORDER BY s.creation_timestamp::timestamp ASC;

Public or weakly protected GCS buckets #

Buckets potentially public if ACL mentions allUsers/allAuthenticatedUsers and PAP is not 'enforced'.

SELECT
  b.project_id,
  b.name,
  b.location,
  b.public_access_prevention_string_value AS pap,
  b.uniform_bucket_level_access,
  b.labels
FROM gcp_storage_buckets b
WHERE COALESCE(b.public_access_prevention_string_value, '') <> 'enforced'
  AND b.acl IS NOT NULL
  AND EXISTS (
        SELECT 1
        FROM jsonb_array_elements(b.acl) a
        WHERE (a->>'entity') IN ('allUsers', 'allAuthenticatedUsers')
      )
ORDER BY b.project_id, b.name;

Wrap-up #

CloudQuery is lightning-fast data movement that runs on your infrastructure. This is perfect for working with sensitive data (like your key GCP resources!). Get started free here to realize these cost savings. We’ve got a 14 day free trial, with no credit card required!

CloudQuery