EAV in PostgreSQL: correct queries before performance

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:

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 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.

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 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 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:

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.

Check that the database is ready:

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.

\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:

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:

  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 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.

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:

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:

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 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: 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.