Cloud SQL vs Firestore in GCP: Which Database Should You Choose?

Simple explanation

The key trade-off: Cloud SQL gives you powerful querying across structured data. Firestore gives you real-time sync and effortless scaling. You give up joins and complex queries in Firestore. You give up real-time push and automatic horizontal scaling in Cloud SQL.

How each one works

Cloud SQL

Cloud SQL runs a fully managed MySQL, PostgreSQL, or SQL Server instance on a virtual machine you choose. You pick the CPU, memory, and storage. Google handles patching, replication, backups, and failover. Your application connects to Cloud SQL using standard database drivers, just like it would connect to any MySQL or PostgreSQL server.

Data lives in tables with defined columns and types. You write SQL to insert, update, query, and delete data. Connections are secured through Cloud SQL connectors, the Auth Proxy, or private IP networking depending on where your application runs. For Cloud Run, the built-in Cloud SQL connector is the simplest path. For GKE, the Cloud SQL Auth Proxy sidecar or private IP works well. For external apps, the Auth Proxy handles IAM authentication and encryption.

Cloud SQL scales vertically. You resize the instance when you need more capacity. Read replicas add read throughput. Write throughput is limited to the primary instance, which is sufficient for the vast majority of web applications.

Firestore

Firestore is serverless. There is no instance to provision. You create a database, define security rules, and start reading and writing documents. Google handles all infrastructure, scaling, and replication behind the scenes.

Data is organised into collections and documents. A “users” collection contains user documents, each identified by a unique ID. Documents hold fields (strings, numbers, arrays, nested objects) and can have subcollections for hierarchical data. The data model is flexible. Documents in the same collection do not need identical fields.

Queries in Firestore work differently from SQL. You query a single collection (or collection group) by filtering on indexed fields. Firestore requires an index for every query pattern, and composite indexes must be created explicitly. There are no joins. If you need data from two collections, you make two queries. This constraint is deliberate: it guarantees that every query scales linearly with the result set size, not the total database size.

Use Firestore in Native mode for all new projects. Native mode provides real-time listeners, offline support, and stronger consistency guarantees than the older Datastore mode.

Side-by-side comparison

When to use Cloud SQL

Cloud SQL is the right choice when your data is fundamentally relational and your queries need to cross entity boundaries.

• Relational data with joins. Users, orders, products, invoices, and line items. Data with foreign key relationships you need to query across. A query like “total revenue by product category for customers in a specific region” requires joins and aggregations that only SQL handles well.
• Complex reporting and analytics. Dashboards, admin panels, and internal tools that run aggregations, window functions, CTEs, and subqueries. For heavier analytical workloads, consider pairing Cloud SQL with BigQuery.
• ORM-backed applications. Frameworks like Django, Rails, Laravel, and Spring expect a relational database. Cloud SQL with PostgreSQL or MySQL works out of the box.
• Strict schema enforcement. When every record must conform to a defined structure. Think financial transactions, compliance data, and inventory systems.
• Migrating existing SQL databases. If your application already uses MySQL or PostgreSQL, Cloud SQL is the lowest-friction path to a managed GCP database.
• Multi-table transactions. Operations that must atomically update multiple tables. Transferring funds between accounts, or processing an order that updates inventory and creates a shipment record.

When to use Firestore

Firestore is the right choice when your data is document-shaped, your access patterns are simple, and you benefit from real-time sync or automatic scaling.

• Real-time applications. Chat, collaboration tools, live dashboards, and multiplayer game state. Any feature where changes must push to connected clients instantly without polling.
• Mobile and web apps with offline support. The Firebase SDK caches data locally. Users can read and write while offline, and changes sync automatically when connectivity returns.
• Flexible or evolving schemas. User profiles, feature flags, content documents, and configuration objects. Data where different records legitimately have different fields.
• Globally distributed users. Firestore multi-region deployments give low-latency reads from any region. For apps with users on every continent, this matters.
• Serverless and zero-ops preference. No instance sizing, no connection pool management, no patching. Firestore scales automatically from zero to millions of operations per second.
• Bursty or unpredictable traffic. A viral moment or seasonal spike does not require pre-provisioning. Firestore absorbs traffic automatically.

When to use both together

Many production applications use Cloud SQL and Firestore side by side, each handling what it does best. This is not over-engineering. It is using the right tool for each data type.

The common pattern: Cloud SQL stores your core transactional data (orders, inventory, billing, user accounts with relational integrity). Firestore stores user-facing real-time data (chat messages, notifications, activity feeds, presence indicators). Both databases are accessible from the same Cloud Run service or GKE pod.

Example architecture: e-commerce with live chat

• Cloud SQL (PostgreSQL) holds the product catalogue, orders, inventory counts, and payment records. Admin dashboards query Cloud SQL for revenue reports and inventory alerts.
• Firestore holds customer support chat threads, order status updates pushed to the buyer in real time, and user notification preferences. The mobile app uses Firestore listeners to show live order tracking.
• A Cloud Run service sits in front of both. Write endpoints hit Cloud SQL for order operations. The chat service reads and writes Firestore directly from the client using security rules, with server-side validation through Cloud Functions when needed.

Example architecture: SaaS with real-time collaboration

• Cloud SQL stores tenant data, subscription billing, audit logs, and role-based access control.
• Firestore stores the real-time collaboration state: document edits, cursor positions, comments, and presence indicators. Each user’s client subscribes to Firestore listeners for instant updates.

The key is that each database handles the access pattern it was built for. Do not force relational queries into Firestore, and do not build a polling system in Cloud SQL when Firestore gives you real-time push natively.

How to choose for common app types

• E-commerce (orders, inventory, checkout): Cloud SQL. Orders and inventory are deeply relational. You need joins for reports and ACID transactions for checkout. Add Firestore only if you need real-time features like live order tracking pushed to mobile clients.
• SaaS with admin dashboard: Cloud SQL for the core data model. Dashboards need joins and aggregations that Firestore cannot provide. Consider Firestore for any real-time collaboration features within the app.
• Chat or collaboration app: Firestore. Chat messages, typing indicators, and presence are document-shaped and benefit enormously from real-time listeners and offline support. Use Cloud SQL only if you need relational user management or analytics.
• Mobile app with offline-first sync: Firestore. The Firebase SDK handles offline caching, conflict resolution, and background sync. Cloud SQL has no built-in offline story for mobile clients.
• CMS or content feed: Either can work. Firestore is a natural fit for flexible content documents with varying fields. Cloud SQL works better if content has complex relationships (tags, categories, authors) you need to query across.
• Reporting-heavy back office: Cloud SQL. Ad-hoc queries, joins, and aggregations are essential for internal tools. For large-scale analytics, pair Cloud SQL with BigQuery.
• IoT data ingestion: Firestore handles high-throughput writes from many devices without provisioning. If you need to run analytical queries over the ingested data, stream it from Firestore to BigQuery.

Cloud SQL vs Firestore for cost

The pricing models are fundamentally different, and which is cheaper depends entirely on your traffic pattern.

Cloud SQL charges for the instance (CPU + memory per hour), storage, and network egress. You pay for the instance whether it is handling one query or one thousand per second. The smallest production-viable instances start around $7–15 per month. A mid-range instance with 2 vCPUs and 8 GB RAM runs roughly $50–100 per month depending on region and database engine. The upside: once you are paying for the instance, additional queries within its capacity cost nothing extra.

Firestore charges per document read ($0.036 per 100K reads), per write ($0.108 per 100K writes), per delete ($0.012 per 100K deletes), plus storage ($0.108 per GB/month in regional, more for multi-region). There is no instance to pay for. The free tier covers 50K reads, 20K writes, and 20K deletes per day, which is enough for development and low-traffic apps. At high traffic, costs scale linearly with operations.

For a detailed breakdown of Cloud SQL pricing strategies, see Cloud SQL cost optimisation.