PostgreSQL advanced types eliminate need for application-level validation and enable database-level constraints. Real examples: (1) UUID: Distributed systems need globally unique IDs. Instagram uses UUIDs for photo IDs (no collisions across shards). (2) INET: Store IP addresses efficiently. Cloudflare uses INET for IP filtering (16 bytes vs 45 bytes VARCHAR). (3) Range types: Scheduling systems need time ranges. Calendly uses TSRANGE for appointment slots (check overlaps in database). (4) ENUM: Order status (pending, processing, shipped). Shopify uses ENUM for order status (type-safe, 4 bytes vs 20+ bytes VARCHAR). (5) Geometric types: Location-based apps need distance calculations. Uber uses POINT with PostGIS for driver locations. Trade-offs: Advanced types are PostgreSQL-specific (not portable to MySQL). But they provide type safety, efficient storage, and database-level validation.

Instagram: UUID for Distributed Photo IDs

Instagram has 2B users, 100M photos/day across 1000+ database shards. Challenge: Generate unique photo IDs without coordination. Solution: Use UUID for globally unique IDs.

Instagram uses UUID for photo IDs: (1) Globally unique (no collisions across shards), (2) No coordination needed (fast), (3) Generate client-side (before insert). Architecture: photos table with photo_id UUID PRIMARY KEY, gen_random_uuid() for auto-generation. Benefits: No coordination, no collisions, fast inserts.

CREATE TABLE photos (

photo_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

user_id UUID,

image_url TEXT

);

INSERT INTO photos (user_id, image_url)

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

RETURNING photo_id;

2B users, 100M photos/day, 1000+ shards

UUID: Globally unique, no coordination

16 bytes storage vs 36 bytes VARCHAR

No collisions across shards

Lesson: UUID for distributed systems

PostgreSQL

Cloudflare: INET for IP Filtering

Cloudflare handles billions of requests/day. Challenge: Store IP addresses efficiently, filter by network. Solution: Use INET type with network operators.

Cloudflare uses INET for IP addresses: (1) Efficient storage (16 bytes vs 45 bytes VARCHAR), (2) Network operators (<< contained by, >> contains), (3) Fast filtering with indexes. Architecture: access_logs table with ip_address INET, index on ip_address. Benefits: 2.8x smaller storage, network operators, fast queries.

CREATE TABLE access_logs (

log_id BIGSERIAL PRIMARY KEY,

ip_address INET,

created_at TIMESTAMP

);

CREATE INDEX idx_ip ON access_logs (ip_address);

SELECT * FROM access_logs

WHERE ip_address << '10.0.0.0/8';

Billions of requests/day

INET: 16 bytes vs 45 bytes VARCHAR (2.8x smaller)

Network operators for filtering

Fast queries with indexes

Lesson: INET for IP addresses

PostgreSQL

Calendly: TSRANGE for Scheduling

Calendly is a scheduling platform. Challenge: Prevent double-booking without race conditions. Solution: Use TSRANGE with exclusion constraints.

Calendly uses TSRANGE for appointments: (1) Exclusion constraint prevents overlaps, (2) Database-level validation (no race conditions), (3) Overlap detection with && operator. Architecture: appointments table with time_slot TSRANGE, EXCLUDE USING GIST constraint. Benefits: No race conditions, database-level validation, simple code.

CREATE TABLE appointments (

appointment_id SERIAL PRIMARY KEY,