Postgres Table Design and Safe Migrations

POSTGRES SCHEMA: User Activity Tracking

CORE TABLES:

1. USERS (Master dimension)
   Column: id [UUID PRIMARY KEY]
   Column: email [VARCHAR(255) NOT NULL UNIQUE]
   Column: created_at [TIMESTAMPTZ NOT NULL DEFAULT now()]
   Constraint: UNIQUE(email)
   Index: idx_users_created_at (for monthly cohort analysis)

2. ACTIVITY_EVENTS (Fact table—write-heavy)
   Column: id [BIGSERIAL PRIMARY KEY]
   Column: user_id [UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE]
   Column: event_type [VARCHAR(50) NOT NULL] -- e.g., 'login', 'purchase', 'view'
   Column: event_time [TIMESTAMPTZ NOT NULL DEFAULT now()]
   Column: metadata [JSONB DEFAULT '{}'] -- flexible attributes
   Column: session_id [UUID] -- group events into sessions
   
   Constraints:
   • FOREIGN KEY(user_id) ensures referential integrity
   • ON DELETE CASCADE removes events if user deleted (adjust if audit required)
   
   Indexes (CRITICAL for query performance):
   • PRIMARY KEY: idx_events_id (default)
   • idx_events_user_id (filter by user)
   • idx_events_event_time (range queries on date)
   • idx_events_user_time (composite: SELECT * FROM activity WHERE user_id = X AND event_time > NOW() - '7 days')
   • idx_events_event_type (cardinality ~20, speeds up reports by event type)
   • idx_events_metadata_gin (GIN index on JSONB for flexible queries on metadata fields)
   
   Partitioning (If >10M rows/month):
   • Partition by RANGE(event_time) quarterly: activity_q1_2025, activity_q2_2025
   • Keeps index size manageable, speeds up archival (DROP PARTITION vs DELETE)

3. EVENT_TYPES (Reference table—normalization)
   Column: id [SMALLINT PRIMARY KEY]
   Column: type_name [VARCHAR(50) UNIQUE NOT NULL]
   Column: description [TEXT]
   
   Why: VARCHAR(50) in activity_events is safe, but if cardinality is truly <100, consider foreign key to event_types for space efficiency
   Trade-off: Adds join cost, saves ~10% storage

QUERY PATTERNS OPTIMIZED:

Pattern 1: "Get all events for a user in past 7 days"
   SELECT * FROM activity_events 
   WHERE user_id = $1 AND event_time > now() - '7 days'::interval
   • Uses: idx_events_user_time
   • Cost: ~10ms (no full table scan)

Pattern 2: "Count events by type today"
   SELECT event_type, COUNT(*) FROM activity_events
   WHERE event_time >= DATE_TRUNC('day', now())
   GROUP BY event_type
   • Uses: idx_events_event_time, idx_events_event_type
   • Cost: ~100ms (depends on today's volume)

Pattern 3: "Find users with property X in metadata"
   SELECT DISTINCT user_id FROM activity_events
   WHERE metadata->>'campaign_id' = 'camp_123'
   • Uses: idx_events_metadata_gin
   • Cost: ~500ms (slower, JSONB queries less optimized than columns)

NORMALIZATION TRADE-OFFS:

Option A (Current—denormalized):
   • Pros: Fast writes, simple schema, flexible metadata
   • Cons: Some data duplication if user attributes change

Option B (Fully normalized):
   • Add columns to activity_events: user_segment, user_country (denormalize)
   • Pros: Faster analysis queries (no join needed)
   • Cons: Must update activity_events if user segment changes retroactively (hard)
   • Recommendation: Use Option A, denormalize ONLY for immutable user attributes at event time

ARCHIVAL STRATEGY:
   • After 12 months: Move activity_q1_2024 to archive schema (or cold storage)
   • Keep indexes only on last 12 months
   • Reduces active index footprint from 40GB to 10GB

TRAP TO AVOID:
   • Don't put user_email in activity_events (denormalization sin). If email changes, you now have stale data.
   • Don't rely on metadata alone—JSON queries are 5x slower than indexed columns.
   • Don't forget ON DELETE CASCADE—orphaned activity rows break reporting.

FINAL SCHEMA (DDL):
   CREATE TABLE users (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
     email VARCHAR(255) NOT NULL UNIQUE,
     created_at TIMESTAMPTZ NOT NULL DEFAULT now()
   );

   CREATE TABLE activity_events (
     id BIGSERIAL PRIMARY KEY,
     user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
     event_type VARCHAR(50) NOT NULL,
     event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
     metadata JSONB DEFAULT '{}',
     session_id UUID
   ) PARTITION BY RANGE(event_time);

   CREATE INDEX idx_events_user_time ON activity_events(user_id, event_time DESC);
   CREATE INDEX idx_events_event_type ON activity_events(event_type);
   CREATE INDEX idx_events_metadata_gin ON activity_events USING GIN(metadata);

Estimated footprint: 100M events/month = 15GB + indexes, 12-month retention.