This document describes how to create efficient queries by using best practices for designing Spanner Graph schemas. You can iterate on your schema design, so we recommend that you first identify critical query patterns to guide your schema design.
For general information about Spanner schema design best practices, see Schema design best practices .
Optimize edge traversal
Edge traversal is the process of navigating through a graph by following its edges, starting at a particular node and moving along connected edges to reach other nodes. The direction of the edge is defined by the schema. Edge traversal is a fundamental operation in Spanner Graph, so improving edge traversal efficiency is key to your application performance.
You can traverse an edge in two directions:
-
forward edge traversal : follows outgoing edges of the source node.
-
reverse edge traversal : follows incoming edges of the destination node.
Given a person, the following example query performs forward edge traversal of Owns
edges:
GRAPH
FinGraph
MATCH
(
person
:
Person
{
id
:
1
}
)
-
[
owns
:
Owns
]
-
> (
accnt
:
Account
)
RETURN
accnt
.
id
;
Given an account, following example query performs reverse edge traversal of Owns
edges:
GRAPH
FinGraph
MATCH
(
accnt
:
Account
{
id
:
1
}
)
< -
[
owns
:
Owns
]
-
(
person
:
Person
)
RETURN
person
.
name
;
Optimize forward edge traversal using interleaving
To improve forward edge traversal performance, interleave the edge input table into the source node input table to colocate edges with source nodes. Interleaving is a storage optimization technique in Spanner that physically colocates child table rows with their corresponding parent rows in storage. For more information about interleaving, see Schemas overview .
The following example demonstrates these best practices:
CREATE
TABLE
Person
(
id
INT64
NOT
NULL
,
name
STRING
(
MAX
),
)
PRIMARY
KEY
(
id
);
CREATE
TABLE
PersonOwnAccount
(
id
INT64
NOT
NULL
,
account_id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
)
PRIMARY
KEY
(
id
,
account_id
),
INTERLEAVE
IN
PARENT
Person
ON
DELETE
CASCADE
;
Optimize reverse edge traversal using foreign key
To efficiently traverse reverse edges, create an enforced foreign key constraint between the edge and the destination node. This enforced foreign key creates a secondary index on the edge keyed by the destination node keys. The secondary index is automatically used during query execution.
The following example demonstrates these best practices:
CREATE
TABLE
Person
(
id
INT64
NOT
NULL
,
name
STRING
(
MAX
),
)
PRIMARY
KEY
(
id
);
CREATE
TABLE
Account
(
id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
)
PRIMARY
KEY
(
id
);
CREATE
TABLE
PersonOwnAccount
(
id
INT64
NOT
NULL
,
account_id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
CONSTRAINT
FK_Account
FOREIGN
KEY
(
account_id
)
REFERENCES
Account
(
id
),
)
PRIMARY
KEY
(
id
,
account_id
),
INTERLEAVE
IN
PARENT
Person
ON
DELETE
CASCADE
;
Optimize reverse edge traversal using secondary index
If you don't want to create an enforced foreign key on the edge, for example, due to the strict data integrity it enforces, you can directly create a secondary index on the edge input table, as shown in the following example:
CREATE
TABLE
PersonOwnAccount
(
id
INT64
NOT
NULL
,
account_id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
)
PRIMARY
KEY
(
id
,
account_id
),
INTERLEAVE
IN
PARENT
Person
ON
DELETE
CASCADE
;
CREATE
INDEX
AccountOwnedByPerson
ON
PersonOwnAccount
(
account_id
),
INTERLEAVE
IN
Account
;
INTERLEAVE IN
declares a data-locality relationship between the secondary index and the table it's interleaved in ( Account
, in the example). With interleaving, rows of the AccountOwnedByPerson
secondary index are co-located with the corresponding rows of the Account
table. For more information on interleaving, see Parent-child table relationships
. For more information on interleaving indexes, see Indexes and interleaving
.
Optimize edge traversal using informational foreign keys
If your scenario has write performance bottlenecks that are caused by enforced foreign keys, such as when you have frequent updates to hub nodes that have many connected edges, then consider using informational foreign keys. Using informational foreign keys on an edge table's referencing columns helps the query optimizer drop redundant node table scans. However, because informational foreign keys don't require secondary indexes on the edge table, they don't improve the speed of lookups when a query tries to find edges using end nodes. For more information, see Comparison of foreign key types .
It's important to understand that if your application can't guarantee referential integrity, then using informational foreign keys for query optimization might lead to incorrect query results.
The following example creates a table with an informational foreign key on the account_id
column:
CREATE
TABLE
PersonOwnAccount
(
id
INT64
NOT
NULL
,
account_id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
CONSTRAINT
FK_Account
FOREIGN
KEY
(
account_id
)
REFERENCES
Account
(
id
)
NOT
ENFORCED
)
PRIMARY
KEY
(
id
,
account_id
),
INTERLEAVE
IN
PARENT
Person
ON
DELETE
CASCADE
;
If interleaving isn't an option, you can mark both edge references with informational foreign keys, such as in the following example:
CREATE
TABLE
PersonOwnAccount
(
id
INT64
NOT
NULL
,
account_id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
CONSTRAINT
FK_Person
FOREIGN
KEY
(
id
)
REFERENCES
Person
(
id
)
NOT
ENFORCED
,
CONSTRAINT
FK_Account
FOREIGN
KEY
(
account_id
)
REFERENCES
Account
(
id
)
NOT
ENFORCED
)
PRIMARY
KEY
(
id
,
account_id
);
Disallow dangling edges
A dangling edge is an edge that connects fewer than two nodes. A dangling edge can occur when a node is deleted without removing its associated edges, or when an edge is created without properly linking it to its nodes.
Disallowing dangling edges provides the following benefits:
- Enforces graph structure integrity.
- Improves query performance by avoiding the extra work to filter out edges where endpoints don't exist.
Disallow dangling edges using referential constraints
To disallow dangling edges, specify constraints on both endpoints:
- Interleave the edge input table into the source node input table. This approach ensures that the source node of an edge always exists.
- Create an enforced foreign key constraint on edges to ensure that the destination node of an edge always exists.
The following example uses interleaving and an enforced foreign key to enforce referential integrity:
CREATE
TABLE
PersonOwnAccount
(
id
INT64
NOT
NULL
,
account_id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
CONSTRAINT
FK_Account
FOREIGN
KEY
(
account_id
)
REFERENCES
Account
(
id
)
ON
DELETE
CASCADE
,
)
PRIMARY
KEY
(
id
,
account_id
),
INTERLEAVE
IN
PARENT
Person
ON
DELETE
CASCADE
;
Use ON DELETE CASCADE to automatically remove edges when deleting a node
When you use interleaving or an enforced foreign key to disallow dangling edges,
use the ON DELETE
clause to control the behavior when you want to delete a
node with edges that are still attached. For more information, see Delete cascading for interleaved tables
and Foreign key actions
.
You can use ON DELETE
in the following ways:
-
ON DELETE NO ACTION(or omitting theON DELETEclause): Deleting a node with edges will fail. -
ON DELETE CASCADE: Deleting a node automatically removes the associated edges in the same transaction.
Delete cascade for edges connecting different types of nodes
-
Delete edges when the source node is deleted. For example,
INTERLEAVE IN PARENT Person ON DELETE CASCADEdeletes all outgoingPersonOwnAccountedges from thePersonnode being deleted. For more information, see Create interleaved tables . -
Delete edges when the destination node is deleted. For example,
CONSTRAINT FK_Account FOREIGN KEY(account_id) REFERENCES Account(id) ON DELETE CASCADEdeletes all incomingPersonOwnAccountedges into theAccountnode being deleted.
Delete cascade for edges connecting the same type of nodes
When the source and destination nodes of an edge have the same type and the
edge is interleaved into the source node, you can define ON DELETE CASCADE
only for the source node or destination node (but not both nodes).
To remove dangling edges in both cases, create an enforced foreign key on the edge source node reference instead of interleaving the edge input table into the source node input table.
We recommend interleaving for optimizing forward edge traversal
.
Be sure to verify the impact on your workloads before you proceed. See the
following example, which uses AccountTransferAccount
as the edge input
table:
--Define two Foreign Keys, each on one end Node of Transfer Edge, both with ON DELETE CASCADE action:
CREATE
TABLE
AccountTransferAccount
(
id
INT64
NOT
NULL
,
to_id
INT64
NOT
NULL
,
amount
FLOAT64
,
create_time
TIMESTAMP
NOT
NULL
,
order_number
STRING
(
MAX
),
CONSTRAINT
FK_FromAccount
FOREIGN
KEY
(
id
)
REFERENCES
Account
(
id
)
ON
DELETE
CASCADE
,
CONSTRAINT
FK_ToAccount
FOREIGN
KEY
(
to_id
)
REFERENCES
Account
(
id
)
ON
DELETE
CASCADE
,
)
PRIMARY
KEY
(
id
,
to_id
);
Filter by node or edge properties with secondary indexes
Secondary indexes are essential for efficient query processing. They support quick lookups of nodes and edges based on specific property values, without having to traverse the entire graph structure. This is important when you're working with large graphs, because traversing all nodes and edges can be very inefficient.
Speed up filtering nodes by property
To speed up filtering by node properties, create secondary indexes on
properties. For example, the following query finds accounts for a given
nickname. Without a secondary index, all Account
nodes are scanned to match
the filtering criteria.
GRAPH
FinGraph
MATCH
(
acct
:
Account
)
WHERE
acct
.
nick_name
=
"abcd"
RETURN
acct
.
id
;
To speed up the query, create a secondary index on the filtered property, as shown in the following example:
CREATE
TABLE
Account
(
id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
is_blocked
BOOL
,
nick_name
STRING
(
MAX
),
)
PRIMARY
KEY
(
id
);
CREATE
INDEX
AccountByNickName
ON
Account
(
nick_name
);
Tip:Use NULL-filtered indexes for sparse properties. For more information, see Disable indexing of NULL values .
Speed up forward edge traversal with filtering on edge properties
When you traverse an edge while filtering on its properties, you can speed up the query by creating a secondary index on the edge properties and interleaving the index into the source node.
For example, the following query finds accounts owned by a given person after a certain time:
GRAPH
FinGraph
MATCH
(
person
:
Person
)
-
[
owns
:
Owns
]
-
> (
acct
:
Account
)
WHERE
person
.
id
=
1
AND
owns
.
create_time
> =
PARSE_TIMESTAMP
(
"%c"
,
"Thu Dec 25 07:30:00 2008"
)
RETURN
acct
.
id
;
By default, this query reads all edges of the specified person and then filters
those edges that satisfy the condition on create_time
.
The following example shows you how to improve query efficiency by creating
a secondary index on the edge source node reference ( id
) and the edge property
( create_time
). Interleave the index under the source node input table to
colocate the index with the source node.
CREATE
TABLE
PersonOwnAccount
(
id
INT64
NOT
NULL
,
account_id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
)
PRIMARY
KEY
(
id
,
account_id
),
INTERLEAVE
IN
PARENT
Person
ON
DELETE
CASCADE
;
CREATE
INDEX
PersonOwnAccountByCreateTime
ON
PersonOwnAccount
(
id
,
create_time
)
INTERLEAVE
IN
Person
;
Using this approach, the query can efficiently find all edges satisfying the
condition on create_time
.
Speed up reverse edge traversal with filtering on edge properties
When you traverse a reverse edge while filtering on its properties, you can speed up the query by creating a secondary index using the destination node and the edge properties for filtering.
The following example query performs reverse edge traversal with filtering on edge properties:
GRAPH
FinGraph
MATCH
(
acct
:
Account
)
< -
[
owns
:
Owns
]
-
(
person
:
Person
)
WHERE
acct
.
id
=
1
AND
owns
.
create_time
> =
PARSE_TIMESTAMP
(
"%c"
,
"Thu Dec 25 07:30:00 2008"
)
RETURN
person
.
id
;
To speed up this query using a secondary index, use one of the following options:
-
Create a secondary index on the edge destination node reference (
account_id) and the edge property (create_time), as shown in the following example:CREATE TABLE PersonOwnAccount ( id INT64 NOT NULL , account_id INT64 NOT NULL , create_time TIMESTAMP , ) PRIMARY KEY ( id , account_id ), INTERLEAVE IN PARENT Person ON DELETE CASCADE ; CREATE INDEX PersonOwnAccountByCreateTime ON PersonOwnAccount ( account_id , create_time );This approach provides better performance because the reverse edges are sorted by
account_idandcreate_time, which lets the query engine efficiently find edges foraccount_idsatisfying the condition oncreate_time. However, if different query patterns filter on different properties, then each property might require a separate index, which can add overhead. -
Create a secondary index on the edge destination node reference (
account_id) and store the edge property (create_time) in a storing column , as shown in the following example:CREATE TABLE PersonOwnAccount ( id INT64 NOT NULL , account_id INT64 NOT NULL , create_time TIMESTAMP , ) PRIMARY KEY ( id , account_id ), INTERLEAVE IN PARENT Person ON DELETE CASCADE ; CREATE INDEX PersonOwnAccountByCreateTime ON PersonOwnAccount ( account_id ) STORING ( create_time );This approach can store multiple properties; however, the query must read all edges of the destination node and then filter on edge properties.
You can combine these approaches by following these guidelines:
- Use edge properties in index columns if they are used in performance-critical queries.
- For properties used in less performance-sensitive queries, add them in the storing columns.
Model node and edge types with labels and properties
Node and edge types are commonly modeled with labels. However, you can also use
properties to model types. Consider an example in which there are many different
types of accounts, like BankAccount
, InvestmentAccount
, and RetirementAccount
. You can store the accounts in separate input tables and
model them as separate labels, or you can store the accounts in a single input
table and use a property to differentiate between the types.
Start the modeling process by modeling the types with labels. Consider using properties in the following scenarios.
Improve schema management
If your graph has many different node and edge types, managing a separate input table for each can become difficult. To make schema management easier, model the type as a property.
Model types in a property to manage frequently changing types
When you model types as labels, adding or removing types requires changes to the schema. If you perform too many schema updates in a short period of time, Spanner might throttle the processing of queued schema updates. For more information, see Limit the frequency of schema updates .
If you need to change the schema frequently, we recommend that you model the type in a property to work around limitations on the frequency of schema updates.
Speed up queries
Modeling types with properties might speed up queries when the node or edge pattern
references multiple labels. The following example query finds all instances of SavingsAccount
and InvestmentAccount
owned by a Person
, assuming account
types are modeled with labels:
GRAPH
FinGraph
MATCH
(:
Person
{
id
:
1
}
)
-
[:
Owns
]
-
> (
acct
:
SavingsAccount
|
InvestmentAccount
)
RETURN
acct
.
id
;
The acct
node pattern references two labels. If this is a
performance-critical query, consider modeling Account
using a property. This
approach might provide better query performance, as shown in the following query
example. We recommend that you benchmark both queries.
GRAPH
FinGraph
MATCH
(:
Person
{
id
:
1
}
)
-
[:
Owns
]
-
> (
acct
:
Account
)
WHERE
acct
.
type
IN
(
"Savings"
,
"Investment"
)
RETURN
acct
.
id
;
Store type in the node element key to speed up queries
To speed up queries with filtering on the node type when a node type is modeled with a property and the type doesn't change across the node lifetime, follow these steps:
- Include the property as part of the node element key.
- Add the node type in the edge input table.
- Include the node type in the edge referencing keys.
The following example applies this optimization to the Account
node and the AccountTransferAccount
edge.
CREATE
TABLE
Account
(
type
STRING
(
MAX
)
NOT
NULL
,
id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
)
PRIMARY
KEY
(
type
,
id
);
CREATE
TABLE
AccountTransferAccount
(
type
STRING
(
MAX
)
NOT
NULL
,
id
INT64
NOT
NULL
,
to_type
STRING
(
MAX
)
NOT
NULL
,
to_id
INT64
NOT
NULL
,
amount
FLOAT64
,
create_time
TIMESTAMP
NOT
NULL
,
order_number
STRING
(
MAX
),
)
PRIMARY
KEY
(
type
,
id
,
to_type
,
to_id
),
INTERLEAVE
IN
PARENT
Account
ON
DELETE
CASCADE
;
CREATE
PROPERTY
GRAPH
FinGraph
NODE
TABLES
(
Account
)
EDGE
TABLES
(
AccountTransferAccount
SOURCE
KEY
(
type
,
id
)
REFERENCES
Account
DESTINATION
KEY
(
to_type
,
to_id
)
REFERENCES
Account
);
Configure TTL on nodes and edges
Spanner Time to live (TTL) is a mechanism that supports automatic expiration and removal of data after a specified period. This is often used for data that has a limited lifespan or relevance, like session information, temporary caches, or event logs. In these cases, TTL helps to maintain database size and performance.
The following example uses TTL to delete accounts 90 days after their closure:
CREATE
TABLE
Account
(
id
INT64
NOT
NULL
,
create_time
TIMESTAMP
,
close_time
TIMESTAMP
,
)
PRIMARY
KEY
(
id
),
ROW
DELETION
POLICY
(
OLDER_THAN
(
close_time
,
INTERVAL
90
DAY
));
If the node table has a TTL and an edge table interleaved within, the interleave
must be defined with ON DELETE CASCADE
. Similarly, if
the node table has a TTL and is referenced by an edge table through a foreign
key, the foreign key must be defined with either ON DELETE CASCADE
to maintain
referential integrity, or defined as an informational foreign key to allow
existence of dangling edge.
In the following example, AccountTransferAccount
is stored for up to ten years
while an account remains active. When an account is deleted, the transfer
history is also deleted.
CREATE
TABLE
AccountTransferAccount
(
id
INT64
NOT
NULL
,
to_id
INT64
NOT
NULL
,
amount
FLOAT64
,
create_time
TIMESTAMP
NOT
NULL
,
order_number
STRING
(
MAX
),
)
PRIMARY
KEY
(
id
,
to_id
),
INTERLEAVE
IN
PARENT
Account
ON
DELETE
CASCADE
,
ROW
DELETION
POLICY
(
OLDER_THAN
(
create_time
,
INTERVAL
3650
DAY
));
Merge node and edge input tables
You can use the same input table to define more than one node and edge in your schema.
In the following example tables, the Account
nodes have a composite key (owner_id, account_id)
. There is an implicit edge definition, Person
node
with key ( id
) owns the Account
node with composite key (owner_id, account_id)
when id
equals owner_id
.
CREATE
TABLE
Person
(
id
INT64
NOT
NULL
,
)
PRIMARY
KEY
(
id
);
-- Assume each account has exactly one owner.
CREATE
TABLE
Account
(
owner_id
INT64
NOT
NULL
,
account_id
INT64
NOT
NULL
,
)
PRIMARY
KEY
(
owner_id
,
account_id
);
In this case, you can use the Account
input table to define the Account
node and the PersonOwnAccount
edge, as shown in the following schema example.
To ensure that all element table names are unique, the example gives the edge
table definition the alias Owns
.
CREATE
PROPERTY
GRAPH
FinGraph
NODE
TABLES
(
Person
,
Account
)
EDGE
TABLES
(
Account
AS
Owns
SOURCE
KEY
(
owner_id
)
REFERENCES
Person
DESTINATION
KEY
(
owner_id
,
account_id
)
REFERENCES
Account
);
