PostgreSQL Arrays and JSONB provide native support for complex data without junction tables or schema changes. Key features: (1) Arrays: Store lists in single column (tags, categories, phone numbers), no junction table needed, 2-3x faster than JOIN. (2) JSONB: Binary JSON format (2-3x faster than MySQL JSON), indexable with GIN, flexible schema. (3) Operators: Array operators (@> contains, && overlaps), JSONB operators (-> get, ->> get text, @> contains, ? exists). (4) Indexing: GIN indexes enable fast queries on arrays and JSONB. Real-world: Amazon uses arrays for product categories. Stripe uses JSONB for metadata. Trade-off: PostgreSQL-specific (not portable) but provides better performance than normalized tables.

**1. PostgreSQL Arrays - Native List Support:**

Arrays store lists in a single column without junction tables.

**Arrays vs Junction Tables:**

- **Junction table**: Requires JOIN (slower), normalized, flexible queries

- **Array**: No JOIN needed (2-3x faster), denormalized, limited queries

**When to use Arrays:**

- Simple lists (tags, categories, phone numbers)

- Read-heavy workloads (no frequent updates)

- Order matters (arrays preserve order)

- Small lists (< 100 items)

**When to use Junction tables:**

- Complex relationships (many-to-many)

- Frequent updates (add/remove items)

- Need to query relationship attributes

- Large lists (> 100 items)

**Array Syntax:**

```sql

-- Create table with array

CREATE TABLE products (

product_id SERIAL PRIMARY KEY,

name VARCHAR(200),

tags TEXT[] -- Array of text

);

-- Insert with array literal

INSERT INTO products (name, tags)

VALUES ('Laptop', ARRAY['electronics', 'computers', 'portable']);

-- Alternative syntax with {}

INSERT INTO products (name, tags)

VALUES ('Phone', '{electronics,mobile,smartphone}');

-- Query: Find products with specific tag

SELECT * FROM products

WHERE 'electronics' = ANY(tags);

-- ANY checks if value exists in array

-- Query: Find products with all tags

SELECT * FROM products

WHERE tags @> ARRAY['electronics', 'portable'];

-- @> checks if array contains all elements

-- Query: Find products with overlapping tags

SELECT * FROM products