Choosing PostgreSQL for the Fashion ERP System

databasepostgresqlerp

Context

A fashion industry client needed an ERP system to manage inventory, orders, suppliers, and product catalogs. The data model was highly relational with complex queries spanning multiple entities -- products with size/color variants, multi-warehouse inventory, purchase orders with line items, and supplier contracts. The database choice would be foundational to the entire system.

Decision

Use PostgreSQL as the primary database for the fashion ERP system

Alternatives Considered

MySQL

Pros
  • Team had extensive MySQL experience from previous projects
  • Wide hosting support and mature tooling
  • Good performance for simple read-heavy workloads
  • Lower memory footprint than PostgreSQL for basic operations
Cons
  • Limited support for complex queries and CTEs (at the time, MySQL 8 CTE support was newer and less optimized)
  • Weaker JSON support compared to PostgreSQL's JSONB
  • Partial index support was absent
  • Less robust handling of concurrent write-heavy workloads with MVCC

MongoDB

Pros
  • Flexible schema for product catalogs with varying attributes
  • Good horizontal scaling story with sharding
  • Native JSON document model
Cons
  • ERP data is inherently relational -- orders reference products, inventory, suppliers
  • No ACID transactions across collections (at the time, multi-document transactions were new and limited)
  • Complex reporting queries would require aggregation pipelines instead of SQL joins
  • Denormalization leads to data consistency challenges in an ERP context

PostgreSQL

Pros
  • Excellent support for complex relational queries, window functions, and CTEs
  • JSONB columns for semi-structured data like product attributes and custom fields
  • Partial indexes for optimizing queries on filtered subsets
  • Strong ACID compliance and MVCC for concurrent operations
  • Rich extension ecosystem (pg_trgm for fuzzy search, PostGIS if needed)
Cons
  • Slightly higher operational complexity than MySQL for basic setups
  • Team needed some upskilling from MySQL-centric experience
  • Vacuum maintenance requires monitoring

Reasoning

The fashion ERP domain is deeply relational -- a single order page requires joining orders, line items, product variants, inventory levels, warehouse locations, and supplier details. PostgreSQL's query planner handles these multi-table joins efficiently, and features like CTEs and window functions were essential for reporting (e.g., inventory turnover by category, supplier lead time analysis). The JSONB column type was particularly valuable for product attributes, where different product categories have different attribute sets (shoes have size charts, clothing has fabric composition) without requiring an EAV anti-pattern.

Context and Background

The fashion ERP project was a greenfield system for a mid-size fashion brand managing roughly 5,000 active SKUs across 200+ product lines, 3 warehouses, and 50+ supplier relationships. The business processes were typical of fashion retail: seasonal buying cycles, complex size/color matrix inventory, multi-channel order fulfillment, and detailed supplier management with different pricing tiers and lead times.

Previous systems the client had used were spreadsheet-based, and the data was a mess of inconsistent formats and duplicated entries. The ERP needed to be the single source of truth for product data, inventory, orders, and supplier contracts. Data integrity was paramount — an inventory miscalculation could mean overselling a popular item or missing a reorder deadline for the next season.

The data model was complex and deeply interconnected. A single product could have 30+ variants (5 sizes times 6 colors), each variant tracked independently across 3 warehouses, with inventory movements triggered by purchase orders, sales orders, transfers, and adjustments. Reporting requirements included real-time inventory valuation, supplier performance dashboards, and seasonal sell-through analysis — all requiring complex multi-table aggregations.

Implementation

  1. Schema design with PostgreSQL-specific features: Designed a normalized schema with ~45 tables. Used PostgreSQL’s JSONB columns for product custom attributes (e.g., fabric_composition, care_instructions) that varied by product category. This avoided the entity-attribute-value pattern while keeping the schema flexible. Added GIN indexes on JSONB columns for efficient attribute filtering.

  2. Inventory tracking with window functions: Built the inventory ledger using an append-only inventory_movements table. Current stock levels were calculated using window functions: SUM(quantity) OVER (PARTITION BY variant_id, warehouse_id ORDER BY created_at). This approach provided a complete audit trail while maintaining query performance through materialized views that refreshed every 5 minutes.

  3. Complex reporting with CTEs: Supplier performance reports used recursive CTEs to traverse the purchase order lifecycle (ordered, shipped, received, inspected) and calculate lead times, defect rates, and on-time delivery percentages. These reports ran against read replicas to avoid impacting transactional workloads.

  4. Partial indexes for performance: Created partial indexes for common query patterns. For example, CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'PENDING' dramatically sped up the order processing dashboard, which only showed actionable orders. Similar partial indexes were created for low-stock inventory alerts and overdue purchase orders.

  5. Connection pooling and Hibernate tuning: Used PgBouncer for connection pooling in front of PostgreSQL, configured in transaction mode. Tuned Hibernate’s batch size to 50 for bulk operations (seasonal catalog imports could create thousands of product variants). Enabled Hibernate’s query plan cache and set hibernate.jdbc.batch_size appropriately.

  6. Backup and disaster recovery: Configured automated daily backups via pg_dump with point-in-time recovery enabled through WAL archiving to S3. Tested recovery procedures monthly. Set up a streaming replica for read scaling and failover.

Results

  • The ERP system handled the client’s full catalog of 5,000+ SKUs with complex variant matrices, with inventory queries returning in under 50ms even on the largest tables
  • JSONB product attributes eliminated the need for ~15 additional tables that an EAV approach would have required, simplifying both the schema and application code
  • Supplier performance reports that previously took the client hours to compile manually in spreadsheets now generated in under 10 seconds via PostgreSQL CTEs and window functions
  • Partial indexes reduced the order dashboard query time from ~800ms to ~15ms by scanning only the relevant subset of orders
  • Zero data integrity issues over the first year of production, with PostgreSQL’s ACID guarantees preventing the inventory discrepancies that plagued the spreadsheet-based system
  • The system successfully handled seasonal peak loads (2-3x normal volume during fashion weeks and holiday seasons) without database performance degradation
All decisions