Product Attribute Management: Filterable, Grouped and Variant Attributes

Give filterable, grouped and variant roles to attributes, not tables

A catalog needs three different views of the same attribute: whether it shows up as a storefront filter, whether it renders in the product spec table, and whether it defines a variant. Bolting each view onto its own structure — a filter column here, a JSON blob there, a hardcoded array in the variant generator — creates three copies of “what values exist” with no foreign key holding them together. Add a value in one place and the other two fall behind without anyone noticing.

The fix is one attributes table where role is a set of independent booleans, not an enum. color is filterable and variant-defining at the same time; care_instructions is grouped for the spec table but neither of the others. An enum would force a single choice per attribute; booleans let an attribute carry however many roles it actually has.

Select-type attributes — the ones with a fixed option list, like color or size — get a second table, attribute_options, so the list has one canonical order. Storefront facets, the spec table and the variant picker all join through attribute_id to read that same list, instead of each keeping a private copy.

graph LR
    A[attributes] --> B[attribute_options]
    B --> C[Storefront facets]
    B --> D[Product spec table]
    B --> E[Variant picker]

CREATE TABLE attributes (
    id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code                text NOT NULL UNIQUE,
    name                text NOT NULL,
    data_type           text NOT NULL CHECK (data_type IN ('text', 'number', 'boolean', 'select', 'multiselect')),
    is_filterable       boolean NOT NULL DEFAULT false,
    is_grouped          boolean NOT NULL DEFAULT true,
    is_variant_defining boolean NOT NULL DEFAULT false
);

CREATE TABLE attribute_options (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    attribute_id bigint NOT NULL REFERENCES attributes (id) ON DELETE CASCADE,
    value        text NOT NULL,
    sort_order   integer NOT NULL,
    UNIQUE (attribute_id, sort_order),
    UNIQUE (attribute_id, value)
);

Query WHERE is_filterable and you get the exact set of attributes the facet sidebar should render. Query WHERE is_variant_defining and you get the exact set the variant generator should use. Neither query touches option values directly — those live in attribute_options, joined by attribute_id, with one sort order every consumer respects.

You should now see: Querying WHERE is_filterable or WHERE is_variant_defining on the attributes table returns the exact set of attributes each subsystem should use, with no duplicated option lists.

Scope attributes to categories with attribute sets instead of migrations

Attributes need scope: tensile_strength makes sense on power tools, not on t-shirts. Rather than encode that scope in application logic or a migration, PIM tools like Akeneo model it as a “family” — a named list of attribute codes a product in that family must or may carry (Family data structure (opens in a new tab)). This schema calls the same idea an attribute set, scoped to a category.

attribute_set_attributes is the join table tying an attribute set to the attributes it includes, with an is_required flag per pairing. This decouples an attribute’s definition — its code, type, roles — from every place it gets used. color can belong to five attribute sets without five copies of its definition existing anywhere.

A product belongs to one attribute set, either assigned directly or inherited from its category default. “Which attributes apply to this product” becomes a join against that single attribute_set_id, not a schema check scattered across the codebase.

erDiagram
    ATTRIBUTE_SETS ||--o{ ATTRIBUTE_SET_ATTRIBUTES : includes
    ATTRIBUTES ||--o{ ATTRIBUTE_SET_ATTRIBUTES : "used in"
    ATTRIBUTE_SETS ||--o{ PRODUCTS : scopes
    ATTRIBUTES ||--o{ ATTRIBUTE_OPTIONS : has

CREATE TABLE attribute_sets (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_id bigint NOT NULL REFERENCES categories (id),
    name        text NOT NULL
);

CREATE TABLE attribute_set_attributes (
    attribute_set_id bigint NOT NULL REFERENCES attribute_sets (id) ON DELETE CASCADE,
    attribute_id      bigint NOT NULL REFERENCES attributes (id) ON DELETE CASCADE,
    is_required       boolean NOT NULL DEFAULT false,
    PRIMARY KEY (attribute_set_id, attribute_id)
);

ALTER TABLE products
    ADD COLUMN attribute_set_id bigint NOT NULL REFERENCES attribute_sets (id);

-- Every attribute (with role flags) applicable to a given product
SELECT a.id, a.code, a.name, a.data_type,
       a.is_filterable, a.is_grouped, a.is_variant_defining,
       asa.is_required
FROM products p
JOIN attribute_set_attributes asa ON asa.attribute_set_id = p.attribute_set_id
JOIN attributes a ON a.id = asa.attribute_id
WHERE p.id = $1
ORDER BY a.name;

Adding a new spec field to a category no longer touches DDL. Insert a row into attributes, insert a row into attribute_set_attributes, and the field exists everywhere it needs to — no ALTER TABLE, no deploy.

You should now see: Running the applicable-attributes query for a product returns only the attributes relevant to its category, and adding a new spec field to that category needs no migration.

Store attribute values in jsonb and pick the right GIN index

PostgreSQL’s own documentation recommends jsonb over json for nearly every use case: jsonb stores a decomposed binary form that supports indexing, while json stores an exact text copy that gets reparsed on every read (PostgreSQL JSON types (opens in a new tab)). Store per-product attribute values as one jsonb column keyed by attribute code — product.attribute_values — rather than one row per value in an EAV table. An EAV table solves a problem jsonb already solves, at the cost of a join per attribute.

A GIN index on a jsonb column defaults to the jsonb_ops operator class, which indexes every key and value pair and supports ?, ?|, ?& and @> (PostgreSQL index types (opens in a new tab)). That flexibility costs index size: jsonb_ops indexes far more than most query patterns actually need.

Facet filtering only ever asks one question: does this product’s attribute_values contain a given key-value pair? That’s the @> containment operator, and jsonb_path_ops builds a smaller, faster index for exactly that operator — at the cost of dropping ?, ?| and ?& entirely. If nothing in the codebase relies on key-existence queries, switch to it.

ALTER TABLE products
    ADD COLUMN attribute_values jsonb NOT NULL DEFAULT '{}'::jsonb;

-- jsonb_ops: supports ?, ?|, ?&, and @> — larger index, more operators
CREATE INDEX idx_products_attrs_ops
    ON products USING gin (attribute_values);

-- jsonb_path_ops: supports only @> — smaller, faster for containment
CREATE INDEX idx_products_attrs_path_ops
    ON products USING gin (attribute_values jsonb_path_ops);

EXPLAIN SELECT id, name
FROM products
WHERE attribute_values @> '{"color": "red"}'::jsonb;

You should now see: EXPLAIN on the containment query shows the GIN index being used instead of a sequential scan.

Fix PostgreSQL 18’s virtual generated column index error

PostgreSQL 18, released 2025-09-25, added virtual generated columns and made VIRTUAL the default behaviour for GENERATED ALWAYS AS (PostgreSQL 18 release notes (opens in a new tab)). A virtual generated column computes its value when the row is read; nothing is written to disk unless STORED is named explicitly (Generated columns (opens in a new tab)).

That default breaks a common pattern: extracting one jsonb key — brand, say — into its own column so a plain B-tree can serve exact-match lookups faster than the GIN index from the previous section serves single-key filters. Add the generated column at its PostgreSQL 18 default and index it, and the index creation fails outright.

ALTER TABLE products
    ADD COLUMN brand text GENERATED ALWAYS AS (attribute_values ->> 'brand');

CREATE INDEX idx_products_brand ON products (brand);

ERROR:  indexes on virtual generated columns are not supported

The fix is one word: name STORED explicitly. That materializes the value on write, trading a small amount of storage and write cost for the ability to index it at all.

ALTER TABLE products
    DROP COLUMN brand;

ALTER TABLE products
    ADD COLUMN brand text GENERATED ALWAYS AS (attribute_values ->> 'brand') STORED;

CREATE INDEX idx_products_brand ON products (brand);

CREATE INDEX

Whenever a generated column needs an index under PostgreSQL 18, it has to be STORED — VIRTUAL and indexable are mutually exclusive in this release.

You should now see: CREATE INDEX succeeds on the STORED column, and the reader knows to name STORED explicitly whenever a generated column needs an index under PostgreSQL 18.

Sync only filterable attributes into Meilisearch’s filterableAttributes

filterableAttributes is empty by default in Meilisearch. A field can appear in every document in an index and still be unusable in a filter until it’s added to this setting — writing the field into documents does nothing on its own (Filter search results (opens in a new tab)).

The setting accepts a plain array of attribute names, or objects using attributePatterns (which support wildcards) with a features block that toggles facetSearch and filter.equality / filter.comparison independently per attribute (Settings API reference (opens in a new tab)).

Generate that payload from the same flag driving the storefront facet UI. Select code WHERE is_filterable = true from attributes, build the array, PATCH it to the index settings endpoint. filterableAttributes can never drift from the flag, because it’s derived from the flag on every sync rather than maintained separately by hand.

import { Client } from "pg";
import { MeiliSearch } from "meilisearch";

const pg = new Client({ connectionString: process.env.DATABASE_URL });
const meili = new MeiliSearch({
  host: "http://127.0.0.1:7700",
  apiKey: "<YOUR_API_TOKEN>",
});

async function syncFilterableAttributes() {
  await pg.connect();
  const { rows } = await pg.query(
    "SELECT code FROM attributes WHERE is_filterable = true ORDER BY code"
  );
  const filterableAttributes = rows.map((r) => r.code);

  const task = await meili
    .index("products")
    .updateFilterableAttributes(filterableAttributes);

  return task.taskUid;
}

Meilisearch also needs a primary key made of an integer or an alphanumeric/hyphen/underscore string. On an empty index it tries to infer the key by finding exactly one top-level field ending in id (Primary key (opens in a new tab)). A document shaped like { id, category_id, ... } has two candidates, and Meilisearch rejects the entire batch with index_primary_key_multiple_candidates_found — no partial write.

Set primaryKey explicitly at index creation instead of relying on inference, and keep other *_id fields nested rather than top-level.

await meili.createIndex("products", { primaryKey: "id" });

{
  "id": "prod_10432",
  "name": "Trail Running Shoe",
  "category": {
    "id": 58,
    "name": "Running Shoes"
  },
  "color": "red",
  "size": "10",
  "brand": "Northline"
}

You should now see: A filter request such as color = "red" succeeds against the index, and re-running the sync after adding a new filterable attribute in Postgres updates filterableAttributes with no manual settings edit.

Wait for Meilisearch’s async indexing task before trusting facet counts

addDocuments, updateDocuments and settings updates all return immediately with a taskUid and status enqueued, moving through processing to succeeded or failed (Asynchronous operations (opens in a new tab)). The index only reflects the write once that task reaches succeeded.

A bulk indexing job that calls addDocuments and immediately reads facetDistribution in the next request sees counts that exclude the products it just wrote. The write hasn’t finished yet, even though the HTTP call already returned.

sequenceDiagram
    participant Job as Sync job
    participant Meili as Meilisearch
    Job->>Meili: POST /indexes/products/documents
    Meili-->>Job: 202 Accepted, taskUid: 42, status: enqueued
    Job->>Meili: GET /tasks/42
    Meili-->>Job: status: processing
    Job->>Meili: GET /tasks/42
    Meili-->>Job: status: succeeded
    Job->>Meili: GET /indexes/products/search?facets=color
    Meili-->>Job: facetDistribution includes new products

Poll GET /tasks/{taskUid} — or call the SDK’s waitForTask helper — until status is succeeded before treating the index as caught up. This matters most after a filterableAttributes change, since that setting update triggers a full background reindex, not just a metadata flip.

async function waitForTask(meili: MeiliSearch, taskUid: number, intervalMs = 500) {
  while (true) {
    const task = await meili.getTask(taskUid);
    if (task.status === "succeeded") return task;
    if (task.status === "failed") {
      throw new Error(`Meilisearch task ${taskUid} failed: ${JSON.stringify(task.error)}`);
    }
    await new Promise((resolve) => setTimeout(resolve, intervalMs));
  }
}

const enqueuedTask = await meili.index("products").addDocuments(productDocuments);
await waitForTask(meili, enqueuedTask.taskUid);

Distinguish this transient lag from real drift. A facet value with zero matching products that’s still zero after every task has finished succeeded isn’t a timing issue — it means something wrote to the index directly, or synced from a stale copy, bypassing the is_filterable-driven sync job from the previous section.

You should now see: Facet counts read immediately after a sync job include the newly written products because the job waited for succeeded before returning.

Cap variant-defining attributes before the option cartesian product explodes

Only attributes flagged is_variant_defining feed the variant generator. brand is filterable, not variant-defining — if it ends up in the generator’s join, the cartesian product multiplies by every brand value that happens to share an attribute set, and variant counts explode for the wrong reason.

Variant rows are the cartesian product of each variant-defining attribute’s option list for a product: 3 sizes times 4 colors is 12 variants, 3 sizes times 4 colors times 5 materials is 60. Shopify caps every product at three options and lets their combinations generate up to 2,048 variants, following the limit increase that took effect 2025-10-15 (Shopify developer changelog (opens in a new tab); Adding variants (opens in a new tab)). That’s a useful reference ceiling for a self-hosted catalog, not a requirement — pick a number that matches what the storefront and fulfilment systems can actually handle.

-- Projected variant count for a product, from its variant-defining attributes only
SELECT EXP(SUM(LN(option_count)))::bigint AS projected_variant_count
FROM (
    SELECT ao.attribute_id, COUNT(*) AS option_count
    FROM attribute_set_attributes asa
    JOIN attributes a ON a.id = asa.attribute_id AND a.is_variant_defining
    JOIN attribute_options ao ON ao.attribute_id = a.id
    JOIN products p ON p.attribute_set_id = asa.attribute_set_id
    WHERE p.id = $1
    GROUP BY ao.attribute_id
) option_counts;

Guard the generator with an explicit count check that multiplies option counts before inserting a single row. Reject or warn before the unbounded insert, not after a slow query or a rejected bulk-create call surfaces it downstream.

const MAX_VARIANTS_PER_PRODUCT = 500;

async function generateVariants(pg: Client, productId: number) {
  const { rows } = await pg.query(
    `SELECT EXP(SUM(LN(option_count)))::bigint AS projected_count
     FROM (
       SELECT ao.attribute_id, COUNT(*) AS option_count
       FROM attribute_set_attributes asa
       JOIN attributes a ON a.id = asa.attribute_id AND a.is_variant_defining
       JOIN attribute_options ao ON ao.attribute_id = a.id
       JOIN products p ON p.attribute_set_id = asa.attribute_set_id
       WHERE p.id = $1
       GROUP BY ao.attribute_id
     ) option_counts`,
    [productId]
  );

  const projected = Number(rows[0]?.projected_count ?? 0);
  if (projected > MAX_VARIANTS_PER_PRODUCT) {
    throw new Error(
      `Product ${productId} would generate ${projected} variants, over the ${MAX_VARIANTS_PER_PRODUCT} cap`
    );
  }

  // safe to insert the cartesian product of variant-defining options
}

flowchart TD
    A[Load variant-defining attributes for product] --> B[Multiply option counts]
    B --> C{Projected count > cap?}
    C -->|Yes| D[Reject: raise ceiling error]
    C -->|No| E[Insert cartesian product as variant rows]

“Far more variants than expected” almost always traces back to the roles model from the first section: an attribute flagged is_variant_defining that should only have been is_filterable.

You should now see: Generating variants for a product with two variant-defining attributes produces exactly the expected row count, and a misconfigured product raises the ceiling error before an unbounded insert.

What this attribute model doesn’t cover

This model doesn’t handle multi-locale attribute labels or per-channel required/optional flags the way Akeneo families do — attribute_set_attributes here is scoped once, not per channel (Family data structure (opens in a new tab)). Add a locale column and a channel dimension to the join table before treating this as a multi-storefront design.

It doesn’t remove the eventual-consistency window between a Postgres write and a succeeded Meilisearch task, either. A read issued immediately after a write can still lag by however long the sync job’s own polling interval takes to notice succeeded — the wait loop from the previous section shortens that window, it doesn’t close it.

It doesn’t replace a PIM. Approval workflows, attribute-level permissions and multi-tenant attribute overrides aren’t modelled here at all.

It also assumes one attribute_values jsonb blob per product stays small enough to reparse cheaply on write. A catalog with thousands of attributes per product should revisit jsonb against per-attribute columns before adopting this design as written.