We’re designing a new ERP system on GCP and debating between Cloud SQL (PostgreSQL) and Firestore for our core transaction processing. The system will handle order management, inventory tracking, and financial transactions requiring strong consistency.
Key requirements: ACID transactions spanning multiple entities (orders, inventory, payments), 500-1000 transactions per second at peak, complex queries with joins across 15-20 tables, and the ability to scale to 50M+ records per table. We need transactional guarantees across related entities but also want horizontal scalability.
Cloud SQL gives us familiar relational patterns and strong ACID guarantees, but I’m concerned about scaling beyond a single instance. Firestore offers better horizontal scalability but I’m unclear on its transactional integrity capabilities for complex multi-document operations. What are the real-world tradeoffs for ERP workload patterns?
The 500 entity limit concerns me for scenarios like month-end close operations where we might need to update thousands of financial records atomically. Also, how do you handle complex queries in Firestore? Our ERP has reports that join 15-20 tables with aggregations and filtering. In SQL this is straightforward, but I’m not sure how to model this in a document database without denormalization that creates consistency challenges.
For ERP workloads, Cloud SQL is the clear choice. Firestore transactions are limited to 500 entities and 10MB of data per transaction, which won’t work for complex ERP operations that might need to update orders, inventory, financial records, and audit logs atomically. Cloud SQL PostgreSQL gives you full ACID compliance with no entity limits in transactions. The concern about scaling is valid, but Cloud SQL can handle 1000 TPS easily, and you can use read replicas for reporting queries. For ERP systems, data integrity trumps horizontal scalability.
I’d push back on that. Firestore absolutely supports transactional integrity - it provides ACID transactions with optimistic concurrency control. Yes, there’s a 500 entity limit per transaction, but most ERP operations don’t need to update 500 entities atomically. The scalability benefits are huge: Firestore automatically shards and scales to millions of operations per second without manual intervention. Cloud SQL requires vertical scaling (bigger machines) and eventually hits limits around 10K TPS even with the largest instances.
This is a classic case where hybrid architecture makes sense. Use Cloud SQL for core transactional data where you need complex joins and multi-table transactions - orders, inventory, financial records. Use Firestore for high-read, eventually consistent data like product catalogs, user sessions, real-time notifications. Many successful ERP implementations use this pattern. The transactional core stays in relational, while peripheral systems that need massive scale use NoSQL. You get best of both worlds.
Having built ERP systems on both architectures, I strongly recommend Cloud SQL for your requirements. ERP workloads have complex relational data models that map naturally to SQL. The operational complexity of maintaining data consistency in Firestore across denormalized collections is significant. You’ll spend more time building consistency mechanisms than you save from Firestore’s scalability. For 1000 TPS, Cloud SQL with proper indexing and connection pooling is more than sufficient. Use read replicas for reporting and you’re set.
After extensive evaluation and this discussion, here’s my analysis of Cloud SQL vs Firestore for ERP transactional workloads:
Transactional Guarantees Comparison:
Cloud SQL (PostgreSQL):
- Full ACID compliance with no entity/size limits
- Multi-table transactions with foreign key constraints
- Serializable isolation level available for critical operations
- Two-phase commit for distributed transactions
- Proven track record in financial systems
Firestore:
- ACID transactions limited to 500 entities and 10MB per transaction
- Optimistic concurrency control (retry on conflicts)
- No native support for cross-collection consistency
- Transactions timeout after 60 seconds
- Eventual consistency for queries across collections
For ERP workloads requiring complex multi-entity updates (order + inventory + payment + audit), Cloud SQL’s unlimited transaction scope is essential. Firestore’s 500 entity limit becomes a hard constraint for batch operations like month-end close.
Scalability and Replication:
Cloud SQL scaling characteristics:
- Vertical scaling: up to 96 vCPUs, 624GB RAM
- Peak throughput: 5,000-10,000 TPS on largest instances
- Read replicas: up to 10 replicas for read scaling
- High Availability: automatic failover in same region
- Manual sharding required for >10K TPS
Firestore scaling characteristics:
- Automatic horizontal sharding
- Unlimited throughput (millions of operations/sec)
- Global replication with multi-region configuration
- No manual scaling or provisioning needed
- Scales linearly with data volume
Firestore clearly wins on scalability, but ERP systems rarely need millions of TPS. The 1000 TPS requirement fits comfortably within Cloud SQL’s capabilities.
ERP Workload Pattern Analysis:
Typical ERP operations:
- Order creation: Update order, reserve inventory, create payment record, log audit trail (4-6 tables)
- Invoice generation: Read order details, calculate taxes, update financial records, create documents (8-12 tables)
- Inventory adjustment: Update stock levels, create transaction history, trigger reorder workflows (5-8 tables)
- Financial reports: Complex joins across orders, payments, inventory, customers (15-20 tables)
These patterns require:
- Multi-table transactions (Cloud SQL advantage)
- Complex joins and aggregations (Cloud SQL advantage)
- Strong consistency across related entities (Cloud SQL advantage)
- Referential integrity enforcement (Cloud SQL advantage)
- Ad-hoc query capabilities for business intelligence (Cloud SQL advantage)
Firestore would require:
- Extensive denormalization (order document contains inventory snapshot, payment details, etc.)
- Application-level consistency management across collections
- Pre-computed aggregations for reporting
- Complex client-side join logic
- Careful transaction boundary design to stay under 500 entity limit
Recommendation for ERP Systems:
Use Cloud SQL (PostgreSQL) as the primary database for these reasons:
-
Data Model Fit: ERP data is inherently relational with complex relationships. SQL’s normalization reduces data redundancy and inconsistency risks.
-
Transaction Requirements: ERP operations frequently need atomic updates across 10+ tables. Cloud SQL handles this naturally; Firestore requires workarounds.
-
Query Complexity: Financial reporting, inventory analysis, and business intelligence require complex SQL queries with joins, aggregations, and window functions. This is SQL’s strength.
-
Scalability Sufficiency: 1000 TPS is well within Cloud SQL’s capabilities. Use high-availability configuration and read replicas for redundancy and read scaling.
-
Operational Maturity: PostgreSQL is battle-tested in financial systems with decades of production experience. The operational playbooks are well-established.
Scaling Strategy:
- Start with Cloud SQL High Availability configuration (automatic failover)
- Use read replicas for reporting and business intelligence queries
- Implement connection pooling (PgBouncer) to maximize connection efficiency
- Partition large tables by date/region for performance
- If you exceed 10K TPS: consider Cloud Spanner for unlimited horizontal scale with SQL compatibility
When to Use Firestore in ERP Context:
- User session management and authentication state
- Real-time notifications and messaging
- Product catalogs and configuration data (high read, low consistency requirements)
- Document attachments and file metadata
- Mobile app offline sync for field sales/service
For your core ERP transactional workload with strong consistency requirements and complex queries, Cloud SQL is the right choice. The relational model, unlimited transaction scope, and mature operational tooling outweigh Firestore’s scalability advantages for this use case.