Architecture · Reference
Database selection, five models sorted honestly
Relational, document, key-value, edge/SQLite and vector databases each optimise for a different shape of data and a different failure mode. The ledger below sorts and filters on consistency, strengths, where each one hurts, and how hard it is to migrate away from later — including an honest answer for when the "boring" relational choice is correct, which is most of the time.
DeployCloud's own guide to choosing a database walks the SQL/NoSQL/edge decision in plain prose; this page adds the sortable ledger and brings vector databases into the comparison.
The honest default
Start relational, and require a reason to leave
Most application data has entities that relate to each other — users have orders, orders have line items, posts have comments. That shape is exactly what relational databases were built for.
PostgreSQL in particular has become a reasonable one-database-does-most-of-it default: it handles strictly relational data, semi-structured JSON documents (via its native jsonb type), full-text search, and — via the widely-used pgvector extension — vector similarity search, all inside one system you already know how to operate, back up and migrate. That consolidation matters: every additional database type in your stack is another thing to secure, monitor, back up and staff for. The other four rows in the ledger below are genuinely useful, but each is a deliberate trade of that simplicity for a specific capability — read the "where it hurts" column before reaching for one on a hunch.
The ledger
Five models, sorted your way
Click any column to sort; type to filter. "Migration difficulty" means the effort to move away later, not the effort to adopt it today.
| Type | Consistency model | What it's good at | Where it hurts | Migration difficulty |
|---|---|---|---|---|
| Relational (PostgreSQL, MySQL) | Strong, ACID transactions across rows and tables by default | Related, structured data; complex joins; anything needing transactional integrity (payments, inventory counts) | Very high write throughput sharded across many independent nodes; deeply nested, wildly variable-shape records | Moderate — schema migrations are a decades-old, well-tooled discipline (versioned, reviewable, reversible) |
| Document (MongoDB) | Strong on single-document operations; tunable read/write concerns across documents | Records whose shape varies a lot between entries; content catalogs; rapid schema iteration during early development | Enforcing integrity or complex joins across collections — that logic moves into application code instead of the database | Low to add new fields day-to-day; harder to retroactively enforce or migrate structure across existing documents |
| Key-value (Redis, DynamoDB, Workers KV) | Simple point lookups are typically strongly consistent per key; DynamoDB offers tunable eventual or strong reads | Extremely fast reads/writes by a known key — caching, sessions, feature flags, rate-limit counters | Any query by something other than the key — no joins, no ad-hoc filtering without a secondary index designed in advance | Low structurally (no schema to migrate) but access patterns must be designed up front, especially on DynamoDB |
| Edge / SQLite (Cloudflare D1, Turso) | Standard SQLite semantics; single primary writer per database, with read replicas available on some platforms | Colocating data with edge compute; small-to-medium relational workloads that benefit from low operational overhead | Very high write concurrency against one database, or datasets much larger than a single SQLite file is comfortable with | Familiar SQL migrations; Cloudflare documents point-in-time recovery for D1 to any minute within the last 30 days |
| Vector (pgvector, Pinecone, Vectorize) | Typically eventually consistent for index updates in dedicated vector databases | Similarity/semantic search over embeddings — RAG, recommendations, "find things like this" | Exact-match transactional workloads and relational integrity — it answers a fundamentally different question than SQL | A genuinely new discipline (embeddings need regenerating when the model changes); pgvector keeps it inside a familiar Postgres migration workflow |
No benchmark or throughput column on purpose — see the FAQ for why, and the sources ledger for each vendor's own current, verifiable numbers.
A distinction worth keeping
"NoSQL" is not one thing — document, key-value and vector solve different problems
Grouping MongoDB, Redis and Pinecone together as "NoSQL" hides more than it reveals; each rejects a different part of the relational model for a different reason.
A document database rejects a fixed schema — every record can look different, which suits catalogs and content where the shape genuinely varies. A key-value store rejects query flexibility entirely in exchange for speed — you get one operation, "fetch by key," done as fast as physically possible, which is exactly right for a cache or a session store and exactly wrong for anything you need to filter or aggregate. A vector database rejects exact matching as the primary operation — it answers "what's similar to this," a question SQL's equality and range operators were never built to ask. Knowing which constraint each model relaxes, rather than lumping them under one "NoSQL" umbrella, is what actually predicts whether it fits your workload.
Sources
- PostgreSQL Global Development Group, official documentation — jsonb, transactions and extension support. postgresql.org/docs/. Accessed 26 Jul 2026.
- pgvector project, README and reference — open-source vector similarity search extension for PostgreSQL. github.com/pgvector/pgvector. Accessed 26 Jul 2026.
- MongoDB, Inc., official manual — document data modelling and schema flexibility. mongodb.com/docs/manual/. Accessed 26 Jul 2026.
- Redis, official documentation — data types and in-memory key-value semantics. redis.io/docs/latest/. Accessed 26 Jul 2026.
- AWS, "What is Amazon DynamoDB?" — the vendor's own stated single-digit-millisecond performance claim, on-demand scale-to-zero with no cold starts, no native JOIN support, and continuous backups with point-in-time recovery up to 35 days. docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html. Accessed 26 Jul 2026.
- Cloudflare, "Cloudflare D1" overview and pricing — SQLite-compatible serverless database, Time Travel point-in-time recovery to any minute within 30 days. developers.cloudflare.com/d1/ and developers.cloudflare.com/workers/platform/pricing/#d1. Accessed 26 Jul 2026.
- Cloudflare, "Cloudflare Vectorize" overview — globally distributed vector database for embeddings, generally available. developers.cloudflare.com/vectorize/. Accessed 26 Jul 2026.
- DeployCloud, "Choosing a Database Guide" (this site) — the narrative, prose companion to this ledger. deploycloud.allfrontierglobal.com/choosing-a-database-guide.
FAQ