ser_events WHERE tenant_id = $1 AND payload->>'type' = $2 `;

// ✅ Best practice: Include partition key in WHERE clause. // Planner prunes irrelevant partitions. const optimizedQuery = SELECT * FROM user_events WHERE tenant_id = $1 AND event_time BETWEEN $2 AND $3 AND payload->>'type' = $4;

async function getEvents(tenantId: string, type: string, start: Date, end: Date) { const res = await pool.query(optimizedQuery, [ tenantId, start.toISOString(), end.toISOString(), type ]); return res.rows; }

#### 5. Architecture Decisions
*   **Automation:** Partition creation must be automated. Manual DDL is error-prone and does not scale. Use extensions like `pg_partman` or application-level cron jobs to create partitions ahead of time.
*   **Granularity:** Balance partition size against catalog overhead. Partitions should ideally range from 1GB to 10GB. Too many partitions (e.g., daily partitions over 10 years) can bloat the system catalog and increase query planning time.
*   **Indexing:** Indexes are created on the parent table and automatically propagated to new partitions. However, index size per partition is smaller, improving index cache efficiency.

## Pitfall Guide

Production partitioning introduces specific failure modes. Avoid these common mistakes to ensure stability and performance.

1.  **Partition Pruning Failure:**
    *   *Mistake:* Queries do not include the partition key or use non-sargable expressions.
    *   *Impact:* The planner scans all partitions, resulting in worse performance than a monolithic table due to overhead.
    *   *Mitigation:* Always verify execution plans with `EXPLAIN ANALYZE`. Ensure partition keys are in the `WHERE` clause and not wrapped in functions.

2.  **Catalog Bloat:**
    *   *Mistake:* Creating excessive partitions (e.g., hourly partitions for multi-year data).
    *   *Impact:* Query planning time increases linearly with partition count. Memory usage for the system catalog grows.
    *   *Mitigation:* Limit total partition count to <1,000 where possible. Use coarser granularity (monthly) unless daily granularity is strictly required for retention.

3.  **Data Skew in Hash Partitioning:**
    *   *Mistake:* Using a partition key with non-uniform distribution in hash partitioning.
    *   *Impact:* One partition becomes a hotspot, negating distribution benefits.
    *   *Mitigation:* Analyze data distribution before choosing hash. If skew exists, consider list partitioning or composite keys.

4.  **The Unique Constraint Trap:**
    *   *Mistake:* Attempting to create a unique index on a column not included in the partition key.
    *   *Impact:* Database rejects the constraint. Application logic assumes uniqueness that cannot be enforced.
    *   *Mitigation:* Design primary keys to include partition columns. If global uniqueness is required, implement application-level checks or use a distributed ID generator.

5.  **Missing Partition Automation:**
    *   *Mistake:* Relying on manual creation or default partitions without monitoring.
    *   *Impact:* Write failures when a new time period arrives, or silent data routing to default partitions, defeating partitioning benefits.
    *   *Mitigation:* Implement robust automation. Alert on default partition growth. Use tools that proactively create partitions days in advance.

6.  **Cross-Partition Joins:**
    *   *Mistake:* Joining large partitioned tables on non-partition keys.
    *   *Impact:* Nested loop joins across partitions become prohibitively expensive.
    *   *Mitigation:* Co-partition tables on the join key where possible. If not, ensure join conditions allow for pruning on at least one side.

7.  **Migration Complexity:**
    *   *Mistake:* Converting a live monolithic table to partitioned without downtime strategy.
    *   *Impact:* Long locks, data inconsistency, or rollback difficulties.
    *   *Mitigation:* Use the "shadow table" pattern. Create the partitioned table, backfill data, sync writes via triggers, then switch over atomically.

## Production Bundle

### Action Checklist

- [ ] **Audit Query Patterns:** Identify top 20 queries and verify they filter on potential partition keys.
- [ ] **Estimate Partition Count:** Calculate total partitions based on granularity and retention. Ensure count < 1,000.
- [ ] **Define Retention Policy:** Map retention requirements to partition boundaries (e.g., drop monthly partitions after 12 months).
- [ ] **Implement Automation:** Deploy partition creation automation (e.g., `pg_partman`, custom cron, or IaC) with alerting on failure.
- [ ] **Validate Pruning:** Run `EXPLAIN ANALYZE` on critical queries to confirm `Partitions Pruned` in the output.
- [ ] **Review Constraints:** Audit unique indexes and foreign keys for compatibility with partition keys.
- [ ] **Test Maintenance:** Simulate partition drop and verify impact on backup size and WAL generation.
- [ ] **Monitor Skew:** Check partition sizes regularly; investigate significant deviations.

### Decision Matrix

| Scenario | Recommended Approach | Why | Cost Impact |
|----------|---------------------|-----|-------------|
| **Time-series logs (>50GB)** | Range by Timestamp | Enables instant retention, efficient range scans, aligns with backup windows. | Low (Storage optimization) |
| **Multi-tenant SaaS** | List by TenantID | Data isolation, per-tenant maintenance, prevents noisy neighbor issues. | Medium (Complexity) |
| **High-write events** | Hash by UserID | Even write distribution, prevents WAL hotspots on single nodes. | Low |
| **Reference data (<10GB)** | None | Partitioning overhead exceeds benefits; monolithic is faster for small tables. | N/A |
| **Global analytics** | None / Materialized Views | Partitioning hinders full-table scans; use OLAP or aggregated tables instead. | Low |

### Configuration Template

**PostgreSQL Range Partitioning with Automation Script:**

```sql
-- 1. Create Parent Table
CREATE TABLE audit_logs (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL,
    action TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 2. Create Index on Parent (Propagates to partitions)
CREATE INDEX idx_audit_logs_user_id ON audit_logs (user_id);

-- 3. Automation SQL (Run via pg_cron or external scheduler)
-- Creates partition for next month if not exists
DO $$
DECLARE
    partition_date DATE := DATE_TRUNC('month', NOW() + INTERVAL '1 month');
    partition_name TEXT := 'audit_logs_' || TO_CHAR(partition_date, 'YYYY_MM');
    start_date DATE := partition_date;
    end_date DATE := partition_date + INTERVAL '1 month';
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = partition_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF audit_logs FOR VALUES FROM (%L) TO (%L)',
            partition_name, start_date, end_date
        );
        RAISE NOTICE 'Created partition: %', partition_name;
    END IF;
END $$;

-- 4. Retention Automation (Drop partitions older than retention period)
-- Example: Drop partitions older than 90 days
DO $$
DECLARE
    cutoff_date DATE := NOW() - INTERVAL '90 days';
BEGIN
    FOR rec IN 
        SELECT c.relname 
        FROM pg_class c 
        JOIN pg_inherits i ON c.oid = i.inhrelid 
        JOIN pg_class p ON i.inhparent = p.oid 
        WHERE p.relname = 'audit_logs' 
        AND c.relname ~ 'audit_logs_\d{4}_\d{2}'
    LOOP
        -- Extract date from partition name and compare
        -- Implementation depends on naming convention
        -- EXECUTE format('DROP TABLE %I', rec.relname);
    END LOOP;
END $$;

Quick Start Guide

1. Analyze Table Size: Run \dt+ in PostgreSQL or sp_spaceused in SQL Server. If table > 20GB with high write volume, proceed.
2. Select Key: Choose a column that appears in 80%+ of queries and correlates with data age or tenant.
3. Create Parent Table: Execute CREATE TABLE ... PARTITION BY [Strategy] (key). Migrate schema constraints.
4. Seed Partitions: Create initial partitions covering current and future data ranges.
5. Migrate Data: Backfill existing data into partitions or switch traffic to the new table using a dual-write strategy. Verify EXPLAIN output shows pruning.