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](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 "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.** ## Safety Guardrails **BLOCK:** DROP DATABASE, DELETE without WHERE, publicly-accessible=true, GRANT ALL ON ALL **WARN then confirm:** RESIZE, RESTORE, VACUUM on large tables, ALTER PASSWORD, WLM config change, TRUNCATE / DROP TABLE — **`TRUNCATE` forces an implicit COMMIT and cannot be rolled back** (it also commits other pending work in the same transaction), so name the target table and say the rows are unrecoverable before proceeding; treat it as routine only on temp/staging tables the user has already called disposable **Confirm:** CREATE, GRANT specific, COPY, UNLOAD ## Security Considerations Apply these defaults when generating anything that connects, loads, or exports. Details are in the reference files noted. - **In transit:** the Data API is HTTPS-only. For JDBC/ODBC set the `require_ssl` parameter and connect with `sslmode=verify-full` so the server certificate is checked. - **At rest:** keep cluster/namespace encryption enabled, and add `ENCRYPTED KMS_KEY_ID '<arn>'` to `UNLOAD` — it writes query results to S3, outside Redshift's own encryption. → `references/redshift-sql-ddl-copy.md` - **Credentials:** prefer `SecretArn` (Secrets Manager) or IAM Identity Center; `DbUser` is acceptable because it issues temporary credentials. Never place database passwords in code, environment variables, or SQL text. → `references/redshift-sql-recipes-load-api.md` - **Least privilege:** scope the namespace `IAM_ROLE` to the specific bucket and prefix (`s3:GetObject` on `arn:aws:s3:::<bucket>/<prefix>/*`), not `s3:*` or a managed full-access policy, and condition its trust policy on both `aws:SourceArn` (the cluster/namespace ARN) and `aws:SourceAccount` — `SourceArn` alone still allows another resource in the account to assume it. Grant per-object privileges rather than `GRANT ALL ON ALL`. - **Audit:** CloudTrail records `redshift-data:*` API calls but not the SQL executed; enable Redshift audit logging (`useractivitylog`, `connectionlog`, `userlog`) for that. Both capture query text and user activity, so encrypt every destination in use: the CloudWatch Logs group (`aws logs associate-kms-key`), the CloudTrail trail (SSE-KMS), and the audit-log S3 bucket (SSE-S3 — audit logging to S3 supports only S3-managed keys, not KMS). Serverless only supports sending audit logs to CloudWatch. - **Network:** keep `PubliclyAccessible=false` and connect over a VPC endpoint. Do not open port 5439 to `0.0.0.0/0` or `::/0` — scope inbound rules to specific CIDRs or to a referencing security group. - **Sensitive data:** Data API results persist for 24h and `sys_load_error_detail` can echo fragments of rejected rows, so treat statement IDs and load-error output as sensitive. - **Further reading:** [Security in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/c_security-overview.html) for the full guidance behind these defaults. ## Routing Table **MANDATORY:** When a question matches a row below, you MUST load and read the referenced file BEFORE answering. **Ask whether the target is provisioned or Serverless before giving troubleshooting steps — unless the question already says which one, in which case use that and do not re-confirm.** | User Intent | Route To | |---|---| | "CREATE TABLE", "DISTKEY/SORTKEY", "ENCODE", "IDENTITY", "COPY", "UNLOAD", "IAM_ROLE", "Iceberg table" | `references/redshift-sql-ddl-copy.md` | | "LISTAGG", "DATEADD/DATEDIFF", "NVL/DECODE", "type mapping", "text type", "VARBYTE", "recursive CTE" | `references/redshift-sql-functions-types.md` | | "QUALIFY", "PIVOT/UNPIVOT", "MERGE", "TOP N", "SUBSTR error", "UNIQUE/PK not enforced", "trailing blanks", "leader-node function", "JSON", "SUPER", "PartiQL", "nested/semi-structured data" | `references/redshift-sql-extensions-semantics.md` | | "system view", "SVV_/SYS_", "SHOW commands", "STL vs SYS", "list tables", "distkey/sortkey lookup", "datashare discovery", "2-part vs 3-part", "permission denied", "GRANT", "privileges", **"relation/table does not exist"** | `references/redshift-sql-metadata.md` | | "how do I write SQL", "PostgreSQL vs Redshift", "which SQL reference", general dialect question | `references/redshift-sql-syntax.md` (index of the 6 SQL references + PostgreSQL-vs-Redshift failure table) | | "COPY failed", "load error", "Data API poll", "async query", "Data API throttle" | `references/redshift-sql-recipes-load-api.md` | | "materialized view", "MV refresh", "AUTO REFRESH", "stale view" | `references/redshift-sql-materialized-views.md` | | "disk full" / error 1016 / 34507, "PercentageDiskSpaceUsed", "storage grew", "what's consuming space", "ghost rows", "tombstone", "spill to disk", "managed storage vs local SSD", "datashare cache", "one node full / storage skew", "Serverless storage inflation", "what is driving the storage increase" / "storage keeps climbing" / "grew N% per month" / "which tables are growing" / "is vacuum missing" — a growth-over-time question, answered by the *"Is it growing, and what is driving it?"* section: split it into four (is it really that full, is it vacuum debt, how much would VACUUM return, what is driving the trend), trend from CloudWatch over months, per-table attribution from `SYS_VACUUM_HISTORY`, and state plainly what cannot be attributed from customer-side views, any snapshot / backup / recovery-point billing or cost question — including "AWS Backup", "cross-region snapshot copy", "backup billed at full size", "why is snapshot storage this expensive", "incremental so it should be cheaper" (answer from the per-type × per-platform matrix in S16, never from AWS Backup's own incremental-copy documentation, which describes AWS Backup's behavior and not Redshift's manual-snapshot metering), "cluster unavailable / stuck after hitting 100% storage" (confirm the fill first — `PercentageDiskSpaceUsed` in CloudWatch, since the SQL views are unreachable while the cluster is down, and rule out the non-storage causes before assuming disk) — attribute the space first | `references/redshift-storage-utilization.md` | | "VACUUM won't finish / times out", "which VACUUM mode — DELETE ONLY / SORT ONLY / FULL / RECLUSTER", "vacuum_sort_benefit", "unsorted", "reclaim space", "deep copy", "auto-vacuum CPU", "distribution key skew", "recovering a cluster stuck or unavailable at ~100% storage — headroom plus escalation", "resize failed and stuck in canceling-resize" — take action | `references/redshift-storage-remediation.md` | | "slow Redshift query", "query was fast now slow", "why is my query slow", "query performance", "time decomposition", "queue vs execution time", "planning/compile time", "result cache hit / false fast", "EXPLAIN plan", "DS_BCAST_INNER / broadcast / nested loop / merge join", "stale statistics / ANALYZE / bad row estimate", "function on join key / plan flip", "distribution skew / skew_rows / one slice", "did the query change / query hash", "row multiplication / join fan-out / DISTINCT slow", "sys_query_history / sys_query_detail" | `references/redshift-sql-query-performance.md` | | "ready-to-run performance query", "which SQL to run", "diagnostic queries", "execution step / skew / spill", "plan diff between runs", "svv_table_info table health", "auto-analyze / auto-vacuum history", "lock wait / transaction history" | `references/redshift-sql-query-performance-queries.md` | | "connection timed out/refused", "SSO/federated login fails", "Response signature invalid", "assumeRoleWithSAML", "RedshiftDbUser principal tag", "sts:TagSession AccessDenied", "IAM Identity Center region", "connection limit exceeded", "ODBC/JDBC driver", "ODBC 1.x EOL/CVE", "SSL error/certificate verify failed", "permission denied to query database created from datashare", "SYSLOG ACCESS/system-table row visibility", "GetClusterCredentials AccessDenied" | `references/redshift-auth-authorization.md` | | General Redshift question not matching above | Answer directly from general knowledge | | Aurora, RDS, DynamoDB, Athena (non-Redshift) | **REFUSE.** State this skill is for Amazon Redshift only. Do not provide guidance for other database services. | ## Data API Quick Reference → **Load `references/redshift-sql-recipes-load-api.md` before answering ANY Data API, COPY-error, or async-query question.** It carries the bounded poll loop, the `HasResultSet` and `ResourceNotFoundException` handling, the per-target parameters, and the auth options. Data API calls are **async by default** — use long polling (`--wait-time-seconds`, 1–30) rather than blind sleeps, and keep a bounded loop for work that can exceed 30s. Serverless takes `--workgroup-name`, provisioned takes `--cluster-identifier`.