mirror of
https://github.com/hanzoai/skills.git
synced 2026-11-19 00:47:36 +00:00
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.
668 lines
33 KiB
HTML
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('users')
|
|
new_user = users_ref.add({
|
|
'name': 'Alice Johnson',
|
|
'email': '[email protected]',
|
|
'created_at': firestore.SERVER_TIMESTAMP,
|
|
'roles': ['admin', 'editor']
|
|
})
|
|
|
|
# Query documents
|
|
active_users = users_ref.where('status', '==', 'active').where('roles', 'array_contains', 'admin').stream()
|
|
|
|
for user in active_users:
|
|
print(f'{user.id} => {user.to_dict()}')
|
|
|
|
# Real-time listener for changes
|
|
def on_snapshot(collection_snapshot, changes, read_time):
|
|
for change in changes:
|
|
if change.type.name == 'ADDED':
|
|
print(f'New user: {change.document.id}')
|
|
|
|
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='my-project', admin=True)
|
|
instance = client.instance('analytics-instance')
|
|
|
|
# Create table with column family
|
|
table = instance.table('events')
|
|
cf = table.column_family('metrics', 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"{reverse_timestamp}#{user_id}".encode()
|
|
|
|
# Write data
|
|
row = table.direct_row(row_key)
|
|
row.set_cell('metrics', 'page_views', str(125), timestamp=datetime.utcnow())
|
|
row.set_cell('metrics', 'session_duration', str(450), timestamp=datetime.utcnow())
|
|
row.commit()
|
|
|
|
# Read data with prefix scan
|
|
rows = table.read_rows(
|
|
start_key=f"{reverse_timestamp}#".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(
|
|
'global-instance',
|
|
configuration_name='nam-eur-asia1', # Multi-region
|
|
node_count=3
|
|
)
|
|
|
|
database = instance.database('orders')
|
|
|
|
# Insert with transaction
|
|
def insert_order(transaction):
|
|
transaction.execute_update(
|
|
"""INSERT INTO Orders (OrderId, CustomerId, Amount, CreatedAt)
|
|
VALUES (@order_id, @customer_id, @amount, CURRENT_TIMESTAMP())""",
|
|
params={'order_id': 'ORD-123', 'customer_id': 'CUST-456', 'amount': 99.99},
|
|
param_types={'order_id': spanner.param_types.STRING,
|
|
'customer_id': spanner.param_types.STRING,
|
|
'amount': spanner.param_types.FLOAT64}
|
|
)
|
|
|
|
database.run_in_transaction(insert_order)
|
|
|
|
# Query with strong consistency
|
|
with database.snapshot() as snapshot:
|
|
results = snapshot.execute_sql(
|
|
"""SELECT o.OrderId, o.Amount, c.Name
|
|
FROM Orders o JOIN Customers c ON o.CustomerId = c.CustomerId
|
|
WHERE o.CreatedAt > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)"""
|
|
)
|
|
for row in results:
|
|
print(f"Order {row[0]}: ${row[1]} - {row[2]}")</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 <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 = '2';
|
|
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 && 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 &&
|
|
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"{sensor_id}#{timestamp}".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"{prefix}#{sensor_id}#{reverse_ts}".encode()
|
|
|
|
# Write to Bigtable
|
|
row = table.direct_row(create_row_key('sensor-42', time.time()))
|
|
row.set_cell('data', 'temperature', '72.5')
|
|
row.commit()
|
|
|
|
# Read recent data for sensor with prefix scan
|
|
prefix = hashlib.md5('sensor-42'.encode()).hexdigest()[:2]
|
|
rows = table.read_rows(
|
|
start_key=f"{prefix}#sensor-42#".encode(),
|
|
end_key=f"{prefix}#sensor-42#~".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's orders
|
|
SELECT * FROM Customers c JOIN Orders o USING (CustomerId)
|
|
WHERE c.CustomerId = 'CUST-123'; -- 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('postgresql://user:pass@host/db')
|
|
with engine.connect() as conn:
|
|
result = conn.execute("SELECT * FROM users")
|
|
return result
|
|
|
|
# ✅ Good: Reuse connection pool across requests
|
|
# Cloud SQL Proxy handles connection pooling
|
|
connection_pool = create_engine(
|
|
'postgresql+pg8000://',
|
|
creator=lambda: connector.connect(
|
|
'project:region:instance',
|
|
'pg8000',
|
|
user='appuser',
|
|
password='SECRET',
|
|
db='appdb'
|
|
),
|
|
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("SELECT * FROM users"))
|
|
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('users')
|
|
query = users_ref.where('status', '==', 'active') \
|
|
.where('country', '==', 'US') \
|
|
.order_by('created_at', 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">{
|
|
"indexes": [
|
|
{
|
|
"collectionGroup": "users",
|
|
"queryScope": "COLLECTION",
|
|
"fields": [
|
|
{"fieldPath": "status", "order": "ASCENDING"},
|
|
{"fieldPath": "country", "order": "ASCENDING"},
|
|
{"fieldPath": "created_at", "order": "DESCENDING"}
|
|
]
|
|
}
|
|
]
|
|
}</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='10.0.0.3', # Memorystore instance IP
|
|
port=6379,
|
|
decode_responses=True
|
|
)
|
|
|
|
def get_user(user_id):
|
|
# Try cache first
|
|
cache_key = f"user:{user_id}"
|
|
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("SELECT * FROM users WHERE id = :id"),
|
|
{"id": 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'T: Use Spanner for small databases (expensive, use Cloud SQL)
|
|
❌ DON'T: Create Bigtable row keys with sequential timestamps
|
|
❌ DON'T: Store large blobs in Firestore documents (max 1 MB)
|
|
❌ DON'T: Disable binary logging on Cloud SQL HA instances
|
|
❌ DON'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('users').document('user-123').set({
|
|
'name': 'Alice',
|
|
'profile_image': 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('user-uploads')
|
|
blob = bucket.blob(f'profiles/{user_id}.jpg')
|
|
blob.upload_from_filename(image_path)
|
|
|
|
db.collection('users').document('user-123').set({
|
|
'name': 'Alice',
|
|
'profile_image_url': f'gs://user-uploads/profiles/{user_id}.jpg'
|
|
})</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't: Use sequential row keys in Bigtable
|
|
row_key = f"{timestamp}#{sensor_id}".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"{prefix}#{sensor_id}#{reverse_ts}".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'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 <1TB. Only use Spanner when you need global distribution or horizontal scaling.</p>
|
|
<pre><code class="lang-python"># ❌ Don't: Perform analytics queries on Firestore
|
|
# Query all orders from last year (millions of documents)
|
|
orders = db.collection('orders').where('created_at', '>', last_year).stream()
|
|
total_revenue = sum(order.to_dict()['amount'] 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:
|
|
"""
|
|
SELECT SUM(amount) as total_revenue
|
|
FROM `project.dataset.orders`
|
|
WHERE created_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 YEAR)
|
|
"""</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> |