Questions about migrating from PostgeSQL to Spanner and how geo-partitioning work.

[bubiche]

I'm looking to migrate a PostgreSQL database to Spanner to make use of the geo-partitioning feature but there are a few things I'm unsure about. Can someone help me with these questions please:

1/ How should I handle "special" data types like Postgis `geography`? What will the migration tool (https://github.com/GoogleCloudPlatform/spanner-migration-tool) turn them into?

2/ Can I move a row between geo-partitions?

3/ Does full text search work with geo-partitions?

4/ If I have a query like `SELECT * FROM table WHERE some_id = 1` and the `some_id` column is indexed. Does Spanner have to go through all geo-partitions to find it? Or it has a way to know which partition to look for?

[ms4446]

1. Handling PostGIS geography Data Types

Google Cloud Spanner does not natively support the PostGIS geography data type. When using the Spanner Migration Tool to migrate your PostgreSQL database, the geography columns will typically be converted to a STRING or BYTES data type in Spanner. To handle this:

• Convert geography Data: Before migration, convert your geography data into a textual format like Well-Known Text (WKT) or GeoJSON. This way, the data can be stored as a STRING in Spanner.

• Store as Binary: Alternatively, you can convert the data into Well-Known Binary (WKB) format and store it as a BYTES type.

• Application Logic: Since Spanner doesn't support spatial functions, you'll need to handle spatial operations at the application level or integrate with an external service for spatial querying. Alternatively, consider using Google BigQuery with GIS extensions for spatial analysis.

2. Moving Rows Between Geo-Partitions

In Cloud Spanner, you can move a row between geo-partitions by updating the column(s) used for partitioning.

• Update Partitioning Column(s): Instead of a delete and insert process, you simply need to update the partitioning column (e.g., region). Spanner automatically reshuffles the data between partitions without requiring manual deletion and reinsertion.

• Consider Performance Implications: Moving data between partitions can temporarily impact performance due to data reshuffling. Minimize frequent moves and plan them during low-traffic periods, if possible.

3. Full-Text Search with Geo-Partitions

Cloud Spanner supports Full-Text Search (FTS) natively, but this feature is only available through the native Spanner interface and does not extend to the PostgreSQL interface. To implement FTS with geo-partitioned data:

• Use Spanner’s Built-in FTS: You can perform full-text searches across your geo-partitioned data using Spanner’s full-text search capabilities, ensuring consistency with your partitioned data model.

• Manage Data Locality: While geo-partitioning doesn't prevent full-text search, consider the geographic distribution of your data to optimize search performance and comply with data residency regulations. Spanner handles partition-aware searching efficiently.

4. Querying Indexed Data Across Geo-Partitions

If you have a query like SELECT * FROM table WHERE some_id = 1 and some_id is indexed:

• Global Indexes: If some_id is indexed using a global index, Spanner can efficiently locate the data without scanning all geo-partitions. Global indexes span all partitions and are ideal for queries that need to access data across multiple partitions. However, global indexes introduce more write overhead compared to local indexes.

• Local Indexes: If the index on some_id is local (interleaved with the base table), it only indexes data within each partition. In this case, Spanner may need to search multiple partitions if some_id isn't part of the partition key, which could impact query performance.

Optimizing Query Performance:

• Include Partitioning Columns in Indexes: To improve query efficiency, include the partitioning column(s) in your indexes. This allows Spanner to quickly narrow down the partition where the data resides.

• Use Query Parameters: Where possible, include partitioning information in your queries to guide Spanner to the correct partition, thus avoiding unnecessary cross-partition scans.