/redshift-guide

name: redshift-guide
description: "Amazon Redshift is NOT PostgreSQL; corrects LLM mistakes; covers SQL, metadata, connectivity/auth, storage, query performance, and operations. Applies ONLY to Redshift (provisioned or Serverless). Pushes back on: CREATE INDEX, string_agg, pg_catalog, text type, SERIAL, stl_query. Triggers on: Redshift SQL, Redshift CREATE TABLE, Redshift COPY/UNLOAD, slow or suddenly slow Redshift query, EXPLAIN plan, queue wait, stale statistics/ANALYZE, result cache, Redshift permission denied, Redshift disk full, storage growth, snapshot/backup cost, ghost rows/bloat, VACUUM, spill to disk, distribution skew, Redshift system views, QUALIFY, PIVOT, MERGE, materialized views, Redshift Data API (Python/boto3), Redshift workload management (WLM), concurrency scaling, Redshift resize, Spectrum, awsdatacatalog, connection failures (SSL/TLS, ODBC/JDBC, connection limit), IAM/SAML federation, IAM Identity Center, datashares. Does NOT apply to: S3 policies, Athena, Glue/Iceberg outside Redshift, Aurora, RDS, DynamoDB."
metadata:
  version: "2"

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](https://docs.aws.amazon.com/agent-toolkit/) — 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".

| Target | System Views | Credentials API | |---|---|---| | **Provisioned** | SYS_, all SVV_ + STL_, STV_, SVL_, SVCS_ (single-AZ only — disabled on Multi-AZ) | `redshift:GetClusterCredentials` | | **Serverless** | `SYS_` + a subset of `SVV_` ONLY (no STL/STV/SVL/SVCS) | `redshift-serverless:GetCredentials` |

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