Files
skills/public/cloud-gcp-databases.html
zeekay 747dafba10 Register WebMCP tools on every page, and name each page's twin from site.py
Every page now loads /webmcp.js, scripts/webmcp.ts with its types
stripped (npm run webmcp). Where the browser has document.modelContext
it registers two read-only tools: search_skills over the published
skills index, and get_skill, which returns a skill's markdown checked
against the index digest. The markdown alternate link moves from
build.py to site.py, so CI holds every page to it.

The API catalog gains the site itself (llms.txt and the skills index)
and hanzo.ai/agents; the live discovery check now fetches every
catalog link. tests/site.mjs drives the tools through a recording
modelContext, and checks a browser without one loads clean.
2026-10-26 20:57:05 -07:00

668 lines
33 KiB
HTML

<!doctype html>
<html lang="en"><head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>cloud-gcp-databases</title>
<meta name="color-scheme" content="dark">
<style>
/* Committed to one visual world: a dark instrument panel. No light variant —
the dot field and the hairline rules only read on black, and a naive
inversion would wash both out. Every colour is painted explicitly so the page
never borrows a host background. */
@font-face{font-family:'Zen';src:url('/fonts/Zen-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
@font-face{font-family:'Zen';src:url('/fonts/Zen-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
@font-face{font-family:'Zen Pixel Square';src:url('/fonts/ZenPixel-Square.woff2') format('woff2');font-style:normal;font-display:swap}
:root{
--ink:#f4f4f2; --soft:#9c9c98; --faint:#6a6a66; --ground:#000; --panel:#0a0a0a;
--line:#232323; --line-hi:#3a3a3a; --accent:#f4f4f2;
--font-zen-sans:'Zen',-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif;
--font-zen-mono:'Zen Mono',ui-monospace,"SF Mono",SFMono-Regular,Menlo,Consolas,monospace;
--font-zen-pixel-square:'Zen Pixel Square',monospace;
--mono:var(--font-zen-mono);
--sans:var(--font-zen-sans);
/* Controls are pills and surfaces are soft, as on hanzo.ai. */
--r:999px; --r-soft:14px;
}
*{box-sizing:border-box}
html{-webkit-text-size-adjust:100%}
body{margin:0;background:var(--ground);color:var(--ink);font-family:var(--sans);
font-size:16px;line-height:1.65;-webkit-font-smoothing:antialiased}
.wrap{max-width:60rem;margin:0 auto;padding:0 1.75rem}
/* The dot field. A halftone made of one repeating radial-gradient, faded out
along the diagonal by a mask so it reads as a wave rather than wallpaper. */
/* No overflow clip here: the dot field is inset to the box already, and a clip
would cut off the copy-prompt menu. */
.hero{position:relative;border-bottom:1px solid var(--line)}
.hero::before{content:"";position:absolute;inset:0;
background-image:radial-gradient(var(--line-hi) 1px, transparent 1px);
background-size:7px 7px;
-webkit-mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
pointer-events:none}
.hero .wrap{position:relative;padding-top:5.5rem;padding-bottom:4rem}
nav{border-bottom:1px solid var(--line)}
nav .in{max-width:60rem;margin:0 auto;padding:1.1rem 1.75rem;display:flex;
justify-content:space-between;align-items:center;gap:1rem}
.brand{font-family:var(--mono);font-size:.85rem;letter-spacing:.14em;
text-transform:uppercase;color:var(--ink);text-decoration:none;
display:inline-flex;align-items:center;gap:.6rem}
.brand i{font-style:normal;color:var(--faint)}
.mark{width:1.15em;height:1.15em;display:block;flex:0 0 auto}
nav a.up{font-family:var(--mono);font-size:.72rem;letter-spacing:.14em;
text-transform:uppercase;color:var(--soft);text-decoration:none}
nav a.up:hover{color:var(--ink)}
h1{font-family:var(--mono);font-size:clamp(2rem,5.4vw,3.4rem);line-height:1.12;
letter-spacing:-.02em;margin:0 0 1.1rem;font-weight:500;text-wrap:balance}
h1 em{font-style:normal;color:var(--faint)}
.lede{color:var(--soft);font-size:1.05rem;max-width:52ch;margin:0 0 2.25rem}
.btns{display:flex;flex-wrap:wrap;gap:.7rem;margin:0 0 .5rem;align-items:center}
.btn{display:inline-flex;align-items:center;gap:.5rem;font-family:var(--mono);
font-size:.72rem;letter-spacing:.14em;text-transform:uppercase;
padding:.8rem 1.4rem;border:1px solid var(--line-hi);border-radius:var(--r);
color:var(--ink);text-decoration:none;background:transparent;cursor:pointer;
line-height:1.2;font-weight:400}
.btn:hover{background:#141414;text-decoration:none;border-color:var(--soft)}
.btn.solid{background:var(--ink);color:#000;border-color:var(--ink)}
.btn.solid:hover{background:#d8d8d4}
.btn .chev{width:.7em;height:.7em;flex:0 0 auto;transition:transform .15s ease}
.btn[aria-expanded="true"] .chev{transform:rotate(180deg)}
/* Copy-prompt selector. Each row copies the prompt for its own target. */
.copy{position:relative;display:inline-flex}
.menu{position:absolute;top:calc(100% + .5rem);left:0;z-index:20;min-width:21rem;
background:var(--panel);border:1px solid var(--line-hi);border-radius:var(--r-soft);
padding:.4rem;display:flex;flex-direction:column;
box-shadow:0 12px 32px rgba(0,0,0,.75)}
.menu[hidden]{display:none}
.mi{display:flex;align-items:center;gap:.7rem;width:100%;text-align:left;
padding:.62rem .8rem;border:0;border-radius:calc(var(--r-soft) - .35rem);
background:transparent;color:var(--ink);font-family:var(--mono);font-size:.76rem;
letter-spacing:.04em;text-decoration:none;cursor:pointer;line-height:1.3;
white-space:nowrap}
.mi:hover,.mi:focus-visible{background:#181818;outline:none}
.mi .mark{width:1.05rem;height:1.05rem;color:var(--soft)}
.mi:hover .mark{color:var(--ink)}
.mi u{margin-left:auto;color:var(--faint);text-decoration:none;font-size:.68rem;
letter-spacing:.1em;text-transform:uppercase}
/* Works-with bar: one scrolling row, name and mark per agent. */
.strip{border-bottom:1px solid var(--line);background:var(--panel)}
.strip .in{max-width:60rem;margin:0 auto;padding:.9rem 1.75rem;display:flex;
align-items:center;gap:1.25rem;font-family:var(--mono);font-size:.72rem;
letter-spacing:.1em;text-transform:uppercase;color:var(--faint)}
.strip b{color:var(--soft);font-weight:400;flex:0 0 auto;
border-right:1px solid var(--line);padding-right:1.25rem;align-self:stretch;
display:flex;align-items:center}
.bar{display:flex;align-items:center;gap:.5rem;flex:1 1 auto;min-width:0;
overflow-x:auto;scrollbar-width:none;scroll-snap-type:x proximity;
-webkit-overflow-scrolling:touch;
-webkit-mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent);
mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent)}
.bar::-webkit-scrollbar{display:none}
.agent{display:inline-flex;align-items:center;gap:.45rem;flex:0 0 auto;
padding:.4rem .85rem;border:1px solid var(--line);border-radius:var(--r);
color:var(--soft);white-space:nowrap;scroll-snap-align:start;
text-decoration:none;background:var(--ground)}
.agent:hover{color:var(--ink);border-color:var(--line-hi);text-decoration:none}
.agent .mark{width:.95rem;height:.95rem}
h2{font-family:var(--mono);font-size:1.1rem;letter-spacing:-.01em;margin:2.75rem 0 .75rem;font-weight:500}
h3{font-size:1rem;margin:2rem 0 .5rem;font-weight:600}
h4,h5,h6{font-size:.94rem;margin:1.5rem 0 .5rem;font-weight:600}
p{margin:0 0 1rem;max-width:70ch}
ul,ol{margin:0 0 1rem;padding-left:1.35rem;max-width:70ch}
li{margin:.22rem 0}
a{color:var(--ink);text-decoration:underline;text-underline-offset:3px;
text-decoration-color:var(--line-hi)}
a:hover{text-decoration-color:var(--ink)}
a:focus-visible{outline:1px solid var(--ink);outline-offset:3px}
code{font-family:var(--mono);font-size:.85em;background:var(--panel);
border:1px solid var(--line);border-radius:5px;padding:.1em .36em}
pre{background:var(--panel);border:1px solid var(--line);padding:1rem 1.1rem;
border-radius:var(--r-soft);overflow-x:auto;margin:0 0 1.25rem}
pre code{background:none;border:0;padding:0;font-size:.82rem;line-height:1.7}
blockquote{margin:0 0 1rem;padding-left:1rem;border-left:1px solid var(--line-hi);color:var(--soft)}
hr{border:0;border-top:1px solid var(--line);margin:2.5rem 0}
table{border-collapse:collapse;width:100%;margin:0 0 1.25rem;font-size:.92rem;
display:block;overflow-x:auto}
th,td{border:1px solid var(--line);padding:.5rem .7rem;text-align:left}
.curl{display:block;background:var(--panel);border:1px solid var(--line);
border-radius:var(--r-soft);padding:.95rem 1.1rem;font-family:var(--mono);
font-size:.8rem;color:var(--soft);overflow-x:auto;margin:0 0 2.5rem;
white-space:pre;line-height:1.9}
.curl b{color:var(--ink);font-weight:400}
.curl i{font-style:normal;color:var(--faint)}
/* index */
#q{width:100%;padding:.85rem 1.2rem;font-family:var(--mono);font-size:.85rem;color:var(--ink);
background:var(--panel);border:1px solid var(--line);border-radius:var(--r);margin:0 0 .6rem}
#q::placeholder{color:var(--faint);text-transform:uppercase;letter-spacing:.1em;font-size:.72rem}
#q:focus{outline:none;border-color:var(--soft)}
#count{color:var(--faint);font-family:var(--mono);font-size:.72rem;letter-spacing:.1em;
text-transform:uppercase;margin:0 0 2rem}
.cat{margin:2.5rem 0 .5rem;font-family:var(--mono);font-size:.7rem;letter-spacing:.16em;
text-transform:uppercase;color:var(--faint);border-bottom:1px solid var(--line);
padding-bottom:.45rem}
.row{display:block;padding:.6rem .9rem;margin:0 -.9rem;border-bottom:1px solid var(--line);
border-radius:var(--r-soft);text-decoration:none}
.row:hover{background:#0a0a0a}
.nm{font-family:var(--mono);font-size:.84rem;color:var(--ink);display:block}
.ds{color:var(--soft);font-size:.9rem;display:block;margin-top:.12rem}
footer{margin-top:4rem;padding:2rem 0 4rem;border-top:1px solid var(--line);
color:var(--faint);font-size:.85rem}
footer a{color:var(--soft)}
.fmap{display:grid;gap:1.75rem;grid-template-columns:repeat(auto-fit,minmax(11rem,1fr));
margin:0 0 2rem;font-size:.85rem}
.fmap b{display:block;font-family:var(--mono);font-size:.68rem;letter-spacing:.16em;
text-transform:uppercase;color:var(--faint);font-weight:400;margin:0 0 .6rem;
border-bottom:1px solid var(--line);padding-bottom:.4rem}
.fmap a{display:inline-block;color:var(--ink);text-decoration:none;margin:.3rem .35rem 0 0}
.fmap a:hover{text-decoration:underline}
.fmap div{color:var(--faint);line-height:1.5}
.fend{border-top:1px solid var(--line);padding-top:1.25rem;font-size:.82rem}
@media (max-width:640px){
.hero .wrap{padding-top:3.25rem;padding-bottom:2.75rem}
.btns{gap:.5rem}
.btn{padding:.75rem 1.15rem;font-size:.68rem}
.copy,.copy .btn{width:100%;justify-content:center}
.menu{left:0;right:0;min-width:0}
.strip .in{flex-direction:column;align-items:flex-start;gap:.6rem;padding:.9rem 1.75rem}
.strip b{border-right:0;padding-right:0}
.bar{width:100%}
}
</style>
<link rel="icon" href="/favicon.svg">
<link rel="canonical" href="https://hanzoskills.com/cloud-gcp-databases">
<link rel="alternate" type="text/markdown" href="/cloud-gcp-databases.md">
<script type="module" src="/webmcp.js"></script>
</head>
<!-- Reading this with a machine? Every page here is also plain markdown.
This one: https://hanzoskills.com/cloud-gcp-databases.md
Index: https://hanzoskills.com/llms.txt
Corpus: https://hanzoskills.com/all.md
Append .md to any path for the markdown twin. -->
<body>
<nav><div class="in">
<a class="brand" href="/"><svg class="mark" viewBox="0 0 67 67" fill="currentColor" aria-hidden="true"><path d="M22.21 67V44.6369H0V67H22.21Z"/><path d="M66.7038 22.3184H22.2534L0.0878906 44.6367H44.4634L66.7038 22.3184Z"/><path d="M22.21 0H0V22.3184H22.21V0Z"/><path d="M66.7198 0H44.5098V22.3184H66.7198V0Z"/><path d="M66.7198 67V44.6369H44.5098V67H66.7198Z"/></svg> HANZO<i>SKILLS</i></a>
<a class="up" href="/">All skills</a>
</div></nav>
<div class="wrap">
<h1>cloud-gcp-databases</h1><p class="lede">Google Cloud managed database services including Cloud SQL, Firestore, Bigtable, and Spanner</p><h1>GCP Database Services</h1>
<p><strong>Scope</strong>: Cloud SQL, Firestore, Bigtable, Spanner, and Memorystore configuration and best practices <strong>Lines</strong>: ~340 <strong>Last Updated</strong>: 2025-10-25 <strong>Format Version</strong>: 1.0 (Atomic)</p>
<hr>
<h2>When to Use This Skill</h2>
<p>Activate this skill when:</p>
<ul><li>Deploying relational databases with Cloud SQL (MySQL, PostgreSQL, SQL Server)</li><li>Building applications with Firestore document database</li><li>Designing wide-column NoSQL solutions with Bigtable</li><li>Implementing globally distributed SQL databases with Spanner</li><li>Setting up Redis or Memcached caching with Memorystore</li><li>Migrating databases from on-premises or other clouds to GCP</li><li>Configuring high availability, backups, and read replicas</li><li>Optimizing database performance and cost</li></ul>
<h2>Core Concepts</h2>
<h3>Concept 1: Cloud SQL Configuration</h3>
<p><strong>Key features</strong>:</p>
<ul><li>Fully managed MySQL, PostgreSQL, and SQL Server</li><li>Automatic backups, point-in-time recovery, replication</li><li>High availability with automatic failover</li><li>Read replicas for scaling read traffic</li></ul>
<pre><code class="lang-bash"># Create PostgreSQL instance with high availability
gcloud sql instances create production-db \
--database-version=POSTGRES_15 \
--tier=db-n1-standard-4 \
--region=us-central1 \
--availability-type=REGIONAL \
--backup-start-time=03:00 \
--enable-bin-log \
--maintenance-window-day=SUN \
--maintenance-window-hour=4
# Create database and user
gcloud sql databases create appdb --instance=production-db
gcloud sql users create appuser \
--instance=production-db \
--password=SECURE_PASSWORD
# Create read replica for scaling reads
gcloud sql instances create production-db-replica \
--master-instance-name=production-db \
--tier=db-n1-standard-2 \
--region=us-central1</code></pre>
<h3>Concept 2: Firestore Document Database</h3>
<p><strong>Data model</strong>:</p>
<ul><li>Collections contain documents (JSON-like)</li><li>Documents contain fields and subcollections</li><li>Automatic indexing for queries</li><li>Real-time listeners for live updates</li></ul>
<pre><code class="lang-python">from google.cloud import firestore
# Initialize Firestore client
db = firestore.Client()
# Create document with auto-generated ID
users_ref = db.collection(&#x27;users&#x27;)
new_user = users_ref.add({
&#x27;name&#x27;: &#x27;Alice Johnson&#x27;,
&#x27;email&#x27;: &#x27;[email protected]&#x27;,
&#x27;created_at&#x27;: firestore.SERVER_TIMESTAMP,
&#x27;roles&#x27;: [&#x27;admin&#x27;, &#x27;editor&#x27;]
})
# Query documents
active_users = users_ref.where(&#x27;status&#x27;, &#x27;==&#x27;, &#x27;active&#x27;).where(&#x27;roles&#x27;, &#x27;array_contains&#x27;, &#x27;admin&#x27;).stream()
for user in active_users:
print(f&#x27;{user.id} =&gt; {user.to_dict()}&#x27;)
# Real-time listener for changes
def on_snapshot(collection_snapshot, changes, read_time):
for change in changes:
if change.type.name == &#x27;ADDED&#x27;:
print(f&#x27;New user: {change.document.id}&#x27;)
users_ref.on_snapshot(on_snapshot)</code></pre>
<h3>Concept 3: Bigtable Schema Design</h3>
<p><strong>Key principles</strong>:</p>
<ul><li>Wide-column NoSQL for massive scale (billions of rows, petabytes)</li><li>Row key design critical for performance (avoid hotspots)</li><li>Column families group related columns</li><li>Time-series data natural fit</li></ul>
<pre><code class="lang-python">from google.cloud import bigtable
from google.cloud.bigtable import column_family, row_filters
# Create Bigtable instance and table
client = bigtable.Client(project=&#x27;my-project&#x27;, admin=True)
instance = client.instance(&#x27;analytics-instance&#x27;)
# Create table with column family
table = instance.table(&#x27;events&#x27;)
cf = table.column_family(&#x27;metrics&#x27;, max_age=timedelta(days=30))
cf.create()
# Row key design for time-series: reverse timestamp + user_id (avoid hotspots)
import time
reverse_timestamp = str(2**63 - int(time.time() * 1000))
row_key = f&quot;{reverse_timestamp}#{user_id}&quot;.encode()
# Write data
row = table.direct_row(row_key)
row.set_cell(&#x27;metrics&#x27;, &#x27;page_views&#x27;, str(125), timestamp=datetime.utcnow())
row.set_cell(&#x27;metrics&#x27;, &#x27;session_duration&#x27;, str(450), timestamp=datetime.utcnow())
row.commit()
# Read data with prefix scan
rows = table.read_rows(
start_key=f&quot;{reverse_timestamp}#&quot;.encode(),
limit=100
)</code></pre>
<h3>Concept 4: Spanner Global Distribution</h3>
<p><strong>Features</strong>:</p>
<ul><li>Horizontally scalable SQL database</li><li>Synchronous replication across regions</li><li>External consistency (linearizable transactions)</li><li>SQL with ACID guarantees at global scale</li></ul>
<pre><code class="lang-python">from google.cloud import spanner
# Create Spanner instance with multi-region configuration
spanner_client = spanner.Client()
instance = spanner_client.instance(
&#x27;global-instance&#x27;,
configuration_name=&#x27;nam-eur-asia1&#x27;, # Multi-region
node_count=3
)
database = instance.database(&#x27;orders&#x27;)
# Insert with transaction
def insert_order(transaction):
transaction.execute_update(
&quot;&quot;&quot;INSERT INTO Orders (OrderId, CustomerId, Amount, CreatedAt)
VALUES (@order_id, @customer_id, @amount, CURRENT_TIMESTAMP())&quot;&quot;&quot;,
params={&#x27;order_id&#x27;: &#x27;ORD-123&#x27;, &#x27;customer_id&#x27;: &#x27;CUST-456&#x27;, &#x27;amount&#x27;: 99.99},
param_types={&#x27;order_id&#x27;: spanner.param_types.STRING,
&#x27;customer_id&#x27;: spanner.param_types.STRING,
&#x27;amount&#x27;: spanner.param_types.FLOAT64}
)
database.run_in_transaction(insert_order)
# Query with strong consistency
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
&quot;&quot;&quot;SELECT o.OrderId, o.Amount, c.Name
FROM Orders o JOIN Customers c ON o.CustomerId = c.CustomerId
WHERE o.CreatedAt &gt; TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)&quot;&quot;&quot;
)
for row in results:
print(f&quot;Order {row[0]}: ${row[1]} - {row[2]}&quot;)</code></pre>
<hr>
<h2>Patterns</h2>
<h3>Pattern 1: Cloud SQL High Availability</h3>
<p><strong>When to use</strong>:</p>
<ul><li>Production databases requiring 99.95% SLA</li><li>Automatic failover to standby instance</li></ul>
<pre><code class="lang-bash"># ❌ Bad: Single-zone instance (no automatic failover)
gcloud sql instances create db \
--tier=db-n1-standard-2 \
--region=us-central1
# Zone failure causes downtime
# ✅ Good: Regional HA instance with automatic failover
gcloud sql instances create db \
--tier=db-n1-standard-2 \
--region=us-central1 \
--availability-type=REGIONAL \
--enable-bin-log
# Standby instance automatically promoted on primary failure
# Typically &lt;60 seconds of downtime</code></pre>
<p><strong>Benefits</strong>:</p>
<ul><li>99.95% SLA (vs 99.5% for zonal)</li><li>Automatic failover with minimal downtime</li><li>Synchronous replication to standby</li></ul>
<h3>Pattern 2: Firestore Security Rules</h3>
<p><strong>Use case</strong>: Secure document access based on authentication and data content</p>
<pre><code class="lang-javascript">rules_version = &#x27;2&#x27;;
service cloud.firestore {
match /databases/{database}/documents {
// Users can only read/write their own documents
match /users/{userId} {
allow read, write: if request.auth != null &amp;&amp; request.auth.uid == userId;
}
// Public read, authenticated write
match /posts/{postId} {
allow read: if true;
allow create: if request.auth != null;
allow update, delete: if request.auth != null &amp;&amp;
request.auth.uid == resource.data.author_id;
}
// Admin-only collection
match /admin/{document=**} {
allow read, write: if request.auth.token.admin == true;
}
}
}</code></pre>
<h3>Pattern 3: Bigtable Row Key Design for Time Series</h3>
<p><strong>Use case</strong>: Store sensor data with efficient time-range queries</p>
<pre><code class="lang-python"># ❌ Bad: Sequential timestamps create hotspot on single tablet
row_key = f&quot;{sensor_id}#{timestamp}&quot;.encode()
# All recent writes go to same tablet server
# ✅ Good: Hash prefix distributes writes, reverse timestamp for recent scans
import hashlib
def create_row_key(sensor_id, timestamp):
# Hash prefix (first 2 hex chars) distributes writes across tablets
prefix = hashlib.md5(sensor_id.encode()).hexdigest()[:2]
# Reverse timestamp for efficient recent-data scans
reverse_ts = str(2**63 - int(timestamp * 1000))
return f&quot;{prefix}#{sensor_id}#{reverse_ts}&quot;.encode()
# Write to Bigtable
row = table.direct_row(create_row_key(&#x27;sensor-42&#x27;, time.time()))
row.set_cell(&#x27;data&#x27;, &#x27;temperature&#x27;, &#x27;72.5&#x27;)
row.commit()
# Read recent data for sensor with prefix scan
prefix = hashlib.md5(&#x27;sensor-42&#x27;.encode()).hexdigest()[:2]
rows = table.read_rows(
start_key=f&quot;{prefix}#sensor-42#&quot;.encode(),
end_key=f&quot;{prefix}#sensor-42#~&quot;.encode(),
limit=100
)</code></pre>
<h3>Pattern 4: Spanner Interleaved Tables</h3>
<p><strong>Use case</strong>: Physically co-locate related rows for faster joins</p>
<pre><code class="lang-sql">-- ❌ Bad: Separate tables (data distributed, slow joins)
CREATE TABLE Customers (
CustomerId STRING(36),
Name STRING(100)
) PRIMARY KEY (CustomerId);
CREATE TABLE Orders (
OrderId STRING(36),
CustomerId STRING(36),
Amount FLOAT64
) PRIMARY KEY (OrderId);
-- Orders scattered across nodes, expensive joins
-- ✅ Good: Interleave Orders within Customers
CREATE TABLE Customers (
CustomerId STRING(36),
Name STRING(100)
) PRIMARY KEY (CustomerId);
CREATE TABLE Orders (
CustomerId STRING(36),
OrderId STRING(36),
Amount FLOAT64
) PRIMARY KEY (CustomerId, OrderId),
INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
-- Orders stored physically with parent Customer row
-- Fast queries for customer&#x27;s orders
SELECT * FROM Customers c JOIN Orders o USING (CustomerId)
WHERE c.CustomerId = &#x27;CUST-123&#x27;; -- Efficient, single node</code></pre>
<h3>Pattern 5: Cloud SQL Connection Pooling</h3>
<p><strong>Use case</strong>: Avoid connection exhaustion in serverless environments</p>
<pre><code class="lang-python">import sqlalchemy
from sqlalchemy import create_engine
# ❌ Bad: Create new connection per request (connection exhaustion)
def query_database():
engine = create_engine(&#x27;postgresql://user:pass@host/db&#x27;)
with engine.connect() as conn:
result = conn.execute(&quot;SELECT * FROM users&quot;)
return result
# ✅ Good: Reuse connection pool across requests
# Cloud SQL Proxy handles connection pooling
connection_pool = create_engine(
&#x27;postgresql+pg8000://&#x27;,
creator=lambda: connector.connect(
&#x27;project:region:instance&#x27;,
&#x27;pg8000&#x27;,
user=&#x27;appuser&#x27;,
password=&#x27;SECRET&#x27;,
db=&#x27;appdb&#x27;
),
pool_size=5, # Max 5 concurrent connections
max_overflow=2,
pool_timeout=30,
pool_recycle=1800 # Recycle connections every 30 min
)
def query_database():
with connection_pool.connect() as conn:
result = conn.execute(sqlalchemy.text(&quot;SELECT * FROM users&quot;))
return result</code></pre>
<h3>Pattern 6: Firestore Composite Indexes</h3>
<p><strong>Use case</strong>: Enable complex queries on multiple fields</p>
<pre><code class="lang-python"># Query requiring composite index
users_ref = db.collection(&#x27;users&#x27;)
query = users_ref.where(&#x27;status&#x27;, &#x27;==&#x27;, &#x27;active&#x27;) \
.where(&#x27;country&#x27;, &#x27;==&#x27;, &#x27;US&#x27;) \
.order_by(&#x27;created_at&#x27;, direction=firestore.Query.DESCENDING)
# Firestore automatically prompts to create index via error message
# Or create via index configuration:</code></pre>
<pre><code class="lang-json">{
&quot;indexes&quot;: [
{
&quot;collectionGroup&quot;: &quot;users&quot;,
&quot;queryScope&quot;: &quot;COLLECTION&quot;,
&quot;fields&quot;: [
{&quot;fieldPath&quot;: &quot;status&quot;, &quot;order&quot;: &quot;ASCENDING&quot;},
{&quot;fieldPath&quot;: &quot;country&quot;, &quot;order&quot;: &quot;ASCENDING&quot;},
{&quot;fieldPath&quot;: &quot;created_at&quot;, &quot;order&quot;: &quot;DESCENDING&quot;}
]
}
]
}</code></pre>
<h3>Pattern 7: Memorystore for Database Caching</h3>
<p><strong>Use case</strong>: Cache frequently accessed database queries in Redis</p>
<pre><code class="lang-python">import redis
from google.cloud import secretmanager
# Connect to Memorystore Redis
redis_client = redis.Redis(
host=&#x27;10.0.0.3&#x27;, # Memorystore instance IP
port=6379,
decode_responses=True
)
def get_user(user_id):
# Try cache first
cache_key = f&quot;user:{user_id}&quot;
cached_user = redis_client.get(cache_key)
if cached_user:
return json.loads(cached_user)
# Cache miss - query database
with connection_pool.connect() as conn:
result = conn.execute(
sqlalchemy.text(&quot;SELECT * FROM users WHERE id = :id&quot;),
{&quot;id&quot;: user_id}
)
user = dict(result.fetchone())
# Store in cache (expire after 1 hour)
redis_client.setex(cache_key, 3600, json.dumps(user))
return user</code></pre>
<h3>Pattern 8: Database Migration with Database Migration Service</h3>
<p><strong>Use case</strong>: Migrate from on-premises or other clouds to Cloud SQL</p>
<pre><code class="lang-bash"># Create migration job from MySQL source to Cloud SQL
gcloud database-migration migration-jobs create migrate-prod-db \
--region=us-central1 \
--type=CONTINUOUS \
--source=on-prem-mysql \
--destination=projects/my-project/instances/production-db \
--dump-path=gs://migration-bucket/dumps
# Monitor migration progress
gcloud database-migration migration-jobs describe migrate-prod-db \
--region=us-central1
# Promote Cloud SQL instance (cutover)
gcloud database-migration migration-jobs promote migrate-prod-db \
--region=us-central1</code></pre>
<hr>
<h2>Quick Reference</h2>
<h3>Database Service Selection</h3>
<pre><code>Service | Type | Scale | Use Case
-------------|----------------|---------------|---------------------------
Cloud SQL | SQL (managed) | Up to 64 TB | Traditional SQL apps
Firestore | Document NoSQL | Unlimited | Mobile/web apps, real-time
Bigtable | Wide-column | Petabyte+ | Time-series, analytics
Spanner | Global SQL | Petabyte+ | Global apps, strong consistency
Memorystore | In-memory | Up to 300 GB | Caching, session storage</code></pre>
<h3>Cloud SQL Tiers</h3>
<pre><code>Tier | vCPUs | Memory | Use Case
------------------|-------|---------|------------------
db-f1-micro | 1 | 0.6 GB | Dev/test
db-g1-small | 1 | 1.7 GB | Small apps
db-n1-standard-1 | 1 | 3.75 GB | Production start
db-n1-standard-4 | 4 | 15 GB | Medium workloads
db-n1-standard-16 | 16 | 60 GB | Large workloads</code></pre>
<h3>Key gcloud Commands</h3>
<pre><code class="lang-bash"># Cloud SQL
gcloud sql instances create NAME --database-version=POSTGRES_15 --tier=TIER
gcloud sql instances list
gcloud sql databases create DB --instance=INSTANCE
gcloud sql backups create --instance=INSTANCE
gcloud sql instances failover INSTANCE # Manual failover test
# Firestore (via Firebase CLI)
firebase deploy --only firestore:rules
firebase deploy --only firestore:indexes
# Spanner
gcloud spanner instances create INSTANCE --config=CONFIG --nodes=3
gcloud spanner databases create DB --instance=INSTANCE</code></pre>
<h3>Key Guidelines</h3>
<pre><code>✅ DO: Enable automated backups for all production databases
✅ DO: Use read replicas to scale read traffic
✅ DO: Implement connection pooling for Cloud SQL
✅ DO: Design Bigtable row keys to avoid hotspots
✅ DO: Use Firestore security rules for access control
✅ DO: Test failover procedures regularly
❌ DON&#x27;T: Use Spanner for small databases (expensive, use Cloud SQL)
❌ DON&#x27;T: Create Bigtable row keys with sequential timestamps
❌ DON&#x27;T: Store large blobs in Firestore documents (max 1 MB)
❌ DON&#x27;T: Disable binary logging on Cloud SQL HA instances
❌ DON&#x27;T: Use Firestore for analytics queries (use BigQuery)</code></pre>
<hr>
<h2>Anti-Patterns</h2>
<h3>Critical Violations</h3>
<pre><code class="lang-python"># ❌ NEVER: Store large files in Firestore documents
db.collection(&#x27;users&#x27;).document(&#x27;user-123&#x27;).set({
&#x27;name&#x27;: &#x27;Alice&#x27;,
&#x27;profile_image&#x27;: base64_encode(image_data) # 10MB image - FAILS!
})
# Firestore has 1MB document size limit
# ✅ CORRECT: Store files in Cloud Storage, reference in Firestore
from google.cloud import storage
storage_client = storage.Client()
bucket = storage_client.bucket(&#x27;user-uploads&#x27;)
blob = bucket.blob(f&#x27;profiles/{user_id}.jpg&#x27;)
blob.upload_from_filename(image_path)
db.collection(&#x27;users&#x27;).document(&#x27;user-123&#x27;).set({
&#x27;name&#x27;: &#x27;Alice&#x27;,
&#x27;profile_image_url&#x27;: f&#x27;gs://user-uploads/profiles/{user_id}.jpg&#x27;
})</code></pre>
<p>❌ <strong>Large documents in Firestore</strong>: Documents are limited to 1MB. Storing large files causes write failures. ✅ <strong>Correct approach</strong>: Store files in Cloud Storage, save references in Firestore documents.</p>
<h3>Common Mistakes</h3>
<pre><code class="lang-python"># ❌ Don&#x27;t: Use sequential row keys in Bigtable
row_key = f&quot;{timestamp}#{sensor_id}&quot;.encode()
# Creates hotspot - all writes go to single tablet server
# ✅ Correct: Distribute writes with hash prefix or reverse timestamp
import hashlib
prefix = hashlib.md5(sensor_id.encode()).hexdigest()[:2]
reverse_ts = str(2**63 - int(timestamp * 1000))
row_key = f&quot;{prefix}#{sensor_id}#{reverse_ts}&quot;.encode()</code></pre>
<p>❌ <strong>Sequential Bigtable keys</strong>: Sequential row keys create hotspots where all writes go to a single tablet server. ✅ <strong>Better</strong>: Use hash prefixes or reverse timestamps to distribute writes across tablet servers.</p>
<pre><code class="lang-bash"># ❌ Don&#x27;t: Use Spanner for small databases
gcloud spanner instances create small-app-db --config=regional-us-central1 --nodes=1
# Minimum cost: ~$650/month for single node!
# ✅ Correct: Use Cloud SQL for small/medium databases
gcloud sql instances create small-app-db \
--database-version=POSTGRES_15 \
--tier=db-n1-standard-1
# Cost: ~$50/month</code></pre>
<p>❌ <strong>Spanner for small databases</strong>: Spanner has high minimum cost. Using it for small databases wastes money. ✅ <strong>Better</strong>: Use Cloud SQL for databases &lt;1TB. Only use Spanner when you need global distribution or horizontal scaling.</p>
<pre><code class="lang-python"># ❌ Don&#x27;t: Perform analytics queries on Firestore
# Query all orders from last year (millions of documents)
orders = db.collection(&#x27;orders&#x27;).where(&#x27;created_at&#x27;, &#x27;&gt;&#x27;, last_year).stream()
total_revenue = sum(order.to_dict()[&#x27;amount&#x27;] for order in orders)
# Slow, expensive, and hits quota limits
# ✅ Correct: Export to BigQuery for analytics
# Use Firestore-BigQuery extension or scheduled export
# Then query in BigQuery:
&quot;&quot;&quot;
SELECT SUM(amount) as total_revenue
FROM `project.dataset.orders`
WHERE created_at &gt; TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 YEAR)
&quot;&quot;&quot;</code></pre>
<p>❌ <strong>Firestore for analytics</strong>: Firestore is optimized for transactional queries, not large-scale analytics. ✅ <strong>Better</strong>: Export Firestore data to BigQuery for analytics and reporting queries.</p>
<hr>
<h2>Related Skills</h2>
<ul><li><code>gcp-compute.md</code> - Connecting Compute Engine instances to databases</li><li><code>gcp-storage.md</code> - Database backups to Cloud Storage</li><li><code>gcp-iam-security.md</code> - Database user management and service account access</li><li><code>gcp-networking.md</code> - Private IP configuration for database security</li></ul>
<hr>
<p><strong>Last Updated</strong>: 2025-10-25 <strong>Format Version</strong>: 1.0 (Atomic)</p>
<footer>
<div class="fmap">
<div><b>Build</b>
<a href="https://hanzo.app">hanzo.app</a> app builder
<a href="https://hanzo.codes">hanzo.codes</a> code with an agent
<a href="https://hanzoskills.com">hanzoskills.com</a> what an agent reads</div>
<div><b>Run</b>
<a href="https://console.hanzo.ai">console.hanzo.ai</a> cloud console
<a href="https://api.hanzo.ai">api.hanzo.ai</a> the API</div>
<div><b>Account</b>
<a href="https://hanzo.id">hanzo.id</a> sign in
<a href="https://pay.hanzo.ai">pay.hanzo.ai</a> top up
<a href="https://billing.hanzo.ai">billing.hanzo.ai</a> invoices and usage</div>
<div><b>Company</b>
<a href="https://hanzo.ai">hanzo.ai</a> canonical
<a href="https://hanzo.ventures">hanzo.ventures</a> ventures</div>
</div>
<div class="fend"><a href="/cloud-gcp-databases.md">This page as plain text</a> · <a href="/llms.txt">Index for agents</a></div>
</footer>
</div></body></html>