redshift-guide

Amazon Redshift Guide

Redshift is NOT PostgreSQL (read first)

Redshift speaks PostgreSQL's wire protocol and shares much of its surface syntax, so LLMs assume PostgreSQL behavior carries over — it frequently does not. Divergences span system tables (pg_catalog is incomplete), DDL (no indexes, no sequences), functions (string_agg, SUBSTR on tables, leader-node-only functions), types (a text column becomes VARCHAR(256)), and comparison semantics (trailing blanks, unenforced constraints). Assume divergence and verify against the reference below — do not answer from PostgreSQL habit. Common PostgreSQL→Redshift divergences are in references/redshift-sql-syntax.md.

Works best with the AWS MCP server — it runs the AWS CLI and Redshift Data API calls below in a sandboxed, audit-logged environment. All guidance here is plain AWS CLI and SQL and works without it.

STEP 0: Serverless or Provisioned?

Establish this before answering — APIs, system tables, and capabilities differ. Take it from the question when it says which one; ask when it does not. SELECT version() does not identify it.

• Serverless — identified by a workgroup (and namespace). Data API calls take --workgroup-name; the user says "workgroup"/"Serverless".
• Provisioned — identified by a cluster. Data API calls take --cluster-identifier; the user says "cluster".

Critical Facts

• SHOW commands are the primary metadata interface — SHOW DATABASES, SHOW SCHEMAS, SHOW TABLES, SHOW COLUMNS, SHOW TABLE, SHOW VIEW. Do NOT default to pg_catalog or information_schema. → Load references/redshift-sql-metadata.md for metadata/discovery questions and any "relation does not exist" report — it has the diagnostic flow.
• SYS_ views are the preferred system views — they work everywhere. STL_, STV_, SVL_, and SVCS_ are provisioned single-AZ only, and some SVV_ views are unsupported on Serverless. → Load references/redshift-sql-metadata.md for any system-view or monitoring question.
• sys_load_error_detail for COPY debugging (not stl_load_errors, which is provisioned single-AZ only).
• DATEADD/DATEDIFF — unit-first argument order: DATEADD(day, -30, GETDATE()), DATEDIFF(day, start, end).
• APPROXIMATE COUNT(DISTINCT col) — Redshift-specific, ~2% error, much faster than exact COUNT(DISTINCT) on large datasets.
• MERGE ... REMOVE DUPLICATES — simplified dedup when source and target have identical schemas.
• COPY should use IAM_ROLE (the namespace role, not the caller role) + supports MANIFEST for explicit file lists + MAXERROR for error tolerance.
• SUBSTR() is leader-node-only — works on literals but errors on table columns (SUBSTR() function is not supported (Hint: use SUBSTRING instead)). Use SUBSTRING() on columns.
• UNIQUE / PRIMARY KEY / FOREIGN KEY are informational only — NOT enforced (duplicate rows are accepted with no error). Optimizer hints; enforce integrity in the application or via MERGE. NOT NULL IS enforced.
• SHOW VIEW <schema.name> returns the definition of a regular view, materialized view, or late-binding view. MV freshness: SVV_MV_INFO (is_stale).
• No FILTER (WHERE ...) on aggregates — PostgreSQL-only, Redshift rejects it. Write COUNT(CASE WHEN cond THEN 1 END) / SUM(CASE WHEN cond THEN x END) instead. This applies to every query you emit, including ones you derive from a reference example.
• TOP N and LIMIT N both work (TOP N PERCENT does not). A text column becomes VARCHAR(256) — use VARCHAR(max) or explicit length.
• Iceberg tables use CREATE TABLE ... USING ICEBERG (not STORED AS ICEBERG, not TABLE_FORMAT=ICEBERG).
• Datashares support read and write operations — consumers can write once the producer grants write privileges. Treat "permission denied" on a datashare write as a missing grant, not an unsupported operation. → Load references/redshift-sql-metadata.md for requirements and limits.
• Storage: attribute before you remediate, and never reflex-VACUUM. Disk-full is usually NOT data growth — rule out transient query spill (temp blocks) and per-node/slice skew first, because the cluster average hides a single full node. On RA3/Serverless a high local reading is cache, not out-of-space, and free managed storage does not prevent a node-local Disk Full. SVV_TABLE_INFO.unsorted alone is misleading — gate sorting on vacuum_sort_benefit. "Storage is growing / what is driving it" is a different question from "storage is high": it needs a trend over months (CloudWatch, not a point-in-time view), per-table attribution is always partial, and the per-table sum is never the cluster total. → Load references/redshift-storage-utilization.md to attribute space, then references/redshift-storage-remediation.md to act.