Why EAV is the Most Underrated Data Model for AI

Forma Engineering Blog · Series Part 1

TL;DR

Your AI pipeline shouldn't crash at 3 AM because your model learned a new field. Your data layer shouldn't need a DBA ticket to accept new attributes. And your downstream models shouldn't hallucinate off partial data read from a mid-migration table.

EAV plus JSON Schema plus a hot table handles all three. New fields take effect in seconds rather than days. Bad data gets rejected before it reaches your training set. And query performance holds up, because hot fields sit behind B-tree indexes while cold fields stay flexible.

This post is about why an "old-school" data model turns out to be the practical choice for AI-era applications.

Starting with a real scenario

You're building an AI-powered CRM. A user speaks into their microphone:

"Log this. I just had a call with Mr. Zhang. He's very interested in our new proposal, budget around $500K, let's follow up next Tuesday."

Your AI Agent turns that into structured data:

json

{
  "contact_name": "Mr. Zhang",
  "interaction_type": "phone_call",
  "sentiment": "positive",
  "budget_estimate": 500000,
  "next_followup": "2024-01-16",
  "notes": "Interested in new proposal"
}

Can your database accept it?

With traditional relational tables, probably not without work:

No sentiment column? Stop the service, ALTER TABLE ADD COLUMN.
A new customer needs an industry field? Stop again.
Different customers need different custom fields? One table per customer?

That doesn't scale. Across our surveys of 50+ enterprise customers, a single DDL change takes 3-7 business days from ticket to deployment. A moderately complex AI Agent produces 10-50 field combination variations per day. The cycle times are two orders of magnitude apart.

Translating DBA concerns to AI engineer problems

Database literature talks about "ACID compliance" and "transaction isolation." Here is what those terms mean for a pipeline:

What DBAs say	What AI engineers experience
"Zero DDL"	Your ingestion script won't crash because a new field appeared
"Schema validation"	Bad data won't silently corrupt your training set
"Transaction isolation"	Your model won't train on half-written records
"Data consistency"	No more debugging why your embeddings are drifting mysteriously

"Zero dirty reads" isn't there to impress database academics. It prevents your embedding model from training on a record caught mid-update, which produces model drift that is nearly impossible to trace back to its cause. The subject here is pipeline stability, not database theory.

JSON Schema: the "type system" for AI output

Validation plus contract

JSON Schema has become the de facto standard for structured output from large language models. OpenAI Structured Outputs uses it to define function return formats. Anthropic Tool Use uses it to describe tool parameters. Google Gemini Function Declarations are built on it too.

So a data structure defined in JSON Schema is simultaneously three things: the output format the LLM knows to return, the validation rules that check type, format, and range before a write, and the database schema that Forma uses to organize storage. One definition, three purposes.

A JSON Schema example

json

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "contact_name": {
      "type": "string",
      "minLength": 1,
      "x-ltbase-column": "text_01"
    },
    "budget_estimate": {
      "type": "integer",
      "minimum": 0,
      "x-ltbase-column": "integer_01"
    },
    "sentiment": {
      "type": "string",
      "enum": ["positive", "neutral", "negative"]
    },
    "next_followup": {
      "type": "string",
      "format": "date"
    },
    "notes": {
      "type": "string"
    }
  },
  "required": ["contact_name"]
}

That x-ltbase-column: "integer_01" is a Forma extension field. Its purpose comes up shortly.

Flexibility without DDL

What happens when AI produces a new field?

The traditional path:

AI outputs new field → Developer notices → Files ticket → DBA approves → Stop service → ALTER TABLE → Deploy

Timeline: 1 day to 1 week.

The Forma path:

AI outputs new field → Update JSON Schema → Immediately effective

Timeline: seconds.

In the EAV pattern, a new field is just new rows in the EAV table, so no table structure changes. Updating JSON Schema is a pure metadata operation with no data migration behind it.

The Pareto principle: why we still need a "hot table"

EAV solves flexibility and creates a different problem. All attributes live in the same table, so every query scans through a large amount of irrelevant data.

Take a CRM with 1 million contact records and 30 attributes per record on average. The EAV table holds 30 million rows. Every search for "contacts with budget over $100K" scans all 30 million, even when only 100 records match.

Look at user behavior, though, and a pattern shows up: 80% of queries touch only 20% of fields. The fields people search and sort by are always the same few, contact_name, created_at, budget_estimate, status. Fields like notes or custom_field_42 only matter on a detail page. That is the Pareto principle showing up in the query logs.

Hot table: promoting the top 20% of fields

Forma's answer is a hot table (entity_main) that stores the frequently-accessed fields:

Hot table structure example:

entity_main table structure:
┌─────────────┬──────────────┬──────────────┬──────────────┐
│ row_id      │ text_01      │ integer_01   │ created_at   │
├─────────────┼──────────────┼──────────────┼──────────────┤
│ uuid-1      │ "Mr. Zhang"  │ 500000       │ 2024-01-09   │
│ uuid-2      │ "Mr. Li"     │ 200000       │ 2024-01-08   │
└─────────────┴──────────────┴──────────────┴──────────────┘

Here text_01 maps to contact_name through JSON Schema's x-ltbase-column marker, and integer_01 maps to budget_estimate. Both columns carry B-tree indexes.

Now search for "budget over $100K". The pure EAV path scans 30 million rows, aggregates, and returns. The hot table path does an index scan on integer_01 > 100000, hits 1,000 rows, and aggregates EAV data for only those 1,000 records. That is a 99% reduction in scan volume, and latency drops from 200-500ms to 20-50ms.

Why not just use JSONB?

PostgreSQL's JSONB is the first thing most developers reach for when they need flexibility, and the instinct is reasonable. It hits walls in production AI pipelines, and the specific failure modes are worth walking through.

The index bottleneck: GIN vs. B-Tree

This is JSONB's weak point, and it stays invisible until you are debugging slow queries in production.

GIN indexes are good at containment queries:

sql

-- PostgreSQL: "Find records where tags contain 'urgent'"
SELECT * FROM records WHERE data @> '{"tags": ["urgent"]}';  -- ✅ GIN works great

They fail at range queries, which is most of business analytics:

sql

-- PostgreSQL: "Find high-confidence predictions"
SELECT * FROM records 
WHERE (data->>'confidence_score')::float > 0.9;  -- ❌ Full table scan

-- PostgreSQL: "Find records from the last 24 hours"  
SELECT * FROM records 
WHERE (data->>'timestamp')::timestamptz > now() - interval '1 day';  -- ❌ Full table scan

The fix is expression indexes, which means DDL:

sql

-- PostgreSQL: You need DDL for each field that needs range queries
CREATE INDEX idx_confidence ON records ((data->>'confidence_score')::float);
CREATE INDEX idx_timestamp ON records ((data->>'timestamp')::timestamptz);

MySQL is more limited still:

sql

-- MySQL 8.0+: JSON extraction works...
SELECT * FROM records 
WHERE JSON_EXTRACT(data, '$.confidence_score') > 0.9;

-- But functional indexes on JSON are restricted:
-- MySQL requires a VIRTUAL generated column first
ALTER TABLE records 
ADD COLUMN confidence_score FLOAT 
GENERATED ALWAYS AS (JSON_EXTRACT(data, '$.confidence_score')) VIRTUAL;

CREATE INDEX idx_confidence ON records (confidence_score);
-- That's TWO DDL statements per field!

Every new field that needs range queries or sorting pulls in a DBA. You are back to the 3-7 business-day bottleneck that JSONB was supposed to remove.

EAV avoids it with typed storage, meaning pre-allocated columns that already carry B-tree indexes:

entity_main table (Hot Table):
┌─────────────┬──────────────┬──────────────┬────────────────┐
│ row_id      │ float_01     │ float_02     │ timestamp_01   │
├─────────────┼──────────────┼──────────────┼────────────────┤
│ uuid-1      │ 0.95         │ 0.87         │ 2024-01-09     │
│ uuid-2      │ 0.72         │ 0.91         │ 2024-01-08     │
└─────────────┴──────────────┴──────────────┴────────────────┘
         ↑              ↑              ↑
    B-tree index   B-tree index   B-tree index
    (pre-existing) (pre-existing) (pre-existing)

When a new numeric attribute like confidence_score shows up, you map it to float_01 through JSON Schema's x-ltbase-column. The B-tree index already exists, so there is no DDL.

Scenario	JSONB (PostgreSQL)	JSON (MySQL)	EAV + Hot Table
New field needs range query	`CREATE INDEX` (DDL)	2× DDL (column + index)	Metadata mapping only
New field needs sorting	`CREATE INDEX` (DDL)	2× DDL	Metadata mapping only
Time to production	3-7 days	3-7 days	Seconds

Write amplification: the hidden cost

JSONB stores a document as a single binary blob. Update one field and PostgreSQL rewrites the whole thing.

Operation	JSONB	EAV
Update 1 field in 50-field record	Rewrite ~4KB blob	Insert/update 1 row (~100 bytes)
Add embedding vector to 1M records	1M × 4KB = 4GB written	1M × 100B = 100MB written
Write amplification factor	40×	1×

AI pipelines do frequent partial updates: enrichment jobs adding embeddings, sentiment scores, extracted entities. At 40× amplification that shows up as cloud storage bills that scale with bytes written, a Write-Ahead Log growing 40× faster, more data to sync to replicas, and more dead tuples for vacuum to clean up. If your pipeline makes small changes across millions of records, which is normal for embedding enrichment, this quietly eats budget.

Cross-database portability

JSONB ties you to PostgreSQL, and that matters more than it first appears. Enterprise environments are rarely single-database:

Capability	PostgreSQL	MySQL 8.0+	Aurora DSQL	CockroachDB	Spanner
JSONB + GIN index	✅	❌	❌	Partial	❌
Expression indexes on JSON	✅	Limited	❌	✅	❌
Functional generated columns	✅	✅	❌	✅	❌
Standard EAV tables	✅	✅	✅	✅	✅
B-tree on typed columns	✅	✅	✅	✅	✅

EAV is pure standard SQL. An entity table, an attribute table, and a value table with typed columns work on every relational database since the 1980s.

Several cloud-native storage services are built on EAV-like models internally: Amazon SimpleDB uses a key-attribute-value structure, Azure Table Storage an entity-property model, Google Cloud Datastore an entity-property-value design. Choosing EAV lines your architecture up with those platforms. If a customer suddenly requires MySQL (common in enterprise deals), or you need to scale to CockroachDB, or AWS ships a compelling new database service, your storage layer travels with you.

The OLTP/OLAP split

Forma's architecture is a separation of concerns, not only a swap of JSONB for EAV:

┌─────────────────────────────────────────────────────────────┐
│  OLTP Side (Writes)           │  OLAP Side (Analytics)      │
│  ─────────────────            │  ──────────────────         │
│  EAV + Hot Table              │  DuckDB + Parquet           │
│  • Max compatibility          │  • Columnar processing      │
│  • Runs on ANY SQL database   │  • Complex aggregations     │
│  • Zero DDL writes            │  • Serverless lakehouse     │
└─────────────────────────────────────────────────────────────┘

Bet everything on JSONB and your writes are optimized for PostgreSQL alone, range queries need DDL per field, and a database migration means rewriting the storage layer. With EAV plus DuckDB, writes work on any SQL database (PostgreSQL today, Aurora DSQL tomorrow), range queries use typed indexes that already exist, and heavy analytics offload to DuckDB's columnar engine, which Part 3 covers.

Writes go in easily through EAV; what comes out for analysis is strictly shaped Parquet, queried with DuckDB. That holds up better than coupling to PostgreSQL-specific features.

When JSONB does make sense

JSONB wins in specific cases: truly unstructured data such as log entries and raw API responses you never query, data accessed only for display and never filtered or sorted, single-document lookups that fetch by ID and return the whole blob, and PostgreSQL-only environments where you are certain you will never migrate.

Forma's position is about where each tool works, not that JSONB is bad. Use JSONB for opaque storage blobs. Use EAV and a hot table for queryable, evolving structures that need to survive database migrations and support efficient range queries without DDL.

The complete AI workflow loop

Here is the full path a piece of AI data takes on write:

┌───────────────────────────────────────────────────────────────┐
│  1. AI generates structured data                              │
│     LLM output: {"contact_name": "Zhang", "budget": 500000}   │
└───────────────────────────────────────────────────────────────┘
                            ↓
┌───────────────────────────────────────────────────────────────┐
│  2. JSON Schema validation                                    │
│     - contact_name: string, minLength 1  ✓                    │
│     - budget: integer, minimum 0         ✓                    │
│     - sentiment: enum [positive/neutral/negative]  ✓          │
└───────────────────────────────────────────────────────────────┘
                            ↓
┌───────────────────────────────────────────────────────────────┐
│  3. Forma write                                               │
│     - Hot fields → entity_main table (contact_name → text_01) │
│     - All fields → EAV table (maintains flexibility)          │
└───────────────────────────────────────────────────────────────┘
                            ↓
┌───────────────────────────────────────────────────────────────┐
│  4. Query optimization                                        │
│     - Filter/sort → Hot table + B-tree index (milliseconds)   │
│     - Detail aggregation → EAV + JSON_AGG (Part 2's trick)    │
└───────────────────────────────────────────────────────────────┘

New fields take effect the moment JSON Schema updates, with no DDL. AI output is validated before it is written. And performance stays predictable, since hot fields get index scans and cold fields are aggregated on demand.

Under the hood: JSON Schema compilation

What does Forma do when you create or update a schema?

1. Parse and validate

Input: JSON Schema definition
Output: Validation pass / Error messages (circular references, type conflicts, etc.)

2. attr_id assignment

Each attribute gets a unique integer ID within the schema:

contact_name → attr_id: 1
budget       → attr_id: 2
sentiment    → attr_id: 3

Queries then compare integers instead of matching strings, which is faster and avoids typos.

3. Hot table column mapping

Fields marked with x-ltbase-column get assigned to hot table columns:

contact_name (string)  → text_01
budget (integer)       → integer_01

Forma compiles the schema when it's created or updated. The flattened field mappings are generated at this time, cached, and used directly during queries without re-parsing the JSON Schema every time.

Managing the metadata tax

EAV's flexibility costs you attribute sprawl. Without discipline you end up with hundreds of attributes, some duplicates, some typos, some abandoned experiments. Critics call this the "metadata tax," and they are right that it exists. Here is how Forma handles it.

The schema registry

Forma keeps a schema_attributes table as a central registry:

sql

-- Simplified schema_attributes structure
CREATE TABLE schema_attributes (
    schema_id       UUID NOT NULL,
    attr_id         INTEGER NOT NULL,
    attr_path       TEXT NOT NULL,        -- "contact.name", "budget_estimate"
    json_type       TEXT NOT NULL,        -- "string", "integer", "boolean"
    hot_column      TEXT,                 -- "text_01", "integer_01", NULL for cold
    created_at      TIMESTAMPTZ DEFAULT now(),
    last_used_at    TIMESTAMPTZ,          -- For identifying stale attributes
    usage_count     BIGINT DEFAULT 0,     -- For identifying hot candidates
    PRIMARY KEY (schema_id, attr_id)
);

The same path can't be registered twice, so duplicates surface immediately. Types stay consistent, so budget can't be an integer in one record and a string in another. And usage tracking tells you which attributes anyone actually reads.

Preventing attribute sprawl

Three strategies keep the attribute space clean.

Strict mode comes first. By default, Forma rejects any field not declared in the schema:

json

{
  "additionalProperties": false,  // Reject undeclared fields
  "properties": {
    "contact_name": { "type": "string" },
    "budget": { "type": "integer" }
  }
}

AI outputs a sentment typo? Rejected. The pipeline has to be explicit about new fields.

Aliasing handles duplicates, merging them without a data migration:

sql

-- "budget_estimate" and "estimated_budget" both exist
-- Point them to the same hot column
UPDATE schema_attributes 
SET hot_column = 'integer_01' 
WHERE attr_path IN ('budget_estimate', 'estimated_budget');

A schema management CLI covers the rest. It is on Forma's roadmap and not yet available:

bash

# List all attributes with usage stats
$ forma schema attributes list --schema crm_contacts
┌─────────────────────┬──────────┬────────────┬─────────────┬───────────────┐
│ Attribute           │ Type     │ Hot Column │ Usage Count │ Last Used     │
├─────────────────────┼──────────┼────────────┼─────────────┼───────────────┤
│ contact_name        │ string   │ text_01    │ 1,234,567   │ 2 minutes ago │
│ budget_estimate     │ integer  │ integer_01 │ 987,654     │ 5 minutes ago │
│ sentiment           │ string   │ text_02    │ 543,210     │ 1 hour ago    │
│ legacy_field_xyz    │ string   │ NULL       │ 0           │ 6 months ago  │  ← Candidate for removal
└─────────────────────┴──────────┴────────────┴─────────────┴───────────────┘

# Find potential duplicates
$ forma schema attributes duplicates --schema crm_contacts
Potential duplicates found:
  - "budget_estimate" vs "estimated_budget" (87% string similarity)
  - "contact_name" vs "contactName" (camelCase variant)

# Promote a cold attribute to hot
$ forma schema attributes promote sentiment --hot-column text_02
✓ Attribute 'sentiment' promoted to hot column 'text_02'
✓ Backfill job queued (ETA: 3 minutes for 543,210 records)

Until the CLI ships, you can query schema_attributes directly for the same information.

What the metadata tax actually costs

EAV does require you to manage your attribute space. Compare that to the alternatives:

Approach	Adding New Field	Removing Unused Field	Finding Duplicates
Traditional SQL	`ALTER TABLE` + migrate	`ALTER TABLE` + careful	Manual code review
JSONB	Just write it	Fields never really "go away"	grep through JSON blobs
EAV + Registry	Update JSON Schema	Query `last_used_at`	Query `schema_attributes`

EAV with a registry doesn't remove metadata management. It makes it queryable and automatable.

Summary: why EAV fits the AI era

Traditional Relational Tables	Forma (EAV + Hot Table)
New fields require ALTER TABLE	New fields effective instantly
Schema changes require downtime	Zero downtime
AI output needs manual adaptation	JSON Schema direct integration
Index design requires upfront planning	Hot fields auto-indexed

EAV was once written off as an anti-pattern because it traded query performance for flexibility. Three things change that trade: the hot table promotes high-frequency fields to physical columns and restores B-tree index speed, JSON Schema supplies type safety and AI integration, and single-query optimization removes the N+1 problem, which Part 2 covers.

Data structures now change far faster than traditional software development cycles. EAV plus JSON Schema takes schema changes off the downtime-and-DDL-approval path and keeps type checking on the write path.

How does Forma compare to NoSQL?

If flexibility is the goal, why not MongoDB or DynamoDB?

Capability	MongoDB	DynamoDB	Forma (EAV + Hot Table)
Schema flexibility	✅ Excellent (schemaless)	✅ Excellent (schemaless)	✅ Excellent (JSON Schema)
Range queries	✅ Good (with indexes)	⚠️ Limited (requires GSI)	✅ Good (B-tree on hot columns)
ACID transactions	⚠️ Single-doc only by default	⚠️ Limited (25 items max)	✅ Full PostgreSQL ACID
JOIN support	❌ Manual aggregation	❌ No native JOINs	✅ Full SQL JOINs
Existing SQL ecosystem	❌ New tooling required	❌ New tooling required	✅ Standard SQL, existing tools
Cost at scale	⚠️ Compute-heavy	⚠️ RCU/WCU can spike	✅ Predictable (PostgreSQL + S3)
Cold data archival	⚠️ Manual sharding	⚠️ TTL + manual export	✅ Built-in (DuckDB + Parquet)

NoSQL wins on pure document workloads with no relational queries, on globally distributed apps that need multi-region writes such as DynamoDB Global Tables, and for teams already invested in the MongoDB or DynamoDB ecosystem.

Forma wins for AI pipelines that need relational joins for enrichment and cross-entity analysis, for teams with existing PostgreSQL infrastructure, for workloads mixing real-time OLTP and analytical OLAP queries, and for cost-sensitive cold data storage, where S3 and Parquet compare well against MongoDB Atlas archival.

A preview of what's possible

Part 2 covers these performance gains:

Metric	Before Optimization	After Optimization
Database round-trips	101	1
Latency (100 records)	1000ms	25ms
Improvement	n/a	97%

These are real numbers from production systems using PostgreSQL's CTE + JSON_AGG, features that have existed since version 9.4 but remain criminally underused. Part 2 has the copy-paste-ready SQL.

What's next: solving EAV's performance problem

This post covered the architecture choices behind EAV, JSON Schema, and the hot table. EAV also has a well-known problem: N+1 queries.

The next post shows how PostgreSQL's CTE + JSON_AGG cuts query count from 101 to 1 and latency from 1 second to 25 milliseconds.

And when historical data reaches billions of records and one PostgreSQL instance can't hold it, Part 3 covers building a serverless lakehouse with DuckDB, CDC, and Parquet, including how to answer the question everyone asks about lakehouses: how do I know I'm not reading dirty data?

Series Navigation

[Part 1] Why EAV is the Most Underrated Data Model for AI ← You are here
[Part 2] Killing N+1: How One SQL Trick Cut Our Latency by 40x
[Part 3] Zero Dirty Reads: Building a Trustworthy Lakehouse with DuckDB (Finale)

This post is based on engineering practices from the Forma project. Forma is a flexible data storage engine designed for the AI era.

Why EAV is the Most Underrated Data Model for AI ​

TL;DR ​

Starting with a real scenario ​

Translating DBA concerns to AI engineer problems ​

JSON Schema: the "type system" for AI output ​

Validation plus contract ​

A JSON Schema example ​

Flexibility without DDL ​

The Pareto principle: why we still need a "hot table" ​

Hot table: promoting the top 20% of fields ​

Why not just use JSONB? ​

The index bottleneck: GIN vs. B-Tree ​

Write amplification: the hidden cost ​

Cross-database portability ​

The OLTP/OLAP split ​

When JSONB does make sense ​

The complete AI workflow loop ​

Under the hood: JSON Schema compilation ​

1. Parse and validate ​

2. attr_id assignment ​

3. Hot table column mapping ​

Managing the metadata tax ​

The schema registry ​

Preventing attribute sprawl ​

What the metadata tax actually costs ​

Summary: why EAV fits the AI era ​

How does Forma compare to NoSQL? ​

A preview of what's possible ​

What's next: solving EAV's performance problem ​

Why EAV is the Most Underrated Data Model for AI

TL;DR

Starting with a real scenario

Translating DBA concerns to AI engineer problems

JSON Schema: the "type system" for AI output

Validation plus contract

A JSON Schema example

Flexibility without DDL

The Pareto principle: why we still need a "hot table"

Hot table: promoting the top 20% of fields

Why not just use JSONB?

The index bottleneck: GIN vs. B-Tree

Write amplification: the hidden cost

Cross-database portability

The OLTP/OLAP split

When JSONB does make sense

The complete AI workflow loop

Under the hood: JSON Schema compilation

1. Parse and validate

2. attr_id assignment

3. Hot table column mapping

Managing the metadata tax

The schema registry

Preventing attribute sprawl

What the metadata tax actually costs

Summary: why EAV fits the AI era

How does Forma compare to NoSQL?

A preview of what's possible

What's next: solving EAV's performance problem