PostgreSQL Arrays and JSONB eliminate need for junction tables and enable flexible schemas. Real examples: (1) Arrays: E-commerce sites store product tags as arrays (no junction table). Amazon uses arrays for product categories (2-3x faster than JOIN). (2) JSONB: SaaS apps store user preferences as JSONB (flexible schema). Stripe uses JSONB for metadata (no schema changes needed). (3) Array aggregation: Analytics dashboards use array_agg() to group data. Shopify uses array_agg() for order items (1 query vs N queries). (4) JSONB indexing: Search features use GIN indexes on JSONB. GitLab uses GIN indexes for issue metadata (10x faster queries). Trade-offs: Arrays/JSONB are PostgreSQL-specific (not portable to MySQL). But they provide better performance and flexibility than normalized tables.

Amazon: Arrays for Product Categories

Amazon has millions of products with multiple categories. Challenge: Store product categories efficiently without junction tables. Solution: Use arrays to store categories directly (2-3x faster than JOIN).

Amazon uses arrays for product categories: (1) Store categories as TEXT[] array (no junction table), (2) GIN index for fast queries, (3) Query with ANY or @> operators. Architecture: products table with categories TEXT[], GIN index on categories. Benefits: No JOIN needed (2-3x faster), simpler schema (1 table vs 3 tables), fast queries with GIN index.

CREATE TABLE products (

product_id BIGSERIAL PRIMARY KEY,

name VARCHAR(200),

categories TEXT[]

);

CREATE INDEX idx_products_categories ON products USING GIN (categories);

INSERT INTO products (name, categories)

VALUES ('MacBook Pro', ARRAY['Electronics', 'Computers', 'Laptops']);

SELECT * FROM products

WHERE 'Electronics' = ANY(categories);

Millions of products, < 50 categories per product

Arrays: 2-3x faster than junction tables

No JOIN needed, simpler queries

GIN index enables fast queries

Lesson: Arrays for small simple lists

PostgreSQL

Stripe: JSONB for Payment Metadata

Stripe processes billions of payments with custom metadata. Challenge: Store flexible metadata without schema changes. Solution: Use JSONB for flexible schema (no ALTER TABLE needed).

Stripe uses JSONB for payment metadata: (1) Store custom metadata as JSONB (flexible schema), (2) GIN index for fast queries, (3) Query with ->, @> operators. Architecture: payments table with metadata JSONB, GIN index on metadata. Benefits: No schema changes needed (add metadata anytime), fast queries with GIN index, 2-3x faster than MySQL JSON.

CREATE TABLE payments (

payment_id BIGSERIAL PRIMARY KEY,

amount DECIMAL(10, 2),

metadata JSONB

);

CREATE INDEX idx_payments_metadata ON payments USING GIN (metadata);

INSERT INTO payments (amount, metadata)

VALUES (99.99, '{"customer_id": "cus_123", "order_id": "ord_456"}');

SELECT * FROM payments

WHERE metadata @> '{"customer_id": "cus_123"}';

Billions of payments, flexible metadata

JSONB: No schema changes needed

GIN index enables fast queries

2-3x faster than MySQL JSON

Lesson: JSONB for flexible schemas

PostgreSQL

GitLab: GIN Indexes for Issue Metadata