Introduction
You're three months into building a SaaS platform. Your product team is ecstatic — the feature velocity is high, customers are happy, and the schema is clean. Then comes the request: "We need to support custom fields. Each customer should be able to define their own attributes for their records." You nod, open your migration file, and suddenly realize you're standing at one of the most consequential architectural crossroads in relational database design.
The instinct is to reach for flexibility: a table with a column for every possible attribute, a JSON blob, or the classic Entity-Attribute-Value (EAV) pattern. Each of these is a different bet — a trade-off between schema rigidity, query performance, developer ergonomics, and long-term maintainability. Get it wrong and you'll spend years fighting your own database. Get it right and you have a system that flexes with your business without folding under query load.
This lesson is a deep dive into two related but distinct problems: polymorphic associations (where a single foreign key can reference multiple tables) and Entity-Attribute-Value patterns (where rows represent attributes rather than entities). By the end, you'll understand not just how to implement these patterns, but when each one is appropriate, what their failure modes look like at scale, and how to extract reasonable query performance from inherently difficult schemas. You'll also see modern alternatives — JSON columns, table inheritance, and normalized multi-table approaches — so you can make the right call for your situation.
What you'll learn:
- The core mechanics of polymorphic associations and three implementation strategies with different performance characteristics
- How EAV tables work, where they legitimately shine, and why they become a performance nightmare at scale
- Concrete indexing and query patterns that recover performance in EAV designs
- Modern alternatives including JSON columns, PostgreSQL's JSONB, and typed-column table inheritance
- How to pivot EAV data back into a relational shape using conditional aggregation and CTEs
- Decision criteria for choosing between approaches based on cardinality, query patterns, and team constraints
Prerequisites
You should be comfortable with multi-table JOINs, including outer joins and self-joins. You should understand how indexes work at a conceptual level. Familiarity with CTEs will help significantly — if you need a refresher, Common Table Expressions (CTEs) for Cleaner SQL covers the fundamentals. You should also have a working understanding of NULL handling in SQL, since EAV patterns interact with NULLs in non-obvious ways — see Understanding SQL NULL Handling: COALESCE, NULLIF, and IS NULL for Reliable Data Queries if you're rusty.
Polymorphic Associations: The Core Problem
Imagine a content platform with comments. Comments can be attached to articles, videos, podcasts, or forum posts. The naive relational approach is a separate comments table for each entity type: article_comments, video_comments, podcast_comments. That works but creates a fragmented feature surface — every comment-related feature (threading, moderation, reporting) has to be duplicated four times.
The polymorphic association pattern instead creates a single comments table where each row knows what type of thing it's attached to, not just the ID of that thing:
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
body TEXT NOT NULL,
author_id BIGINT NOT NULL REFERENCES users(id),
commentable_id BIGINT NOT NULL,
commentable_type VARCHAR(50) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Index that supports the most common lookup: "all comments for this thing"
CREATE INDEX idx_comments_polymorphic
ON comments (commentable_type, commentable_id);
The commentable_type column holds a string like 'Article', 'Video', or 'Podcast'. The commentable_id holds the primary key of the record in that table. Together they form a logical foreign key — but it's a foreign key the database cannot enforce natively, because there's no single target table.
This is the fundamental tension of polymorphic associations: flexibility purchased at the cost of referential integrity.
Querying Polymorphic Associations
Retrieving comments for a specific article is straightforward:
SELECT c.id, c.body, u.display_name, c.created_at
FROM comments c
JOIN users u ON u.id = c.author_id
WHERE c.commentable_type = 'Article'
AND c.commentable_id = 12345
ORDER BY c.created_at;
That composite index on (commentable_type, commentable_id) makes this fast. The tricky query is the reverse: join comments back to their parent entities. Because the parent can be in any of several tables, you need conditional logic:
SELECT
c.id AS comment_id,
c.body,
c.commentable_type,
c.commentable_id,
COALESCE(a.title, v.title, p.episode_name) AS parent_title,
COALESCE(a.slug, v.slug, p.slug) AS parent_slug
FROM comments c
LEFT JOIN articles a ON c.commentable_type = 'Article' AND c.commentable_id = a.id
LEFT JOIN videos v ON c.commentable_type = 'Video' AND c.commentable_id = v.id
LEFT JOIN podcasts p ON c.commentable_type = 'Podcast' AND c.commentable_id = p.id
WHERE c.created_at > now() - INTERVAL '7 days';
This is functional, but notice what the planner has to do: it executes all three LEFT JOINs on every row, even though for any given comment only one will match. At small scale this is invisible. At tens of millions of comments, those wasted join probes add up. Use EXPLAIN ANALYZE to verify — you'll likely see index scans on the target tables, but you're doing three of them per comment row.
Warning: The multi-LEFT JOIN approach for polymorphic associations does not scale gracefully. If you have eight parent entity types instead of three, you're running eight index lookups per row in the result set. Profile this early with realistic data volumes before committing to this pattern. Query Profiling and Statistics in SQL covers the diagnostic tooling you'll need.
Three Implementation Strategies for Polymorphic Associations
There's more than one way to implement this pattern. The right choice depends on your query patterns and whether you need the database to enforce referential integrity.
Strategy 1: Single Polymorphic Table (shown above)
Best when: Comments, tags, attachments, audit events — anything where the relationship is the primary unit of interest and you rarely need to join back to the parent in bulk.
Worst when: You need foreign key enforcement or you're doing bulk reporting across entity types.
Strategy 2: Separate Join Tables (the purist approach)
Instead of one comments table with a type discriminator, you create explicit join tables:
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
body TEXT NOT NULL,
author_id BIGINT NOT NULL REFERENCES users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE article_comments (
comment_id BIGINT PRIMARY KEY REFERENCES comments(id) ON DELETE CASCADE,
article_id BIGINT NOT NULL REFERENCES articles(id) ON DELETE CASCADE
);
CREATE TABLE video_comments (
comment_id BIGINT PRIMARY KEY REFERENCES comments(id) ON DELETE CASCADE,
video_id BIGINT NOT NULL REFERENCES videos(id) ON DELETE CASCADE
);
Now foreign key constraints are real. The query to get a comment's parent becomes a union:
SELECT c.id, c.body, 'Article' AS parent_type, a.title AS parent_title
FROM comments c
JOIN article_comments ac ON ac.comment_id = c.id
JOIN articles a ON a.id = ac.article_id
WHERE c.id = 42
UNION ALL
SELECT c.id, c.body, 'Video', v.title
FROM comments c
JOIN video_comments vc ON vc.comment_id = c.id
JOIN videos v ON v.id = vc.video_id
WHERE c.id = 42;
More boilerplate, but the database can actually enforce your data contracts. Adding a new parent type means a new join table, not a new string constant floating around your application code.
Strategy 3: Exclusive Arc (null-safe explicit columns)
Each comment row gets a nullable foreign key column for each possible parent:
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
body TEXT NOT NULL,
author_id BIGINT NOT NULL REFERENCES users(id),
article_id BIGINT REFERENCES articles(id),
video_id BIGINT REFERENCES videos(id),
podcast_id BIGINT REFERENCES podcasts(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT chk_exactly_one_parent CHECK (
(article_id IS NOT NULL)::INT +
(video_id IS NOT NULL)::INT +
(podcast_id IS NOT NULL)::INT = 1
)
);
The CHECK constraint enforces the "exactly one parent" rule that polymorphic designs require but usually can't express. Real foreign keys work. Query joins are clean. The cost is column sprawl — every new parent type requires a schema migration adding a nullable column and a new JOIN.
Key insight: The Exclusive Arc pattern is often the best choice when you have a small, stable set of parent entity types (2–5) and you value query simplicity and referential integrity over flexibility. When the number of types is large or unknown, the separate join tables strategy scales better with less column pollution.
Entity-Attribute-Value: When Rows Become Columns
Polymorphic associations handle "one thing related to many types of another thing." EAV handles a different problem: "one type of thing that can have arbitrarily many different attributes."
Consider a healthcare system where patient records need to store clinical observations. A blood pressure reading has a systolic value and a diastolic value. A body temperature reading has a single numeric value and a scale (Celsius vs. Fahrenheit). A physician's narrative note has free text. You cannot pre-define columns for all of these in a fixed schema, especially when new observation types are added by clinicians without engineering involvement.
The EAV model looks like this:
CREATE TABLE entities (
id BIGSERIAL PRIMARY KEY,
entity_type VARCHAR(50) NOT NULL, -- 'Patient', 'Device', 'Location'
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE attributes (
id BIGSERIAL PRIMARY KEY,
entity_type VARCHAR(50) NOT NULL,
attribute_key VARCHAR(100) NOT NULL,
data_type VARCHAR(20) NOT NULL, -- 'text', 'numeric', 'boolean', 'date'
UNIQUE (entity_type, attribute_key)
);
CREATE TABLE attribute_values (
id BIGSERIAL PRIMARY KEY,
entity_id BIGINT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
attribute_id BIGINT NOT NULL REFERENCES attributes(id),
value_text TEXT,
value_numeric NUMERIC(18, 4),
value_boolean BOOLEAN,
value_date DATE,
UNIQUE (entity_id, attribute_id)
);
CREATE INDEX idx_av_entity ON attribute_values (entity_id);
CREATE INDEX idx_av_attribute ON attribute_values (attribute_id);
CREATE INDEX idx_av_entity_attribute ON attribute_values (entity_id, attribute_id);
Notice the separate typed value columns. This is the typed EAV variant, and it's meaningfully better than the alternative (a single value_text VARCHAR column for everything) because it avoids implicit type casting in queries and allows numeric range filters to use indexes. We'll see why this matters in a moment.
Reading EAV Data: The Pivot Problem
The natural query shape for EAV is ugly. Fetching all attributes for a single patient returns a set of rows, not a single row with named columns:
SELECT a.attribute_key,
COALESCE(av.value_text, av.value_numeric::TEXT, av.value_boolean::TEXT, av.value_date::TEXT) AS value
FROM entities e
JOIN attribute_values av ON av.entity_id = e.id
JOIN attributes a ON a.id = av.attribute_id
WHERE e.id = 1001
AND e.entity_type = 'Patient';
Result:
attribute_key | value
-----------------+---------
first_name | Maria
last_name | Santos
date_of_birth | 1985-03-12
systolic_bp | 122
diastolic_bp | 78
height_cm | 164
weight_kg | 61.5
This is fine for displaying a record, but analysis requires pivoting. To compare blood pressure across patients, you need each attribute in its own column. This is where Conditional Aggregation with CASE WHEN: Pivoting Logic Without Reshaping Your Data becomes essential.
SELECT
e.id AS patient_id,
MAX(CASE WHEN a.attribute_key = 'first_name' THEN av.value_text END) AS first_name,
MAX(CASE WHEN a.attribute_key = 'last_name' THEN av.value_text END) AS last_name,
MAX(CASE WHEN a.attribute_key = 'systolic_bp' THEN av.value_numeric END) AS systolic_bp,
MAX(CASE WHEN a.attribute_key = 'diastolic_bp' THEN av.value_numeric END) AS diastolic_bp,
MAX(CASE WHEN a.attribute_key = 'date_of_birth' THEN av.value_date END) AS date_of_birth
FROM entities e
JOIN attribute_values av ON av.entity_id = e.id
JOIN attributes a ON a.id = av.attribute_id
WHERE e.entity_type = 'Patient'
GROUP BY e.id;
The MAX() aggregate with CASE WHEN is the standard SQL pivot. Because each patient has exactly one value per attribute, MAX is just picking the one non-NULL value. This query works, but it requires one pass through all the attribute_values rows per patient. If a patient has 50 attributes and you're pivoting 10 of them for a cohort of 10,000 patients, you're scanning 500,000 rows to build a 10,000-row result.
Tip: When pivoting EAV data, filter your attribute_values join to only the attributes you need before aggregating. Add a WHERE a.attribute_key IN ('first_name', 'last_name', 'systolic_bp', ...) clause to the query. This lets the database use the attribute index to restrict rows early rather than reading every attribute value for every entity in the result set.
Filtering on EAV Attributes
Filtering is where EAV gets genuinely painful. In a normal relational schema, "find all patients with systolic blood pressure above 140" is trivial:
SELECT * FROM patients WHERE systolic_bp > 140;
In EAV, this becomes a semi-join or a subquery:
SELECT e.id, e.entity_type
FROM entities e
WHERE e.entity_type = 'Patient'
AND EXISTS (
SELECT 1
FROM attribute_values av
JOIN attributes a ON a.id = av.attribute_id
WHERE av.entity_id = e.id
AND a.attribute_key = 'systolic_bp'
AND av.value_numeric > 140
);
Or with an explicit join:
SELECT DISTINCT e.id
FROM entities e
JOIN attribute_values av ON av.entity_id = e.id
JOIN attributes a ON a.id = av.attribute_id
WHERE e.entity_type = 'Patient'
AND a.attribute_key = 'systolic_bp'
AND av.value_numeric > 140;
Multi-attribute filtering gets worse. "Patients with high systolic BP who are also over 50 years old" requires two separate joins to attribute_values:
SELECT e.id
FROM entities e
JOIN attribute_values av_bp ON av_bp.entity_id = e.id
JOIN attributes a_bp ON a_bp.id = av_bp.attribute_id
AND a_bp.attribute_key = 'systolic_bp'
JOIN attribute_values av_dob ON av_dob.entity_id = e.id
JOIN attributes a_dob ON a_dob.id = av_dob.attribute_id
AND a_dob.attribute_key = 'date_of_birth'
WHERE e.entity_type = 'Patient'
AND av_bp.value_numeric > 140
AND av_dob.value_date < CURRENT_DATE - INTERVAL '50 years';
Each additional filter condition adds another self-join to attribute_values. This is the EAV query tax: query complexity scales linearly with the number of filter conditions. A five-condition filter requires five joins. Performance degrades predictably.
Warning: Do not use a single value_text VARCHAR column for all EAV values and then cast to numeric at query time. You lose the ability to use indexes on range comparisons (e.g., WHERE value_text::NUMERIC > 140), the database can't use statistics on the column effectively, and you'll get silent type errors when non-numeric strings are stored for numeric attributes. Typed value columns — or a typed storage backend like JSONB — are not optional at any serious scale.
The right indexes on an EAV table make an enormous difference. Let's work through the specific indexes you need and why.
The Covering Index for Attribute Lookups
The most common EAV query pattern is "for entity X, get the value of attribute Y." A covering index that includes the value columns eliminates table heap access entirely:
-- For the typed EAV variant
CREATE INDEX idx_av_covering_numeric
ON attribute_values (entity_id, attribute_id, value_numeric)
WHERE value_numeric IS NOT NULL;
CREATE INDEX idx_av_covering_text
ON attribute_values (entity_id, attribute_id, value_text)
WHERE value_text IS NOT NULL;
The partial WHERE clause keeps these indexes small — only rows with non-null values in that column are included. For a mixed EAV table where most attributes are text, this keeps the numeric index lean and fast.
Indexing for Attribute-First Queries
When you're filtering entities by an attribute value (the "find patients with high BP" pattern), the query approaches the index from the attribute side, not the entity side. You need an index where attribute_id leads:
CREATE INDEX idx_av_attribute_numeric_value
ON attribute_values (attribute_id, value_numeric, entity_id)
WHERE value_numeric IS NOT NULL;
This lets the planner do an index scan on attribute_id = <bp_attribute_id> AND value_numeric > 140 and pull entity_id from the index without touching the heap. You're turning the filter query into something that resembles a column scan.
Key insight: In EAV, you're essentially rebuilding what a column-store database does natively. Each attribute effectively becomes a vertical slice of data, and if you index (attribute_id, value_numeric, entity_id), you've created a column-like data structure inside a row-oriented database. This is why columnar databases like Redshift and BigQuery handle EAV-style access patterns so much better than OLTP row stores.
Partial Indexes on Specific Attributes
For attributes you query constantly, a partial index that targets just that attribute is the most aggressive optimization:
-- Create a filtered index just for systolic_bp
-- First, know the attribute_id for systolic_bp (let's say it's 7)
CREATE INDEX idx_av_systolic_bp
ON attribute_values (value_numeric, entity_id)
WHERE attribute_id = 7 AND value_numeric IS NOT NULL;
Now filtering by systolic blood pressure hits a narrow, pre-filtered index with nothing else in it. This can be dramatically faster for high-cardinality attributes that are queried frequently. The trade-off: you're managing an index per hot attribute, which increases write overhead and maintenance complexity. Use this for the 5–10 attributes that drive 80% of your filtering queries.
For a deeper look at evaluating index choices in production, Indexing Fundamentals for Query Performance: How to Choose, Create, and Evaluate Indexes on Real Tables covers the diagnostic toolkit in detail.
Modern Alternatives: JSON Columns and Hybrid Schemas
Before committing to a full EAV schema, consider whether your database's native JSON support might be a better fit. PostgreSQL's JSONB type and MySQL 5.7+'s JSON column are genuinely powerful for semi-structured data, and they avoid many of EAV's pain points.
PostgreSQL JSONB as a Flexible Attribute Store
Instead of the EAV pattern, store flexible attributes as a JSONB column on the primary entity row:
CREATE TABLE patients (
id BIGSERIAL PRIMARY KEY,
mrn VARCHAR(20) NOT NULL UNIQUE, -- Medical Record Number
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
attributes JSONB NOT NULL DEFAULT '{}'
);
-- Insert a patient with arbitrary attributes
INSERT INTO patients (mrn, attributes)
VALUES ('MRN-2024-001', '{
"first_name": "Maria",
"last_name": "Santos",
"date_of_birth": "1985-03-12",
"systolic_bp": 122,
"diastolic_bp": 78,
"height_cm": 164,
"weight_kg": 61.5
}');
Querying a specific attribute:
SELECT mrn,
attributes->>'first_name' AS first_name,
(attributes->>'systolic_bp')::NUMERIC AS systolic_bp
FROM patients
WHERE (attributes->>'systolic_bp')::NUMERIC > 140;
With a GIN index on the JSONB column, attribute existence queries are fast:
CREATE INDEX idx_patients_attributes ON patients USING GIN (attributes);
But for range queries on numeric values inside JSON, you need a more targeted expression index:
CREATE INDEX idx_patients_systolic_bp
ON patients (((attributes->>'systolic_bp')::NUMERIC))
WHERE attributes ? 'systolic_bp';
This is roughly equivalent to the partial EAV index we built earlier, but with much simpler query syntax and no multi-table joins. For more on JSON query patterns including working with arrays and nested structures, Working with JSON and Arrays in Modern SQL: Complete Guide is essential reading.
When JSONB Beats EAV
JSONB wins over traditional EAV when:
- Attributes are entity-specific and not queried across entities frequently
- The attribute schema evolves rapidly (new keys with zero schema migrations)
- You need nested/hierarchical attribute structures
- Most of your access pattern is "fetch one entity's full attribute set"
EAV wins over JSONB when:
- Attributes have formal definitions (data types, validation rules, display labels) stored in the
attributes table
- You need the database to enforce that certain attributes always exist
- Cross-entity attribute queries are the primary access pattern (EAV indexes can be more targeted than GIN)
- You're on an older RDBMS that doesn't support JSON well
Hybrid Architecture: The Pragmatic Choice
The most scalable real-world approach is usually a hybrid: keep the most critical, frequently-queried attributes as proper typed columns, and overflow everything else into either an EAV side table or a JSONB column.
CREATE TABLE patients (
id BIGSERIAL PRIMARY KEY,
mrn VARCHAR(20) NOT NULL UNIQUE,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
date_of_birth DATE NOT NULL,
-- Core clinical metrics as typed columns
systolic_bp SMALLINT,
diastolic_bp SMALLINT,
-- Custom/overflow attributes in JSONB
extended_attrs JSONB NOT NULL DEFAULT '{}'
);
This gives you proper indexes, FK constraints, and NOT NULL enforcement on the attributes that matter most, with arbitrary flexibility for everything else. The engineering cost is a clear decision process: when does an attribute "graduate" from extended_attrs to a proper column? Make this explicit in your team's data modeling guidelines.
Table Inheritance: The Structural Alternative
PostgreSQL offers actual table inheritance as a structural solution to polymorphic schemas. Rather than a discriminator column or JSON blob, you define a parent table and child tables that extend it:
-- Base table for all content
CREATE TABLE content_items (
id BIGSERIAL,
title TEXT NOT NULL,
author_id BIGINT NOT NULL REFERENCES users(id),
published_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Child tables inherit all columns from content_items
CREATE TABLE articles (
word_count INT,
reading_time_minutes SMALLINT,
body TEXT NOT NULL
) INHERITS (content_items);
CREATE TABLE videos (
duration_seconds INT,
thumbnail_url TEXT,
stream_url TEXT NOT NULL
) INHERITS (content_items);
CREATE TABLE podcasts (
duration_seconds INT,
episode_number INT,
transcript_url TEXT
) INHERITS (content_items);
Now a query against content_items automatically includes rows from all child tables:
-- Returns articles, videos, and podcasts together
SELECT id, title, published_at
FROM content_items
WHERE author_id = 456
ORDER BY published_at DESC;
Comments can now reference content_items without polymorphic trickery:
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
body TEXT NOT NULL,
author_id BIGINT NOT NULL REFERENCES users(id),
content_item_id BIGINT NOT NULL, -- References content_items.id
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
Warning: PostgreSQL table inheritance has significant gotchas. Unique constraints on the parent table do NOT span child tables. Foreign keys that reference the parent table do NOT enforce that the referenced row exists in a child table. Primary keys defined on the parent are not automatically unique across children. You must manage these constraints carefully at the application level, or use declarative partitioning (a related but different feature) instead for partition-like use cases.
The real value of table inheritance is that type-specific queries against child tables are clean and the database enforces the right schema for each type. The downside is that cross-type queries hit inheritance overhead, and the FK/unique constraint limitations are genuinely limiting for complex schemas.
Pivoting EAV Data for Analysis: Advanced Patterns with CTEs
For reporting on EAV data at scale, the conditional aggregation approach works but can become unwieldy for large attribute sets. A more maintainable pattern uses CTEs to stage the pivot:
WITH bp_readings AS (
SELECT
av.entity_id,
av.value_numeric AS value,
a.attribute_key
FROM attribute_values av
JOIN attributes a ON a.id = av.attribute_id
WHERE a.attribute_key IN ('systolic_bp', 'diastolic_bp')
AND av.value_numeric IS NOT NULL
),
patient_base AS (
SELECT
e.id AS patient_id,
MAX(CASE WHEN av.attribute_key = 'first_name' THEN av.value_text END) AS first_name,
MAX(CASE WHEN av.attribute_key = 'last_name' THEN av.value_text END) AS last_name,
MAX(CASE WHEN av.attribute_key = 'date_of_birth' THEN av.value_date END) AS date_of_birth
FROM entities e
JOIN attribute_values av ON av.entity_id = e.id
JOIN attributes a ON a.id = av.attribute_id
WHERE e.entity_type = 'Patient'
AND a.attribute_key IN ('first_name', 'last_name', 'date_of_birth')
GROUP BY e.id
),
bp_pivoted AS (
SELECT
entity_id,
MAX(CASE WHEN attribute_key = 'systolic_bp' THEN value END) AS systolic_bp,
MAX(CASE WHEN attribute_key = 'diastolic_bp' THEN value END) AS diastolic_bp
FROM bp_readings
GROUP BY entity_id
)
SELECT
pb.patient_id,
pb.first_name,
pb.last_name,
pb.date_of_birth,
bp.systolic_bp,
bp.diastolic_bp,
CASE
WHEN bp.systolic_bp >= 140 OR bp.diastolic_bp >= 90 THEN 'High'
WHEN bp.systolic_bp >= 120 OR bp.diastolic_bp >= 80 THEN 'Elevated'
ELSE 'Normal'
END AS bp_classification
FROM patient_base pb
LEFT JOIN bp_pivoted bp ON bp.entity_id = pb.patient_id
ORDER BY bp.systolic_bp DESC NULLS LAST;
This CTE-based approach has two advantages. First, it's readable — each CTE has a clear job, and you can test them in isolation. Second, in many query planners, separating the attribute groups into different CTEs helps the optimizer focus index usage on each group's specific attributes rather than trying to optimize one massive join.
For complex analysis like cohort segmentation or retention analysis on top of EAV data, the CTE nesting pattern is almost essential — the alternative is a single query that's hundreds of lines long and impossible to debug. If you're doing this kind of analysis, SQL for Data Analysis: Cohort Analysis, Funnels, and Retention shows how these analytical patterns compose.
Let's be direct about EAV performance ceilings so you can plan around them.
A well-indexed EAV table with 10 million entity rows and 50 attributes per entity has 500 million rows in attribute_values. Even with ideal indexes, multi-attribute pivot queries that need to touch 10+ attributes for 100,000+ entities are going to be slow. You're asking the database to do what amounts to 10 index scans per entity, materializing 1 million partial result sets and aggregating them.
The architectural responses to this ceiling, in order of increasing complexity:
1. Materialized views for common pivots. Precompute the denormalized view of your most common pivot shapes and refresh on a schedule or via triggers. This moves the cost to write-time rather than read-time.
CREATE MATERIALIZED VIEW patient_clinical_summary AS
SELECT
e.id AS patient_id,
MAX(CASE WHEN a.attribute_key = 'first_name' THEN av.value_text END) AS first_name,
-- ... all the attributes you commonly need
MAX(CASE WHEN a.attribute_key = 'systolic_bp' THEN av.value_numeric END) AS systolic_bp
FROM entities e
JOIN attribute_values av ON av.entity_id = e.id
JOIN attributes a ON a.id = av.attribute_id
WHERE e.entity_type = 'Patient'
GROUP BY e.id;
CREATE UNIQUE INDEX ON patient_clinical_summary (patient_id);
Query Optimization with Materialized Views: Caching Complex Aggregations and Refreshing Strategies for High-Performance Analytics covers the refresh strategy trade-offs in depth.
2. Graduated column promotion. Identify the attributes that drive 80% of your filter and sort operations, and migrate them to actual columns on the entity table. Keep EAV for the long tail. This hybrid approach is often the final destination for EAV systems that outgrow their schema.
3. Dual-write to a columnar store. For reporting and analytics, write denormalized records to a columnar store (Redshift, BigQuery, Snowflake, DuckDB) in parallel with your OLTP EAV tables. The columnar store handles analytical queries natively; the OLTP store handles transactional writes. This is the enterprise-scale solution and requires incremental load patterns to keep the two in sync.
Note: EAV is not inherently wrong. It's the right tool for legitimately open-ended, user-defined attribute schemas — CRM custom fields, IoT sensor configurations, product catalog attributes. It's the wrong tool for data that feels dynamic but is actually just normalized data in disguise. Be honest about which situation you're in before reaching for EAV.
Handling Data Integrity in Polymorphic and EAV Schemas
Without foreign key enforcement, you need application-level and database-level constraints to prevent data corruption.
For polymorphic associations, at minimum enforce valid type strings with a CHECK constraint:
ALTER TABLE comments
ADD CONSTRAINT chk_commentable_type
CHECK (commentable_type IN ('Article', 'Video', 'Podcast', 'ForumPost'));
For EAV, the attributes table is your schema registry. Enforce that attribute values reference valid attribute definitions, and use your data type metadata to validate values at insertion. In PostgreSQL, you can do this with a trigger:
CREATE OR REPLACE FUNCTION validate_attribute_value()
RETURNS TRIGGER AS $$
DECLARE
v_data_type VARCHAR(20);
BEGIN
SELECT data_type INTO v_data_type
FROM attributes
WHERE id = NEW.attribute_id;
IF v_data_type = 'numeric' AND NEW.value_numeric IS NULL AND NEW.value_text IS NOT NULL THEN
RAISE EXCEPTION 'Attribute % expects numeric value, got text', NEW.attribute_id;
END IF;
IF v_data_type = 'text' AND NEW.value_text IS NULL AND NEW.value_numeric IS NOT NULL THEN
RAISE EXCEPTION 'Attribute % expects text value, got numeric', NEW.attribute_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_validate_attribute_value
BEFORE INSERT OR UPDATE ON attribute_values
FOR EACH ROW EXECUTE FUNCTION validate_attribute_value();
This is not free — triggers add write overhead — but for an EAV system without type enforcement, data quality degrades quickly and silently. The cost is worth it during development; in high-throughput production systems, move validation to the application layer and use the trigger only in lower environments.
For testing these integrity constraints systematically, Writing Effective SQL Unit Tests: Validating Query Logic, Edge Cases, and Data Contracts in CI/CD Pipelines provides the framework to make this part of your deployment pipeline.
Hands-On Exercise
Build a product catalog system for an e-commerce platform where products have both common attributes (name, price, SKU) and category-specific attributes (a shirt has size and color; a laptop has RAM, storage, and CPU; a book has ISBN, author, and page count).
Part 1: Schema Design
Design a schema that handles this requirement. Create:
- A
products table with the universal columns
- An
attribute_definitions table that acts as a schema registry, with columns for category (e.g., 'clothing', 'electronics', 'books'), attribute_key, data_type, and required (boolean)
- An
product_attribute_values table using the typed-column EAV pattern
Add appropriate indexes for both entity-first access (fetch all attributes for product 123) and attribute-first filtering (find all laptops with RAM >= 16GB).
Part 2: Sample Data and Basic Queries
Insert at least 3 products per category with their attributes. Write queries for:
- Fetch all attributes for a specific product as a key-value list
- Pivot product attributes into a flat row for all products in the
'electronics' category
- Find all products in
'clothing' where size is 'L' or 'XL'
Part 3: Complex Analysis
Write a query that:
- Finds the top 5 most expensive products in each category (hint: you'll want window functions here —
RANK() OVER (PARTITION BY category ORDER BY price DESC))
- For each of those top 5, shows their name, price, and a JSON object of all their custom attributes (use
json_object_agg in PostgreSQL or equivalent)
Part 4: Performance Investigation
Run EXPLAIN ANALYZE on your attribute-first filter query from Part 2. Note whether it's using your index. If it's doing a sequential scan on product_attribute_values, investigate why and adjust your index design.
Bonus Challenge: Implement the hybrid approach — add a JSONB extra_attributes column to the products table and migrate one attribute per category to it. Rewrite the pivot query to pull from both sources.
Common Mistakes & Troubleshooting
Mistake 1: Using a single value text column in EAV
The symptom: slow range queries, silent type coercion bugs, inability to use numeric indexes. The fix: use typed value columns (value_text, value_numeric, value_boolean, value_date) and enforce which column is used at the application layer or via triggers.
Mistake 2: Missing the (entity_id, attribute_id) composite unique constraint
Without UNIQUE (entity_id, attribute_id), you can insert multiple values for the same attribute on the same entity. Pivot queries using MAX(CASE WHEN ...) will silently pick one value arbitrarily. Add the unique constraint and test it explicitly.
Mistake 3: Polymorphic joins without filtering by type first
A query that joins to three parent tables without filtering on commentable_type first forces the planner to do three full-scan probes. Always filter WHERE commentable_type = 'Article' before joining the specific parent table. If your query spans multiple types, use UNION ALL with one branch per type rather than one query with multiple LEFT JOINs.
Mistake 4: Not creating an index that leads with attribute_id
If your only index on attribute_values is (entity_id, attribute_id), attribute-first queries (filtering entities by attribute value) can't use it efficiently. You need a separate (attribute_id, value_numeric, entity_id) index for that access pattern.
Mistake 5: Storing truly relational data in EAV
This is the most expensive mistake. If you can enumerate all the attributes a type will ever have at design time, those should be columns — not EAV rows. EAV is for legitimately dynamic, user-defined attributes. Using it for a fixed schema because it "feels more flexible" produces all the pain with none of the benefit. A clear signal you've done this: you always query the same fixed set of attributes from your EAV table, never more and never less.
Mistake 6: No strategy for orphaned EAV values
When an entity is deleted, EAV rows must be cleaned up. Always define ON DELETE CASCADE on the foreign key from attribute_values to entities. Without it, deleted entities leave orphaned attribute rows that bloat the table and can cause integrity issues if IDs are ever reused.
Troubleshooting: Pivot query is slow even with indexes
If your conditional aggregation pivot is slow at scale, check:
- Is the planner using the right index? Run
EXPLAIN (ANALYZE, BUFFERS) and look for sequential scans.
- Are you filtering by entity_type before joining? The planner needs the filter on
entities to reduce the join cardinality.
- Consider materializing the intermediate EAV join (just the relevant attributes for the relevant entities) as a CTE that the planner can optimize separately before pivoting.
- For very large pivot operations, consider whether a materialized view refreshed on a schedule would be more appropriate than a live query.
Summary & Next Steps
You've now worked through the full landscape of flexible schema design in SQL. Let's consolidate the decision framework:
Use polymorphic associations when:
- A single entity type (comments, tags, attachments) relates to multiple parent entity types
- The parent types are stable and small in number → prefer Exclusive Arc or Separate Join Tables
- The parent types are numerous or grow frequently → prefer the Single Polymorphic Table with type string
Use EAV when:
- Attributes are genuinely user-defined at runtime (CRM custom fields, product catalog attributes, clinical observation types)
- The attribute schema cannot be known at design time
- You pair it with typed value columns, strong indexing strategy, and either materialized views or a hybrid approach for analytics
Use JSONB/JSON columns when:
- Attributes are entity-specific rather than cross-entity queryable
- Schema evolution speed matters more than query performance on attribute values
- You're on PostgreSQL 9.4+ and can leverage GIN indexes and expression indexes
Use the hybrid approach (typed columns + JSONB overflow) when:
- You know which attributes are analytically important
- You need the query performance of proper columns for high-frequency filters
- You still need flexibility for the long tail of edge-case attributes
The next natural progression from this material is understanding how these patterns interact with concurrent writes — if multiple application servers are writing EAV rows for the same entity simultaneously, you need to think carefully about locking and isolation. SQL Transactions, Isolation Levels, and Locking: A Complete Guide to Concurrent Database Programming covers the mechanics you'll need. For the analytical reporting side, Advanced Pivoting and Unpivoting Data Transformations in SQL goes deeper on the pivot patterns introduced here, including database-specific PIVOT syntax that can simplify the CASE WHEN approach in SQL Server and Oracle environments.
The schema designs in this lesson are not set and forget. They require ongoing profiling as data volumes grow. Build the habit of running EXPLAIN ANALYZE on your most critical EAV queries monthly — what's fast at one million rows often needs an index change at one hundred million. Build that feedback loop in from the start.
