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:

-- 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

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:

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:

// 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);

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

Storage Model Differences

Redshift uses columnar storage which requires different table design:

-- 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

-- 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)

-- 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

Cost Model (Redshift Serverless)

Compute

Storage

Cost Estimates

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:
4. Tables queried by store → DISTKEY(store_id)
5. Time-series tables → SORTKEY(timestamp_column)
6. Small dimension/lookup tables → DISTSTYLE ALL
7. Audit all SQL in monitor specs (12 seeded monitors) for PG-specific syntax:
8. Replace ON CONFLICT → MERGE
9. Replace generate_series → number/date tables
10. Replace array operations → normalized tables or SUPER type
11. Replace JSON operators → JSON_EXTRACT_PATH_TEXT
12. 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
2. Set base RPU capacity (start at 32 RPU)
3. Set MaxRPU cap (64 or 128 RPU)
4. Configure daily RPU-hour limit for budget safety
5. Create namespace with encryption and audit logging
6. Set up IAM roles for:
7. Application access (Npgsql with IAM auth or secrets)
8. Spectrum access (S3 read for future data lake queries)
9. Monitoring (CloudWatch)
10. Create RBAC roles with scoped permissions (see Permission Management section above)
11. 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:
3. Apply distribution keys and sort keys
4. Replace SERIAL → IDENTITY
5. Remove/replace unsupported constraints
6. Add compression encoding (or let Redshift auto-choose with ENCODE AUTO)
7. Create views that match existing query patterns
8. Validate schema with SCT assessment report

Phase 4: Data Migration (1–2 weeks)

1. Export data from PostgreSQL to S3 as Parquet or CSV:

   # 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

1. Load data into Redshift via COPY command:

   COPY schema.table
   FROM 's3://bucket/migration/schema/table/'
   IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
   FORMAT AS CSV
   IGNOREHEADER 1;

1. Alternatively, use AWS DMS for continuous replication during migration window
2. Run ANALYZE on all tables after loading to update query planner statistics
3. 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:
2. Add connection string with Server Compatibility Mode=Redshift
3. Configure for Redshift Serverless endpoint (port 5439)
4. Update DataAccessAgentFactory:
5. Update connection string configuration
6. Validate SQL generation produces Redshift-compatible queries
7. Update system prompts for AI agent to generate Redshift SQL (no PG-specific syntax)
8. Update monitor spec SQL — fix any PG-specific queries in the 12 seeded specs
9. Update Simulator — ensure traffic simulator writes Redshift-compatible SQL
10. 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:
3. Time-series aggregations (order surge, prep time monitors)
4. Join-heavy queries (menu item profitability, demand drivers)
5. Window functions (station bottleneck, slow kitchen monitors)
6. Load test with concurrent monitor executions
7. Cutover checklist:
8. [ ] All monitors produce equivalent results
9. [ ] Query latencies are acceptable
10. [ ] RBAC roles and scoped permissions are configured
11. [ ] Budget limits (MaxRPU, RPU-hour caps) are set
12. [ ] CloudWatch alarms for cost and performance
13. [ ] Backup/snapshot strategy configured
14. [ ] Runbook for common operations (VACUUM, ANALYZE, user management)
15. Switch production connection strings to Redshift
16. 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)

-- 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