Choosing a Storage Engine

You have designed your schema, added the right indexes, and decided which data to normalize and which to denormalize. Now comes the question that ties all of that work together: where does this data actually live?

Choosing a storage engine means picking the specific database technology that will store each piece of your system's data. It is one of the hardest architectural decisions to reverse — migrating a production database from PostgreSQL to DynamoDB, or extracting a feature into a time-series store, requires a data migration, service rewrites, and often downtime. The right time to make this decision is before you write a single line of code.

The decision has three inputs — all of which come directly from your data modeling work:

1. Data shape: Is the data structured with clear relationships, or hierarchical and schema-flexible?
2. Access patterns: What queries does the system actually run? Are they varied and ad-hoc, or specific and predictable?
3. Consistency needs: Must every read reflect the latest write, or can temporary staleness be tolerated?

The wrong way to make this decision is picking a technology because it sounds modern or because it appeared in a viral blog post. The right way is matching your data's characteristics to the system designed to handle them.

The Storage Engine Landscape#

The era of "just use SQL" or "NoSQL is the future" has given way to a more nuanced landscape. Modern production systems use several storage engines together — each chosen for a specific type of data or access pattern.

The Three Questions#

Before choosing a storage engine for any piece of data, answer three questions. The answers come directly from the data modeling work you have already done.

Question 1: Data Shape#

Data shape describes the structural characteristics of your data — whether it has a fixed schema, how complex its relationships are, and how much the structure varies from record to record. Your ERD from schema design is the primary answer to this question.

The most common mistake here is forcing data into the wrong shape — normalizing something that is naturally hierarchical, or flattening something that is naturally relational. If your ERD has one table with 40 nullable columns where only a handful are set on any given row, your data is probably better modeled as a document. If your ERD has 12 tightly-related tables joined by foreign keys, a relational database is the right tool — do not force it into a flat key-value structure to chase hypothetical scale.

Question 2: Access Patterns#

Access patterns are the specific queries your application will run: how you read data, how often, under what filters, and in what order. This is the most important question for distinguishing between SQL and NoSQL.

SQL can answer any query you give it — the query planner figures out how to execute it. NoSQL requires you to define every query upfront and design the schema around those specific queries. The reason is architectural: NoSQL distributes data across many servers using a partition key (the field that determines which server stores a given row). A sort key optionally determines the ordering of rows within a partition. Every query must target a known partition key — otherwise the database would have to scan every server, which defeats the purpose. If you discover a new access pattern after your NoSQL system is in production, you may need a new Global Secondary Index (a separate copy of the data indexed by a different key) or a full table redesign.

Question 3: Consistency Needs#

Consistency needs determine what level of data correctness your application requires when multiple things happen at the same time — concurrent writes, replica lag, or partial failures.

This question has two canonical answers:

• Strong consistency (ACID — Atomicity, Consistency, Isolation, Durability): Every read reflects the most recent committed write. If you update a record, the next read returns the updated value — guaranteed. Required when stale data causes real harm.
• Eventual consistency (BASE — Basically Available, Soft state, Eventually consistent): Replicas may lag behind the primary. Reads may return slightly stale data. Eventually, all replicas converge. Acceptable when the data is informational and temporary staleness is harmless.

The practical rule: If a user could lose money, be locked out of their account, or receive a duplicate order because of stale data, you need strong consistency. If the worst outcome is a number that is slightly behind for a few seconds, eventual consistency is acceptable.

SQL as the Correct Default#

Given the above framework, PostgreSQL is the correct default for the structured relational data in the vast majority of new applications. It is not a fallback or a "beginner" choice — it is the production-proven choice used by Shopify, Instagram, Notion, and Reddit at massive scale.

The question is not whether PostgreSQL can scale — it can handle millions of reads per day on a modern instance, and horizontal read scaling via read replicas extends this further. The question is whether your specific workload has characteristics that push beyond what a relational database can offer.

A well-designed PostgreSQL schema with proper indexes, connection pooling (PgBouncer), and read replicas will outlast most early-stage systems' scaling requirements. Don't introduce NoSQL complexity before you have the scale problem it solves.

Specialized Engines: Beyond SQL and NoSQL#

Once your core relational data is in PostgreSQL and any high-throughput key-value patterns are in DynamoDB or Cassandra, there are four specialized categories that solve problems neither SQL nor wide-column NoSQL are designed for.

Redis: The In-Memory Speed Layer#

Redis is an in-memory data store — all data lives in RAM, which gives it sub-millisecond read and write latency. It is not a primary database. It is a speed layer placed in front of your database for specific, high-frequency operations.

Vector Databases: The AI Data Layer#

Vector databases are purpose-built for a new kind of query that became central to AI applications: similarity search. Instead of asking "give me rows where title = 'Password Reset'" (exact match), you ask "give me the 10 records most semantically similar to this sentence."

AI models represent meaning as vectors — arrays of hundreds or thousands of floating-point numbers. Two semantically similar sentences produce vectors that are numerically close in this high-dimensional space. A vector database indexes these vectors and efficiently answers nearest-neighbor queries.

The central question in 2026 for vector search is whether to use pgvector (a PostgreSQL extension) or a dedicated vector database (Pinecone, Qdrant, Weaviate).

Start with pgvector. You avoid introducing a separate system, and you can combine vector similarity search with relational filters in a single SQL query:

-- Find the 10 documents most similar to a query, for a specific user
SELECT id, title, content
FROM documents
WHERE user_id = 42
ORDER BY embedding <=> $query_vector   -- pgvector cosine distance operator
LIMIT 10;

Time-Series Databases#

A time-series database is optimized for a specific data shape: records with a mandatory timestamp where queries aggregate or filter over time ranges. This pattern is everywhere: infrastructure metrics (CPU, memory every 10 seconds), IoT sensor readings, application performance data (request latency per minute), and financial tick data.

PostgreSQL handles time-series data reasonably well with BRIN indexes (optimized for sequential timestamps), but two options go further:

• TimescaleDB is a PostgreSQL extension that adds automatic time-based partitioning (hypertables), column-level compression (often 90%+ size reduction on old data), and continuous aggregates (pre-computed downsampled views that update automatically). It runs inside PostgreSQL — same connection string, same SQL, same tooling.
• InfluxDB is a standalone time-series database built for extreme write volume — millions of data points per second. Used at the scale of infrastructure monitoring platforms and large IoT deployments.

Search Engines: Elasticsearch#

When users type into a search box and expect relevant results ranked by how well they match — not just exact string matches — a standard database index is not enough. Elasticsearch (and its open-source managed variant OpenSearch) provides:

• Full-text relevance scoring: ranks results by how well they match a query, accounting for term frequency, document length, and fuzzy matching ("pasword" → "password")
• Faceted filtering: "show all products where category = 'Electronics' AND price < $100, ordered by relevance" across millions of records

The cost: Elasticsearch is a separate, operationally complex system with eventual consistency. You typically write data to PostgreSQL (your source of truth) and sync a copy to Elasticsearch for search. Two systems, two writes, and a sync pipeline to maintain and monitor.

When the trade-off is worth it: Users expect search results ranked by relevance over large amounts of text — product catalogs, documentation, content libraries. When it is not: Your "search" is filtering by category or exact field match — PostgreSQL's ILIKE or the pg_trgm extension handles that without an additional system.

Polyglot Persistence: Using Multiple Engines Together#

Real production systems use multiple storage engines together — each chosen for a specific data type and access pattern. This is called polyglot persistence.

The right order to add each system:

1. Start: PostgreSQL only. This handles 95% of applications correctly.
2. When you add auth: Redis for session storage with TTL-based expiry.
3. When users upload files: S3 for object storage from day one — retrofitting it later is painful.
4. When you build AI features: pgvector for embeddings — already inside PostgreSQL, zero new system.
5. When search quality is inadequate: Elasticsearch, with PostgreSQL as the sync source.
6. When metrics volume overloads PostgreSQL: TimescaleDB extension first, then InfluxDB if extension is insufficient.

How AI Agents Get This Wrong#

AI agents make predictable storage selection mistakes. They have no visibility into your data shape, access patterns, or consistency requirements unless you explicitly provide them — so they fall back to defaults.

The most useful constraint to add to your architectural spec is the graduation trigger — the concrete, measurable condition that justifies moving data out of PostgreSQL into a specialized engine. Without it, AI agents either never recommend specialized systems or add them all immediately.

Example graduation triggers to put in your spec:

• "If p99 read latency for the product catalog exceeds 50ms after indexes are optimized, evaluate Elasticsearch."
• "If write throughput on the events table exceeds 5,000 writes/second sustained, evaluate TimescaleDB."
• "If pgvector recall benchmarks fall below 95% at our target queries per second, evaluate Qdrant."

Summary#

The storage decision is not a one-time choice — it evolves as your system grows. But the sequence matters: start with the simplest system that works, measure its actual limits, and move only the specific data type that needs a different engine. A five-database architecture is not evidence of good engineering — it is evidence that each component was added because the system genuinely outgrew the previous one.

Sources:

• SQL vs NoSQL: Choosing the Right Database for An Application — ByteByteGo
• SQL vs NoSQL: How to Answer This Interview Question — Hello Interview
• pgvector: Key features, tutorial, and pros and cons — Instaclustr
• Pgvector vs. Qdrant — Tiger Data
• NoSQL vs. SQL databases: 7 key differences and how to choose — Instaclustr
• How to Choose Between Relational, NoSQL, and Vector Databases — OctaByte
• Beyond Relational: Understanding Vector Databases and NewSQL Architectures — Design Gurus
• SQL vs. NoSQL vs. NewSQL — TechTarget