This topic describes how to write a commit timestamp for each insert and update
operation that you perform with Spanner. To use this feature, set the
allow_commit_timestamp
option on a TIMESTAMP
column, then write
the timestamp as part of each transaction.
Overview
The commit timestamp, based on TrueTime
technology, is the time when a
transaction is committed in the database. The allow_commit_timestamp
column
option allows you to atomically store the commit timestamp into a column.
Using the commit timestamps stored in tables, you can determine the exact
ordering of mutations and build features like changelogs.
To insert commit timestamps in your database, complete the following steps:
Create a column with type
TIMESTAMP
with the column optionallow_commit_timestamp
set totrue
in the schema definition. For example:CREATE TABLE Performances ( ... LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true) ... ) PRIMARY KEY (...);
If you are performing inserts or updates with DML, use the
PENDING_COMMIT_TIMESTAMP
function to write the commit timestamp.If you are performing inserts or updates with mutations, use the placeholder string
spanner.commit_timestamp()
on insertions or updates to your commit timestamp column. You can also use the commit timestamp constant provided by the client library. For example, this constant in the Java client isValue.COMMIT_TIMESTAMP
.
When Spanner commits the transaction using these placeholders as column
values, the actual commit timestamp is written to the specified column (For
example: the LastUpdateTime
column). You could then use this column value
to create a history of updates to the table.
Commit timestamp values are not guaranteed to be unique. Transactions that write to non-overlapping sets of fields might have the same timestamp. Transactions that write to overlapping sets of fields have unique timestamps.
Spanner commit timestamps have microsecond granularity,
and they are converted to nanoseconds when stored in TIMESTAMP
columns.
Create and delete a commit timestamp column
Use the allow_commit_timestamp
column option to add and remove support for
commit timestamps:
- When creating a new table to specify that a column supports commit timestamps.
- When altering an existing table:
- to add a new column supporting commit timestamps,
- to alter an existing
TIMESTAMP
column to support commit timestamps, - to alter an existing
TIMESTAMP
column to remove commit timestamp support
Keys and indexes
You can use a commit timestamp column as a primary key column or as a non-key
column. Primary keys can be defined as ASC
or DESC
.
ASC
(default) - Ascending keys are ideal for answering queries from a specific time forward.DESC
- Descending keys keep the latest rows at the top of the table. They provide quick access to the latest records.
The allow_commit_timestamp
option must be consistent across the primary
keys of parent and child tables. If the option is not consistent across
primary keys, Spanner returns an error. The only time the option
can be inconsistent is when you are creating or updating the schema.
Using commit timestamps under the following scenarios creates hotspots which reduce data performance:
Commit timestamp column as the first part of the primary key of a table:
CREATE TABLE Users ( LastAccess TIMESTAMP NOT NULL, UserId INT64 NOT NULL, ... ) PRIMARY KEY (LastAccess, UserId);
The first part of the primary key of a secondary index:
CREATE INDEX UsersByLastAccess ON Users(LastAccess)
or
CREATE INDEX UsersByLastAccessAndName ON Users(LastAccess, FirstName)
Hotspots reduce data performance, even with low write rates. There is no performance overhead if commit timestamps are enabled on non-key columns that are not indexed.
Create a commit timestamp column
The following DDL creates a table with a column that supports commit timestamps.
CREATE TABLE Performances (
SingerId INT64 NOT NULL,
VenueId INT64 NOT NULL,
EventDate Date,
Revenue INT64,
LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE
Adding the option changes the timestamp column as follows:
- You can use the
spanner.commit_timestamp()
placeholder string (or a constant provided by the client library) for inserts and updates. - The column can only contain values in the past. For more information, see Providing your own value for the timestamp.
The option allow_commit_timestamp
is case sensitive.
Add a commit timestamp column to an existing table
To add a commit timestamp column to an existing table, use the ALTER TABLE
statement. For example to add a LastUpdateTime
column to the Performances
table, use the following statement:
ALTER TABLE Performances ADD COLUMN LastUpdateTime TIMESTAMP
NOT NULL OPTIONS (allow_commit_timestamp=true)
Convert a timestamp column to a commit timestamp column
You can convert an existing timestamp column into a commit timestamp column, but doing so requires Spanner to validate that the existing timestamp values are in the past. For example:
ALTER TABLE Performances ALTER COLUMN LastUpdateTime
SET OPTIONS (allow_commit_timestamp=true)
You cannot change the data type or NULL
annotation of a column in an
ALTER TABLE
statement that includes SET OPTIONS
. For details, see
Data Definition Language.
Remove the commit timestamp option
If you want to remove commit timestamp support from a column, use the option
allow_commit_timestamp=null
in an ALTER TABLE
statement. The commit timestamp behavior is removed, but the column is still
a timestamp. Changing the option does not alter any other characteristics of the
column, such as type or nullability (NOT NULL
). For example:
ALTER TABLE Performances ALTER COLUMN LastUpdateTime
SET OPTIONS (allow_commit_timestamp=null)
Write a commit timestamp using a DML statement
You use the PENDING_COMMIT_TIMESTAMP
function to write the commit
timestamp in a DML statement. Spanner selects the commit timestamp when the transaction
commits.
The following DML statement updates the LastUpdateTime
column in the
Performances
table with the commit timestamp:
UPDATE Performances SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP()
WHERE SingerId=1 AND VenueId=2 AND EventDate="2015-10-21"
The following code example uses the
PENDING_COMMIT_TIMESTAMP
function to write the commit timestamp in the LastUpdateTime
column.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Ruby
Commit timestamps can only be written to columns annotated with the
allow_commit_timestamp=true
option.
If you have mutations on rows in multiple tables, you must specify
spanner.commit_timestamp()
(or the client library
constant) for the commit timestamp column in each table.
Query a commit timestamp column
The following example queries the commit timestamp column of the table.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Provide your own value for the commit timestamp column
You can provide your own value for the commit timestamp column, instead of
passing spanner.commit_timestamp()
(or client library constant) as the
column value. The value must be a timestamp in the past. This restriction
ensures that writing timestamps is an inexpensive and fast operation. The server
returns a FailedPrecondition
error if a future timestamp is specified.
Create a changelog
Suppose that you want to create a changelog of every mutation that happens to a table and then use that changelog for auditing. An example would be a table that stores the history of changes to word processing documents. The commit timestamp makes creating the changelog easier, because the timestamps can enforce ordering of the changelog entries. You could build a changelog that stores the history of changes to a given document using a schema like the following example:
CREATE TABLE Documents (
UserId INT64 NOT NULL,
DocumentId INT64 NOT NULL,
Contents STRING(MAX) NOT NULL,
) PRIMARY KEY (UserId, DocumentId);
CREATE TABLE DocumentHistory (
UserId INT64 NOT NULL,
DocumentId INT64 NOT NULL,
Ts TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
Delta STRING(MAX),
) PRIMARY KEY (UserId, DocumentId, Ts),
INTERLEAVE IN PARENT Documents ON DELETE NO ACTION;
To create a changelog, insert a new row in DocumentHistory
in the same
transaction in which you insert or update a row in Document
. In the insertion
of the new row in DocumentHistory
, use the placeholder
spanner.commit_timestamp()
(or client library constant) to tell
Spanner to write the commit timestamp into column Ts
. Interleaving
the DocumentsHistory
table with the Documents
table will allow for data
locality and more efficient inserts and updates. However, it also adds the
constraint that the parent and child rows
must be deleted together. To keep the rows in DocumentHistory
after rows
in Documents
are deleted, do not interleave the tables.
Optimize recent-data queries with commit timestamps
Commit timestamps enable a Spanner optimization that can reduce query I/O when retrieving data written after a particular time.
To activate this optimization, a query's WHERE
clause must include a
comparison between a table's commit timestamp column and a specific time
that you provide, with the following attributes:
Provide the specific time as a constant expression: a literal, a parameter, or a function whose own arguments evaluate to constants.
Compare whether the commit timestamp is more recent than the given time, through either the
>
or>=
operators.Optionally, add further restrictions to the
WHERE
clause withAND
. Extending the clause withOR
disqualifies the query from this optimization.
For example, consider the following Performances
table, which includes
a commit timestamp column:
CREATE TABLE Performances (
SingerId INT64 NOT NULL,
VenueId INT64 NOT NULL,
EventDate DATE,
Revenue INT64,
LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate);
This query benefits from the commit-timestamp optimization described earlier, because it has a greater-than-or-equal-to comparison between the table's commit timestamp column and a constant expression—in this case, a literal:
SELECT * FROM Performances WHERE LastUpdateTime >= "2022-05-01";
The following query also qualifies for the optimization, since it has a greater-than comparison between the commit timestamp and a function whose arguments all evaluate to constants during the query's execution:
SELECT * FROM Performances
WHERE LastUpdateTime > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);
What's next
- Use commit timestamps to create a change log with Go.