Database Services

Database Services

A checkout needs an atomic order. A product page needs a lookup by ID. A fraud investigation follows relationships, while a monthly report scans many records. “Managed database” does not make these workloads interchangeable: choose the data model, consistency and recovery contract before choosing a service.

This lesson develops those choices through four applications: checkout, regional profiles, Aurora Serverless capacity, and read scaling. Examples assume current boto3, prepared IAM/network access and service availability in the selected Region. SQL examples use SQLAlchemy 2.x and PostgreSQL. Resource creation is asynchronous; illustrative names and deployment parameters must be replaced deliberately.

1. Match the database to the operation

RDS manages infrastructure and supported backup/maintenance operations; it does not remove schema design, query tuning, connection budgeting, access control, upgrade planning or restore testing. Its engines include PostgreSQL, MySQL, MariaDB, Oracle, SQL Server and Db2. Aurora offers MySQL- and PostgreSQL-compatible engines with a different storage architecture. Compatibility is not a promise that every upstream extension or behavior exists. RDS overview, Aurora overview.

For specialized services, avoid memorizing a stale version table. DocumentDB has its own compatibility matrix and functional differences. Neptune supports property graphs with Gremlin/openCypher and RDF with SPARQL; a query for two outgoing knows edges can include the starting person or direct friends unless explicitly excluded. Keyspaces is Cassandra-compatible, not a customer-managed Cassandra cluster. DocumentDB differences, Neptune engines, Keyspaces.

Service availability is part of selection. QLDB shut down on July 31, 2025; it is not a service to select for a new audit-log architecture. Timestream for LiveAnalytics closed to new customers on June 20, 2025; existing-customer eligibility is distinct from Timestream for InfluxDB. A replacement must be evaluated against the actual retention, query and verification requirements, not a “1,000× faster” slogan. QLDB notice, LiveAnalytics availability, Timestream for InfluxDB.

2. Availability, read scaling and backup are different jobs

Do not label every Multi-AZ deployment “synchronous with an unreadable standby.” Nor does Multi-AZ alone address a whole-Region outage or restore an accidentally deleted row. Clients need reconnect/retry logic, and recovery must distinguish a transaction that failed from one that committed before the response was lost. Multi-AZ instance, Multi-AZ cluster, read replicas.

Conventional RDS Multi-AZ DB instance:
  app reads/writes -> primary in AZ A --sync--> standby in AZ B
  reporting reads -> separate read replica <--async-- primary

Aurora regional cluster:
  app writes           -> writer  --> shared cluster volume across 3 AZs
  stale-tolerant reads -> readers --> the same shared cluster volume

The arrows describe application requests and replication separately. An Aurora regional cluster normally supports up to fifteen Aurora Replicas alongside its writer. “All other RDS engines allow only five” is false: RDS for PostgreSQL, for example, supports fifteen direct read replicas within a Region. Check the engine, topology and account quotas before designing a fanout. Aurora replication, PostgreSQL replicas.

For an RDS DB instance, automated-backup retention is 0–35 days; zero disables it. A Multi-AZ DB cluster uses 1–35 days. Manual snapshots follow separate retention and billing rules. Point-in-time restoration creates a separate database resource; the application must validate it and switch connections deliberately. A backup window is not a promise of an exact recovery time. Retention, point-in-time restore.

3. Aurora: separate storage capacity from compute

Aurora's writer and readers share a distributed cluster volume. Adding a reader does not require a separate complete logical copy of the database. The storage replication layout does not depend on having six database instances. Storage grows automatically, but its limit is engine/version-dependent: supported versions can reach 256 TiB, while earlier versions have lower limits. Do not teach 128 TB as a universal ceiling or confuse decimal TB with binary TiB. Aurora storage, size limits.

An advertised “up to 5× MySQL” or “3× PostgreSQL” result is a benchmark comparison, not a multiplier for every query. Compare representative workloads, query plans, connection pressure and the whole bill. Storage, backup retention beyond allowances, replicas, I/O where applicable and data transfer can contribute costs; Aurora backup storage is not universally free. Aurora pricing.

Aurora Global Database uses a primary Region for writes and secondary Regions for regional reads and recovery. Current configurations support up to ten secondary Regions, subject to engine/version requirements. Typical subsecond replication is not a universal one-second RPO. A switchover or failover procedure must also account for endpoints, client reconnection, permissions, outstanding writes and application verification. Global Database.

Aurora Serverless v2 scales compute for each db.serverless instance within a cluster-level ACU range. One ACU represents approximately 2 GiB of memory plus associated CPU/networking; it is not a fixed vCPU count. Current supported combinations can scale from zero to 256 ACUs in half-ACU increments, but engine/platform versions affect the allowed range. Zero requires supported auto-pause behavior; a positive minimum continues to incur compute usage while idle. Storage and other charges remain even when compute pauses. Requirements, capacity planning.

4. DynamoDB: model the questions before the items

A table can have a simple partition key or a composite partition-and-sort key. With a composite key, the pair identifies an item; the sort key orders items within one partition-key value. A field named Timestamp is not automatically a key: the declared key schema decides that.

For OrdersByCustomer, suppose customerId is a string partition key and orderId a string sort key. GetItem needs both. Query requires equality on one partition-key value and can add a sort-key condition. It reads matching key data before applying any FilterExpression. Scan walks the table or index; its filtering also occurs in the service after reading, not in the client. Both operations paginate, and a page may contain no returned items while still having a LastEvaluatedKey. Query, Scan.

from boto3.dynamodb.conditions import Key

def customer_orders(table, customer_id):
    params = {
        "KeyConditionExpression": Key("customerId").eq(customer_id),
        "ConsistentRead": True
    }
    while True:
        page = table.query(**params)
        yield from page.get("Items", [])
        last_key = page.get("LastEvaluatedKey")
        if not last_key:
            break
        params["ExclusiveStartKey"] = last_key

This example uses base-table strong reads. Each request reflects writes completed before that read; the entire paginated traversal is not an atomic snapshot. GSIs support eventual reads only, while base tables and LSIs can support strong reads. GSIs offer alternate key schemas, but a GSI Query still needs its partition-key value. LSIs must be created with the table and have additional item-collection limits. Read consistency, secondary indexes.

On-demand billing uses PAY_PER_REQUEST; provisioned mode specifies capacity and can use auto scaling. Neither is an unconditional cheapest choice, and on-demand is not infinite instantaneous capacity. Hot keys, configured maximum throughput, warm throughput, service quotas and abrupt growth still matter. Choose keys for both traffic distribution and access patterns; replacing every key with a random ID can make required queries impossible without another index. Capacity modes, partition-key design.

For an ordinary nontransactional single-item read, one RCU supports one strongly consistent read per second of up to 4 KiB, or two eventual reads. A 6-KiB item therefore needs two RCUs per strong read/second, or one per eventual read/second. One WCU supports one nontransactional write per second of up to 1 KiB, so a 1.2-KiB item write requires two WCUs. Transactions, indexes, batches and minimum charges have their own rules; filtering is not a way to avoid reading cost. Read/write operations.

DynamoDB encrypts all table data at rest by default. Selecting a customer-managed KMS key changes key control and permissions; omitting that selection does not create an unencrypted table. Boto3 resource items use native Python types, but numbers should use integers or Decimal, not unsupported native floats. Encryption, boto3 data types.

5. Application: commit a checkout as one transaction

Start with a relational source of truth for the order and its lines. A DynamoDB product catalog, cache or graph recommendation system can be added for a measured access need, but none should silently become the authority for payment or stock. A cache hit is not proof that an item is still available at the quoted price.

Prepare these PostgreSQL tables with a schema migration. Amounts are integer cents in one explicitly chosen currency; prices are resolved by trusted server code before this function runs:

CREATE TABLE orders (
    order_id VARCHAR(64) PRIMARY KEY,
    customer_id VARCHAR(64) NOT NULL,
    total_cents BIGINT NOT NULL CHECK (total_cents >= 0)
);
CREATE TABLE order_lines (
    order_id VARCHAR(64) NOT NULL REFERENCES orders(order_id),
    line_no INTEGER NOT NULL,
    product_id VARCHAR(64) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_cents BIGINT NOT NULL CHECK (unit_cents >= 0),
    PRIMARY KEY (order_id, line_no)
);

from sqlalchemy import text

def place_order(engine, order_id, customer_id, lines):
    # lines are server-priced (product_id, quantity, unit_cents) tuples.
    if not lines or len(lines) > 100:
        raise ValueError("An order needs 1–100 lines")
    if not all(isinstance(x, str) and 1 <= len(x) <= 64
               for x in (order_id, customer_id)):
        raise ValueError("Invalid order/customer ID")
    for product_id, quantity, cents in lines:
        if not isinstance(product_id, str) or not 1 <= len(product_id) <= 64:
            raise ValueError("Invalid product ID")
        if type(quantity) is not int or not 1 <= quantity <= 1000:
            raise ValueError("Invalid quantity")
        if type(cents) is not int or not 0 <= cents <= 10**9:
            raise ValueError("Invalid unit cents")
    total = sum(q * c for _, q, c in lines)
    with engine.begin() as conn:
        conn.execute(text("INSERT INTO orders VALUES (:oid, :cid, :total)"),
                     {"oid": order_id, "cid": customer_id, "total": total})
        conn.execute(text("""INSERT INTO order_lines
            (order_id, line_no, product_id, quantity, unit_cents)
            VALUES (:oid, :n, :pid, :q, :c)"""),
            [{"oid": order_id, "n": n, "pid": p, "q": q, "c": c}
             for n, (p, q, c) in enumerate(lines, 1)])
    return total

The context commits on success and rolls back when an exception escapes. Parameter binding separates values from SQL syntax. For two units at 1,299 cents and one at 550 cents, the total is 3,148 cents, not the sum of unit prices alone. SQLAlchemy transactions.

The primary key prevents two order rows with the same ID, but this function does not implement a complete idempotency protocol. After a lost commit response, retrieve the order by the stable request ID and compare its complete intent before deciding whether to retry. Do not charge again merely because the client saw a timeout. Payment, stock reservation and cross-service notifications need their own durable workflow/outbox; the SQL transaction does not span them. Transactional outbox.

6. Application: regional profiles with a stated consistency contract

Global tables support multi-Region eventual consistency (MREC) and multi-Region strong consistency (MRSC), with different topology, feature and Region constraints. MREC uses asynchronous replication and conflict resolution; it does not promise every Region sees a write in under a second. A strong local read from an MREC replica is not a guarantee that a remote write has arrived. MRSC must be selected and designed deliberately rather than assumed from the name “global table.” Global-table behavior.

For this same-account MREC example, start with an existing ACTIVE current-version table in us-east-1, key userId, and Streams enabled with NEW_AND_OLD_IMAGES. The destination Region must be supported, permissions/quotas must permit replica creation, and there must be no conflicting destination table. Add one replica and wait for both the table and target replica to become ACTIVE:

import time
import boto3

def add_profile_replica(table_name, target_region, timeout=1200):
    client = boto3.client("dynamodb", region_name="us-east-1")
    client.update_table(
        TableName=table_name,
        ReplicaUpdates=[{"Create": {"RegionName": target_region}}])
    deadline = time.monotonic() + timeout
    while time.monotonic() < deadline:
        table = client.describe_table(TableName=table_name)["Table"]
        target = next((r for r in table.get("Replicas", [])
                       if r["RegionName"] == target_region), None)
        if (table["TableStatus"] == "ACTIVE" and target is not None
                and target.get("ReplicaStatus") == "ACTIVE"):
            return target
        time.sleep(5)
    raise TimeoutError("Replica not ready; inspect status before retrying creation")

This is provisioning logic, not a request handler. A timeout does not undo the operation; inspect the live table before resuming. Add any further replica only after the previous operation is ready. UpdateTable, current-version tutorials.

After readiness, a client in the target Region can write a profile. All applications must agree on the key and conflict policy. Two Regions concurrently updating one preference can overwrite one another under MREC conflict resolution; a condition checked in one Region is not a global lock. Options include assigning an authoritative write Region per user or redesigning the domain around independently mergeable changes. Measure replication lag, and practice routing writes and reads during a regional failure.

7. Application: create usable Aurora Serverless v2 capacity

Setting ServerlessV2ScalingConfiguration on a cluster is not enough: the cluster also needs a db.serverless DB instance. The following creates one writer with a chosen range of 1–8 ACUs. Supply an Aurora PostgreSQL version that supports Serverless v2 in the selected Region, an existing suitable DB subnet group, and a security group permitting only required clients. The caller needs the required RDS, KMS and Secrets Manager permissions.

import boto3

def create_serverless(region, engine_version, subnet_group, security_group):
    rds = boto3.client("rds", region_name=region)
    cluster_id = "example-serverless-orders"
    rds.create_db_cluster(
        DBClusterIdentifier=cluster_id,
        Engine="aurora-postgresql", EngineMode="provisioned",
        EngineVersion=engine_version,
        MasterUsername="dbowner", ManageMasterUserPassword=True,
        StorageEncrypted=True, BackupRetentionPeriod=7,
        DBSubnetGroupName=subnet_group,
        VpcSecurityGroupIds=[security_group],
        ServerlessV2ScalingConfiguration={"MinCapacity": 1, "MaxCapacity": 8})
    rds.get_waiter("db_cluster_available").wait(
        DBClusterIdentifier=cluster_id,
        WaiterConfig={"Delay": 15, "MaxAttempts": 80})
    rds.create_db_instance(
        DBInstanceIdentifier=cluster_id + "-writer",
        DBClusterIdentifier=cluster_id, Engine="aurora-postgresql",
        DBInstanceClass="db.serverless", PubliclyAccessible=False)
    rds.get_waiter("db_instance_available").wait(
        DBInstanceIdentifier=cluster_id + "-writer",
        WaiterConfig={"Delay": 15, "MaxAttempts": 80})
    return rds.describe_db_clusters(
        DBClusterIdentifier=cluster_id)["DBClusters"][0]["Endpoint"]

For Serverless v2, EngineMode="provisioned" is correct; serverless refers to the older v1 mode. The password is managed through Secrets Manager, not embedded in lesson code. Retrieve credentials through the application's approved mechanism and use a least-privilege application account with TLS. Creation procedure, managed passwords.

This creates one compute instance, not a separate failover reader. Its 1-ACU minimum does not auto-pause. If two instances each have this range, each can consume 1–8 ACUs: the cluster range is not a shared eight-ACU budget. One instance averaging four ACUs for three hours uses 12 ACU-hours, before storage/I/O/other charges. A maximum can constrain performance; a minimum affects idle cost and scale-up behavior. Test representative bursts and connection counts. Creation failures can leave billable resources; inspect and reconcile the named resources before retrying or cleaning up.

8. Application: scale reads without breaking confirmation screens

An order-confirmation request immediately after commit should read the writer through an appropriate fresh transaction, not a randomly chosen asynchronous replica. A reporting page may tolerate lag and use a replica. Aurora's reader endpoint balances connections, not every SQL statement; a long-lived pool can keep using existing connections. Ordinary RDS read replicas have their own endpoints and engine-specific limits. Aurora reader endpoint.

Create reusable SQLAlchemy engines for the writer and chosen reader endpoints, with credentials provided securely. URL.create handles special characters in passwords without manual URL concatenation:

from sqlalchemy import create_engine, URL, text

def db_engine(host, username, password, database, ca_file):
    return create_engine(
        URL.create("postgresql+psycopg", username=username, password=password,
                   host=host, database=database),
        connect_args={"sslmode": "verify-full", "sslrootcert": ca_file},
        pool_size=5, max_overflow=5, pool_timeout=2, pool_pre_ping=True)

def read_order(writer, reader, customer_id, order_id, require_fresh=False):
    selected = writer if require_fresh else reader
    with selected.connect() as conn:
        row = conn.execute(text("""SELECT order_id, customer_id, total_cents
            FROM orders WHERE customer_id = :cid AND order_id = :oid"""),
            {"cid": customer_id, "oid": order_id}).mappings().first()
        return dict(row) if row is not None else None

Authorize customer_id from the authenticated identity; do not trust an arbitrary URL parameter. A pooled connection is returned to the pool when its context exits. Pre-ping detects stale connections on checkout but does not replay a transaction interrupted by failover. With these settings, one engine can open up to ten connections; four worker processes each owning writer and reader engines can reach 80 aggregate connections, up to forty toward each endpoint. Budget against each database's limit and other clients. SQLAlchemy pooling, PostgreSQL TLS.

Before adding replicas, inspect plans, indexes, slow queries and transaction duration. Reader lag can invalidate a report's freshness assumptions. Database Insights and CloudWatch provide evidence, but an alarm should monitor the intended metric and notify a real action target. UserErrors is not a DynamoDB throttling counter:

import boto3

def alarm_on_read_throttles(table_name, topic_arn):
    cw = boto3.client("cloudwatch", region_name="us-east-1")
    return cw.put_metric_alarm(
        AlarmName=table_name + "-ReadThrottles", Namespace="AWS/DynamoDB",
        MetricName="ReadThrottleEvents", Dimensions=[
            {"Name": "TableName", "Value": table_name}],
        Statistic="Sum", Period=60, EvaluationPeriods=1,
        Threshold=0, ComparisonOperator="GreaterThanThreshold",
        TreatMissingData="ignore", AlarmActions=[topic_arn])

The SNS topic must be valid in the chosen Region and configured to deliver notifications. This is a table-read alarm, not coverage of every index/write throttling mode. This example explicitly ignores missing data. When missing-data treatment is needed, the alarm retains its prior state, which can leave an earlier ALARM active during an idle period. Absence of metrics is not evidence that throttling stopped; validate clearing behavior and monitor telemetry separately. Monitor writes, indexes, latency, capacity and replication separately as appropriate. DynamoDB metrics, alarm actions, missing-data evaluation.

9. Keep cache and recovery promises explicit

ElastiCache supports Valkey, Redis OSS and Memcached. Valkey/Redis OSS support structures such as sorted sets and hashes; Memcached is a simpler key/value cache, including serialized bytes. Upstream Redis persistence options are not automatically ElastiCache features. Current node-based Valkey also has optional durability through a Multi-AZ transactional log with version/deployment constraints; snapshots, replication and durable acknowledgment are different mechanisms. The next lesson develops cache-aside, DAX consistency, session expiration and safe refill coordination. Engine comparison, durability.

A database write followed by a cache write has a failure gap. A read that fetched old data can also repopulate the cache after invalidation. Neither sequence alone keeps a cache “always current.” For authoritative decisions, use the database contract or a versioned/validated design. Caching strategies.

For a final design exercise, specify one read that can be stale and one that cannot; identify its key/index, writer or reader endpoint, and recovery behavior. Then state an RPO and RTO and test them. A managed service reduces operational work, but it does not turn a performance slogan, cache TTL or backup checkbox into an application guarantee.