9.2 AlloyDB Schemas and BigQuery Ingestion

AlloyDB Schemas and Analytical Data Integration

The blueprint names AlloyDB, Cloud SQL, Spanner, Firestore, Bigtable, Cloud Storage and BigQuery because selecting storage is only the beginning. Developers must understand schema design, client connections, reads/writes and replication behavior. A relational application transaction and an analytics ingestion pipeline have different correctness boundaries.

AlloyDB's Application Model

AlloyDB for PostgreSQL is a managed PostgreSQL-compatible service with separated compute and storage. A cluster contains its databases and metadata; a primary instance serves read-write requests, and optional read pool instances scale read-only access. Use standard PostgreSQL clients and supported connectors, while checking extension/version compatibility before treating a migration as entirely transparent.

Route writes and read-modify-write transactions to the primary. Read pools are useful for suitable read workloads, but replication/replay lag can affect freshness even when compute instances share the same storage layer. A user who has just changed an order might need the authoritative primary for a read-your-writes response. A report can often tolerate lag. Choose consistency from the business requirement rather than assuming every read endpoint is interchangeable.

Regional HA and cross-region disaster recovery are also separate. A supported HA primary has redundant nodes across zones; a secondary cluster in another region supports a distinct replication/recovery design. Monitor lag and rehearse failover, endpoint changes and reconnection. Do not turn a planned zero-data-loss switchover description into a promise that every unplanned cross-region failure loses no data.

The AlloyDB Auth Proxy automates supported secure connection handling. It does not create a private network route or replace PostgreSQL object privileges. Check API enablement, caller permissions, VPC/private endpoint reachability and the database user. Keep application connection pools bounded and validate idle connections after failover. Closing a dead connection and safely retrying an entire transaction is different from blindly resending the final statement.

Relational Schema from a Real Access Pattern

Suppose an order service needs an order header, many order items, and a guarantee that a request ID cannot create two orders. A normalized schema might use Orders(order_id, customer_id, request_id, created_at) plus OrderItems(order_id, line_number, product_id, quantity). Make the appropriate identifiers primary/unique keys, require positive quantities, and use foreign keys where the engine and workload support them. The request ID's uniqueness is a correctness control, not merely a query optimization.

An index on (customer_id, created_at) can help a customer's recent-order query. It does not automatically accelerate a query that filters only by a different field or scans every row. Inspect the query plan and representative selectivity. Additional indexes consume storage and add write work; do not create an index for every column without an access-pattern reason. Parameterize input values and keep schema migrations compatible with both application versions during a rollout.

Spanner adds distributed key-range considerations: avoid a high-write monotonic leading key, and select interleaving/indexes for actual locality and query needs. Firestore instead uses documents, denormalization and indexes for its query model; Bigtable uses row-key locality and column families for large key-range access. These are schema decisions even though the products do not all use the same SQL DDL. A service name cannot resolve a poorly chosen key or query pattern.

BigQuery Is an Analytical Destination

BigQuery is appropriate for analytical scans, aggregations and AI/ML datasets. It is usually not a replacement for the transaction that reserves the last inventory unit or checks an account balance under concurrent requests. Keep the authoritative OLTP update in the appropriate transaction system, then transfer the analytical event with explicit delivery semantics.

For periodic files already in Cloud Storage, submit a BigQuery load job with the correct location, format and schema. Track the job's outcome; an accepted request is not the same as a completed successful load. Use stable job identifiers/retry behavior supported by the API to avoid accidentally submitting duplicate work. Choose partitioning and clustering from query filters and expected volume so routine analysis does not scan unnecessary data.

For streaming, the BigQuery Storage Write API uses write streams. The default stream is convenient and provides at-least-once semantics. An application-created committed stream can use offsets for exactly-once writes within that stream. Persist the stream/offset state correctly and inspect append responses before advancing it. Offsets do not globally deduplicate an event independently published into another stream or eliminate duplicates already present upstream.

Cross-System Transactions and Recovery

A common failure occurs when an application commits an order to PostgreSQL, then crashes before publishing its analytics event. Publishing before the order commits reverses the problem: analytics can contain an order that was rolled back. A transactional outbox writes the business row and an event record in the same database transaction. A separate publisher retries outbox delivery and records progress. The consumer deduplicates using the stable business event ID.

This design does not make every external system part of one distributed ACID transaction. It makes the handoff recoverable and observable. Retain failed events with a reason and replay procedure, monitor oldest unpublished age and ingestion error rate, and avoid acknowledging a source message before the destination's durable write outcome is known. For schema evolution, allow compatible additions where supported and reject/quarantine incompatible payloads explicitly instead of discarding fields silently.

When troubleshooting a missing dashboard record, follow one event through the OLTP commit, outbox, messaging delivery, warehouse append/load result and final query filter. A query against the wrong partition or a delayed read replica can look like data loss. The evidence should identify the broken stage before the repair is selected.

Sources checked October 18, 2026: AlloyDB overview, replication lag, BigQuery streaming semantics.