Storage: Choosing Where Your Data Lives

Every application stores data. The question is not whether to store it, but where, how, and why — because every storage technology makes a different set of trade-offs between consistency, scalability, query flexibility, and cost. Choosing the wrong one for the wrong workload doesn't break your app today, but it creates a ceiling on what your app can do tomorrow.

There are three fundamental categories of storage that appear in virtually every production system:

• Relational databases (SQL): Structured data with relationships, transactions, and arbitrary queries — your source of truth.
• NoSQL databases: A broad family — document databases (MongoDB) offer flexible schemas and rich queries; key-value and wide-column stores (DynamoDB, Cassandra) optimize for extreme write throughput on known access patterns.
• Object storage: Cheap, durable, effectively unlimited storage for binary files — images, videos, backups, and AI model artifacts.

AI agents will generate database code readily, but they make specific, consistent choices that may not match your needs. They usually default to PostgreSQL for everything, store file bytes in the database instead of object storage, and rarely recommend NoSQL unless you explicitly ask. Understanding these three categories — and when each applies — is what allows you to set the right constraints before prompting, so the AI builds the data layer you actually need.

Relational Databases (SQL)#

A relational database organizes data into tables of rows and columns, with explicit relationships between tables enforced through foreign keys. The defining feature is ACID compliance — a set of guarantees that make relational databases the default choice for any data where correctness matters more than raw speed.

ACID: The Four Guarantees That Make SQL Trustworthy#

ACID is not a single feature — it is four separate guarantees that relational databases provide together. Understanding each one explains why SQL is the default for data where correctness matters.

PostgreSQL implements isolation through MVCC (Multi-Version Concurrency Control): instead of locking rows, the database retains multiple historical versions of each row. When a transaction starts, it receives a consistent snapshot of the database as it existed at that moment, and reads from that snapshot for its entire lifetime — even while other transactions are writing new versions of the same rows. Readers never block writers, and writers never block readers. This is why PostgreSQL handles high-concurrency workloads efficiently without the lock contention you would see in a naive locking approach.

PostgreSQL vs MySQL#

Both are mature, production-proven relational databases. In practice, most new projects should start with PostgreSQL — the industry has largely converged on this recommendation. But the choice matters if you are extending an existing system.

The practical rule: Start with PostgreSQL. Its richer feature set, better MVCC implementation, and support for pgvector (which lets you run vector similarity search in the same database as your relational data, avoiding a separate vector DB) make it the correct default in 2026. Switch to MySQL only when you are extending an existing MySQL system or must integrate with a MySQL-specific ecosystem.

Indexes: How Queries Stay Fast#

An index is a separate data structure that the database maintains alongside your table to make specific queries faster. Without an index, finding a user by email means reading every row in the users table — a full table scan that takes O(n) time. With a B-tree index on email, the database jumps directly to the matching row in O(log n) time.

Indexes come at a cost: every INSERT, UPDATE, or DELETE must also update the index. Read-heavy tables benefit greatly from indexes; write-heavy tables pay a higher overhead for each index maintained.

The most common AI-generated index mistake: AI agents create foreign key columns (user_id, order_id) but frequently omit the index on them. When you run SELECT * FROM orders WHERE user_id = 42, PostgreSQL performs a full scan of the orders table unless there is an index on user_id. Always add indexes on every foreign key column and every column used in WHERE or ORDER BY clauses.

NoSQL Databases#

NoSQL databases — "Not Only SQL" — is a broad family of databases that trade the relational model's query flexibility for horizontal scalability and predictable low latency. The umbrella covers meaningfully different database types: key-value and wide-column stores (DynamoDB, Cassandra) that optimize for extreme write throughput on known access patterns, and document databases (MongoDB) that offer flexible schemas and richer queries while still scaling horizontally. What they share is the absence of fixed schemas, SQL joins, and the single-primary write bottleneck of relational databases.

The key-value and wide-column stores (DynamoDB, Cassandra) split data into partitions — logical chunks of records that live on the same physical node. A partition key determines which node holds each record. Add more nodes, get more partitions, and write capacity grows proportionally.

The critical insight that beginners often miss: NoSQL does not eliminate the need for data modeling. For key-value stores it shifts the burden from schema design to access pattern design — you must define every query before designing the model. Document databases like MongoDB relax this constraint with secondary indexes and aggregation pipelines, but schema design decisions (what to embed vs. reference) still have significant performance consequences.

DynamoDB vs Cassandra#

The two dominant NoSQL databases in production today take different approaches to the same problem. DynamoDB is fully managed by AWS — you provision capacity, and AWS handles everything else. Cassandra is open-source and self-managed (or available through managed services like DataStax Astra) — you own the cluster, but you also own the control.

The partition key is the most important design decision in any NoSQL data model. It determines which physical node stores the record, and every query must include the partition key. If your query needs to filter by a field that isn't the partition key, you need a Global Secondary Index (DynamoDB) or a separate table with a different partition key (Cassandra). Getting the partition key wrong causes the hot partition problem: if the chosen field has very few unique values (for example, a status field with only "active" or "inactive"), the vast majority of writes land on the same one or two nodes while the rest sit idle — those nodes become a bottleneck, and the horizontal scaling advantage disappears entirely.

A common pattern in DynamoDB for e-commerce:

• Partition key: USER#<userId>
• Sort key: ORDER#<timestamp>

This co-locates all orders for a user on one partition and returns them in chronological order with a single query — no cross-partition scan needed.

MongoDB: Document Database#

MongoDB occupies a distinct position in the NoSQL landscape. While DynamoDB and Cassandra optimize for predictable key-based access at extreme write scale, MongoDB optimizes for developer flexibility and query richness — it gives you a document model with no fixed schema, secondary indexes on any field, and a rich aggregation pipeline for analytics, all without the rigid access-pattern-first discipline that DynamoDB requires. It sits between a relational database and a pure key-value store.

MongoDB stores data as BSON documents (Binary JSON) — field-value pairs with support for nested objects, arrays, and typed fields like Decimal128, ObjectId, and Date. Documents live in collections (analogous to tables) with no enforced schema: two documents in the same collection can have completely different fields. The document model maps directly to the objects in application code, eliminating the impedance mismatch between object-oriented code and relational tables.

DynamoDB vs Cassandra vs MongoDB#

The three dominant NoSQL databases cover different regions of the trade-off space. Choosing between them comes down to the shape of your data, the predictability of your access patterns, and the operational model your team can support.

The practical rule: If your team is on AWS and all access patterns are known and key-based, DynamoDB's zero-ops model and extreme scale make it the right choice. If you need massive write scale across multiple data centers without cloud lock-in, Cassandra. If your schema is evolving, your data is hierarchical, or you need rich queries beyond key-based lookups — without moving to a full relational database — MongoDB is the strongest fit.

With SQL, you design the schema first and write queries later. With NoSQL, you invert this: design your queries first, then build the schema around them.

This is why AI agents generate poor NoSQL schemas without guidance: they apply relational thinking (normalize the schema first, write queries later) to a system that requires the opposite approach. Before prompting an AI agent to generate DynamoDB or Cassandra code, write out every query your application needs to run — including the fields you filter by and the sort order — and include that list explicitly in your prompt.

Object Storage#

Object storage manages data as self-contained blobs — images, videos, audio files, database backups, log archives, AI model weights, training datasets — in a flat namespace indexed by a unique key. It is the cheapest, most durable, and most scalable way to store files, and it is the correct choice for any data that does not need to be queried by its contents.

Object Storage vs Block Storage vs File Storage#

Beginners often confuse the three storage types. They solve fundamentally different problems.

The database BLOB antipattern: A very common AI-generated mistake is storing file bytes as BLOB (Binary Large OBject) columns in a relational database. This works for small prototypes but causes real production problems: the database swells in size (affecting backup times, restore times, and the amount of RAM needed for the buffer cache), files cannot be served directly via CDN, and every file download passes through your application server and database — making both the bottleneck. The correct pattern is always: store the file bytes in object storage, and store only the object key — a short string like uploads/users/42/avatar.jpg — in the database column. The database row stays small, and the file can be served directly from S3 through a CDN without touching your application server at all.

S3 vs Google Cloud Storage (GCS)#

Both offer the same 11-nines durability guarantee and very similar feature sets. The choice is primarily driven by your cloud provider.

MinIO is worth knowing about: it is an open-source S3-compatible object store that runs on your own hardware or any cloud. Used when regulatory requirements prevent using cloud-managed storage, or for local development environments that need to replicate S3 behavior without network calls.

Choosing the Right Storage: A Decision Framework#

Most production systems use multiple storage technologies together — a pattern called polyglot persistence. The decision of where each piece of data lives should be driven by the data's shape, how it is accessed, and what consistency guarantees it requires.

The Most Common Architectural Pattern: SQL + Object Storage#

For applications with file uploads, the relational database and object storage always work together. The database stores metadata; object storage stores the bytes.

Prompting AI Agents for Storage#

AI agents make consistent, predictable storage decisions unless you constrain them upfront. Here is what they do by default — and what to specify instead.

Summary#

The storage decision is one of the hardest to reverse. Migrating a production database from SQL to NoSQL, or extracting file bytes out of a BLOB column into S3, requires careful data migration, service changes, and often downtime — all after the fact. Set the storage architecture before you prompt, and the AI agent will generate implementation code that fits the system you actually need. Skip this step, and you may spend weeks undoing what the AI built in minutes.

Sources:

• PostgreSQL vs MySQL 2025: Complete Database Comparison — Liquibase
• PostgreSQL vs MySQL: Which Database Should You Choose in 2026? — Bytebase
• Cassandra vs DynamoDB 2026: Complete Guide — Knowi
• DynamoDB vs Cassandra — Bytebase
• The What, Why, and When of Single-Table Design with DynamoDB — Alex DeBrie
• AWS S3 vs Google Cloud Storage Comparison — AutoMQ
• Sharing objects with presigned URLs — AWS Documentation
• SQL vs NoSQL: Choosing the Right Database — ByteByteGo
• Top 5 NoSQL Database Myths — DataStax
• It's 2026, Just Use Postgres — Tiger Data
• Best Database Solutions for AI Agents (2025) — Fast.io
• Understanding Postgres GIN Indexes — pganalyze
• Apache Cassandra Case Studies — cassandra.apache.org
• MongoDB Sharding Documentation — mongodb.com
• MongoDB Schema Design Anti-Patterns — mongodb.com
• MongoDB ACID Transactions — mongodb.com
• MongoDB Atlas Vector Search — mongodb.com
• NoSQL Labyrinth: DynamoDB, Cassandra, MongoDB, Redis — Medium

• DynamoDB vs MongoDB vs Cassandra for Geo-Distributed Apps — Yugabyte