PostgreSQL advanced types provide type safety, efficient storage, and database-level validation. Key types: (1) UUID: 128-bit unique identifiers (16 bytes, globally unique). (2) INET/CIDR: IP addresses with network operators (16 bytes vs 45 bytes VARCHAR). (3) Range types: Store ranges (INT4RANGE, TSRANGE, DATERANGE) with overlap detection. (4) ENUM: Custom enumerated types (4 bytes, type-safe). (5) Geometric types: POINT, POLYGON for GIS data. Real-world: Instagram uses UUID for distributed IDs. Cloudflare uses INET for IP filtering. Calendly uses TSRANGE for scheduling. Shopify uses ENUM for order status. Trade-off: PostgreSQL-specific (not portable) but provides better type safety and efficiency.

**1. UUID - Universally Unique Identifier:**

UUID is 128-bit identifier, globally unique without coordination.

**UUID vs INT:**

- **INT**: 4 bytes, sequential, requires coordination (auto-increment)

- **UUID**: 16 bytes, random, no coordination needed

**When to use UUID:**

- Distributed systems (multiple databases, no coordination)

- Public IDs (hide sequential IDs from users)

- Merging databases (no ID conflicts)

**When to use INT:**

- Single database (no distribution)

- Performance critical (INT is faster for joins)

- Sequential IDs acceptable

**UUID Example:**

```sql

-- Create table with UUID

CREATE TABLE photos (

photo_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

user_id UUID,

image_url TEXT,

created_at TIMESTAMP DEFAULT NOW()

);

-- Insert with auto-generated UUID

INSERT INTO photos (user_id, image_url)

VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'https://...');

-- photo_id generated automatically

-- Query by UUID

SELECT * FROM photos

WHERE photo_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

```

**Storage comparison:**

- UUID: 16 bytes

- VARCHAR(36): 36+ bytes (2.25x larger)

- INT: 4 bytes (4x smaller than UUID)

**Real-world: Instagram uses UUID for photo IDs**

- Distributed across 1000+ servers

- No coordination needed for ID generation

- No collisions

**2. Network Types - INET, CIDR, MACADDR:**