Cloud Databases Guide: SQL, NoSQL, and Data Warehousing
Your application has grown from a hundred users to a hundred thousand, and the queries that once returned in five milliseconds now take five seconds. The database sits on the same virtual machine as the application, running out of disk space at the worst possible moment. You know there is a better way, but the array of database services offered by cloud providers feels overwhelming: relational, document, key-value, graph, columnar, time-series, data warehouse. Each has its own strengths, and picking the wrong one locks you into months of painful migration.
The choice of database shapes your application architecture, development velocity, and operational complexity for years to come. Understanding the trade-offs between consistency, availability, performance, and cost across cloud database options is essential knowledge for anyone building modern applications. The cloud computing basics of storage and compute apply to databases as well, but databases add dimensions of schema design, query patterns, and transactional guarantees that require dedicated consideration.
Managed Relational Databases
Relational databases organize data into tables with predefined schemas and support SQL queries with ACID transactions. They remain the dominant choice for applications that require strong consistency, complex joins, and structured data. Cloud providers offer managed versions of popular relational databases including MySQL, PostgreSQL, MariaDB, SQL Server, and Oracle.
Managed database services handle the undifferentiated heavy lifting of database administration. They automate provisioning, patching, backup, replication, and failover. AWS RDS and Aurora, Azure Database for PostgreSQL, and Google Cloud SQL provide these capabilities with configuration options for performance, storage, and availability.
Amazon Aurora
Aurora is AWS’s proprietary relational database engine compatible with MySQL and PostgreSQL. It separates compute from storage, allowing each to scale independently. The storage layer is distributed across multiple availability zones and replicates data six times. In the event of a failure, the database recovers automatically from the storage layer without needing to replay the transaction log.
Performance benchmarks show Aurora providing five times the throughput of standard MySQL and three times the throughput of standard PostgreSQL. The storage auto-scaling capability means you never provision storage ahead of time. Aurora grows from ten gigabytes to one hundred twenty-eight terabytes as your data grows.
Multi-AZ Deployments
Multi-AZ deployments provide high availability by maintaining a standby replica in a different availability zone. When the primary database fails, the service automatically fails over to the standby with minimal downtime. The failover typically completes within sixty seconds and requires no application changes.
A global e-commerce platform running PostgreSQL on RDS with Multi-AZ deployment experienced an availability zone outage that lasted four hours. The database failed over in forty-five seconds, and the application continued serving traffic without human intervention. Without Multi-AZ, the outage would have caused four hours of downtime.
NoSQL Databases
NoSQL databases trade some of the guarantees of relational databases for scalability, flexibility, or specialized query capabilities. They excel in scenarios where the data model does not fit neatly into tables, where write throughput must scale to millions of operations per second, or where the schema evolves rapidly.
Document Databases
Document databases store data in JSON-like documents with flexible schemas. Each document can have a different structure, making document databases ideal for content management systems, user profiles, and catalog data. MongoDB, Amazon DocumentDB, and Azure Cosmos DB are popular document database services.
A social media platform using MongoDB for user profiles can store different fields for different users. Some users have profile pictures and bios, others have none. Some have links to external accounts, others do not. In a relational database, each of these optional fields would require nullable columns or separate tables. In a document database, each user document contains only the fields that exist.
Key-Value Stores
Key-value stores provide the simplest data access pattern: retrieve a value by its key. They offer extremely low latency, measured in single-digit milliseconds, and can scale to billions of keys. Amazon DynamoDB, Redis, and Azure Cosmos DB Table API are key-value store services.
DynamoDB is a fully managed key-value and document database that delivers single-digit millisecond latency at any scale. It supports auto-scaling of read and write capacity and can handle over ten trillion requests per day for AWS’s largest workloads. DynamoDB is appropriate for session management, shopping carts, gaming leaderboards, and metadata storage.
Graph Databases
Graph databases store relationships as first-class entities. They excel at queries involving connected data, such as recommendation engines, fraud detection, social networks, and knowledge graphs. Amazon Neptune and Neo4j on AWS are graph database options in the cloud.
A fraud detection system uses graph queries to detect rings of fraudulent accounts. The system starts with a suspicious transaction and traverses the graph of accounts, devices, IP addresses, and transaction patterns. A graph database can find connections across four or five hops in milliseconds, a query that would require dozens of self-joins in a relational database.
Data Warehousing
Data warehouses are optimized for analytical queries over large volumes of historical data. They use columnar storage and parallel query execution to scan billions of rows in seconds. Amazon Redshift, Google BigQuery, and Azure Synapse Analytics are cloud data warehouse services.
Redshift is a petabyte-scale data warehouse that uses columnar compression and massively parallel processing. Data is distributed across multiple compute nodes, and queries execute across all nodes in parallel. Redshift Spectrum extends query capabilities to data stored in S3, enabling analysis of data without loading it into the warehouse first.
BigQuery
BigQuery is Google Cloud’s serverless data warehouse. It separates compute from storage and scales automatically. You do not provision clusters or manage infrastructure. You simply load data and run SQL queries, and BigQuery handles the rest.
BigQuery’s architecture decouples query processing from storage using a distributed compute engine called Dremel. Queries can scan terabytes of data in seconds. The serverless model means you pay per query rather than per provisioned capacity, which is cost-effective for workloads with variable query volumes.
Choosing the Right Database
The choice between database types depends on your application’s data model and access patterns. Relational databases are appropriate when data is highly structured, relationships are complex, and transactions require ACID guarantees. Document databases fit flexible schemas and hierarchical data. Key-value stores handle high-throughput, simple lookups. Data warehouses serve analytical queries over historical data.
A single application often uses multiple database types. An e-commerce platform might use a relational database for orders and inventory, a document database for product catalog, a key-value store for shopping carts and session data, and a data warehouse for business analytics. This polyglot persistence approach uses the best database for each workload rather than forcing everything into one.
Backup and Recovery
Cloud database services include automated backup capabilities. Relational databases typically provide point-in-time recovery, allowing you to restore to any second within a retention period. Backup retention ranges from one to thirty-five days in standard configurations, with extended retention available through manual snapshots.
Cloud storage guide best practices apply to databases as well. Database backups should be stored in a different region from the primary database for disaster recovery. Cross-region snapshot replication ensures that a regional outage does not destroy both the database and its backups.
Read Replicas for Scale
Read replicas address a common scaling challenge: database read traffic grows faster than write traffic. A read replica is a copy of the primary database that accepts only read queries. Applications direct read-heavy operations such as reporting, search, and analytics to the replica, reducing load on the primary database.
Cloud providers support cross-region read replicas, allowing you to serve read traffic from a replica in the same geographic region as your users. A social media platform with users in Europe, Asia, and North America might deploy a primary database in us-east-1 and read replicas in eu-west-1 and ap-southeast-1. European users query the local replica with latency under ten milliseconds rather than crossing the Atlantic for every request.
Read replicas also provide a migration path for database upgrades. You can create a read replica running a newer database version, promote it to a standalone instance, and redirect application traffic. The cutover takes seconds compared to hours for an in-place upgrade.
FAQ
Should I use a managed database or self-manage on a VM? Managed databases reduce operational overhead by automating backups, patching, replication, and failover. Self-managed databases provide more control over configuration and can reduce costs at very large scale. For most production workloads, managed databases are the better choice.
What is the difference between SQL and NoSQL databases? SQL databases use structured schemas and support complex queries with joins and ACID transactions. NoSQL databases offer flexible schemas, horizontal scaling, and specialized data models optimized for specific access patterns.
When should I use a data warehouse instead of a regular database? Use a data warehouse when you need to run analytical queries over large volumes of historical data. Data warehouses optimize for columnar scan performance and parallel execution, while regular databases optimize for transactional workloads with many small reads and writes.
How do I migrate between database types? Database migration tools such as AWS Database Migration Service can migrate between different database engines with minimal downtime. The service handles schema conversion, data copying, and continuous replication during the cutover window.
What is polyglot persistence? Polyglot persistence is the practice of using different database types for different parts of an application. Each workload uses the database that best matches its data model and access patterns, rather than forcing all workloads into a single database.