Databases
Overview
Databases are the persistence layer beneath most systems. Two broad families dominate: relational stores (tables, fixed schema, joins, strong ACID guarantees) and NoSQL stores such as document databases (flexible schema, denormalized/embedded data, horizontal scale). The right choice is a modeling and trade-off decision — normalization and join integrity on one side, read locality and scale-out on the other.
Relational Modeling
- ERD / EER — entities, relationships, and cardinality drive the schema.
- Keys — composite, super key, candidate key (a minimal super key), secondary key; entity integrity (unique row id) and referential integrity (FK is null or a valid value elsewhere).
- Normalization — 1NF (remove repeating groups, identify PK), 2NF (eliminate partial dependencies), 3NF (eliminate transitive dependencies). Reduces redundancy at the cost of more joins.
- Set operations —
UNION,INTERSECT,EXCEPTmirror mathematical set theory over query results.
Concepts in this domain
- NoSQL and Document Databases — MongoDB schema design, aggregation pipeline, replication, sharding
- Database Transactions — ACID, concurrency control, deadlock handling, crash recovery
- Query Optimization — indexing, covered queries, access plans, and profiling
- Vector Databases and pgvector in Production — IVFFlat vs HNSW, real-time indexing costs, pre- vs post-filtering, and when a dedicated engine wins
SQL vs Document — the modeling fork
| Relational | Document (MongoDB) | |
|---|---|---|
| Schema | Declared, enforced | Schema-less, per-document |
| Relationships | Joins + FK constraints | Embed (pre-join) or join in the application |
| Integrity | DB-enforced (referential) | Application-enforced |
| Scale | Vertical + read replicas | Horizontal via sharding |
| Best when | Complex relationships, strong consistency | Read locality, evolving shape, scale-out |
Embedding trades write duplication for read locality: you fetch the first bit with lower latency and the rest with less bandwidth, avoiding joins entirely.
Choosing a managed database on AWS
Every AWS option still falls into the two families above; the managed layer changes the operational cost, not the modeling fork. The hard limits are what usually decide it.
Relational — Aurora. Fully managed, PostgreSQL- and MySQL/MariaDB-compatible. Storage limit 128TB; table size 64TB (MySQL) or 32TB (Postgres).
Document stores. The item-size ceiling is the first thing to check, because it is the limit most likely to be hit by accident:
| Store | Ceiling | Choose when |
|---|---|---|
| DynamoDB | 400KB per item, storage unlimited in theory (transaction limits apply) | low-latency, high-throughput NoSQL at scale; document and key-value models |
| DocumentDB | 16MB per document, 32TB per collection, 64TB per database | you already run MongoDB and want the API, drivers and tools to keep working |
| SimpleDB | 10GB per domain | small amounts of structured data needing simple query and indexing |
DynamoDB’s 400KB is large enough for most operations but is far lower than its peers — MongoDB allows 16MB documents, Cassandra 2GB blobs, and Postgres rows up to 1.6TB (1600 columns × 1GB).
Graph — Neptune. For highly connected data, speaking Gremlin and SPARQL. Neo4j is the market comparison, and AWS publishes a migration guide from it.
Column-oriented / warehouse — Redshift. Columnar with massively parallel processing across nodes, plus encryption, network isolation and fine-grained access control. One sharp limitation: no updates or deletes on tables with a sort key, which can force a schema redesign. If you need a column store or a warehouse, this is the one.
Key-value and in-memory. ElastiCache (Redis, Memcached) is a cache, not a durable store — lose it and you rebuild from the source, and it does not replicate across regions. MemoryDB is the durable, highly available in-memory database for sub-millisecond workloads (gaming, real-time bidding, finance), at the cost of less flexibility and limited regional availability. The rule: cache → ElastiCache; latency or real-time is the requirement → MemoryDB.
Not covered above: Timestream and the ledger database. Other providers map closely — Oracle’s JSON database is near-equivalent to DocumentDB.
Relationships
- Query Optimization — indexing strategy is the highest-leverage database performance lever
- Data Warehousing and Business Intelligence — the analytical (OLAP) counterpart to transactional databases
- Dimensional Modeling — star/snowflake schemas and SCD for analytical data
- System Design — data store choice is a core scalability decision
- Microservices — database-per-service and polyglot persistence
- Software Architecture & Distributed Systems — replication and sharding are distributed-systems trade-offs
- Cloud & AWS Infrastructure — the managed services these options sit inside
- Hexagonal Architecture — an outbound port is what makes swapping between these cheap
References
- MongoDB notes — schema design, aggregation, performance, application engineering