Creating Spanner Tables and Indexes seem to take a long time

Question

We are prototyping setting up separate databases for each developer on the same instance of a GCP Spanner 1 node instance and have noticed that tables take ~20 seconds to create and indexes take 60 to 120 seconds to create. Is this normal? Is there anything that can be done to improve these timings? We are sequentially creating these objects in a batch utilizing the GCP Console tools and have used Squirrel. GCP Console seems to be a little faster but would be difficult to automate the creation process.

Big Picture: There are approximately 10+ databases with roughly 70+ tables with over 60 indexes for each developer (15+ developers) that will need to be setup. In a relational world this took several seconds. With Spanner, this is taking hours to setup for each developer. Any suggestions on how to improve this would be greatly appreciated.

Answer 1

(I work on the Cloud Spanner team and will try to add some suggestions)

As was mentioned above, batching is useful here. In particular, I would highly recommend creating secondary indexes in the same batch as their parent tables. If you don't do this, then Cloud Spanner has to go through a backfill process to ensure that the new index is consistent with the base table, but it can short-circuit this if base table and index are created concurrently. Note that this is different than the advice we typically give for bulk-loading large datasets, because in that case it's typically faster to bulk-load the data before creating the secondary indexes.

Also, it may be faster to create your tables and indexes at database creation time. In the API, you can do this by creating the tables and indexes in the extraStatements field of the createDatabase operation. You can also do it directly from the user interface or from gcloud.

So in summary for you, I would recommend:

• For each separate developer database, create all tables and indexes at the same time as you create the database.

It still won't be instantaneous, but it's your best option. Using this approach, I just tried to create a new database with 100 tables and 2 secondary indexes per table, and I was able to see much lower latencies than you were reporting.