Comprehensive and Detailed In Depth Explanation:
Let's evaluate each database option against the requirements: automatic scaling, multi-region support, and low latency for a growing social media platform:
A: Bigtable: Bigtable is a highly scalable NoSQL database designed for large analytical and operational workloads with low latency. It offers excellent horizontal scalability and can be deployed across multiple regions for high availability and lower latency for a global user base. However, it's a NoSQL database and might require significant changes to your existing PostgreSQL data model and application code.
B: BigQuery: BigQuery is a fully managed, serverless data warehouse optimized for analytical queries on large datasets. It's not designed for low-latency transactional workloads that a social media platform would require for real-time user interactions. While it's globally available, its primary use case is not operational database needs.
C: Spanner: Spanner is a globally distributed, horizontally scalable relational database service with strong consistency. It offers automatic scaling, built-in multi-region and multi-continental configurations for high availability and low latency across a global user base, and supports standard SQL (with some extensions).
This makes it a strong candidate for a rapidly growing platform needing scalability, global presence, and low latency. While it's not directly PostgreSQL, it offers a relational model and tools to aid migration.
D: Cloud SQL for PostgreSQL: Cloud SQL offers managed PostgreSQL instances with automatic scaling capabilities. It supports high availability within a region and cross-region read replicas for disaster recovery and read scaling. However, its multi-region capabilities for write operations and automatic scaling across regions are more limited compared to Spanner. For a rapidly growing platform with a primarily North American user base but future global expansion in mind and a need for low latency, Spanner's architecture is better suited for true multi-region write capabilities and consistent low latency globally.
Considering the requirements for automatic scaling, multi-region support for both reads and writes with low latency for a growing user base, Spanner is the most appropriate choice.
Google Cloud Documentation References:
Cloud Spanner Overview: https://cloud.google.com/spanner/docs/overview - This document highlights Spanner's global scalability, strong consistency, and multi-region capabilities.
Cloud Bigtable Overview: https://cloud.google.com/bigtable/docs/overview - While scalable and low-latency, it's a NoSQL database, which might require significant application changes.
BigQuery Overview: https://cloud.google.com/bigquery/docs/introduction - Focuses on analytics, not low- latency transactional workloads.
Cloud SQL for PostgreSQL Overview: https://cloud.google.com/sql/docs/postgres/overview - While it offers scaling and regional HA, its multi-region write capabilities are not as robust as Spanner's.

As to mexblood1's point, CPU utilization is a recommended proxy for traffic when it comes to Cloud Spanner. See: Alerts for high CPU utilization The following table specifies our recommendations for maximum CPU usage for both single-region and multi-region instances. These numbers are to ensure that your instance has enough compute capacity to continue to serve your traffic in the event of the loss of an entire zone (for single-region instances) or an entire region (for multi-region instances). - https://cloud.google.
com/spanner/docs/cpu-utilization