DEV Community

Code Green
Code Green

Posted on

NoSQL vs. SQL Databases

#aws #dynamodb

DynamoDB vs. Traditional SQL Databases

Characteristic DynamoDB (NoSQL) Relational SQL DB (e.g., PostgreSQL, MySQL)
Data model Key‑value + document; items are schemaless. Fixed schema with tables, rows, columns, data types.
Query language Primary‑key lookups, secondary indexes; limited ad‑hoc queries. Full‑featured SQL (joins, aggregates, sub‑queries).
Scalability Horizontal scaling automatically; virtually unlimited throughput. Typically vertical scaling; sharding/partitioning is manual.
Consistency Configurable (eventual or strong per‑item). Strong ACID consistency by default.
Transactions Up to 25 items per transaction (ACID). Multi‑row, multi‑table ACID transactions are native.
Joins Not supported; denormalize or use secondary indexes. Native joins across tables.
Performance model Predictable O(1) reads/writes when accessed by PK/SK. Performance depends on indexes, query plans, and data size.
Cost model Pay‑per‑request (on‑demand) or provisioned capacity; no licensing. Usually per‑instance (CPU, RAM, storage) plus licensing.

Primary Key (PK) and Sort Key (SK) in DynamoDB

Concept Description
Partition Key (PK) Hashes the key value to determine the physical partition that stores the item. All items with the same PK reside on the same partition and are co‑located.
Sort Key (SK) (optional) Within a partition, items are ordered by the SK value. Enables range queries (BETWEEN, begins_with) on items that share the same PK.
Composite Primary Key Combination of PK + SK uniquely identifies an item. The PK alone can also be a primary key (no SK).

Why PK/SK Matter

  • Fast lookups: GetItem or Query on PK (and optional SK) is O(1) because DynamoDB knows exactly which partition to read.
  • Data modeling: By choosing PK/SK wisely you can represent one‑to‑many or many‑to‑many relationships in a single table (single‑table design).
  • Access patterns: All queries that share the same PK are served from the same partition, giving low latency and high throughput for that access pattern.

Comparison with a Traditional SQL Primary Key

Aspect DynamoDB PK/SK SQL Primary Key
Uniqueness PK alone must be unique; PK + SK together must be unique. Single column or composite column(s) must be unique across the whole table.
Physical placement PK determines the physical partition; SK only orders items within that partition. No concept of physical partitioning at the key level (unless using sharding).
Range queries SK enables efficient range queries only within the same PK. SQL primary key (or any indexed column) can be used for range queries across the whole table.
Joins Not supported; relationships are expressed by embedding related data or using PK/SK patterns. Primary keys are used to join tables (JOIN … ON pk = fk).
Schema flexibility Items with the same PK can have different attributes; SK does not enforce a schema. All rows must conform to the table’s column definitions.
Scalability impact Adding more items with the same PK can create a hot partition; you may need to shard the PK. Adding rows does not affect partitioning unless you manually shard; indexes handle scaling.

Bottom line: DynamoDB’s PK/SK model is a distribution and ordering mechanism optimized for massive scale and predictable latency, whereas a SQL primary key is primarily a uniqueness constraint used for relational integrity and joins. The choice of PK and SK drives your access patterns and performance in DynamoDB, while in SQL you design primary keys to support relational queries and referential integrity.

Top comments (0)