Migrating to Cloud Spanner ? Here’s what you need to know.
Cloud Spanner under the right circumstances could be a great choice but when chosen without proper investigation, a lot of things can go wrong making it a terrible choice.
Like other migrations, one cannot lift and shift databases on to Spanner.
An organization that uses traditional RDBMS will have certain features such as:
- Sequences
- Foreign Keys
- UDF’s (User defined functions)
- Triggers
- Stored procedures
- ACLs
and a few others implemented already. Migrating these traditional databases to Cloud Spanner requires us to understand whether Spanner offers same features and If not what are the equivalents.
Let us look at a few examples:
Sequences
As the name says, Sequences are unique integer values assigned to each row in a table to ensure uniqueness. Cloud Spanner do not support Sequences like RDBMS.
This is because Cloud Spanner stores the data alphanumerically by primary key and then the table is split up across different machines.
So when you are ordering your table using a Sequence as primary key, data always go to the same split and increases the load on one split thus causing Hotspots.
Do not choose a column whose value monotonically increases or decreases as the first key part for a high write rate table.
Foreign Keys
Cloud Spanner support foreign keys like the RDBMS to define relationships between two tables. Alternatively, Cloud Spanner’s table interleaving also helps define relationships between two tables too.
We may either choose interleaving or foreign keys but not both.
Are interleaving and foreign keys same? The answer is No.
- Table Interleaving only supports one parent while Foreign Keys support any number of parents.
- Interleaving happens only by using a primary key but while defining foreign keys user may or may not choose to use primary keys.
- When interleaving, data of parent and child table co-locate which is not the case with a foreign key.
- The issue with interleaving is, to remove it you have to delete the whole child table while this is not the case with foreign keys.
Triggers, Stored Procedures & UDF’s
Cloud Spanner does not support triggers, stored procedures and UDF’s.
If your production database has a lot of these then you might need to rethink about moving to Cloud Spanner.
ACL (Access Control Lists)
Unlike RDBMS where ACLs can even be applied at table or row level, Spanner only supports access control at database level.
