Unit 3: Database Services - Subjective Questions

A Relational Database is a type of database that stores and organizes data in tables (relations) consisting of rows (records/tuples) and columns (attributes/fields). It is based on the relational model proposed by E.F. Codd.

Key Characteristics:

• Tables: Data is organized into structured tables with a predefined schema.
• Primary Key: Each table has a unique identifier for every row.
• Foreign Key: Establishes relationships between tables, enforcing referential integrity.
• ACID Properties: Ensures reliable transactions (Atomicity, Consistency, Isolation, Durability).
• Normalization: Reduces data redundancy and improves integrity.

Role of SQL (Structured Query Language):

• Used to define schema (DDL - CREATE, ALTER, DROP).
• Used to manipulate data (DML - INSERT, UPDATE, DELETE).
• Used to query data (SELECT with JOIN, WHERE, GROUP BY).
• Used to control access (DCL - GRANT, REVOKE).

Examples of relational databases include MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.

Amazon RDS is a fully managed relational database service provided by AWS that makes it easy to set up, operate, and scale a relational database in the cloud. It automates time-consuming administrative tasks such as hardware provisioning, database setup, patching, and backups.

Key Benefits over Self-Managed Databases:

• Automated Administration: AWS handles patching, backups, and software installation.
• Automated Backups: Point-in-time recovery and automated snapshots.
• Scalability: Compute and storage can be scaled with a few clicks or API calls.
• High Availability: Multi-AZ deployments provide automatic failover.
• Read Replicas: Improve read performance and offload read traffic.
• Security: Encryption at rest and in transit, VPC isolation, and IAM integration.
• Monitoring: Integrated with Amazon CloudWatch for metrics and alerts.
• Cost-Effective: Pay-as-you-go pricing without upfront hardware costs.

By offloading operational overhead to AWS, developers can focus on application development and schema design rather than infrastructure management.

Amazon RDS supports several popular database engines, giving users flexibility to choose based on application needs, licensing, and familiarity.

Supported Database Engines:

• Amazon Aurora: A MySQL- and PostgreSQL-compatible engine built by AWS for the cloud, offering up to 5x the throughput of standard MySQL.
• MySQL: A widely-used open-source relational database, popular for web applications.
• MariaDB: A community-developed fork of MySQL, fully open-source.
• PostgreSQL: A powerful open-source object-relational database with advanced features.
• Oracle Database: A commercial enterprise-grade database with options for Bring Your Own License (BYOL) or License Included.
• Microsoft SQL Server: A commercial database from Microsoft, available in multiple editions (Express, Web, Standard, Enterprise).

Each engine supports different versions, features, and licensing models, and Amazon Aurora offers the deepest integration with AWS cloud infrastructure.

Licensing Considerations in Amazon RDS determine how commercial database software (like Oracle and SQL Server) is paid for and used. Open-source engines (MySQL, PostgreSQL, MariaDB) do not require separate licensing.

1. License Included Model:

• The license cost is bundled into the hourly RDS price.
• No need to purchase a separate license from the vendor.
• AWS holds the license on behalf of the customer.
• Suitable for customers who do not already own a license.
• Available for Microsoft SQL Server and some Oracle editions.

2. Bring Your Own License (BYOL) Model:

• The customer uses an existing license they already purchased.
• Suitable for organizations with existing enterprise agreements.
• Requires compliance with the vendor's licensing terms.
• Commonly used with Oracle Database.

Comparison:

Choosing the right model helps optimize cost and compliance.

Database Option Groups in Amazon RDS are containers that allow you to enable and configure additional features for certain database engines. They provide engine-specific functionality beyond the default settings.

Purpose:

• To add and manage optional features that are not part of the base database engine.
• To group related options together for easy management and reuse across instances.
• To customize engine behavior according to application requirements.

Examples of Options:

• Oracle: Oracle Advanced Security (TDE - Transparent Data Encryption), Oracle Enterprise Manager, Native Network Encryption, Statspack.
• Microsoft SQL Server: SQL Server Audit, Transparent Data Encryption (TDE).
• MySQL/MariaDB: MEMCACHED support, audit plugins.

Key Points:

• Each DB instance can be associated with one option group.
• Option groups are engine- and version-specific.
• Some options are persistent (cannot be removed once added) or permanent (cannot be removed and persist for the instance's lifetime).

Note: Option Groups differ from Parameter Groups, which manage engine configuration parameters, whereas Option Groups add extra features.

A DB Instance Class in Amazon RDS determines the computation and memory capacity of a database instance. It defines the CPU, memory, network performance, and I/O capacity available to the database.

Categories of DB Instance Classes:

1. Standard / General Purpose Classes (e.g., db.m5, db.m6g):

• Balanced compute, memory, and networking resources.
• Suitable for a wide range of workloads and most applications.

2. Memory Optimized Classes (e.g., db.r5, db.r6g, db.x1e):

• Provide high memory relative to vCPUs.
• Ideal for memory-intensive workloads like high-performance databases and in-memory caching.

3. Burstable Performance Classes (e.g., db.t3, db.t4g):

• Provide a baseline CPU performance with the ability to burst above the baseline using CPU credits.
• Cost-effective for workloads with low to moderate and intermittent CPU usage, such as dev/test environments.

Key Considerations:

• Instance classes can be changed by modifying the DB instance (may cause brief downtime).
• Choosing the correct class balances performance and cost.
• Graviton-based (ARM) instances (e.g., db.m6g) offer better price-performance.

Amazon RDS provides different storage types built on Amazon EBS (Elastic Block Store), allowing you to balance performance and cost based on workload requirements.

Storage Types:

1. General Purpose SSD (gp2 / gp3):

• Cost-effective storage for a broad range of workloads.
• gp2: Performance scales with volume size (3 IOPS per GB).
• gp3: Allows independent scaling of IOPS and throughput.
• Best for: Development, testing, and small-to-medium production databases.

2. Provisioned IOPS SSD (io1 / io2):

• Designed for I/O-intensive, latency-sensitive workloads.
• Allows you to specify a precise IOPS value.
• Delivers consistent and predictable performance.
• Best for: Large production databases with high transaction rates (OLTP).

3. Magnetic Storage (Standard):

• Legacy storage type, kept for backward compatibility.
• Lowest performance; not recommended for new workloads.

Key Features:

• Storage Auto Scaling: Automatically increases storage when nearing capacity.
• Storage can be scaled up without downtime in most cases.

Selection Guideline: Choose gp3 for balanced cost/performance and io1/io2 when consistent high IOPS is critical.

Read Replicas in Amazon RDS are read-only copies of a source (primary) database instance. They use the database engine's built-in asynchronous replication to copy data from the primary instance.

How They Work:

• Changes made to the primary (source) database are asynchronously replicated to the read replica.
• Replicas can serve read-only queries, reducing load on the primary.
• You can create multiple read replicas (up to 5 or 15 for Aurora) for a single source.
• Replicas can be in the same region, different region (cross-region), or different AZ.

Primary Use Cases:

• Scaling Read Traffic: Offload read-heavy queries (reports, analytics) from the primary.
• Improved Performance: Distribute queries across multiple replicas.
• Cross-Region Disaster Recovery: Replicas in other regions provide geographic redundancy.
• Promotion: A read replica can be promoted to a standalone primary database.

Key Points:

• Replication is asynchronous, so there may be replication lag.
• Read Replicas are primarily for scalability, NOT for high availability (which Multi-AZ handles).

High Availability (HA) in Amazon RDS ensures that a database remains accessible and operational even during failures. This is achieved primarily through Multi-AZ (Multi Availability Zone) deployments.

Multi-AZ Deployment:

• RDS maintains a synchronous standby replica in a different Availability Zone.
• Data is synchronously replicated from the primary to the standby.
• The standby is not used for read traffic (unlike read replicas) in traditional Multi-AZ.

How Failover Works:

1. In case of a primary instance failure (hardware failure, AZ outage, maintenance), RDS automatically detects the problem.
2. RDS performs an automatic failover to the standby replica.
3. The DNS CNAME record is updated to point to the standby, which becomes the new primary.
4. Applications reconnect using the same endpoint, so no connection string changes are needed.
5. Failover typically completes in 60–120 seconds.

Benefits:

• Automatic failover with minimal downtime.
• Data durability through synchronous replication.
• Protection against AZ-level failures and planned maintenance.

Note: Multi-AZ is for availability and durability, while Read Replicas are for scalability.

Single Master and Multi-Master are two architectures that define how write operations are handled in a distributed database system.

Single Master Architecture:

• Only one node (the master) accepts write operations.
• Other nodes (replicas) are read-only and receive updates from the master.
• Writes are centralized, ensuring consistency.

Advantages:

• Simpler conflict management (single write source).
• Strong data consistency.

Challenges:

• Master is a single point of failure for writes.
• Write scalability is limited to one node.

Multi-Master Architecture:

• Multiple nodes can accept write operations simultaneously.
• Each master can read and write, and changes are replicated to others.

Advantages:

• High write availability (no single write point of failure).
• Write scalability across multiple nodes.
• Lower write latency with geographically distributed masters.

Challenges:

• Write conflicts must be detected and resolved.
• More complex replication and consistency management.

Comparison Table:

Amazon Aurora offers a Multi-Master option for continuous write availability.

Amazon Redshift is a fully managed, petabyte-scale cloud data warehouse service designed for analytical (OLAP) workloads and business intelligence. It enables fast querying of large datasets using standard SQL.

Architecture:

• Cluster-based: A Redshift cluster consists of a leader node and one or more compute nodes.
• Leader Node: Receives queries, develops execution plans, and distributes work to compute nodes.
• Compute Nodes: Store data and execute query steps in parallel.
• Node Slices: Each compute node is divided into slices for parallel processing.

Key Features:

• Columnar Storage: Data stored by column, improving analytical query performance and compression.
• Massively Parallel Processing (MPP): Distributes queries across multiple nodes for speed.
• Data Compression: Reduces storage footprint and improves I/O.
• Redshift Spectrum: Query data directly in Amazon S3 without loading it.
• Scalability: Scale from a few hundred GB to petabytes.
• Integration: Works with BI tools, S3, and other AWS services.

Use Cases:

• Business intelligence and reporting.
• Big data analytics.
• Aggregating and analyzing historical data.

Redshift is optimized for read-heavy analytical queries, not for transactional (OLTP) workloads.

A Non-Relational (NoSQL) Database is a database that does not use the traditional tabular relational model. It is designed to handle large volumes of unstructured, semi-structured, or rapidly changing data with flexible schemas and horizontal scalability.

Characteristics:

• Schema-less / flexible schema.
• Horizontal scalability (scale-out across servers).
• High performance for specific access patterns.
• Often follow BASE (Basically Available, Soft state, Eventual consistency) instead of strict ACID.

Types of NoSQL Databases:

1. Key-Value Stores:

• Data stored as key-value pairs.
• Examples: Amazon DynamoDB, Redis.

2. Document Databases:

• Store data as documents (JSON, BSON, XML).
• Examples: MongoDB, Amazon DocumentDB.

3. Column-Family (Wide-Column) Stores:

• Store data in columns grouped into families.
• Examples: Apache Cassandra, HBase.

4. Graph Databases:

• Store data as nodes and edges representing relationships.
• Examples: Amazon Neptune, Neo4j.

Use Cases:

• Real-time web applications, IoT data, content management, and large-scale caching.

NoSQL databases are chosen when scalability, flexibility, and high throughput are more important than strict relational consistency.

Amazon DynamoDB is a fully managed, serverless NoSQL key-value and document database service that provides single-digit millisecond latency at any scale. It automatically handles scaling, replication, and infrastructure management.

Core Components:

• Tables: Collections of data (similar to tables in relational databases but schema-less).
• Items: Individual records in a table (like rows), each uniquely identifiable.
• Attributes: Data elements within an item (like columns).
• Primary Key: Uniquely identifies each item; can be:
  • Partition Key (Simple): A single attribute.
  • Partition Key + Sort Key (Composite): Combination of two attributes.

Key Features:

• Fully Managed & Serverless: No servers to provision or manage.
• Automatic Scaling: Scales throughput up and down automatically.
• High Availability & Durability: Data replicated across multiple AZs.
• Global Tables: Multi-region, multi-master replication.
• DynamoDB Streams: Capture data modification events in real-time.
• DAX (DynamoDB Accelerator): In-memory cache for microsecond read latency.
• Secondary Indexes: Global (GSI) and Local (LSI) for flexible query patterns.

Use Cases:

• Mobile and web applications, gaming, IoT, real-time bidding, and session management.

Relational and Non-Relational databases differ fundamentally in structure, scalability, and use cases.

Comparison Table:

Detailed Points:

• Relational databases excel at structured data and complex transactions requiring strong consistency (e.g., banking systems).
• NoSQL databases excel at scalability and flexibility for unstructured or rapidly growing data (e.g., social media, IoT).

Conclusion: The choice depends on the application's requirements for consistency, scalability, data structure, and query complexity. Many modern systems use both in a polyglot persistence approach.

Both Multi-AZ deployments and Read Replicas involve creating copies of a database, but they serve different purposes.

Multi-AZ Deployment:

• Purpose: High Availability and Disaster Recovery.
• Replication: Synchronous.
• Standby location: Different Availability Zone, same region.
• Standby usage: Cannot serve read traffic (traditional Multi-AZ); used only for failover.
• Failover: Automatic.

Read Replicas:

• Purpose: Scaling read performance.
• Replication: Asynchronous.
• Replica location: Same AZ, different AZ, or different region.
• Usage: Serves read-only queries to offload the primary.
• Promotion: Can be manually promoted to a standalone primary.

Comparison Table:

Summary:

• Use Multi-AZ for fault tolerance and automatic failover.
• Use Read Replicas for distributing read load.
• They can be combined for both high availability and scalability.

Amazon RDS provides robust backup and recovery mechanisms to protect data and enable point-in-time restoration.

1. Automated Backups:

• Enabled by default; RDS automatically backs up the database.
• Captures daily snapshots and transaction logs.
• Enables Point-In-Time Recovery (PITR) to any second within the retention period.
• Retention period: Configurable from 1 to 35 days.
• Stored in Amazon S3.

2. Manual DB Snapshots:

• User-initiated backups of the DB instance.
• Retained until explicitly deleted (not bound by retention period).
• Useful for long-term retention or before major changes.
• Can be shared across accounts and copied across regions.

3. Restore Process:

• Restoring a snapshot creates a new DB instance (does not overwrite the existing one).
• The new instance gets a new endpoint.

Key Points:

• Backups during the backup window may cause a brief I/O suspension (single-AZ) but not in Multi-AZ.
• Snapshots are incremental (only changed data is stored after the first).
• Combined with Multi-AZ and Read Replicas, RDS provides a comprehensive data protection strategy.

Amazon DynamoDB offers two capacity modes to manage read and write throughput, allowing optimization for performance and cost.

1. Provisioned Capacity Mode:

• You specify the number of reads and writes per second your application requires.
• Measured in RCUs (Read Capacity Units) and WCUs (Write Capacity Units).
• Supports Auto Scaling to adjust capacity based on utilization.
• Best for: Predictable, steady-state workloads.
• Cost: Lower if traffic is predictable; you pay for provisioned capacity.

2. On-Demand Capacity Mode:

• DynamoDB automatically scales to accommodate workload.
• No capacity planning required; pay per request.
• Instantly handles unpredictable or spiky traffic.
• Best for: New applications, unpredictable workloads, or variable traffic.
• Cost: Pay-per-request; can be higher for steady high-volume loads.

Comparison Table:

Capacity Unit Reference:

• RCU = one strongly consistent read/sec for an item up to .
• WCU = one write/sec for an item up to .

Secondary Indexes in DynamoDB allow you to query data using attributes other than the primary key, enabling more flexible and efficient access patterns.

1. Global Secondary Index (GSI):

• Can have a partition key and sort key different from the base table.
• Can be created or deleted at any time (even after table creation).
• Has its own provisioned throughput separate from the base table.
• Supports only eventual consistency for reads.
• Spans all partitions of the table (global).

2. Local Secondary Index (LSI):

• Has the same partition key as the base table but a different sort key.
• Must be created at table creation time (cannot be added later).
• Shares throughput with the base table.
• Supports both strong and eventual consistency.
• Limited to a 10 GB size per partition key value.

Comparison Table:

Usage: Use GSI for queries on completely different attributes; use LSI when you need an alternate sort key with the same partition key.

OLTP and OLAP are two categories of data processing systems optimized for different workloads.

OLTP (Online Transaction Processing):

• Handles a large number of short, fast transactions (INSERT, UPDATE, DELETE).
• Optimized for write-heavy, real-time operational tasks.
• Data is highly normalized.
• Examples: Banking, e-commerce order processing.
• AWS Services: Amazon RDS, Amazon Aurora, Amazon DynamoDB.

OLAP (Online Analytical Processing):

• Handles complex analytical queries on large volumes of historical data.
• Optimized for read-heavy, aggregation operations.
• Data is often denormalized and stored in columnar format.
• Examples: Business intelligence, reporting, data mining.
• AWS Services: Amazon Redshift.

Comparison Table:

Summary: Choose OLTP databases (RDS/DynamoDB) for operational applications and OLAP (Redshift) for analytics and data warehousing.

Selecting the right AWS database service requires evaluating multiple factors to match the application's requirements.

Key Factors to Consider:

• Data Model: Is the data structured (relational) or unstructured/flexible (NoSQL)?
• Consistency Requirements: Does the application need strong ACID consistency or can it tolerate eventual consistency?
• Scalability Needs: Expected read/write volume and growth — vertical vs horizontal scaling.
• Query Complexity: Need for complex joins and SQL vs simple key-based lookups.
• Latency Requirements: Need for single-digit millisecond response times.
• Workload Type: Transactional (OLTP) vs analytical (OLAP).
• Availability & Durability: Need for Multi-AZ, failover, and backups.
• Cost & Licensing: Open-source vs commercial engine licensing.

Illustrative Scenarios:

• Scenario 1 — E-commerce transactions: Needs ACID and relational structure → Amazon RDS / Aurora.
• Scenario 2 — Gaming leaderboard with massive scale: Needs high throughput, low latency, flexible schema → Amazon DynamoDB.
• Scenario 3 — Business analytics on terabytes of data: Needs fast analytical queries → Amazon Redshift.
• Scenario 4 — Highly connected data (social network): Needs relationship traversal → Amazon Neptune (Graph DB).

Conclusion: AWS encourages a purpose-built database strategy, selecting the best service for each workload rather than forcing one database to handle all needs.