[Data Architecture] Data Store Selection

About this article

As the second installment of the “Data Architecture” category in the series “Architecture Crash Course for the Generative-AI Era,” this article explains data store selection.

In 2026, using multiple stores per app for different use cases has become normal. This article specializes in app-level detailed selection, presenting the strengths and weaknesses of RDB/KVS/document/columnar/time-series/search/vector and a phased matrix by data volume x use case (the system-wide placement strategy lives in the separate “System Architecture” article).

What is data store selection in the first place

Data store selection is “deciding which type of database to entrust this app’s data to.”

Imagine a toolbox. A screwdriver for screws, a hammer for nails, a saw for cutting wood - no single all-purpose tool handles every job. Databases are the same: an RDB for “accurate transaction records,” a KVS for “high-speed responses to massive access,” and a search engine for “full-text search.” The optimal tool differs by use case. Modern apps routinely combine multiple data stores, and how you pick them determines the speed, extensibility, and development efficiency of the entire application.

Why data store selection matters

What happens if you pick wrong? A bad choice directly leads to fundamental problems: no speed, no scale, dropping dev efficiency. And once operations begin, data migration is extremely high-cost - “changing later is practically impossible” as a decision.

Data stores are the foundation of all application design. Without a settled DB, entity design, API design, and transaction boundaries can’t be settled. Furthermore, data stores are tightly coupled to cloud vendors - even between “relatively close products” like AWS Aurora to GCP Cloud SQL, the migration bar is high.

“For now, RDB” is correct in many cases. But knowing the exceptions is the architect’s job.

The main categories, and a quick decision flow

Here is the flow for narrowing it down quickly.

Going down the questions in order, your top candidate gets pinned for your use case.

ACID integrity required?
  └ Yes → RDB (PostgreSQL)
  └ No  → ultra-fast on a single key?
            └ Yes → KVS (Redis/DynamoDB)
            └ No  → schema fluctuates?
                      └ Yes → Document DB
                      └ No  → mass-data aggregation/analysis?
                                └ Yes → Columnar DB (BigQuery/Snowflake)
                                └ No  → similarity search/AI embeddings?
                                          └ Yes → Vector DB (pgvector)
                                          └ No  → full-text search?
                                                    └ Yes → OpenSearch
                                                    └ No  → Time-series DB

The default of PostgreSQL + cache (Redis) as needed in two layers covers 90% of systems.

Where each category fits

RDB — the only choice below mid-scale

The most traditional and powerful data store, handling row-and-column structured data via SQL. ACID integrity (Atomicity, Consistency, Isolation, Durability) is guaranteed, making it the standard for money calculations and inventory management - work that doesn’t tolerate mistakes. The flexibility of combining multiple tables with JOIN is unmatched by other DBs.

Weaknesses are “horizontal scaling (boosting performance by adding servers) is hard” and “schema changes are heavy,” but cloud-era managed RDBs (Aurora, Cloud SQL) have largely solved these, and at mid-scale and below, RDB is the only choice - safe to say.

Representatives: PostgreSQL, MySQL, SQL Server, Oracle

Without a special reason: PostgreSQL. It comes with full-text search, JSON, and geospatial as standard - very rare to be in trouble.

KVS — the standard cache layer

A DB specialized for the simple structure of getting one value from one key. In exchange for losing search and JOIN, it has sub-millisecond response and near-infinite horizontal scalability. It shines where “lots of simple lookups” arise - sessions, cache, rankings, rate limiting.

There are also “KVS usable as the main DB” like DynamoDB, but JOIN and transaction constraints are severe, so it’s unsuitable as the main DB for business systems. The standard pattern in many sites is to use Redis as a cache layer alongside RDB.

Representatives: Redis, Memcached, DynamoDB

For cache layer, Redis is the only choice. As main DB, DynamoDB is an option, but design carefully.

Document databases — mostly replaced by JSONB

A DB that stores JSON-format documents as is. Without pre-defining schemas, records with different structures can be stored - suitable for early phases with frequent spec changes or accumulating log data with undefined structure. The convenience of saving app-side objects directly is also attractive.

On the other hand, schemas tend to become vague, and problems like dropping data quality and JOIN-less, hard-to-design are likely - the larger the scale, the easier RDB becomes. After the MongoDB heyday of the 2010s, the modern trend is “substituting with PostgreSQL JSONB (JSON Binary, a JSON storage type with fast indexed search)” in growing numbers of cases.

Representatives: MongoDB, Firestore, CouchDB

The mainstream today is substituting with PostgreSQL’s JSONB type. Pure document DBs are seeing fewer use cases.

Columnar databases — analytics is BigQuery or Snowflake

A DB with structure specialized for analytical queries. Because it stores data column-wise rather than row-wise, aggregation like “summing sales over 10 million rows” runs orders of magnitude faster (seconds → milliseconds) - used as the heart of DWHs (data warehouses).

Modern composition is to use a business DB (RDB) and analysis DB (columnar) together, moving data from business DB to analysis DB via ETL/ELT (Extract/Transform/Load, the mechanism for transferring data to another DB). Running analytics on the RDB drops business-side performance, so this separation has become the de facto standard.

Representatives: BigQuery, Snowflake, Redshift, ClickHouse

The analytics platform is between BigQuery or Snowflake. Latecomer ClickHouse is rising for cost.

Time-series, search and vector — specialists you add for a purpose

Use-case-specific emerging categories - basics is to adopt for specific use cases, not as the lead role. Cases of using one alone as the main DB are rare; introduce as a complement to RDB or columnar DB.

Vector DBs are an area that surged with the LLM / RAG craze, with options spanning from existing-DB extensions (pgvector) to dedicated products (Pinecone).

For ranges substitutable by PostgreSQL extensions (pgvector etc.), it’s better not to introduce a new vector DB. The advantage of doing it in one DB is large.

How to choose — a table by data volume and use

Industry baseline values as of April 2026.

“Choosing by guesswork” is the losing move. Narrow it down mechanically by data volume and access volume.

The numeric gates on row count are: review the index design at ten million rows, consider partitioning at a hundred million, consider sharding at a billion. Building a distributed setup before you can even see the data volume is the classic over-engineering; when in doubt, expand in the order single PostgreSQL, then a cache, then replicas, then sharding.

Three scenarios

If you are building solo or at a startup

At this size, a single PostgreSQL (Supabase, Neon, or the smallest RDS configuration) with images and video on S3 is enough. Honestly, you do not even need Redis at first. This setup carries you to something like ten million rows and 100 GB, so building a distributed configuration at MVP stage is textbook over-engineering.

Personal / Startup: Ship in One Month Is Correcten.senkohome.com/arch-intro-case-startup/

If you are a small or mid-size SaaS

PostgreSQL (Aurora or Cloud SQL) plus Redis holds up to several million users. Add OpenSearch if full-text search matters, as in e-commerce or a job board, or BigQuery or Snowflake if BI dashboards do — but in both cases add it when the requirement actually appears. The urge to put it in ahead of time is understandable, and usually it goes unused while the operational cost stays.

Small-Mid SaaS - Lean on Managed and Run with Few Peopleen.senkohome.com/arch-intro-case-saas/

If you are a large enterprise

Only past 10 TB and ten billion rows do fully distributed setups — DynamoDB, Spanner, Cassandra — come into view. Even then it is what lies beyond cache, replicas and partitioning rather than something to jump straight to. At this size, audit requirements such as history retention and access logging also become conditions on the choice.

Large-Enterprise Core: Design That Holds Up for Yearsen.senkohome.com/arch-intro-case-enterprise/

For embedding AI or RAG, PostgreSQL with pgvector is the fastest route; for IoT and high-volume metrics, TimescaleDB is the specialised answer. Common to every case: never adopt a database nobody can operate.

AI decision axes — PostgreSQL has become the safe bet of the AI era

The accuracy of AI generation tracks the volume of training data

AI coding tools’ SQL generation accuracy directly correlates with the target DB’s training data volume. PostgreSQL is open-source with massive usage examples, so Stack Overflow, GitHub, and official documentation are overwhelmingly abundant. As a result, AI-generated SQL targeting PostgreSQL has higher accuracy than other DBs.

Furthermore, PostgreSQL handles vector search with pgvector, full-text search with pg_trgm, and document storage with JSONB - fulfilling purpose-specific DB roles in a single instance. The more DBs increase, the more operational complexity grows and the harder it is for AI to grasp context, making PostgreSQL’s “all-in-one” strategy rational in the AI era.

How a schemaless database gets in the way of AI

When an AI generates a query against a relational database, the table definition — the CREATE TABLE statement — serves directly as context. In a schemaless database, documents inside the same collection can have different structures, so the AI cannot tell “which fields are always present” and there is a standing risk that it produces code which fails at runtime.

Declaring the structure with Zod or a JSON schema mitigates it, and if you are going to do that, expressing it in a CREATE TABLE statement in the first place is more concise. The positive reasons for choosing schemaless are fewer than they used to be. Minor databases and those with proprietary query languages have less training data behind them, so generation accuracy drops there too — worth knowing when you are tempted by a niche database on the grounds that it is “technically interesting.”

Pitfalls and forbidden moves

This is the most one-way-door area there is, so a mistake here cannot be taken back. Here are the six most dangerous.

Author’s note - the cost of “schema-less for speed”

In early 2017, “tens of thousands of MongoDB instances exposed to the internet without auth set up” were hit by a wave of ransom attacks - data deleted and ransom demanded. The issue was less a flaw in MongoDB itself and more that compositions advertised as “schema-less for speed” were put into production without deep understanding, leaving security settings behind - as has been pointed out since.

Another story: companies running years of business data on the “MongoDB + Node.js” stack popular in the 2010s, when they tried to use it for analysis, found “field names are different by era, types fluctuate, arrays and scalars mixed,” and spent six months reprocessing nearly all records. Effort that could have been avoided by using PostgreSQL JSONB type with stricter typing.

I also watched - around 2018 - a colleague stand up a service saying “MongoDB is fast enough for now,” then witness hellish reckoning the moment aggregation reports were needed a year later. The cost of “schema-less for speed” rebounds across operations, security, and analysis. This lesson laid the groundwork for the PostgreSQL re-evaluation of the 2020s.

When in doubt: managed PostgreSQL + PITR + multi-AZ. This triad prevents 90% of accidents.

What to decide - what is your project’s answer?

For each of the following, try to articulate your project’s answer in 1-2 sentences. Starting work with these vague always invites later questions like “why did we decide this again?”

• What’s the main DB (PostgreSQL / MySQL / other)
• Whether to introduce a cache (Redis, Memcached)
• Whether to introduce an analytics DB (BigQuery, Snowflake)
• Is full-text search needed (Elasticsearch, OpenSearch)
• Is vector search needed (pgvector, Pinecone)
• Use a managed service (self-operation is mostly discouraged)
• Backup and restore policy

How to record your reasoning

Data store selection is hard to redo once decided, because data migration costs are enormous. Recording why you chose that data store in an ADR supports future review decisions.

ADRs are best managed as Markdown in docs/adr/ within the code repository, not in spreadsheets or wikis. The greatest value of an ADR is that when you look back, “why this choice was made” is immediately clear.

Related Articles

Summary

This article covered data store selection, including the strengths and weaknesses of RDB, KVS, document, columnar, time-series, search, and vector DBs, plus a phased data-volume x use-case matrix and AI-era favored options.

When in doubt, PostgreSQL; add use-case specialization at exception; managed first. That is the practical answer for data store selection in 2026.

Next time we’ll cover data modeling (table design, normalization, primary keys, indexes).

Back to series TOC -> ‘Architecture Crash Course for the Generative-AI Era’: How to Read This Book

I hope you’ll read the next article as well.