Documentation
Advanced Topics

Advanced Topics

Advanced Topics

This section covers more advanced concepts and features, particularly those specific to Google Spanner or requiring a deeper understanding of spanner-orm’s capabilities.

Google Spanner: Interleaved Tables

Interleaved tables are a powerful feature in Google Spanner that allow you to co-locate child table rows physically with their parent row in storage. This can significantly improve performance for queries that access parent and child data together, especially for common hierarchical relationships.

Concept:

  • A child table is defined as being “interleaved in” a parent table.
  • The primary key of the child table must include the primary key of the parent table as a prefix.
  • This physical co-location means that related data can often be read with fewer splits and less network overhead.

spanner-orm and Interleaved Tables:

spanner-orm is designed with Spanner’s unique features in mind. While direct, high-level ORM syntax for defining interleaved tables might still be evolving, the ORM aims to support this either through:

  1. Specific Schema Definition Syntax: Future versions may include explicit options in the table definition to specify interleaving.
  2. Migration Customization: Generated Spanner DDL within migration files can be manually adjusted to include INTERLEAVE IN PARENT clauses if needed.
  3. Querying: The query builder and fluent API should transparently work with correctly defined interleaved schemas.

Current Status & Guidance:

  • For now, if you need to define interleaved tables, you might need to customize the Spanner DDL generated by the migration tool.
  • Ensure your primary key definitions align with Spanner’s requirements for interleaving.
  • Refer to the official Google Cloud Spanner documentation for detailed information on designing and using interleaved tables:

As spanner-orm matures, more direct support for defining and managing interleaved tables via the ORM’s schema syntax is anticipated.

Google Spanner: Simulating Graph Queries

Google Spanner does not have a native graph query language like Cypher (Neo4j) or Gremlin (TinkerPop). However, its relational capabilities, combined with appropriate schema design and indexing, allow it to efficiently handle and query graph-like data structures.

Common Approaches for Representing Graphs:

  • Adjacency List: A table stores pairs of connected nodes (e.g., edges table with from_node_id and to_node_id columns, both referencing a nodes table).
  • Materialized Paths: Store the full path from a root to a node.

Querying Graph Data in Spanner with SQL:

  • Recursive Common Table Expressions (RCTEs) can be used for graph traversal (e.g., finding all descendants of a node, shortest paths). Spanner supports RCTEs.
  • Standard SQL joins can be used effectively on adjacency list tables, especially with proper indexing on foreign key columns.

spanner-orm and Graph-like Data:

  • You can define your nodes and edges tables (or other graph structures) using spanner-orm’s standard schema definition tools.
  • For complex traversals or graph-specific algorithms, you might use the db.raw() method to execute sophisticated SQL queries, including those with RCTEs.
  • The standard query builder can be used for simpler join-based queries on your graph tables.

Guidance:

  • Focus on a good relational schema design for your graph data.
  • Leverage Spanner’s indexing capabilities on columns involved in joins or lookups.
  • For an overview of how Spanner can be used for graph workloads, you might find general articles on “Graph Databases on Relational Systems” or Spanner’s own documentation on query capabilities helpful. While there isn’t a specific “Graph Queries on Spanner” page, understanding its SQL power is key.

PostgreSQL Equivalents/Fallbacks for Spanner-Specific Features

When using spanner-orm with a single object model, it’s important to understand how Spanner-specific concepts translate or behave when targeting PostgreSQL:

  • Interleaved Tables: PostgreSQL does not have a direct equivalent to Spanner’s interleaved tables.
    • spanner-orm Behavior: When generating DDL for PostgreSQL from a schema that might conceptually represent an interleaved relationship (e.g., via primary key composition), spanner-orm will typically create standard parent and child tables with foreign key constraints. The physical co-location aspect of Spanner’s interleaving will not be present. Performance characteristics will be those of standard PostgreSQL joins.
  • Spanner-Specific Data Types: If the ORM introduces abstract types that map to a very Spanner-specific type with no direct PostgreSQL equivalent, the ORM will attempt a reasonable mapping to a standard PostgreSQL type. For example, Spanner’s NUMERIC (for arbitrary precision) might map to PostgreSQL’s NUMERIC or DECIMAL.
  • Query Hints or Spanner Functions: If you were to use raw SQL with Spanner-specific query hints or functions, these would likely cause errors if executed against PostgreSQL. The ORM itself aims to generate dialect-neutral queries from its builder APIs where possible.

The general principle is that spanner-orm strives to provide a functional equivalent in PostgreSQL for the logical schema defined, even if the underlying performance characteristics or specific physical storage optimizations of Spanner cannot be replicated.