Need advice about which tool to choose?Ask the StackShare community!
Amazon RDS vs Amazon Redshift: What are the differences?
Introduction: Here we will explore the key differences between Amazon RDS and Amazon Redshift.
Database Purpose: Amazon RDS is a relational database service designed for OLTP workloads, offering support for various database engines such as MySQL, PostgreSQL, SQL Server, etc. On the other hand, Amazon Redshift is a data warehousing service ideal for OLAP workloads, specifically optimized for analyzing large datasets.
Data Scaling: Amazon RDS allows for vertical scaling, meaning you can resize your database instance, but there are limits to how much you can scale vertically. In contrast, Amazon Redshift enables horizontal scaling by adding nodes to the data warehouse cluster, providing superior scalability for handling massive amounts of data.
Query Performance: Amazon RDS is optimized for transactional processing and may not perform as efficiently for complex analytical queries on large datasets. Amazon Redshift, being a cloud-based data warehouse, is specifically designed to offer fast query performance for analytical workloads, including complex joins and aggregations.
Pricing Model: Amazon RDS charges based on the instance type, storage, and data transferred. In contrast, Amazon Redshift pricing is based on a combination of the type and number of nodes in the cluster, along with the amount of data stored, providing more flexibility for cost optimization depending on usage patterns.
Backup and Restore: Amazon RDS provides automated backups and restores, allowing you to recover your database to any point in time within the retention period. With Amazon Redshift, you can take snapshots of your data warehouse at specific points in time and restore them, but the process is more geared towards creating and restoring full cluster backups.
Concurrency: Amazon RDS is suited for handling multiple concurrent connections typical in OLTP applications. Amazon Redshift, being optimized for analytics, can efficiently handle multiple queries running in parallel and support high levels of concurrency for complex analytical workloads.
In Summary, Amazon RDS is ideal for OLTP workloads with relational database needs, while Amazon Redshift is tailored for OLAP workloads with a focus on analytical processing and scalability.
We need to perform ETL from several databases into a data warehouse or data lake. We want to
- keep raw and transformed data available to users to draft their own queries efficiently
- give users the ability to give custom permissions and SSO
- move between open-source on-premises development and cloud-based production environments
We want to use inexpensive Amazon EC2 instances only on medium-sized data set 16GB to 32GB feeding into Tableau Server or PowerBI for reporting and data analysis purposes.
You could also use AWS Lambda and use Cloudwatch event schedule if you know when the function should be triggered. The benefit is that you could use any language and use the respective database client.
But if you orchestrate ETLs then it makes sense to use Apache Airflow. This requires Python knowledge.
Though we have always built something custom, Apache airflow (https://airflow.apache.org/) stood out as a key contender/alternative when it comes to open sources. On the commercial offering, Amazon Redshift combined with Amazon Kinesis (for complex manipulations) is great for BI, though Redshift as such is expensive.
You may want to look into a Data Virtualization product called Conduit. It connects to disparate data sources in AWS, on prem, Azure, GCP, and exposes them as a single unified Spark SQL view to PowerBI (direct query) or Tableau. Allows auto query and caching policies to enhance query speeds and experience. Has a GPU query engine and optimized Spark for fallback. Can be deployed on your AWS VM or on prem, scales up and out. Sounds like the ideal solution to your needs.
Pros of Amazon RDS
- Reliable failovers165
- Automated backups156
- Backed by amazon130
- Db snapshots92
- Multi-availability87
- Control iops, fast restore to point of time30
- Security28
- Elastic24
- Push-button scaling20
- Automatic software patching20
- Replication4
- Reliable3
- Isolation2
Pros of Amazon Redshift
- Data Warehousing41
- Scalable27
- SQL17
- Backed by Amazon14
- Encryption5
- Cheap and reliable1
- Isolation1
- Best Cloud DW Performance1
- Fast columnar storage1