Product Catalog Development: EAV, SEO, Variants & Caching

When clients come to us with a slow catalog, filters taking minutes to load, and SEO traffic dropping due to duplicate pages, the root cause is often poor architecture: wrong category model, inefficient pagination, or lack of caching. Developing a product catalog is the central task for an e-commerce store, and getting it right can save up to 40% of support budget (infrastructure cost reduction up to 300,000 RUB per year). Statistics show 70% of users leave a site if the catalog takes longer than 3 seconds to load, and a well-designed catalog can boost conversion by 20%. With 8 years in catalog development and over 50 projects for stores with 10,000+ daily visitors, we know what works.

The catalog is the core module of an e-commerce site. Its architecture determines search speed, navigation ease, and SEO traffic. Mistakes here are the most costly, requiring data migration and reworking dependent modules. A properly designed product catalog handles up to 500,000 items without performance loss.

Which Category Model to Choose?

The category tree is stored in the database. Two common approaches:

Adjacency List – each record stores parent_id. Simple writes, but reading the full tree requires a recursive CTE:

WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id, 0 AS depth FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, ct.depth + 1 FROM categories c JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree ORDER BY depth, name; 

Nested Sets (MPTT) – each record stores lft and rgt values. Subtree query: WHERE lft BETWEEN :parent_lft AND :parent_rgt – one query, no recursion. Writes are more complex: adding a node updates all right siblings.

Closure Table – a separate table with all ancestor–descendant pairs. Most flexible, more storage. Optimal for complex tree operations (moving subtrees).

Our recommendation: for catalogs up to 10,000 categories, Adjacency List with Redis tree caching is sufficient. MPTT when subtree queries are frequent and no cache is used.

How to Implement Product Attributes Correctly?

Products of different categories have different attribute sets. Three approaches:

• Fixed columns table: products.color, products.size, products.weight. Works only with homogeneous assortment. Adding a new attribute requires ALTER TABLE, migration, deploy.
• EAV (Entity-Attribute-Value): flexible but slow with JOINs. For filtering, you need Elasticsearch or a denormalized index.
• JSONB column in PostgreSQL:

ALTER TABLE products ADD COLUMN attributes JSONB; CREATE INDEX ON products USING GIN (attributes); 

A compromise: flexibility of EAV without excessive JOINs. As documented in PostgreSQL documentation, JSONB columns provide EAV flexibility without extra tables. Suitable for catalogs up to 500,000 products.

Product Variants: Parent-Child

Products with variants (color × size) are common. Two patterns:

• Simple SKU: each combination is a separate record in products. Simple but hard to manage parent card.
• Parent-Child: parent product type 'variable' and child 'variant' with specific attribute combinations.

products (id, type, parent_id, sku, name, price, stock) -- type: 'simple' | 'variable' | 'variant' -- variant: parent_id → variable product 

When displaying the product card, load parent + all variants. User selects attribute combination → find corresponding variant → update price, photo, stock. For the variant matrix, use an object indexed by attribute ID.

URL Structure and SEO

Category URLs are critical for SEO. Three options:

• Flat: /catalog/noutbuki – simple, loses hierarchy context.
• Hierarchical: /catalog/elektronika/kompyutery/noutbuki – better for SEO, harder when category is moved.
• Hybrid: /noutbuki-c142 – readable slug + unique ID (resilient to renames).

For filtered pages: /noutbuki?brand=apple&ram=16 with canonical to /noutbuki or separate SEO pages for popular combinations (/noutbuki-apple-16gb as static aggregator). Schema.org: ItemList on category pages with ListItem for each product in listing.

Pagination and Infinite Scroll

Offset pagination: LIMIT 48 OFFSET 144. Works, but on deep pages (OFFSET 10000) PostgreSQL still reads 10048 rows. Solution – keyset pagination:

SELECT * FROM products WHERE (sort_value, id) > (:last_sort_value, :last_id) ORDER BY sort_value, id LIMIT 48; 

Keyset pagination is instant at any depth, but doesn't support arbitrary page jumps.

For mobile – infinite scroll with IntersectionObserver, for desktop with SEO priority – classic pagination (search engines better index pages with explicit numbers).

Catalog Management in CMS

Admin interface for the catalog:

• Bulk editing: select 50 products → change category/status/price.
• Import from CSV/XLSX: column mapping, preview with errors, background loading via queue.
• Drag-and-drop sorting: visual tree with reorder ability.
• Attribute management: add attribute to category – it appears on edit forms of all category products.

For bulk import: Laravel Jobs + Horizon. File uploaded to S3, job picks from queue, parses line-by-line (via league/csv or PhpSpreadsheet), products inserted in batches of 100.

How to Speed Up the Catalog with Caching?

Catalog pages are the main DB load. Caching strategy:

When a product changes, flush cache only on pages where it appears. Cache tags in Laravel: Cache::tags(['category:electronics'])->flush(). Optimal caching configuration can reduce page load time from 3 to 0.5 seconds. Caching can cut server costs up to 200,000 RUB per year.

For catalogs with high dynamics (frequent price or stock changes), reduce listing TTL to 1-2 minutes and use tag-based invalidation. For static catalogs, increase TTL to an hour.

Timelines

• Basic catalog (categories, product list, card, pagination): 2–3 weeks.
• With variants, EAV attributes, import, and caching: 4–7 weeks.
• Integration with Elasticsearch for search and filters adds 2–3 weeks.

What's Included

1. Preparation of technical specification with detailed data model.
2. Design and implementation of category hierarchy, attributes, and variants.
3. Development of SEO-optimized URL structure and Schema.org markup.
4. Configuration of pagination and caching.
5. Integration with admin panel for assortment management.
6. Code and API documentation, team training, access handover.
7. 30-day warranty support after delivery.

If you want to estimate the scope for your project, contact us – we will analyze your current catalog and propose a solution. Get a free catalog audit: request an engineer consultation. Our specialist will contact you within a day.

Common Mistakes in Catalog Design

• Using offset pagination for catalogs >10,000 products – leads to slowdowns on deep pages.
• EAV without indexing and caching – kills performance during filtering.
• Missing canonical on filtered pages – creates duplicates and lowers ranking.
• Flat URL without rename protection – loses SEO weight.

These mistakes increase load time by 40% and reduce conversion. Avoid them with proper design using modern practices.