Amazon Redshift

Amazon Redshift Amazon Redshift is a columnar, massively-parallel cloud data warehouse. It began life as a fork of PostgreSQL 8.0.2 and still speaks the PostgreSQL wire protocol, so the prorm Redshift dialect reuses the pg driver and mirrors much of the Postgres dialect's query building. That shared heritage is convenient, but Redshift diverges from Postgres in ways that matter a great deal in practice — this guide focuses on those differences and on the analytical features that have no Postgres equivalent. Implementation: redshift. Status: Experimental. The dialect implements the full CRUD/DDL surface plus Redshift-specific extras (COPY/UNLOAD, DISTKEY/SORTKEY, datashares, VACUUM/ANALYZE). Some DDL that Redshift accepts but does not enforce (see below) is emitted faithfully rather than silently dropped. Overview Columnar + MPP. Data is stored column-by-column and spread across compute-node slices. This makes wide aggregate scans fast and single-row OLTP-style access comparatively expensive. Design for batch loads and analytical queries, not high-frequency row writes. Postgres wire, different engine. The pg driver connects and the SQL looks familiar, but the storage engine, planner, and constraint behavior are all different. No secondary indexes. There is no CREATE INDEX. Physical layout is governed by table-level DISTSTYLE/DISTKEY (distribution) and SORTKEY (on-disk ordering) instead. Constraints are advisory. PRIMARY KEY, UNIQUE, and FOREIGN KEY DDL is accepted and stored for the planner, but never enforced at write time. Bulk-first I/O. The idiomatic load path is COPY FROM S3, and the idiomatic export path is UNLOAD TO S3. Connection Select the dialect with dialect: 'redshift'. The dialect connects over the Postgres wire protocol on Redshift's default port 5439 (not 5432). Connection tuning fields (max, idleTimeoutMillis, connectionTimeoutMillis, statementTimeout, queryTimeout, ssl) are passed straight through to the pg pool. The distKey / sortKey entries under dialectOptions are Redshift-specific defaults applied to tables created without their own keys. The port defaults to 5439 if omitted; the pool defaults to max: 10. Columnar / analytical nature Redshift is built for scan-heavy analytics, and the dialect reflects that: queryStream() pages through results with repeated LIMIT/OFFSET queries rather than holding a whole result set in memory — appropriate for warehouse-sized reads. There is no autovacuum/autoanalyze. The planner relies on statistics you refresh explicitly with analyze(), and space from deleted/updated rows is reclaimed with vacuum() (see Maintenance). Prefer set-based batch operations. Row-by-row INSERT works for compatibility but is slow at scale; use copyFromS3() for real loads. DISTKEY / SORTKEY (instead of indexes) Redshift has no B-tree secondary indexes. Instead you tune tables with distribution and sort keys, passed as extra options to createTable (typed as RedshiftTableOptions). The Redshift-specific methods below (createTable with key options, copyFromS3, unloadToS3, upsert, vacuum, analyze, ...) live on the dialect instance, which you reach via getDialectInstance(). Some of them (COPY/UNLOAD, datashares) are not part of the generic Dialect interface, so cast the instance to reach them: This emits: Notes: diststyle: 'KEY' requires distKey; 'ALL' replicates the table to every node (good for small dimension tables); 'EVEN' round-robins; 'AUTO' lets Redshift decide. sortKey accepts a single column or an array. sortkeyType is COMPOUND (default) or INTERLEAVED. Column compression is a first-class perf feature: set encode on a column definition ('AZ64', 'ZSTD', 'LZO', 'BYTEDICT', 'DELTA', 'RAW', ...). The value is passed through verbatim, so future encodings work without a dialect update. Any request for a traditional index fails loudly rather than being silently ignored. Passing options.indexes to createTable, or calling createPartitionedTable() / createPartition(), throws a DatabaseError pointing you at DISTKEY/SORTKEY. Redshift has no declarative partitioning. Loading data: COPY and UNLOAD The idiomatic bulk-load path is COPY FROM S3, exposed as copyFromS3() (a Redshift-specific method, not part of the generic Dialect interface). Authenticate with either an IAM role ARN (recommended) or static credentials — supplying both, or neither, throws. For CSV you can add delimiter and ignoreHeader; for JSON/AVRO pass jsonOption (a jsonpaths file path or 'auto'). The extra field appends raw COPY options verbatim as an escape hatch. The write-side counterpart, unloadToS3(), exports the result of a query: Ordinary bulkInsert() / buildInsertQuery() still work via plain multi-row INSERT for ORM compatibility, but they are not the right tool for large loads. Upserts Redshift has never supported INSERT ... ON CONFLICT, and native MERGE only arrived in 2023. Because of that, buildInsertQuery does not generate ON CONFLICT (calling the internal insert-upsert path throws with guidance), and the default upsert uses AWS's documented staging-table pattern instead: Under the hood this runs, on a single pinned connection inside BEGIN/COMMIT: Pinning one connection matters: Redshift temp tables are session-scoped, so the staging table must live on the same physical connection for every statement. bulkUpsert() builds one staging table fed by a single multi-row INSERT for a whole array of rows. If your cluster is new enough, opt into native MERGE with useMerge: true (optionally restrict the updated columns with updateColumns): Data types getDataTypeSql maps prorm DataTypes to Redshift's type system. The mappings that differ from Postgres: prorm DataType | Redshift type | Note | STRING | VARCHAR(255) | length honored | TEXT | VARCHAR(65535) | Redshift has no unbounded TEXT | FLOAT | REAL | | DOUBLE | DOUBLE PRECISION | | DATE | TIMESTAMP | date+time | DATEONLY | DATE | | BLOB | VARBYTE | no BYTEA | JSON / JSONB | SUPER | semi-structured type | ARRAY | SUPER | no native array types | UUID | VARCHAR(36) | no native UUID | ENUM | VARCHAR(n) | no native ENUM; widened to fit values | The semi-structured SUPER type is Redshift's answer to JSON and nested/array data. Array JS values are escaped with JSONPARSE('...') so they round-trip into SUPER columns. Auto-increment columns use Redshift's IDENTITY(seed, step) clause rather than Postgres SERIAL/BIGSERIAL (Redshift has no SERIAL type and no sequence objects). The column keeps its declared width — a SMALLINT identity stays SMALLINT IDENTITY, it is not silently widened. Seed/step come from an optional identity field on the column definition and default to (1, 1). Maintenance: VACUUM / ANALYZE Because there is no background autovacuum, reclaim space and refresh planner stats explicitly — especially after the DELETE-heavy staging-table upsert path: vacuum() also accepts toPercent (1–100) to stop once a table is sufficiently sorted. analyze() rejects passing both columns and predicateColumns. Transactions Redshift supports only two isolation levels: READ COMMITTED (default) and SERIALIZABLE. It has no MVCC snapshot mode equivalent to REPEATABLE READ and no dirty-read READ UNCOMMITTED. Requesting either of the unsupported levels is rejected outright (rather than silently downgraded to a different, unrequested guarantee). Transactions may additionally be marked readOnly via RedshiftTransactionOptions. How Redshift differs from Postgres Even though the driver and much of the SQL are shared, do not assume Postgres semantics. Key divergences the dialect encodes: Default port 5439, not 5432. No enforced constraints. PRIMARY KEY / UNIQUE / FOREIGN KEY DDL is emitted and stored for the planner and for ERD/documentation tooling, but inserts/updates that violate them succeed silently. Enforce integrity in application code. CHECK constraints are not supported at all and throw. No B-tree indexes / no partitioning. Use DISTKEY/SORTKEY; index and partition APIs throw. No SERIAL/sequences. Auto-increment is IDENTITY(seed, step); createSequence() throws. No CREATE EXTENSION, no triggers, no row-level-security policies. All throw a clear DatabaseError. (Redshift's answer to external/federated data is Spectrum's CREATE EXTERNAL SCHEMA and federated queries, exposed separately.) SUPER instead of JSON/JSONB, VARBYTE instead of BYTEA, no native TEXT/UUID/ENUM/array types. No ON CONFLICT. Upserts use the staging-table pattern (or opt-in native MERGE). Manual maintenance. No autovacuum/autoanalyze; call vacuum()/analyze() yourself. Redshift does support views, materialized views (with optional AUTO REFRESH, BACKUP NO, and their own DISTKEY/SORTKEY), and a limited form of plpgsql stored procedures (CREATE OR REPLACE PROCEDURE ... LANGUAGE plpgsql), all implemented much like Postgres. The RedshiftTableOptions, RedshiftColumnDefinition, CopyFromS3Options, UnloadOptions, RedshiftUpsertOptions, and related option interfaces are declared alongside RedshiftDialect in the dialect source. See also Source: redshift GAPS and redshift in the dialect directory Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL