INSERTs with default values are too slow

[halnique]

I noticed that when inserting into a column with a default value set, omitting the column's value in the INSERT statement (so that the default value is used) results in extremely slow performance. Compared to explicitly specifying the column's value during INSERT, it seems to be more than 10 times slower, although the exact slowdown is not consistent.

# table
CREATE TABLE Singers (
  SingerId STRING(36),
  Name STRING(MAX) default (\"foo\"),
  Age INT64 default (20)
) PRIMARY KEY (SingerId)

# insert pattern A (slow)
id, _ := uuid.NewRandom()
spanner.Insert("Singers", []string{"SingerId"}, []interface{}{id.String()})

# insert pattern B (fast)
id, _ := uuid.NewRandom()
spanner.Insert("Singers", []string{"SingerId", "Name", "Age"}, []interface{}{id.String(), "foo", 20})

Click to expand sample code

package main

import (
	"context"
	"fmt"
	"log"
	"os"
	"strings"
	"time"

	"cloud.google.com/go/spanner"
	database "cloud.google.com/go/spanner/admin/database/apiv1"
	"cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
	instance "cloud.google.com/go/spanner/admin/instance/apiv1"
	"cloud.google.com/go/spanner/admin/instance/apiv1/instancepb"
	"github.com/google/uuid"
	_ "github.com/googleapis/go-sql-spanner"
	"google.golang.org/api/option"
	"google.golang.org/grpc"
	"google.golang.org/grpc/credentials/insecure"
)

func main() {
	ctx := context.Background()

	setup(ctx)

	client, _ := spanner.NewClient(ctx, "projects/emulator-project/instances/emulator-instance/databases/emulator-database", option.WithoutAuthentication())
	defer client.Close()

	times := 10
	num := 10000

	var start time.Time

	// run with default values
	fmt.Println("run with default values")
	for i := 0; i < times; i++ {
		var m []*spanner.Mutation
		for i := 0; i < num; i++ {
			id, _ := uuid.NewRandom()
			m = append(m, spanner.Insert("Singers", []string{"SingerId"}, []interface{}{id.String()}))
		}

		start = time.Now()
		client.Apply(ctx, m)
		fmt.Printf("inserts: %d, times: %d, elapsed: %v\n", num, i, time.Since(start))
	}

	// run with specified values
	fmt.Println("run with specified values")
	for i := 0; i < times; i++ {
		var m []*spanner.Mutation
		for i := 0; i < num; i++ {
			id, _ := uuid.NewRandom()
			m = append(m, spanner.Insert("Singers", []string{"SingerId", "Name", "Age"}, []interface{}{id.String(), "foo", 20}))
		}
		start = time.Now()
		client.Apply(ctx, m)
		fmt.Printf("inserts: %d, times: %d, elapsed: %v\n", num, i, time.Since(start))
	}
}

func setup(ctx context.Context) {
	emulatorHost, _ := os.LookupEnv("SPANNER_EMULATOR_HOST")

	// create instance admin client
	instanceAdminClient, _ := instance.NewInstanceAdminClient(ctx,
		option.WithEndpoint(emulatorHost),
		option.WithGRPCDialOption(grpc.WithTransportCredentials(insecure.NewCredentials())))
	defer instanceAdminClient.Close()

	// create emulator instance
	instanceReq := &instancepb.CreateInstanceRequest{
		Parent:     "projects/emulator-project",
		InstanceId: "emulator-instance",
		Instance: &instancepb.Instance{
			Config:      "emulator-config",
			DisplayName: "Emulator Instance",
			NodeCount:   1,
		},
	}
	_, err := instanceAdminClient.CreateInstance(ctx, instanceReq)
	if err != nil {
		if strings.Contains(err.Error(), "already exists") {
			log.Println("instance already exists")
		} else {
			panic(err)
		}
	}

	// create database admin client
	databaseAdminClient, _ := database.NewDatabaseAdminClient(ctx,
		option.WithEndpoint(emulatorHost),
		option.WithGRPCDialOption(grpc.WithTransportCredentials(insecure.NewCredentials())))
	defer databaseAdminClient.Close()

	// create database
	dbReq := &databasepb.CreateDatabaseRequest{
		Parent:          "projects/emulator-project/instances/emulator-instance",
		CreateStatement: "CREATE DATABASE `emulator-database`",
	}
	_, err = databaseAdminClient.CreateDatabase(ctx, dbReq)
	if err != nil {
		if strings.Contains(err.Error(), "already exists") {
			log.Println("database already exists")
		} else {
			panic(err)
		}
	}

	// cleanup old tables
	dropTableStmt := "DROP TABLE Singers"
	op, err := databaseAdminClient.UpdateDatabaseDdl(ctx, &databasepb.UpdateDatabaseDdlRequest{
		Database:   "projects/emulator-project/instances/emulator-instance/databases/emulator-database",
		Statements: []string{dropTableStmt},
	})
	if err != nil {
		if strings.Contains(err.Error(), "not found") {
			log.Println("table not found")
		} else {
			panic(err)
		}
	}
	if op != nil {
		op.Wait(ctx)
	}

	// create tables DDL
	createTableStmt := "CREATE TABLE Singers (SingerId STRING(MAX), Name STRING(MAX) default (\"foo\"), Age INT64 default (20)) PRIMARY KEY (SingerId)"
	op, _ = databaseAdminClient.UpdateDatabaseDdl(ctx, &databasepb.UpdateDatabaseDdlRequest{
		Database:   "projects/emulator-project/instances/emulator-instance/databases/emulator-database",
		Statements: []string{createTableStmt},
	})
	op.Wait(ctx)
}

logs

run with default values
inserts: 10000, times: 0, elapsed: 11.975785053s
inserts: 10000, times: 1, elapsed: 12.343125291s
inserts: 10000, times: 2, elapsed: 12.261675672s
inserts: 10000, times: 3, elapsed: 11.776418881s
inserts: 10000, times: 4, elapsed: 14.155175691s
inserts: 10000, times: 5, elapsed: 11.782318013s
inserts: 10000, times: 6, elapsed: 13.715314927s
inserts: 10000, times: 7, elapsed: 16.505922306s
inserts: 10000, times: 8, elapsed: 11.992866841s
inserts: 10000, times: 9, elapsed: 15.93990089s
run with specified values
inserts: 10000, times: 0, elapsed: 172.457302ms
inserts: 10000, times: 1, elapsed: 193.484206ms
inserts: 10000, times: 2, elapsed: 173.783946ms
inserts: 10000, times: 3, elapsed: 172.511135ms
inserts: 10000, times: 4, elapsed: 189.831526ms
inserts: 10000, times: 5, elapsed: 218.340877ms
inserts: 10000, times: 6, elapsed: 173.787363ms
inserts: 10000, times: 7, elapsed: 183.582263ms
inserts: 10000, times: 8, elapsed: 182.347453ms
inserts: 10000, times: 9, elapsed: 186.916689ms

This issue has been observed in version 1.5.24.

Request

Is it possible to achieve the same level of performance as when specifying the default value explicitly?
I understand that since this is an emulator, it's important that it satisfies the interface, and performance is a lower priority. However, because it's also used as a test database in some cases, it would be great if the performance could be improved.