# Migrating from PostgreSQL to AWS Redshift Serverless

## Why Redshift?

### The Permission Problem

In PostgreSQL, every new table/schema requires manually granting permissions per role. `ALTER DEFAULT PRIVILEGES` only applies to objects created by a specific user — if any other user/role creates a table, grants don't propagate. This makes multi-tenant and multi-schema management tedious and error-prone.

Redshift solves this with **Scoped Permissions** — grants that apply to all current AND future objects:

```sql
-- One-time setup: covers ALL current AND FUTURE tables
GRANT USAGE FOR SCHEMAS IN DATABASE warehouse TO ROLE app_reader;
GRANT SELECT FOR TABLES IN DATABASE warehouse TO ROLE app_reader;
-- Done. Forever. Regardless of who creates what.
```

### Redshift Serverless vs Provisioned

| Aspect | Serverless | Provisioned |
|---|---|---|
| Capacity | Auto-scales RPUs, shuts down when idle | Manual node type/count, manual resize |
| Idle cost | **$0** | Pays per-hour even when idle (unless paused) |
| Concurrency scaling | Included | Free 1hr/day, then per-second charges |
| Spectrum (S3 queries) | Included in RPU billing | $5/TB scanned separately |
| Startup cost | ~$1.50/hr at base 128 RPU (configurable 4–1024 RPU) | ~$0.543/hr (ra3.xlplus, 2-node minimum) |
| Best for | Variable workloads, growing SaaS | Stable 24/7 high-utilization |

**Decision: Redshift Serverless** — zero idle cost, auto-scaling, budget caps via MaxRPU and RPU-hour limits.

---

## .NET Compatibility

### Current State

The warehouse is accessed via **raw Npgsql** (not EF Core) through the `DataAccessAgentFactory`. The AI agent generates SQL queries and executes them with a 500-row limit and 30-second timeout. This maps directly to Redshift.

### Connection via Npgsql

Redshift speaks the PostgreSQL wire protocol. Npgsql works with a required configuration:

```csharp
var connString = "Host=workgroup.account.region.redshift-serverless.amazonaws.com;" +
                 "Port=5439;" +
                 "Database=dev;" +
                 "Username=admin;" +
                 "Password=xxx;" +
                 "Server Compatibility Mode=Redshift;" +  // REQUIRED for SSL
                 "SSL Mode=Require";

using var conn = new NpgsqlConnection(connString);
await conn.OpenAsync();

using var cmd = new NpgsqlCommand("SELECT * FROM sales LIMIT 10", conn);
using var reader = await cmd.ExecuteReaderAsync();
```

### Alternative: AWS Redshift Data API

For serverless/Lambda scenarios or avoiding connection management:

```csharp
// NuGet: AWSSDK.RedshiftDataAPIService
var client = new AmazonRedshiftDataAPIServiceClient();

var execReq = new ExecuteStatementRequest
{
    WorkgroupName = "my-workgroup",
    Database = "dev",
    Sql = "SELECT * FROM sales LIMIT 10"
};
var execResp = await client.ExecuteStatementAsync(execReq);

// Poll for results (async model)
var getReq = new GetStatementResultRequest { Id = execResp.Id };
var result = await client.GetStatementResultAsync(getReq);
```

| | Npgsql | Data API (AWS SDK) |
|---|---|---|
| Connection model | Persistent TCP (port 5439) | HTTP REST, no pooling needed |
| Latency | Low, synchronous | Higher, async polling |
| Auth | Username/password or IAM temp creds | IAM roles natively |
| Best for | Application queries, migration from PG | Lambda, admin ops, serverless |

**Decision: Use Npgsql with `Server Compatibility Mode=Redshift`** for the application layer. Consider Data API for administrative scripts.

---

## SQL Compatibility

### What works the same
- Basic SQL: SELECT, INSERT, UPDATE, DELETE, JOINs, CTEs, subqueries
- Schema/table/view creation
- COPY command for bulk loading (similar syntax)
- Window functions (most)
- Aggregate functions

### What's different or unsupported

| Feature | PostgreSQL | Redshift |
|---|---|---|
| Indexes | B-tree, GIN, GiST, HNSW, etc. | **None** — use sort keys + distribution keys |
| Constraints | Enforced (PK, FK, UNIQUE) | **Metadata hints only** — not enforced |
| Sequences/SERIAL | Supported | **Not supported** — use IDENTITY columns |
| Triggers | Supported | **Not supported** |
| LISTEN/NOTIFY | Supported | **Not supported** |
| UPSERT | `ON CONFLICT DO UPDATE` | **Use MERGE** |
| Array types | Supported | **Not supported** |
| JSON operators | `->>`  `@>` etc. | **Limited** — use JSON_EXTRACT_PATH_TEXT |
| LATERAL joins | Supported | **Not supported** |
| generate_series | Supported | **Not supported** — use number tables |

### Storage Model Differences

Redshift uses **columnar storage** which requires different table design:

```sql
-- PostgreSQL (row-oriented)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    store_id INT NOT NULL,
    order_time TIMESTAMP NOT NULL,
    total DECIMAL(10,2)
);

-- Redshift (columnar)
CREATE TABLE orders (
    id INT IDENTITY(1,1),           -- IDENTITY instead of SERIAL
    store_id INT NOT NULL,
    order_time TIMESTAMP NOT NULL,
    total DECIMAL(10,2)
)
DISTKEY(store_id)                    -- Distribute by tenant for co-location
SORTKEY(order_time);                 -- Sort by query filter column
```

**Distribution styles:**
- `DISTKEY(column)` — distribute rows by a column (use tenant/store ID for co-located joins)
- `DISTSTYLE EVEN` — round-robin across slices
- `DISTSTYLE ALL` — full copy on every slice (for small dimension tables)
- `DISTSTYLE AUTO` — Redshift chooses automatically

**Sort keys:**
- `SORTKEY(column)` — compound sort key (most common)
- `INTERLEAVED SORTKEY(col1, col2)` — for multi-column filter patterns

---

## Permission Management (RBAC)

### Role Setup

```sql
-- Create roles
CREATE ROLE app_reader;
CREATE ROLE app_writer;
CREATE ROLE data_engineer;
CREATE ROLE analyst;

-- Role hierarchy
GRANT ROLE app_reader TO ROLE app_writer;
GRANT ROLE app_writer TO ROLE data_engineer;

-- System roles available:
-- sys:monitor    — catalog/system table access
-- sys:operator   — monitor + ANALYZE, VACUUM, CANCEL
-- sys:dba        — operator + CREATE/DROP schemas, tables
-- sys:secadmin   — user/role management, RLS/DDM
-- sys:superuser  — all permissions
```

### Scoped Permissions (The Key Feature)

```sql
-- Read-only app service: all current + future tables
GRANT USAGE FOR SCHEMAS IN DATABASE warehouse TO ROLE app_reader;
GRANT SELECT FOR TABLES IN DATABASE warehouse TO ROLE app_reader;

-- Read/write for specific schema
GRANT USAGE ON SCHEMA app_data TO ROLE app_writer;
GRANT SELECT, INSERT, UPDATE, DELETE FOR TABLES IN SCHEMA app_data TO ROLE app_writer;

-- Full DDL + DML for engineers
GRANT ALL FOR SCHEMAS IN DATABASE warehouse TO ROLE data_engineer;
GRANT ALL FOR TABLES IN DATABASE warehouse TO ROLE data_engineer;

-- Assign to users
GRANT ROLE app_reader TO svc_anomaly_detector;
GRANT ROLE app_writer TO svc_etl_pipeline;
GRANT ROLE analyst TO alice, bob, carol;
```

### Comparison

| Scenario | PostgreSQL | Redshift |
|---|---|---|
| New table in existing schema | Must GRANT per-role manually | **Automatic** via scoped permissions |
| New schema created | Must GRANT USAGE + set DEFAULT PRIVILEGES per-creator | **Automatic** via `FOR SCHEMAS IN DATABASE` |
| New user joins team | Grant role + manually verify access | Grant role — inherits all scoped permissions |

---

## Cost Model (Redshift Serverless)

### Compute

| Component | Rate (US East) |
|---|---|
| On-demand RPU-hour | $0.375/RPU-hour |
| 1-year reservation | ~$0.285/RPU-hour (24% savings) |
| 3-year reservation | ~$0.206/RPU-hour (45% savings) |
| Minimum charge per query | 60 seconds |
| Billing granularity | Per-second after 60s minimum |
| Idle cost | **$0** |

### Storage

| Component | Rate |
|---|---|
| Managed storage (RMS) | $0.024/GB-month |
| Automated recovery points (< 24hr) | Free |

### Cost Estimates

| Workload | RPU | Active Hours/Day | Monthly Cost |
|---|---|---|---|
| Light analytics | 32 | 2 | ~$720 |
| Moderate (13 runs/day, 10 min each) | 128 | 2.2 | ~$3,276 |
| Heavy analytics | 128 | 8 | ~$10,800 |

### Budget Controls

- **MaxRPU**: Cap auto-scaling (e.g., max 64 RPU)
- **RPU-hour limits**: Daily, weekly, or monthly caps
- **Free trial**: $300 credit, 90-day expiration

### Cost Gotchas

- Connection pool health checks (`SELECT 1`) **are billed** — configure pooling carefully
- Unclosed transactions continue consuming RPUs — always COMMIT/ROLLBACK
- System table queries are billed
- 60-second minimum per query execution

---

## Migration Plan

### Phase 1: Assessment & Schema Design (1–2 weeks)

1. **Run AWS Schema Conversion Tool (SCT)** against the 172 warehouse tables across `aloha`, `kdsdev`, `labpos` schemas
2. **Identify incompatibilities**: triggers, unsupported data types, PG-specific SQL
3. **Design distribution and sort keys** for each table based on query patterns:
   - Tables queried by store → `DISTKEY(store_id)`
   - Time-series tables → `SORTKEY(timestamp_column)`
   - Small dimension/lookup tables → `DISTSTYLE ALL`
4. **Audit all SQL in monitor specs** (12 seeded monitors) for PG-specific syntax:
   - Replace `ON CONFLICT` → `MERGE`
   - Replace `generate_series` → number/date tables
   - Replace array operations → normalized tables or SUPER type
   - Replace JSON operators → `JSON_EXTRACT_PATH_TEXT`
5. **Audit DataAccessAgentFactory SQL generation** — ensure AI-generated queries use Redshift-compatible syntax

### Phase 2: Provision & Configure (1 week)

1. **Create Redshift Serverless workgroup** in target VPC
   - Set base RPU capacity (start at 32 RPU)
   - Set MaxRPU cap (64 or 128 RPU)
   - Configure daily RPU-hour limit for budget safety
2. **Create namespace** with encryption and audit logging
3. **Set up IAM roles** for:
   - Application access (Npgsql with IAM auth or secrets)
   - Spectrum access (S3 read for future data lake queries)
   - Monitoring (CloudWatch)
4. **Create RBAC roles** with scoped permissions (see Permission Management section above)
5. **Configure VPC, security groups, and network** — Redshift Serverless endpoint in private subnet

### Phase 3: Schema Migration (1–2 weeks)

1. **Create schemas** (`aloha`, `kdsdev`, `labpos`) in Redshift
2. **Create tables** with Redshift-compatible DDL:
   - Apply distribution keys and sort keys
   - Replace SERIAL → IDENTITY
   - Remove/replace unsupported constraints
   - Add compression encoding (or let Redshift auto-choose with `ENCODE AUTO`)
3. **Create views** that match existing query patterns
4. **Validate schema** with SCT assessment report

### Phase 4: Data Migration (1–2 weeks)

1. **Export data from PostgreSQL** to S3 as Parquet or CSV:
   ```bash
   # Using pg_dump or COPY TO for each table
   psql -c "COPY schema.table TO STDOUT WITH CSV HEADER" | aws s3 cp - s3://bucket/migration/schema/table/data.csv
   ```
2. **Load data into Redshift** via COPY command:
   ```sql
   COPY schema.table
   FROM 's3://bucket/migration/schema/table/'
   IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
   FORMAT AS CSV
   IGNOREHEADER 1;
   ```
3. **Alternatively, use AWS DMS** for continuous replication during migration window
4. **Run ANALYZE** on all tables after loading to update query planner statistics
5. **Validate row counts and data integrity** between source PG and target Redshift

### Phase 5: Application Changes (1–2 weeks)

1. **Update Aspire AppHost** — add Redshift connection resource:
   - Add connection string with `Server Compatibility Mode=Redshift`
   - Configure for Redshift Serverless endpoint (port 5439)
2. **Update DataAccessAgentFactory**:
   - Update connection string configuration
   - Validate SQL generation produces Redshift-compatible queries
   - Update system prompts for AI agent to generate Redshift SQL (no PG-specific syntax)
3. **Update monitor spec SQL** — fix any PG-specific queries in the 12 seeded specs
4. **Update Simulator** — ensure traffic simulator writes Redshift-compatible SQL
5. **Test all monitors end-to-end** against Redshift with sample data

### Phase 6: Validation & Cutover (1 week)

1. **Run all 12 monitor specs** against Redshift and compare results with PostgreSQL
2. **Performance test** — compare query latencies, especially for:
   - Time-series aggregations (order surge, prep time monitors)
   - Join-heavy queries (menu item profitability, demand drivers)
   - Window functions (station bottleneck, slow kitchen monitors)
3. **Load test** with concurrent monitor executions
4. **Cutover checklist**:
   - [ ] All monitors produce equivalent results
   - [ ] Query latencies are acceptable
   - [ ] RBAC roles and scoped permissions are configured
   - [ ] Budget limits (MaxRPU, RPU-hour caps) are set
   - [ ] CloudWatch alarms for cost and performance
   - [ ] Backup/snapshot strategy configured
   - [ ] Runbook for common operations (VACUUM, ANALYZE, user management)
5. **Switch production connection strings** to Redshift
6. **Decommission PostgreSQL warehouse** after monitoring period

---

## Integration with Future Data Lake (S3 + Spectrum)

Once Redshift Serverless is in place, it becomes the query engine for both:
- **Local warehouse tables** (migrated from PostgreSQL)
- **S3 data lake** (future customer data via Redshift Spectrum)

```sql
-- Create external schema pointing to Glue Data Catalog
CREATE EXTERNAL SCHEMA data_lake
FROM DATA CATALOG DATABASE 'kdsai_lake'
IAM_ROLE 'arn:aws:iam::123456789012:role/SpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

-- Query S3 data alongside warehouse tables
SELECT w.store_id, w.metric, s.raw_pos_data
FROM warehouse.aloha.metrics w
JOIN data_lake.pos_events s ON w.store_id = s.store_id
WHERE w.event_date = CURRENT_DATE;
```

This creates a natural bridge to the ETL pipeline architecture (S3 → Glue Catalog → Lake Formation → Redshift Spectrum).

---

## Risks & Mitigations

| Risk | Impact | Mitigation |
|---|---|---|
| AI agent generates PG-specific SQL | Query failures | Update system prompts with Redshift SQL guidelines; add SQL validation layer |
| Columnar storage performs differently | Slow queries for row-level lookups | Design sort keys based on actual query patterns; DISTKEY on join columns |
| Cost overruns from auto-scaling | Budget surprise | Set MaxRPU cap + daily RPU-hour limit; CloudWatch billing alarms |
| Connection pool health checks billed | Unnecessary cost | Tune connection pool settings; use keepalive carefully |
| 60-second minimum billing per query | High cost for many small queries | Batch small queries; consider query grouping in monitor execution |
| Npgsql compatibility edge cases | Runtime errors | Test all 172 tables + all monitor SQL before cutover |