---
title: "EAV in PostgreSQL: correct queries before performance"
source: "https://www.matteovincenti.com/en/blog/eav-data-model-custom-fields-performance"
language: en
author: "Matteo Vincenti"
published: 2026-10-21
updated: 2026-10-23
category: "Performance"
tags: ["EAV", "PostgreSQL", "Database"]
---

# EAV in PostgreSQL: correct queries before performance

EAV, relational columns or JSONB for custom fields? A reproducible SQL lab in Docker shows how to avoid false matches before testing performance.

![An adjustable divider is added to an orderly card catalogue, illustrating configurable fields and data structure.](https://cdn.sanity.io/images/xrqkwry7/production/cb5e84d8eb550367beffee65de30a4b4c2d8c3ad-1729x910.png?w=1400&fit=max&auto=format)

Category: Performance · Tags: EAV, PostgreSQL, Database

A supplier directory needs a new field: the delivery time for each site, so users can find suppliers delivering within 48 hours. The person responsible wants to add it through configuration, without waiting for a software change. Soon afterwards, they ask to restrict the search to active sites.

That is a reasonable request. The complication appears when a supplier has one active but slow site and another fast but suspended site. Searching for those properties separately includes the supplier by mistake.

I am researching an architecture for configurable fields of this kind. Performance remains an open question; the concrete result here is a SQL demonstration of filter correctness that runs in PostgreSQL on Docker. Before comparing indexes or adding a cache, I want to establish which suppliers should appear.

## What EAV changes

EAV stands for Entity–Attribute–Value. Our site's delivery time can be represented as follows:

_What EAV changes_

| Entity | Attribute | Value |
| --- | --- | --- |
| Site 11 | Delivery time, in hours | 72 |

In an ordinary table, delivery time occupies a column. With EAV, the field definition is a record, and values are rows linked to both the entity and that definition. An organisation can therefore add a field through metadata without creating a column for every new attribute.

A typed variant stores numbers, text and dates in separate structures. The number 72 stays numeric: filtering hours does not require converting a column that also contains descriptions and dates. [Laurenz Albe describes this model](https://www.cybertec-postgresql.com/en/entity-attribute-value-eav-design-in-postgresql-dont-do-it/) and criticises its costs for reconstructing records and writing complex queries. That objection deserves attention when configurability becomes the overriding design criterion.

For this case, I would keep identity, ownership and status in ordinary columns. Their meaning is stable. Delivery time represents the lab's configurable numeric field.

_What EAV changes_

| Requirement | Option to compare | Work to consider |
| --- | --- | --- |
| Stable information used in core rules | Relational column | Schema evolution |
| Variable information mainly read with the record | Columns and JSONB | Validation and access to searched values |
| Fields with their own definition, type and independent use in filters | Typed EAV | Queries, definition management and record reconstruction |

JSONB remains an alternative when searches are needed. PostgreSQL supports GIN and expression indexes, with different capabilities depending on the operators used. The [JSONB indexing documentation](https://www.postgresql.org/docs/18/datatype-json.html#JSON-INDEXING) shows why the comparison should start with an actual query rather than dismissing JSONB as a container that always needs a full scan.

The choice also concerns the people maintaining those filters. Adding the twentieth field through configuration is useful if the team can still explain and check the search that uses it.

## The supplier that must stay out

Use this small teaching dataset. The requirement is: **suppliers in organisation 1 with at least one active site delivering within 48 hours**.

_The supplier that must stay out_

| Organisation | Supplier | Site | Status | Delivery, hours |
| --- | --- | --- | --- | --- |
| 1 | 1 | 11 | Active | 72 |
| 1 | 1 | 12 | Suspended | 24 |
| 1 | 2 | 21 | Active | 24 |
| 1 | 3 | 31 | Active | 36 |
| 1 | 3 | 32 | Active | 48 |
| 1 | 4 | 41 | Active | 72 |
| 2 | 4 | 41 | Active | 12 |

The expected result contains suppliers **2 and 3**. Supplier 1 has no site meeting both conditions. Supplier 3 appears once despite having two qualifying sites. Supplier 4 in organisation 1 is slow; the identically numbered supplier in organisation 2 must not affect its selection.

The mistake comes from two independent questions: is there an active site, and is there a fast site? Both answers are yes for supplier 1, but they refer to different sites. This correlation error can also occur in an ordinary relational model or with JSONB. When EAV values are distributed across separate rows, explicitly identifying the entity to which each condition applies is particularly important.

## Reproduce the case in Docker

You need a running Docker Engine or Docker Desktop, a Bash or Zsh terminal and a text editor. PostgreSQL does not need to be installed on your computer.

Create an empty directory for the lab and open a terminal there. The database holds only teaching data in memory; removing the container deletes it. Choose an unused name. If a container called `eav-demo` already exists, change that name in every command.

Start the official image pinned to PostgreSQL 18.6 and the digest used for this test:

```bash
docker run --detach --name eav-demo \
  --network none \
  --tmpfs /var/lib/postgresql:rw,size=256m \
  --env POSTGRES_PASSWORD=eav-demo-only \
  --env POSTGRES_DB=eav_demo \
  postgres:18.6-bookworm@sha256:bbf83594798e144bff365a9c191c5d779f181fb3135902064ab8ff11918020fc
```

The password is for the demonstration. The container has no network access and publishes no host ports; we connect through `docker exec`. The tmpfs path follows the layout of the [official PostgreSQL 18 image](https://hub.docker.com/_/postgres).

Check that the database is ready:

```bash
docker exec eav-demo pg_isready -U postgres -d eav_demo
```

If initialisation is still running, repeat the command until it reports `accepting connections`. Using your editor, create `demo.sql` in the open directory and paste **all** the following content. It creates the tables, inserts the data, defines both queries and checks their results.

```sql
\set ON_ERROR_STOP on
BEGIN;

CREATE TABLE supplier (
  tenant_id integer,
  id integer,
  name text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);

CREATE TABLE site (
  tenant_id integer,
  id integer,
  supplier_id integer NOT NULL,
  status text NOT NULL CHECK (status IN ('active', 'suspended')),
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, supplier_id) REFERENCES supplier (tenant_id, id)
);

CREATE TABLE number_field (
  tenant_id integer,
  id integer,
  key text NOT NULL,
  unit text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  UNIQUE (tenant_id, key)
);

CREATE TABLE site_number_value (
  tenant_id integer,
  site_id integer,
  field_id integer,
  value numeric NOT NULL CHECK (value >= 0),
  PRIMARY KEY (tenant_id, site_id, field_id),
  FOREIGN KEY (tenant_id, site_id) REFERENCES site (tenant_id, id),
  FOREIGN KEY (tenant_id, field_id) REFERENCES number_field (tenant_id, id)
);

INSERT INTO supplier VALUES
  (1, 1, 'Split conditions'),
  (1, 2, 'One qualifying site'),
  (1, 3, 'Two qualifying sites'),
  (1, 4, 'Slow local site'),
  (2, 4, 'Another organisation');

INSERT INTO site VALUES
  (1, 11, 1, 'active'),
  (1, 12, 1, 'suspended'),
  (1, 21, 2, 'active'),
  (1, 31, 3, 'active'),
  (1, 32, 3, 'active'),
  (1, 41, 4, 'active'),
  (2, 41, 4, 'active');

INSERT INTO number_field VALUES
  (1, 1, 'delivery_hours', 'hour'),
  (2, 1, 'delivery_hours', 'hour');

INSERT INTO site_number_value VALUES
  (1, 11, 1, 72),
  (1, 12, 1, 24),
  (1, 21, 1, 24),
  (1, 31, 1, 36),
  (1, 32, 1, 48),
  (1, 41, 1, 72),
  (2, 41, 1, 12);

CREATE VIEW wrong_result AS
SELECT s.id
FROM supplier AS s
WHERE s.tenant_id = 1
  AND EXISTS (
    SELECT 1
    FROM site AS x
    WHERE x.tenant_id = s.tenant_id
      AND x.supplier_id = s.id
      AND x.status = 'active'
  )
  AND EXISTS (
    SELECT 1
    FROM site AS x
    JOIN site_number_value AS v
      ON (v.tenant_id, v.site_id) = (x.tenant_id, x.id)
    JOIN number_field AS f
      ON (f.tenant_id, f.id) = (v.tenant_id, v.field_id)
    WHERE x.tenant_id = s.tenant_id
      AND x.supplier_id = s.id
      AND f.key = 'delivery_hours'
      AND f.unit = 'hour'
      AND v.value <= 48
  );

CREATE VIEW correct_result AS
SELECT s.id
FROM supplier AS s
WHERE s.tenant_id = 1
  AND EXISTS (
    SELECT 1
    FROM site AS x
    JOIN site_number_value AS v
      ON (v.tenant_id, v.site_id) = (x.tenant_id, x.id)
    JOIN number_field AS f
      ON (f.tenant_id, f.id) = (v.tenant_id, v.field_id)
    WHERE x.tenant_id = s.tenant_id
      AND x.supplier_id = s.id
      AND x.status = 'active'
      AND f.key = 'delivery_hours'
      AND f.unit = 'hour'
      AND v.value <= 48
  );

SELECT 'wrong' AS query, array_agg(id ORDER BY id) AS supplier_ids
FROM wrong_result
UNION ALL
SELECT 'correct', array_agg(id ORDER BY id)
FROM correct_result;

DO $$
BEGIN
  IF ARRAY(SELECT id FROM wrong_result ORDER BY id)
       IS DISTINCT FROM ARRAY[1,2,3] THEN
    RAISE EXCEPTION 'Unexpected wrong-query result';
  END IF;
  IF ARRAY(SELECT id FROM correct_result ORDER BY id)
       IS DISTINCT FROM ARRAY[2,3] THEN
    RAISE EXCEPTION 'Correct query failed';
  END IF;
END $$;

\echo PASS: wrong={1,2,3}; correct={2,3}
COMMIT;
```

Run it from the terminal in that same directory:

```bash
docker exec -i eav-demo psql -X -U postgres -d eav_demo \
  -v ON_ERROR_STOP=1 < demo.sql
```

Among the table-creation messages, you should see:

```txt
  query  | supplier_ids
---------+--------------
 wrong   | {1,2,3}
 correct | {2,3}

PASS: wrong={1,2,3}; correct={2,3}
```

The checks compare the IDs against two explicitly written results. A different result raises an error, and `ON_ERROR_STOP` stops execution. The file expects an empty database. To start again, remove the lab container, recreate it and rerun the file.

This test ran on PostgreSQL 18.6, using the Debian image on Linux arm64. It checks these logical results; seven sites are not a workload for choosing the fastest index.

## Where EAV appears and how the query changes

`number_field` defines numeric fields; `site_number_value` stores the site–field–value relationship. Delivery time is identified by `field_id`, with the readable key `delivery_hours`. The definitions table is already restricted to numeric fields; text and dates would need corresponding structures. The lab stores one current value per site and field, without history or translations.

The primary key `(tenant_id, site_id, field_id)` prevents two current values for the same combination. Composite foreign keys link each value to a site and a field definition in the same organisation. These are integrity constraints; the application still needs to authenticate and authorise the request's tenant. The [PostgreSQL constraints documentation](https://www.postgresql.org/docs/18/ddl-constraints.html) explains composite keys.

In the `wrong_result` view, the first `EXISTS` looks for an active site. The second looks for a site delivering within 48 hours. Both are linked to the supplier, but nothing requires them to use the same site.

In `correct_result`, status and delivery time belong to the same row `x` inside a single `EXISTS`. It asks whether at least one qualifying site exists, so supplier 3's two sites do not duplicate the outer row. This is the behaviour described in the [EXISTS documentation](https://www.postgresql.org/docs/18/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS).

The join `(v.tenant_id, v.site_id) = (x.tenant_id, x.id)` matters too. Remove the tenant and join only on site IDs, and organisation 2's 12-hour value can qualify organisation 1's site 41. Supplier 4 then appears incorrectly. A local ID alone is insufficient in a shared database.

You can query both views again; each runs its query against the current data:

```bash
docker exec eav-demo psql -X -U postgres -d eav_demo \
  -c 'SELECT * FROM correct_result ORDER BY id;'
```

When finished, remove **the container created for this lab**:

```bash
docker rm -f eav-demo
```

The image remains in Docker's cache for reuse.

## Performance comes after this result

The next question is how much work finding those suppliers takes as the data grows. A search can start with suppliers and inspect their sites, or find qualifying sites first and work back to suppliers. The better path depends on value distribution and the relationships traversed.

For example, 20 sites linked to 20 zones each and 400 sites linked to one zone each both involve 400 relationships. Counting only the sites found hides that work. My EAV–JSONB comparison will use the same data and expected results, including filters, permissions and page loading.

On a representative dataset, I would record the plan with `EXPLAIN (ANALYZE, BUFFERS)`, rows processed, index storage and update costs. `ANALYZE` executes the query, so measurement needs a suitable test environment. The [PostgreSQL EXPLAIN guide](https://www.postgresql.org/docs/18/using-explain.html) helps interpret the plan; service latency also needs concurrent requests, a stated load and repeated measurements.

The results page is part of the requirement. Filters and permissions must select suppliers before pagination. Taking twenty rows and then discarding those without qualifying sites can hide other available matches. Ordering also needs a rule for breaking ties.

Query analysis could become a [reusable skill](https://www.matteovincenti.com/en/blog/optimise-skills-claude-codex): take a schema, version, query, expected results and actual plan, then propose a change and a check. The skill has yet to be built; its procedure would start from the correctness test already available.

Before introducing an application cache, I therefore want to know which operation exceeds an acceptable threshold and why. The `correct_result` test becomes the constraint to preserve while the plan changes: optimisation must keep returning 2 and 3, exclude 1 and avoid borrowing another organisation's data.