AWS_Databases - kamialie/knowledge_corner GitHub Wiki

Content

Database overview:

Name Use case
RDS RDBMS / OLTP, SQL queries, transactional inserts / update / delete
Aurora same as RDS with less maintenance, more flexibility, more performace
ElastiCache key/value store, frequent reads, less writes, cache for DB, web session storage
DynamoDB NoSQL, can replace ElastiCache (f.e. for web session storage), query only on primary, sort keys or indexes
S3 big objects, static files, web hosting
Redshift analytics, BI, data warehouse
Neptune graph relationship

Relational Database Service

Generally used for OLAP (Online Transaction Processing) use cases: customer orders, bank transactions, booking systems, etc. Supports MySQL, PostgreSQL, MariaDB, Oracle Database, Microsoft SQL Server, and Aurora.

Managed service features:

  • automated provisioning, OS patching
  • continuous backups
  • monitoring dashboards
  • read replicas
  • multi-AZ setup
  • scaling capability both vertical and horizontal
  • storage options - general purpose or IOPS SSD

Storage Auto Scaling - automatically increases storage dynamically. Maximum storage threshold must be set. Scaling is triggered when:

  • free storage is less than 10%
  • low storage lasts at least 5 minutes
  • 6 hours passed since last modification

Replication

RDS can have up to 5 (Oracle, SQL Server) or 15 (MySQL, MariaDB, PostgreSQL) read replicas in the same AZ, different AZ or different region. No network costs, if replica is within the same region. Replication is ASYNC (eventual read consistency). Clients must update connection string to leverage read replicas (replicas and primary databases have different DNS endpoints). Can be used for additional reporting or analytical application that makes just read requests.

Replica can also be promoted to be independent databases (breaks replication with original database).

Multi-AZ

Used for disaster recovery. Application is provided with one DNS name that supports automatic failover. SYNC replication is used with a standby database in a different AZ (both writes must happen for a request to be successful). Read replica can be set up as multi-AZ for disaster recovery.

Multi-AZ can be enabled afterwards and does not require downtime - primary database creates a snapshot, which is restored in the new standby database, then databases get in sync.

Backup

Automatically enabled:

  • daily full backup (during maintenance window)
  • transaction logs (every 5 minutes) - allow point-in-time recovery, AWS restores database from the most recent backup and applies transaction logs from when backup was created up to specified time

Retention is 7 days by default, but can be set up to 35 days.

Backup can be triggered manually by user (database or manual snapshot). Snapshots are retained even after database is deleted and provide only point-in-time snapshot (no transaction logs).

Automated backup happens in user-defined backup window. Both automated and manual backups are stored in S3.

Restored database both from automated or manual backup gets its own DNS endpoint.

Security

Encryption at rest can be set for both master and read replicas using KMS - must be defined at launch time. If master is not encrypted, read replicas can not be encrypted. Transparent Data Encryption (TDE) is available for Oracle and SQL Server.

Snapshot's encryption setting is the same as the database's. However, unencrypted snapshot can be copied over to encrypted snapshot. To encrypt a database take its snapshot, created encrypted snapshot from it, restore new database from the encrypted snapshot, migrate application to new instance and delete old one.

Encryption in transit is done via SSL. To enforce SSL:

  • PostgreSQL - set rds.force_ssl to 1 in RDS Console (parameter group)
  • MySQL - run the following command 
     GRANT USAGE ON *.* TO 'mysqluser'@'%' REQUIRE SSL;

Security Groups are used to control traffic in/out of database.

IAM authentication allows user credentials to be managed through IAM users and roles. Works with PostgreSQL and MySQL. Authentication token (lifetime of 15 minutes) obtained from RDS is used instead of password to access database.

RDS Proxy

Fully managed service - serverless, autoscaling, multi-AZ. Allows applications to pool and share established connection to database, thus, improving overall efficiency by reducing stress on database and minimizing open connections. Supports MySQL, MariaDB, PostgreSQL, MS SQL Server, Aurora. Accessible only inside VPC.

Also preserves database connection during failover event.

Pricing

Pricing points:

  • instance type and size
  • storage size
  • data transfer out between AZs and regions
  • backup storage

Payment options:

  • on-demand
  • reserved instances - 1 or 3 year term (up to 69% saving)

Aurora

Aurora - most managed relational database option (compatible with MySQL and PostgreSQL). Provides data replication (6 locations across 3 AZs), up to 15 read replicas (one master node taking write requests), continuous backup to S3, point-in-time recovery (from a specific period). Generally faster and cheaper (at scale, initially more expensive for about 20% than RDS) than MySQL and PostgreSQL. Storage automatically grows in increments of 10GB up to 128TB.

Read replicas can have instances of different types. Custom endpoint can be created to only connect to a subset of replicas. Reader endpoint is generally not used after defining custom endpoints. Supports cross region replication. Auto-scaling can be enabled for read replicas.

Provides writer DNS endpoint (that points to the master) and reader endpoint (connection load balancing).

Serverless option includes automated instantiation and auto-scaling based on actual usage. Good for infrequent, intermittent or unpredictable workloads.

Multi-master feature allows every node to do R/W operation (immediate failover for write node). Client is provided with multiple connections, so that another one can be used in case of failure.

Global Aurora provides up to 5 secondary (read-only) regions (up to 16 read replicas per region), while only primary region is able to serve write requests. Replication lag is less than 1 second. In case of failure another region can be promoted to primary.

Integrated with ML services: SageMaker and Comprehend. Enables adding ML-based predictions via SQL queries.

Backtrack feature - restore data at any point of time without using backups.

Dynamo Database

Fully managed, serverless, automatically scalable, non-relational, NoSQL database.

Exposes tables to client. Maximum item size - 400KB. Table can have infinite number of items (or rows), each item can have multiple and possibly nested attributes that can change over time (even become null).

Supported value types:

  • scalar - string, number, binary, boolean, null
  • document - list, map; also JSON, HTML, XML
  • set - string set, number set, binary set

Supported features:

  • transactions
  • global tables
  • encryption
  • evolving schema
  • integrations with Lambda
  • DynamoDB Local - simulation of cloud service on laptop for local development

Data is highly resilient - always stored across 3 geographically distinct data center. Offers eventually consistent reads (default) and strongly consistent reads consistency models: first guarantees consistency across all copies to be reached within 1 second (best for performance), second reflects all successful reads (best for consistency).

User access can be restricted up to specific items within a table via IAM policy conditions.

{
    ...
    "Condition": {
        "ForAllValues:StringEquals": {
            "dynamodb:LeadingKeys": [ # if partition key matches user's id
                "${www.mygame.com:user_id}"
            ]
            "dynamodb:Attributes": [ # condition applies to following attributes
                "UserId",
                "GameTitle",
                "Score"
            ]
        }
    }
}

DynamoDB TTL

TTL feature automatically deletes items after an expiration timestamp, which is set as one of the attributes. First, item is marked for deletion upon expiry, then it is deleted within next 48 hours - this means that clients may still see expired items; can be solved with additional filter. Expiration is expressed in epoch time format and represent the time when an item should expire. Items are also deleted for LSI and GSI.

Transactions

All-or-nothing operations (add, update or delete) to multiple items across one or more tables. This corresponds to ACID (Atomic, Consistent, Isolated, Durable) transactions. Can be applied to Read or Write modes. Consume twice as much WCU or RCU, because database performs 2 operations in the background: prepare and commit.

Use cases include financial transactions, managing orders, or multiplayer games.

S3 patterns

  1. Clients talk to DynamoDB table, which stores direct S3 link of an object as one of the attributes.
  2. Objects are stored in S3, which invokes Lambda function to store metadata in DynamoDB table, which can later be queried for insights.

Operations

Write operations such as write, update or delete can be performed in a batch, up to 25 items for PutItem and/or DeleteItem, up to 16MB data written with up to 400KB per item. Read bath is also possible (even across multiple tables) - up to 100 items, up to 16MB.

Filter operation is performed on client side.

Conditional write can be used to determine which items should be affected for PutItem, UpdateItem, DeleteItem, and BatchItem operations. The following conditions are supported: attribute_exists, attribute_not_exists, attribute_type, contains, begins_with, size, etc. To make sure that existing item is not overwritten one can use attribute_not_exists condition on partition key and optionally sort key.

Optimistic locking is a strategy to ensure that an item hasn't changed before one updates or deletes it. For this each item has a version attribute; each client ensures that this field has expected value through conditional write feature. Helps in cases when multiple clients want to update an item at the same time.

Copying a table options:

  1. Use AWS Data Pipeline. Uses Amazon EMR cluster and S3 bucket in the backend.
    1. DynamoDB source -> EMR -> S3
    2. S3 -> EMR -> DynamoDB destination
  2. Backup and restore into a new table
  3. Scan plus PutItem or BatchWriteItem

Keys and indexes

Each table has a primary key, which is chosen at creation time. It can be made of partition key and optionally sort key (if partition key is not always unique, e.g. user id in forum). Partition key is used to store data on different nodes, which affects performance. The goal is to choose an attribute that would evenly distribute data across nodes. Integrates with ELB, CloudFront, API Gateway, and web apps.

If partition key only includes couple values (2-3 or so), this could result in hot partition problem. Can be solved by adding a suffix to a partition key value: random or calculated suffix.

Secondary indexes allow to perform fast queries on attributes that are not part of primary key. Both types can include all or a set of attributes of the original table.

  • Local Secondary Indexes (LSI) - must be specified at creation time (can not be added, removed or modified later), contains same partition key as original table, but allows to specify different sort key, which results in a different view on the data. Up to 5 LSIs per table. Main table's Write and read capacities are used.
  • Global Secondary Indexes (GSI) - completely different partition and/or sort keys that can be added at any time. Since adding GSI results in sort of another table it requires provisioning RCUs and WCUs. If writes are throttled on GSI, they are also throttled on the main table - choose carefully WCUs of GSI.

Query and scan

Query finds items based on primary key and distinct value. Optional sort key can be used to refine query results, e.g. timestamp attribute could be used to select items for specific time period. ProjectionExpression parameter can be used to return only desired attributes instead of all by default. Results are always sorted by the sort key in ascending order (can be reverted by setting ScanIndexForward parameter to false).

Scan examines every item in the table and returns all attributes by default (can by changed with ProjectionExpression parameter). Filters allow narrowing the output result. By default scan operation runs sequentially, but could also be configured to run in parallel, which could affect other applications that run other operations at the same time. Isolating scan operation to specific tables could improve performance, but in general avoid using scans.

Smaller page size can reduce the impact of query or scan operation.

PartyQL

SQL-compatible query language for DynamoDB. Supports select, insert, update and delete (not join).

DynamoDB Streams

Ordered list of item-level modifications (create/update/delete). Records can be sent or read by Kinesis Data Streams, Lambda or KCL apps. Maximum retention is 24 hours. Exposed via dedicated endpoint. Made up of shards. Records are not retroactively populated after stream has been enabled.

Information to be included in the stream can be adjusted to key attributes, entire object before or after it was modified or both (referred as image).

Use cases:

  • audit and archive transactions (ElasticSearch, derivative tables)
  • react to changes, trigger events
  • analytics
  • implement cross-region replication

Global tables

Multiple tables in different regions with 2 way replication (Active-Active). DynamoDB Streams must be enabled.

Provides low-latency access in multiple regions - apps can READ and WRITE in any region.

Pricing

2 capacity modes provide different management and pricing strategies. Setting can be changed once every 24 hours.

Provisioned (default) option allows clients to purchase read and write capacity beforehand. If application uses all available stock, AWS throttles further requests, until more capacity is purchased. Reserved capacity is available as 1 or 3 year term commitment. Auto-scaling can be added to scale based on percentage of provisioned capacity (consists of min, max capacity units and target utilization percentage). Throughput can be temporary exceeded using Burst Capacity.

  • write capacity unit (WCU) - 1 x 1KB per second
  • read capacity unit (RCU) - 1 x 4KB strongly consistent read per second or 2 x 4KB of eventually consistent read (default); amount to purchase depends on desired level of consistency (from less to more): eventual, strong, transactional
  • provisioned (default) - specify number of writes/reads beforehand

ProvisionedThroughputExceededException appears when request rate is higher than provisioned read/write capacity. AWS SDK automatically retries requests until they are successful.

On-demand option automatically scales based on workload. Charges for data reads and writes at the end of a month. Good choice for unknown, new workloads, unpredictable applications and spiky/short-lived peaks. About 2.5 times more expensive than provisioned capacity.

Global secondary indexes need their own capacity units. Global tables also need additional capacity units.

Storage is charged at a GB per month basis.

DynamoDB Accelerator

Fully managed, clustered in-memory cache for DynamoDB, also known as DAX. Doesn't require application logic changes (compatible with existing DynamoDB APIs). TTL is 5 minutes by default. Performs eventually consistent read on cache miss, therefore, not suitable for applications that require strong consistency; also not suitable for write-intensive applications.

Generally used as individual object or query and scan cache. Covers "hot key" problem (too many reads). ElastiCache is a better choice for storing aggregation result.

DynamoDB CLI

Popular CLI options:

  • --projection-expression - specify one or more attributes to retrieve
  • --filter-expression - specify which items to show

DynamoDB Security

For applications accessing DynamoDB table directly a fine-grained access control can be established. Each user gets temporary credentials via Web Identity Federation (Google, Facebook, etc) or Cognito Identity Pools. IAM Role is then assigned to these users with a policy that provides access only to DynamoDB.LeadingKeys condition can be used to limit access to specific rows (items), while Attributes can be used to limit access to specific attributes of items.

Redshift

Data warehousing service (based on PostgreSQL). OLAP (Online Analytical processing) system.

Can be used for data analytics (historical analysis as opposed to operational analysis). Collect data from many sources and understand relationships and trends in the data. Columnar storage of data (instead of rows). Massively Parallel Query Execution (MPP).

Provides large scale analytics and queries across warehouse or data lake in S3. Can scale up to petabytes.

Redshift Enhanced VPC Routing - COPY/UPLOAD requests from source go through VPC instead of public internet.

Available node types (can be changes afterwards):

  • dense compute
  • dense storage (more economical)

Snapshots

Does not have multi-AZ feature (deployed in a single AZ). DR can be implemented via incremental snapshots to S3. Snapshot can be restored to a new cluster.

Options:

  • automated - every 8 hours or every 5GB or on schedule (set snapshot retention)
  • manual - retained until manually deleted

Can be configured to automatically copy snapshots (either option) to another region, where they can be restored to a cluster.

Spectrum

Enable querying of exabytes of data directly in S3. Must have Redshift cluster available.

Database Migration Service

Migration is performed by EC2 instance that runs DMS software. Multi AZ option deploys multiple instances, marking one primary and other(s) as standbys. Endpoints are registered databases, which can be source or destination. Replication tasks are assigned to a running instance and make use of registered endpoints.

Can also be used to continuously stream data from source to destination.

Sources:

  • on-prem
  • EC2
  • Azure SQL Database
  • RDS including Aurora
  • S3

Targets:

  • on-prem
  • EC2
  • RDS
  • Redshift
  • DynamoDB
  • OpenSearch
  • Kinesis Data Streams
  • DocumentDB

Migration types:

  • homogeneous - databases of the same type
  • heterogeneous - databases of different types; first Schema Conversion Tool is used to convert source schema and code, then migration of the data takes place

Can also be used for development and test database migrations (copy production data for testing), database consolidation (many dbs into one), continuous database replication.

ElastiCache

Caching layer for databases - Memcached or Redis flavor.

Patterns:

  • lazy loading - all read data is cached, can become stale
  • write through - add or update data in cache when it is written to database
  • session store - store temporary data with TTL

Uses cases:

  • stateless application - save user session data
  • reduce read intensive load from database - check cache first, if data is there (cache hit), retrieve it, if not (cache miss), retrieve from database and save in cache

Redis

Features:

  • multi-AZ with auto-failover
  • read replicas
  • data durability using AOF persistence
  • backup and restore
  • SSL

Sorted sets - guarantees uniqueness and element ordering; each time new element is added is it ranked in real time. Applications can avoid implementing ordering logic and use Redis's feature.

Auth - enable password/token authentication.

Typical use case is a session data of a website.

Memcached

Great for basic object caching. Scales horizontally, but doesn't provide persistence, multi-AZ or failover.

Features:

  • multi-node architecture with sharding of data
  • multi-threaded

Provides SASL-based authentication.

Other services

Database solutions:

DocumentDB

MongoDB compatible NoSQL database.

Good for content management, catalogs, or user profiles

Neptune

Graph database.

Good for social networking and recommendation engines

Quantum Ledger database

Immutable ledger.

Generally used for supply chain, banking, financial records.

Managed BlockChain

SimpleDB

NoSQL database.

Generally used for smaller workloads. Provides flexible queries, as it is automatically indexes all item attributes.

Limitation compared to DynamoDB:

  • storage up to 10 GB
  • limited request capacity
  • managing partitioning for additional space is on customer

OpenSearch

Managed ElasticSearch. Comes with Kibana and LogStash forming ELK stack.

MemoryDB for Redis

Massively scalable in-memory database (GB to 100TB). Multi-AZ, includes transaction log. Can be used as a primary database instead of database plus a cache layer. Provides ultra-fast performance.

Use cases:

  • Ultra-fast Redis compatible workloads
  • High performance apps
  • Data intensive, low-latency apps
  • Highly scalable microservices architecture