Large Schema Creation Speed

Hello all,

Thanks for the continued support of the Spanner emulator. We use the emulator in our test suite fairly extensively.

Our tests are set up to use a shared emulator instance for all tests, and distinct databases are created for the life of each test. Some of the databases have a high number of tables (~350) and we observe creating them for each test is slow.

We instrumented the emulator and noticed there might be an opportunity to optimize `SchemaGraphEditor::IsOriginalNode` by using a Set for lookups instead of traversing the nodes:


```cpp
// 845fe2e2
bool SchemaGraphEditor::IsOriginalNode(const SchemaNode* node) const {
for (const auto* schema_node : original_graph_->GetSchemaNodes()) {
if (schema_node == node) {
return true;
}
}
return false;
}

```

```diff
diff --git a/backend/schema/graph/schema_graph_editor.cc b/backend/schema/graph/schema_graph_editor.cc
index 0f95ef28..c94f6608 100644
--- a/backend/schema/graph/schema_graph_editor.cc
+++ b/backend/schema/graph/schema_graph_editor.cc
@@ -133,12 +133,7 @@ absl::Status SchemaGraphEditor::AddNode(
}

bool SchemaGraphEditor::IsOriginalNode(const SchemaNode* node) const {
- for (const auto* schema_node : original_graph_->GetSchemaNodes()) {
- if (schema_node == node) {
- return true;
- }
- }
- return false;
+ return original_nodes_.contains(node);
}

absl::StatusOr>
diff --git a/backend/schema/graph/schema_graph_editor.h b/backend/schema/graph/schema_graph_editor.h
index 397e13aa..a867b432 100644
--- a/backend/schema/graph/schema_graph_editor.h
+++ b/backend/schema/graph/schema_graph_editor.h
@@ -67,6 +67,10 @@ class SchemaGraphEditor {
context_(context),
cloned_pool_(std::make_unique()) {
context_->set_added_nodes(&added_nodes_);
+ original_nodes_.reserve(original_graph_->GetSchemaNodes().size());
+ for (const SchemaNode* node : original_graph_->GetSchemaNodes()) {
+ original_nodes_.insert(node);
+ }
}

template
@@ -277,6 +281,9 @@ class SchemaGraphEditor {
// The original graph.
const SchemaGraph* original_graph_ = nullptr;

+ absl::flat_hash_set original_nodes_;
+
// Validation context passed to Validate() and ValidateUpdate() methods for
// SchemaNode.
// This is also being used in SchemaUpdaterImpl. This is kept as a pointer
```

The following script approximates the behavior we see in our test suite:



go.mod

```go
module go-repro

go 1.23.0

require (
cloud.google.com/go/spanner v1.84.1
google.golang.org/api v0.247.0
google.golang.org/grpc v1.74.2
)

require (
cel.dev/expr v0.24.0 // indirect
cloud.google.com/go v0.121.4 // indirect
cloud.google.com/go/auth v0.16.4 // indirect
cloud.google.com/go/auth/oauth2adapt v0.2.8 // indirect
cloud.google.com/go/compute/metadata v0.8.0 // indirect
cloud.google.com/go/iam v1.5.2 // indirect
cloud.google.com/go/longrunning v0.6.7 // indirect
cloud.google.com/go/monitoring v1.24.2 // indirect
github.com/GoogleCloudPlatform/grpc-gcp-go/grpcgcp v1.5.3 // indirect
github.com/GoogleCloudPlatform/opentelemetry-operations-go/detectors/gcp v1.27.0 // indirect
github.com/cespare/xxhash/v2 v2.3.0 // indirect
github.com/cncf/xds/go v0.0.0-20250501225837-2ac532fd4443 // indirect
github.com/envoyproxy/go-control-plane/envoy v1.32.4 // indirect
github.com/envoyproxy/protoc-gen-validate v1.2.1 // indirect
github.com/felixge/httpsnoop v1.0.4 // indirect
github.com/go-jose/go-jose/v4 v4.0.5 // indirect
github.com/go-logr/logr v1.4.3 // indirect
github.com/go-logr/stdr v1.2.2 // indirect
github.com/golang/groupcache v0.0.0-20210331224755-41bb18bfe9da // indirect
github.com/google/s2a-go v0.1.9 // indirect
github.com/google/uuid v1.6.0 // indirect
github.com/googleapis/enterprise-certificate-proxy v0.3.6 // indirect
github.com/googleapis/gax-go/v2 v2.15.0 // indirect
github.com/planetscale/vtprotobuf v0.6.1-0.20240319094008-0393e58bdf10 // indirect
github.com/spiffe/go-spiffe/v2 v2.5.0 // indirect
github.com/zeebo/errs v1.4.0 // indirect
go.opencensus.io v0.24.0 // indirect
go.opentelemetry.io/auto/sdk v1.1.0 // indirect
go.opentelemetry.io/contrib/detectors/gcp v1.36.0 // indirect
go.opentelemetry.io/contrib/instrumentation/google.golang.org/grpc/otelgrpc v0.61.0 // indirect
go.opentelemetry.io/contrib/instrumentation/net/http/otelhttp v0.61.0 // indirect
go.opentelemetry.io/otel v1.36.0 // indirect
go.opentelemetry.io/otel/metric v1.36.0 // indirect
go.opentelemetry.io/otel/sdk v1.36.0 // indirect
go.opentelemetry.io/otel/sdk/metric v1.36.0 // indirect
go.opentelemetry.io/otel/trace v1.36.0 // indirect
golang.org/x/crypto v0.41.0 // indirect
golang.org/x/net v0.43.0 // indirect
golang.org/x/oauth2 v0.30.0 // indirect
golang.org/x/sync v0.16.0 // indirect
golang.org/x/sys v0.35.0 // indirect
golang.org/x/text v0.28.0 // indirect
golang.org/x/time v0.12.0 // indirect
google.golang.org/genproto v0.0.0-20250603155806-513f23925822 // indirect
google.golang.org/genproto/googleapis/api v0.0.0-20250811230008-5f3141c8851a // indirect
google.golang.org/genproto/googleapis/rpc v0.0.0-20250804133106-a7a43d27e69b // indirect
google.golang.org/protobuf v1.36.7 // indirect
)
```





main.go


```go
package main

import (
"context"
"crypto/rand"
"encoding/hex"
"flag"
"fmt"
"log"
"os"
"time"

"cloud.google.com/go/spanner"
database "cloud.google.com/go/spanner/admin/database/apiv1"
databasepb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
"google.golang.org/api/option"
"google.golang.org/grpc"
"google.golang.org/grpc/credentials/insecure"
)

const (
tableCount = 400
)

func main() {
host := flag.String("emulator_host", "127.0.0.1", "Spanner emulator host")
grpcPort := flag.Int("grpc_port", 9010, "Spanner emulator gRPC port")
project := flag.String("project", "local-dev", "Spanner project ID")
instance := flag.String("instance", "local-test", "Spanner instance ID")
iterations := flag.Int("iterations", 1, "Number of times to create and drop the database")
quiet := flag.Bool("quiet", false, "Suppress informational logs")
flag.Parse()

endpoint := fmt.Sprintf("%s:%d", *host, *grpcPort)
if err := os.Setenv("SPANNER_EMULATOR_HOST", endpoint); err != nil {
log.Fatalf("failed to set SPANNER_EMULATOR_HOST: %v", err)
}

opts := []option.ClientOption{
option.WithEndpoint(endpoint),
option.WithGRPCDialOption(grpc.WithTransportCredentials(insecure.NewCredentials())),
option.WithoutAuthentication(),
}

baseCtx := context.Background()
adminClient, err := database.NewDatabaseAdminClient(baseCtx, opts...)
if err != nil {
log.Fatalf("failed to create database admin client: %v", err)
}
defer func() {
if err := adminClient.Close(); err != nil {
log.Printf("warning: failed to close database admin client: %v", err)
}
}()

info := log.Printf
if *quiet {
info = func(string, ...any) {}
}

iterationCount := max(*iterations, 0)

for range iterationCount {
err := createDropDB(baseCtx, adminClient, opts, *project, *instance, tableCount, info)
if err != nil {
log.Fatalf("create failed: %v", err)
return
}
}

info("Created and dropped %d DBs each with %d tables", iterationCount, tableCount)
}

func createDropDB(ctx context.Context, adminClient *database.DatabaseAdminClient, clientOpts []option.ClientOption, project, instance string, tableCount int, info func(string, ...any)) error {
parent := fmt.Sprintf("projects/%s/instances/%s", project, instance)
dbID, err := buildDatabaseID()
if err != nil {
return fmt.Errorf("build database id: %w", err)
}
fullName := fmt.Sprintf("%s/databases/%s", parent, dbID)

info("Creating database %s", dbID)
op, err := adminClient.CreateDatabase(ctx, &databasepb.CreateDatabaseRequest{
Parent: parent,
CreateStatement: fmt.Sprintf("CREATE DATABASE `%s`", dbID),
ExtraStatements: schemaStatements(tableCount),
})
if err != nil {
return fmt.Errorf("create database: %w", err)
}
if _, err := op.Wait(ctx); err != nil {
return fmt.Errorf("wait for database creation: %w", err)
}

client, err := spanner.NewClient(ctx, fullName, clientOpts...)
if err != nil {
return fmt.Errorf("create data client: %w", err)
}
defer client.Close()

defer func() {
dropCtx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
defer cancel()
if err := adminClient.DropDatabase(dropCtx, &databasepb.DropDatabaseRequest{Database: fullName}); err != nil {
log.Printf("warning: failed to drop database %s: %v", dbID, err)
}
}()

info("Database %s workload complete", dbID)
return nil
}

func schemaStatements(tableCount int) []string {
s := make([]string, tableCount)
for i := range tableCount {
s[i] = fmt.Sprintf(
`CREATE TABLE Accounts%d (
AccountId STRING(36) NOT NULL,
CreatedAt TIMESTAMP NOT NULL,
Status STRING(16) NOT NULL
) PRIMARY KEY (AccountId)`, i,
)
}
return s
}

func buildDatabaseID() (string, error) {
const (
prefix = "perf"
bytesLength = 6
)

b := make([]byte, bytesLength)
if _, err := rand.Read(b); err != nil {
return "", err
}

return fmt.Sprintf("%s_%s", prefix, hex.EncodeToString(b)), nil
}
```





Running this script with the official docker image:

```shell
$ docker run -d --rm --name=spanner-emulator -p 9010:9010 -p 9020:9020 gcr.io/cloud-spanner-emulator/emulator:1.5.41 > /dev/null
$ gcloud config configurations activate $PROJECT && gcloud spanner instances create local-test --config=emulator-config --description="local emulator" --nodes=1
Activated [$PROJECT].
Creating instance...done.
$ for in in {1..10}; do time go run . --quiet; done
go run . --quiet 0.26s user 0.47s system 58% cpu 1.238 total
go run . --quiet 0.28s user 2.17s system 227% cpu 1.080 total
go run . --quiet 0.30s user 2.11s system 232% cpu 1.035 total
go run . --quiet 0.28s user 2.24s system 234% cpu 1.075 total
go run . --quiet 0.29s user 2.31s system 247% cpu 1.055 total
go run . --quiet 0.29s user 2.09s system 228% cpu 1.042 total
go run . --quiet 0.29s user 2.07s system 229% cpu 1.028 total
go run . --quiet 0.29s user 2.07s system 227% cpu 1.039 total
go run . --quiet 0.29s user 2.30s system 247% cpu 1.043 total
go run . --quiet 0.27s user 2.23s system 240% cpu 1.040 total
```

And a comparison of my aarch64 macOS builds:

Original IsOriginalNode
Modified IsOriginalNode

```shell
$ for in in {1..10}; do time go run . --quiet; done
go run . --quiet 0.25s user 0.41s system 53% cpu 1.243 total
go run . --quiet 0.28s user 2.23s system 237% cpu 1.053 total
go run . --quiet 0.29s user 2.26s system 239% cpu 1.064 total
go run . --quiet 0.28s user 2.13s system 230% cpu 1.047 total
go run . --quiet 0.30s user 2.11s system 227% cpu 1.058 total
go run . --quiet 0.30s user 2.29s system 244% cpu 1.056 total
go run . --quiet 0.28s user 2.25s system 242% cpu 1.048 total
go run . --quiet 0.27s user 2.36s system 248% cpu 1.059 total
go run . --quiet 0.28s user 2.16s system 232% cpu 1.048 total
go run . --quiet 0.28s user 2.04s system 223% cpu 1.041 total
```

```shell
$ for in in {1..10}; do time go run . --quiet; done
go run . --quiet 0.28s user 2.11s system 428% cpu 0.558 total
go run . --quiet 0.29s user 2.25s system 445% cpu 0.569 total
go run . --quiet 0.29s user 2.23s system 441% cpu 0.571 total
go run . --quiet 0.30s user 2.16s system 436% cpu 0.564 total
go run . --quiet 0.29s user 1.95s system 405% cpu 0.552 total
go run . --quiet 0.28s user 2.11s system 431% cpu 0.553 total
go run . --quiet 0.28s user 2.04s system 416% cpu 0.557 total
go run . --quiet 0.27s user 2.00s system 415% cpu 0.545 total
go run . --quiet 0.28s user 1.93s system 401% cpu 0.551 total
go run . --quiet 0.27s user 2.08s system 426% cpu 0.550 total
```

I’m not sure I fully appreciate the potential effects of this change, so if there’s more I can do to validate it I would be happy to do so.

Is there some way the emulator could be updated to improve this use case? We are looking to speed up our test suite so any improvements to the "CreateDatabase with ExtraStatements" path are appealing. Happy to provide more information about our setup if needed too.

Thanks!