Document history - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Document history

Note

For a description of new features in Amazon Redshift, see What's new.

The following table describes the important documentation changes to the Amazon Redshift Database Developer Guide after May 2018. For notification about updates to this documentation, you can subscribe to an RSS feed.

API version: 2012-12-01

For a list of the changes to the Amazon Redshift Management Guide, see Amazon Redshift Management Guide Document History.

For more information about new features, including a list of fixes and the associated cluster version numbers for each release, see Cluster Version History.

ChangeDescriptionDate

Support for spatial 3D and 4D geometries and new spatial functions

You can now use additional spatial functions and 3D and 4D geometry support is added to some functions.

August 19, 2021

Support for column compression encoding for automatic table optimization

You can specify the ENCODE AUTO option for a table to automatically manage compression encoding for all columns in the table.

August 3, 2021

Support for multiple SQL statements or an SQL statement with parameters using the Amazon Redshift Data API

You can now run multiple SQL statements or a statement with parameters with the Amazon Redshift Data API.

July 28, 2021

Support for case-insensitive collation with column level overrides

You can now use the COLLATE clause within a CREATE DATABASE statement to specify the default collation.

June 24, 2021

Support for data sharing across accounts

You can now share data across Amazon Web Services accounts.

April 30, 2021

Support for hierarchical data queries with recursive CTE

You can now use a recursive common table expression (CTE) in your SQL.

April 29, 2021

Support for cross-database queries

You can now query data across databases in a cluster.

March 10, 2021

Support for fine-grained access control on COPY and UNLOAD commands

You can now grant the privilege to run COPY and UNLOAD commands to specific users and groups in your Amazon Redshift cluster to create more fine-grained access control policy.

January 12, 2021

Support for native JSON and semi-structured data

You can now define the SUPER data type.

December 9, 2020

Support for federated query to MySQL

You can now write a federated query to a supported MySQL engine.

December 9, 2020

Support for data sharing

You can now share data across Amazon Redshift clusters.

December 9, 2020

Support for automatic table optimization

You can now define automatic distribution and sort keys.

December 9, 2020

Support for Amazon Redshift ML

You can now create, train, and deploy machine learning (ML) models.

December 8, 2020

Support for automatic refresh and query rewrite of materialized views

You can now keep materialized views up-to-date with automatic refresh and query performance can be improved with automatic rewrite.

November 11, 2020

Support for TIME and TIMETZ data types

You can now create tables with TIME and TIMETZ data types. TIME data type stores the time of day without timezone information, and TIMETZ stores the time of day including timezone information

November 11, 2020

Support for Lambda UDFs and tokenization

You can now can write Lambda UDFs to enable external tokenization of data.

October 26, 2020

Support for altering a table column encoding

You can now alter a table column encoding.

October 20, 2020

Support for querying across databases

Amazon Redshift can now query across databases in a cluster.

October 15, 2020

Support for HyperLogLog Sketches

Amazon Redshift can now store and process HyperLogLogSketches.

October 2, 2020

Support for Apache Hudi and Delta Lake

Enhancements to creating external tables for Redshift Spectrum.

September 24, 2020

Support for enhancements to querying spatial data

Enhancements include loading a shapefile and several new spatial SQL functions.

September 15, 2020

Materialized view support external tables

You can create materialized views in Amazon Redshift that reference external data sources.

June 19, 2020

Support to write to external table

You can write to external tables by running CREATE EXTERNAL TABLE AS SELECT to write to a new external table or INSERT INTO to insert data into an existing external table.

June 8, 2020

Support for storage controls for schemas

Updates to commands and views that manage storage controls for schemas.

June 2, 2020

Support for federated query general availability

Updated information about querying data with federated queries.

April 16, 2020

Support for additional spatial functions

Added descriptions of additional spatial functions.

April 2, 2020

Support for materialized views general availability

Materialized views are generally available starting with cluster version 1.0.13059.

February 19, 2020

Support for column-level privileges

Column-level privileges are available starting with cluster version 1.0.13059.

February 19, 2020

ALTER TABLE

You can use an ALTER TABLE command with the ALTER DISTSTYLE ALL clause to change the distribution style of a table.

February 11, 2020

Support for federated query

Updated the guide to describe federated query with an updated CREATE EXTERNAL SCHEMA.

December 3, 2019

Support for data lake export

Updated the guide to describe new parameters of the UNLOAD command.

December 3, 2019

Support for spatial data

Updated the guide to describe support for spatial data.

November 21, 2019

Support for the new console

Updated the guide to describe the new Amazon Redshift console.

November 11, 2019

Support for automatic table sort

Amazon Redshift can automatically sort table data.

November 7, 2019

Support for VACUUM BOOST option

You can use the BOOST option when vacuuming tables.

November 7, 2019

Support for default IDENTITY columns

You can create tables with default IDENTITY columns.

September 19, 2019

Support for AZ64 compression encoding

You can encode some columns with AZ64 compression encoding.

September 19, 2019

Support for query priority

You can set the query priority of an automatic WLM queue.

August 22, 2019

Support for Amazon Lake Formation

You can use a Lake Formation Data Catalog with Amazon Redshift Spectrum.

August 8, 2019

COMPUPDATE PRESET

You can use a COPY command with COMPUPDATE PRESET to enable Amazon Redshift to choose the compression encoding.

June 13, 2019

ALTER COLUMN

You can use an ALTER TABLE command with ALTER COLUMN to increase the size of a VARCHAR column.

May 22, 2019

Support for stored procedures

You can define PL/pgSQL stored procedures in Amazon Redshift.

April 24, 2019

Support for an automatic workload management (WLM) configuration

You can enable Amazon Redshift to run with automatic WLM.

April 24, 2019

UNLOAD to Zstandard

You can use the UNLOAD command to apply Zstandard compression to text and comma-separated value (CSV) files unloaded to Amazon S3.

April 3, 2019

Concurrency scaling

When concurrency scaling is enabled, Amazon Redshift automatically adds additional cluster capacity when you need it to process an increase in concurrent read queries.

March 21, 2019

UNLOAD to CSV

You can use the UNLOAD command to unload to a file formatted as CSV text.

March 13, 2019

AUTO distribution style

To enable automatic distribution, you can specify the AUTO distribution style with a CREATE TABLE statement. When you enable automatic distribution, Amazon Redshift assigns an optimal distribution style based on the table data. The change in distribution occurs in the background, in a few seconds.

January 23, 2019

COPY from Parquet supports SMALLINT

COPY now supports loading from Parquet formatted files into columns that use the SMALLINT data type. For more information, see COPY from Columnar Data Formats

January 2, 2019

DROP EXTERNAL DATABASE

You can drop an external database by including the DROP EXTERNAL DATABASE clause with a DROP SCHEMA command.

December 3, 2018

Cross-region UNLOAD

You can UNLOAD to an Amazon S3 bucket in another Amazon Region by specifying the REGION parameter.

October 31, 2018

Automatic vacuum delete

Amazon Redshift automatically runs a VACUUM DELETE operation in the background, so you rarely, if ever, need to run a DELETE ONLY vacuum. Amazon Redshift schedules the VACUUM DELETE to run during periods of reduced load and pauses the operation during periods of high load.

October 31, 2018

Automatic distribution

When you don't specify a distribution style with a CREATE TABLE statement, Amazon Redshift assigns an optimal distribution style based on the table data. The change in distribution occurs in the background, in a few seconds.

October 31, 2018

Fine grained access control for the Amazon Glue Data Catalog

You can now specify levels of access to data stored in the Amazon Glue Data Catalog.

October 15, 2018

UNLOAD with data types

You can specify the MANIFEST VERBOSE option with an UNLOAD command to add metadata to the manifest file, including the names and data types of columns, file sizes, and row counts.

October 10, 2018

Add multiple partitions using a single ALTER TABLE statement

For Redshift Spectrum external tables, you can combine multiple PARTITION clauses in a single ALTER TABLE ADD statement. For more information, see Alter External Table Examples.

October 10, 2018

UNLOAD with header

You can specify the HEADER option with an UNLOAD command to add a header line containing column names at the top of each output file.

September 19, 2018

New system table and views

SVL_S3Retries, SVL_USER_INFO, and STL_DISK_FULL_DIAG documentation added.

August 31, 2018

Support for nested data in Amazon Redshift Spectrum

You can now query nested data stored in Amazon Redshift Spectrum tables. For more information, see Tutorial: Querying Nested Data with Amazon Redshift Spectrum.

August 8, 2018

SQA on by default

Short query acceleration (SQA) is now enabled by default for all new clusters. SQA uses machine learning to provide higher performance, faster results, and better predictability of query execution times. For more information, see Short Query Acceleration.

August 8, 2018

Amazon Redshift Advisor

You can now get tailored recommendations on how to improve cluster performance and reduce operating costs from the Amazon Redshift Advisor. For more information, see Amazon Redshift Advisor.

July 26, 2018

Immediate alias reference

You can now refer to an aliased expression immediately after you define it. For more information, see SELECT List.

July 18, 2018

Specify compression type when creating an external table

You can now specify compression type when creating an external table with Amazon Redshift Spectrum. For more information, see Create External Tables.

June 27, 2018

PG_LAST_UNLOAD_ID

Documentation added for a new System Information function: PG_LAST_UNLOAD_ID. For more information, see PG_LAST_UNLOAD_ID.

June 27, 2018

ALTER TABLE RENAME COLUMN

ALTER TABLE now supports renaming columns for external tables. For more information, see Alter External Table Examples.

June 7, 2018

Earlier updates

The following table describes the important changes in each release of the Amazon Redshift Database Developer Guide before June 2018.

Change Description Date changed
COPY from Parquet includes SMALLINT

COPY now supports loading from Parquet formatted files into columns that use the SMALLINT data type. For more information, see COPY from columnar data formats

January 2, 2019
COPY from columnar formats

COPY now supports loading from files on Amazon S3 that use Parquet and ORC columnar data formats. For more information, see COPY from columnar data formats

May 17, 2018
Dynamic maximum run time for SQA

By default, workload management (WLM) now dynamically assigns a value for the short query acceleration (SQA) maximum run time based on analysis of your cluster's workload. For more information, see Maximum runtime for short queries.

May 17, 2018
New column in STL_LOAD_COMMITS

The STL_LOAD_COMMITS system table has a new column, file_format.

May 10, 2018
New columns in STL_HASHJOIN and other system log tables

The STL_HASHJOIN system table has three new columns, hash_segment, hash_step, and checksum. Also, a checksum was added to STL_MERGEJOIN, STL_NESTLOOP, STL_HASH, STL_SCAN, STL_SORT, STL_LIMIT, and STL_PROJECT.

May 17, 2018
New columns in STL_AGGR

The STL_AGGR system table has two new columns, resizes and flushable.

April 19, 2018
New options for REGEX functions

For the REGEXP_INSTR and REGEXP_SUBSTR functions, you can now specify which occurence of a match to use and whether to perform a case-sensitive match. REGEXP_INSTR also allows you specify whether to return the position of the first character of the match or the position of the first character following the end of the match.

March 22, 2018
New columns in system tables The tombstonedblocks, tossedblocks, and batched_by columns were added to the STL_COMMIT_STATS system table. The localslice column was added to the STV_SLICES system view. March 22, 2018
Add and drop columns in external tables ALTER TABLE now supports ADD COLUMN and DROP COLUMN for Amazon Redshift Spectrum external tables. March 22, 2018
Redshift Spectrum new Amazon Regions Redshift Spectrum is now available in the Mumbai and São Paulo Regions. For a list of supported Regions, see Amazon Redshift Spectrum Regions. March 22, 2018
Table limit increased to 20,000

The maximum number of tables is now 20,000 for 8xlarge cluster node types. The limit for large and xlarge node types is 9,900. For more information, see Limits and quotas.

March 13, 2018
Redshift Spectrum support for JSON and Ion

Using Redshift Spectrum, you can reference files with scalar data in JSON or Ion data formats. For more information, see CREATE EXTERNAL TABLE.

February 26, 2018
IAM role chaining for Redshift Spectrum

You can chain Amazon Identity and Access Management (IAM) roles so that your cluster can assume other roles not attached to the cluster, including roles belonging to another Amazon account. For more information, see Chaining IAM roles in Amazon Redshift Spectrum.

February 1, 2018
ADD PARTITION supports IF NOT EXISTS The ADD PARTITION clause for ALTER TABLE now supports an IF NOT EXISTS option. For more information, see ALTER TABLE. January 11, 2018
DATE data for external tables Redshift Spectrum external tables now support the DATE data type. For more information, see CREATE EXTERNAL TABLE. January 11, 2018
Redshift Spectrum new Amazon Regions Redshift Spectrum is now available in the Singapore, Sydney, Seoul, and Frankfurt Regions. For a list of supported Amazon Regions, see Amazon Redshift Spectrum Regions. November 16, 2017
Short query acceleration in Amazon Redshift workload management (WLM) Short query acceleration (SQA) prioritizes selected short-running queries ahead of longer-running queries. SQA executes short-running queries in a dedicated space, so that SQA queries aren't forced to wait in queues behind longer queries. With SQA, short-running queries begin executing more quickly and users see results sooner. For more information, see Short query acceleration. November 16, 2017
WLM reassigns hopped queries Instead of canceling and restarting a hopped query, Amazon Redshift workload management (WLM) now reassigns eligible queries to a new queue. When WLM reassigns a query, it moves the query to the new queue and continues execution, which saves time and system resources. Hopped queries that are not eligible to be reassigned are restarted or canceled. For more information, see WLM query queue hopping. November 16, 2017
System log access for users In most system log tables that are visible to users, rows generated by another user are invisible to a regular user by default. To permit a regular user to see all rows in user-visible tables, including rows generated by another user, run ALTER USER or CREATE USER and set the SYSLOG ACCESS parameter to UNRESTRICTED. November 16, 2017
Result caching With Result caching , when you run a query Amazon Redshift caches the result. When you run the query again, Amazon Redshift checks for a valid, cached copy of the query result. If a match is found in the result cache, Amazon Redshift uses the cached result and doesn't run the query. Result caching is turned on by default. To turn off result caching, set the enable_result_cache_for_session configuration parameter to off. November 16, 2017
Column metadata functions PG_GET_COLS and PG_GET_LATE_BINDING_VIEW_COLS return column metadata for Amazon Redshift tables, views, and late-binding views. November 16, 2017
WLM queue hopping for CTAS Amazon Redshift workload management (WLM) now supports query queue hopping for CREATE TABLE AS (CTAS) statements as well as read-only queries, such as SELECT statements. For more information, see WLM query queue hopping. October 19, 2017
Amazon Redshift Spectrum manifest files When you create a Redshift Spectrum external table, you can specify a manifest file that lists the locations of data files on Amazon S3. For more information, see CREATE EXTERNAL TABLE. October 19, 2017
Amazon Redshift Spectrum new Amazon Regions Redshift Spectrum is now available in the EU (Ireland) and Asia Pacific (Tokyo) Regions. For a list of supported Amazon Regions, see Amazon Redshift Spectrum limitations. October 19, 2017
Amazon Redshift Spectrum added file formats You can now create Redshift Spectrum external tables based on Regex, OpenCSV, and Avro data file formats. For more information, see CREATE EXTERNAL TABLE. October 5, 2017
Pseudocolumns for Amazon Redshift Spectrum external tables You can select the $path and $size pseudocolumns in a Redshift Spectrum external table to view the location and size of the referenced data files in Amazon S3. For more information, see Pseudocolumns. October 5, 2017
Functions to validate JSON You can use the IS_VALID_JSON and IS_VALID_JSON_ARRAY functions to check for valid JSON formatting. The other JSON functions now have an optional null_if_invalid argument. October 5, 2017
LISTAGG DISTINCT You can use the DISTINCT clause with the LISTAGG aggregate function and the LISTAGG window function to eliminate duplicate values from the specified expression before concatenating. October 5, 2017
View column names in uppercase To view column names in SELECT results in uppercase, you can set the describe_field_name_in_uppercase configuration parameter to true. October 5, 2017
Skip header lines in external tables You can set the skip.header.line.count property in the CREATE EXTERNAL TABLE command to skip header lines at the beginning of Redshift Spectrum data files. October 5, 2017
Scan row count WLM query monitor rules uses the scan_row_count metric to return the number of rows in a scan step. The row count is the total number of rows emitted before filtering rows marked for deletion (ghost rows) and before applying user-defined query filters. For more information, see Query monitoring metrics for Amazon Redshift provisioned. September 21, 2017
SQL user-defined functions A scalar SQL user-defined function (UDF) incorporates a SQL SELECT clause that executes when the function is called and returns a single value. For more information, see Scalar SQL UDFs. August 31, 2017
Late-binding views A late-binding view is not bound to the underlying database objects, such as tables and user-defined functions. As a result, there is no dependency between the view and the objects it references. You can create a view even if the referenced objects don't exist. Because there is no dependency, you can drop or alter a referenced object without affecting the view. Amazon Redshift doesn't check for dependencies until the view is queried. To create a late-binding view, specify the WITH NO SCHEMA BINDING clause with your CREATE VIEW statement. For more information, see CREATE VIEW. August 31, 2017
OCTET_LENGTH function OCTET_LENGTH returns the length of the specified string as the number of bytes. August 18, 2017
ORC and Grok files types supported Amazon Redshift Spectrum now supports the ORC and Grok data formats for Redshift Spectrum data files. For more information, see Data files for queries in Amazon Redshift Spectrum. August 18, 2017
RegexSerDe now supported Amazon Redshift Spectrum now supports the RegexSerDe data format. For more information, see Data files for queries in Amazon Redshift Spectrum. July 19, 2017
New columns added to SVV_TABLES and SVV_COLUMNS The columns domain_name and remarks were added to SVV_COLUMNS. A remarks column was added to SVV_TABLES. July 19, 2017
SVV_TABLES and SVV_COLUMNS system views The SVV_TABLES and SVV_COLUMNS system views provide information about columns and other details for local and external tables and views. July 7, 2017
VPC no longer required for Amazon Redshift Spectrum with Amazon EMR Hive metastore Redshift Spectrum removed the requirement that the Amazon Redshift cluster and the Amazon EMR cluster must be in the same VPC and the same subnet when using an Amazon EMR Hive metastore. For more information, see Working with external catalogs in Amazon Redshift Spectrum. July 7, 2017
UNLOAD to smaller file sizes By default, UNLOAD creates multiple files on Amazon S3 with a maximum size of 6.2 GB. To create smaller files, specify the MAXFILESIZE with the UNLOAD command. You can specify a maximum file size between 5 MB and 6.2 GB. For more information, see UNLOAD. July 7, 2017
TABLE PROPERTIES You can now set the TABLE PROPERTIES numRows parameter for CREATE EXTERNAL TABLE or ALTER TABLE to update table statistics to reflect the number of rows in the table. June 6, 2017
ANALYZE PREDICATE COLUMNS To save time and cluster resources, you can choose to analyze only the columns that are likely to be used as predicates. When you run ANALYZE with the PREDICATE COLUMNS clause, the analyze operation includes only columns that have been used in a join, filter condition, or group by clause, or are used as a sort key or distribution key. For more information, see Analyzing tables. May 25, 2017
IAM policies for Amazon Redshift Spectrum To grant access to an Amazon S3 bucket only using Redshift Spectrum, you can include a condition that allows access for the user agent AWS Redshift/Spectrum. For more information, see IAM policies for Amazon Redshift Spectrum. May 25, 2017
Amazon Redshift Spectrum Recursive Scan Redshift Spectrum now scans files in subfolders as well as the specified folder in Amazon S3. For more information, see External tables for Redshift Spectrum. May 25, 2017
Query monitoring rules Using WLM query monitoring rules, you can define metrics-based performance boundaries for WLM queues and specify what action to take when a query goes beyond those boundaries—log, hop, or abort. You define query monitoring rules as part of your workload management (WLM) configuration. For more information, see WLM query monitoring rules. April 21, 2017
Amazon Redshift Spectrum Using Redshift Spectrum, you can efficiently query and retrieve data from files in Amazon S3 without having to load the data into tables. Redshift Spectrum queries execute very fast against large datasets because Redshift Spectrum scans the data files directly in Amazon S3. Much of the processing occurs in the Amazon Redshift Spectrum layer, and most of the data remains in Amazon S3. Multiple clusters can concurrently query the same dataset on Amazon S3 without the need to make copies of the data for each cluster. For more information, see Amazon Redshift Spectrum April 19, 2017
New system tables to support Redshift Spectrum The following new system views have been added to support Redshift Spectrum: April 19, 2017
APPROXIMATE PERCENTILE_DISC aggregate function The APPROXIMATE PERCENTILE_DISC aggregate function is now available. April 4, 2017
Server-side encryption with KMS You can now unload data to Amazon S3 using server-side encryption with an Amazon Key Management Service key (SSE-KMS). In addition, COPY now transparently loads KMS-encrypted data files from Amazon S3. For more information, see UNLOAD. February 9, 2017
New authorization syntax You can now use the IAM_ROLE, MASTER_SYMMETRIC_KEY, ACCESS_KEY_ID, SECRET_ACCESS_KEY, and SESSION_TOKEN parameters to provide authorization and access information for COPY, UNLOAD, and CREATE LIBRARY commands. The new authorization syntax provides a more flexible alternative to providing a single string argument to the CREDENTIALS parameter. For more information, see Authorization parameters. February 9, 2017
Schema limit increase You can now create up to 9,900 schemas per cluster. For more information, see CREATE SCHEMA. February 9, 2017
Default table encoding CREATE TABLE and ALTER TABLE now assign LZO compression encoding to most new columns. Columns defined as sort keys, columns that are defined as BOOLEAN, REAL, or DOUBLE PRECISION data types, and temporary tables are assigned RAW encoding by default. For more information, see ENCODE. February 6, 2017
ZSTD compression encoding Amazon Redshift now supports ZSTD column compression encoding. January 19, 2017
PERCENTILE_CONT and MEDIAN aggregate functions PERCENTILE_CONT and MEDIAN are now available as aggregate functions as well as window functions. January 19, 2017
User-defined function (UDF) User Logging You can use the Python logging module to create user-defined error and warning messages in your UDFs. Following query execution, you can query the SVL_UDF_LOG system view to retrieve logged messages. For more information about user-defined messages, see Logging errors and warnings in Python UDFs December 8, 2016
ANALYZE COMPRESSION estimated reduction The ANALYZE COMPRESSION command now reports an estimate for percentage reduction in disk space for each column. For more information, see ANALYZE COMPRESSION. November 10, 2016
Connection limits You can now set a limit on the number of database connections a user is permitted to have open concurrently. You can also limit the number of concurrent connections for a database. For more information, see CREATE USER and CREATE DATABASE. November 10, 2016
COPY sort order enhancement COPY now automatically adds new rows to the table's sorted region when you load your data in sort key order. For specific requirements to enable this enhancement, see Load your data in sort key order November 10, 2016
CTAS with compression CREATE TABLE AS (CTAS) now automatically assigns compression encodings to new tables based on the column's data type. For more information, see Inheritance of column and table attributes. October 28, 2016
Time stamp with time zone data type Amazon Redshift now supports a timestamp with time zone (TIMESTAMPTZ) data type. Also, several new functions have been added to support the new data type. For more information, see Date and time functions. September 29, 2016
Analyze threshold To reduce processing time and improve overall system performance for ANALYZE operations, Amazon Redshift skips analyzing a table if the percentage of rows that have changed since the last ANALYZE command run is lower than the analyze threshold specified by the analyze_threshold_percent parameter. By default, analyze_threshold_percent is 10. August 9, 2016
New STL_RESTARTED_SESSIONS system table When Amazon Redshift restarts a session, STL_RESTARTED_SESSIONS records the new process ID (PID) and the old PID. August 9, 2016
Updated the Date and Time Functions documentation Added a summary of functions with links to the Date and time functions, and updated the function references for consistency. June 24, 2016
New columns in STL_CONNECTION_LOG The STL_CONNECTION_LOG system table has two new columns to track SSL connections. If you routinely load audit logs to an Amazon Redshift table, you will need to add the following new columns to the target table: sslcompression and sslexpansion. May 5, 2016
MD5-hash password You can specify the password for a CREATE USER or ALTER USER command by supplying the MD5-hash string of the password and user name. April 21, 2016
New column in STV_TBL_PERM The backup column in the STV_TBL_PERM system view indicates whether the table is included in cluster snapshots. For more information, see BACKUP. April 21, 2016
No-backup tables For tables, such as staging tables, that won't contain critical data, you can specify BACKUP NO in your CREATE TABLE or CREATE TABLE AS statement to prevent Amazon Redshift from including the table in automated or manual snapshots. Using no-backup tables saves processing time when creating snapshots and restoring from snapshots and reduces storage space on Amazon S3. April 7, 2016
VACUUM delete threshold By default, the VACUUM command now reclaims space such that at least 95 percent of the remaining rows are not marked for deletion. As a result, VACUUM usually needs much less time for the delete phase compared to reclaiming 100 percent of deleted rows. You can change the default threshold for a single table by including the TO threshold PERCENT parameter when you run the VACUUM command. April 7, 2016
SVV_TRANSACTIONS system table The SVV_TRANSACTIONS system view records information about transactions that currently hold locks on tables in the database. April 7, 2016
Using IAM roles to access other Amazon resources To move data between your cluster and another Amazon resource, such as Amazon S3, DynamoDB, Amazon EMR, or Amazon EC2, your cluster must have permission to access the resource and perform the necessary actions. As a more secure alternative to providing an access key pair with COPY, UNLOAD, or CREATE LIBRARY commands, you can now you specify an IAM role that your cluster uses for authentication and authorization. For more information, see Role-based access control. March 29, 2016
VACUUM sort threshold The VACUUM command now skips the sort phase for any table where more than 95 percent of the table's rows are already sorted. You can change the default sort threshold for a single table by including the TO threshold PERCENT parameter when you run the VACUUM command. March 17, 2016
New columns in STL_CONNECTION_LOG The STL_CONNECTION_LOG system table has three new columns. If you routinely load audit logs to an Amazon Redshift table, you will need to add the following new columns to the target table: sslversion, sslcipher, and mtu. March 17, 2016
UNLOAD with bzip2 compression You now have the option to UNLOAD using bzip2 compression. February 8, 2016
ALTER TABLE APPEND ALTER TABLE APPEND appends rows to a target table by moving data from an existing source table. ALTER TABLE APPEND is usually much faster than a similar CREATE TABLE AS or INSERT INTO operation because data is moved, not duplicated. February 8, 2016
WLM query queue hopping If workload management (WLM) cancels a read-only query, such as a SELECT statement, due to a WLM timeout, WLM attempts to route the query to the next matching queue. For more information, see WLM query queue hopping January 7, 2016
ALTER DEFAULT PRIVILEGES You can use the ALTER DEFAULT PRIVILEGES command to define the default set of access privileges to be applied to objects that are created in the future by the specified user. December 10, 2015
bzip2 file compression The COPY command supports loading data from files that were compressed using bzip2. December 10, 2015
NULLS FIRST and NULLS LAST You can specify whether an ORDER BY clause should rank NULLS first or last in the result set. For more information, see ORDER BY clause and Window function syntax summary. November 19, 2015
REGION keyword for CREATE LIBRARY If the Amazon S3 bucket that contains the UDF library files does not reside in the same Amazon Region as your Amazon Redshift cluster, you can use the REGION option to specify the region in which the data is located. For more information, see CREATE LIBRARY. November 19, 2015
User-defined scalar functions (UDFs) You can now create custom user-defined scalar functions to implement non-SQL processing functionality provided either by Amazon Redshift-supported modules in the Python 2.7 Standard Library or your own custom UDFs based on the Python programming language. For more information, see User-defined functions in Amazon Redshift. September 11, 2015
Dynamic properties in WLM configuration The WLM configuration parameter now supports applying some properties dynamically. Other properties remain static changes and require that associated clusters be rebooted so that the configuration changes can be applied. For more information, see WLM dynamic and static configuration properties and Workload management. August 3, 2015
LISTAGG function The LISTAGG function and LISTAGG window function return a string created by concatenating a set of column values. July 30, 2015
Deprecated parameter

The max_cursor_result_set_size configuration parameter is deprecated. The size of cursor result sets are constrained based on the cluster's node type. For more information, see Cursor constraints.

July 24, 2015
Revised COPY command documentation The COPY command reference has been extensively revised to make the material friendlier and more accessible. July 15, 2015
COPY from Avro format The COPY command supports loading data in Avro format from data files on Amazon S3, Amazon EMR, and from remote hosts using SSH. For more information, see AVRO and Copy from Avro examples. July 8, 2015
STV_STARTUP_RECOVERY_STATE The STV_STARTUP_RECOVERY_STATE system table records the state of tables that are temporarily locked during cluster restart operations. Amazon Redshift places a temporary lock on tables while they are being processed to resolve stale transactions following a cluster restart. May 25, 2015
ORDER BY optional for ranking functions The ORDER BY clause is now optional for certain window ranking functions. For more information, see CUME_DIST window function, DENSE_RANK window function, RANK window function, NTILE window function, PERCENT_RANK window function, and ROW_NUMBER window function. May 25, 2015
Interleaved sort keys Interleaved sort keys give equal weight to each column in the sort key. Using interleaved sort keys instead of the default compound keys significantly improves performance for queries that use restrictive predicates on secondary sort columns, especially for large tables. Interleaved sorting also improves overall performance when multiple queries filter on different columns in the same table. For more information, see Sort keys and CREATE TABLE. May 11, 2015
Revised tuning query performance topic Query performance tuning has been expanded to include new queries for analyzing query performance and more examples. Also, the topic has been revised to be clearer and more complete. Amazon Redshift best practices for designing queries has more information about how to write queries to improve performance. March 23, 2015
SVL_QUERY_QUEUE_INFO The SVL_QUERY_QUEUE_INFO view summarizes details for queries that spent time in a WLM query queue or commit queue. February 19, 2015
SVV_TABLE_INFO You can use the SVV_TABLE_INFO view to diagnose and address table design issues that can influence query performance, including issues with compression encoding, distribution keys, sort style, data distribution skew, table size, and statistics. February 19, 2015
UNLOAD uses server-side file encryption The UNLOAD command now automatically uses Amazon S3 server-side encryption (SSE) to encrypt all unload data files. Server-side encryption adds another layer of data security with little or no change in performance. October 31, 2014
CUME_DIST window function The CUME_DIST window function calculates the cumulative distribution of a value within a window or partition. October 31, 2014
MONTHS_BETWEEN function The MONTHS_BETWEEN function determines the number of months between two dates. October 31, 2014
NEXT_DAY function The NEXT_DAY function returns the date of the first instance of a specified day that is later than the given date. October 31, 2014
PERCENT_RANK window function The PERCENT_RANK window function calculates the percent rank of a given row. October 31, 2014
RATIO_TO_REPORT window function The RATIO_TO_REPORT window function calculates the ratio of a value to the sum of the values in a window or partition. October 31, 2014
TRANSLATE function The TRANSLATE function replaces all occurrences of specified characters within a given expression with specified substitutes. October 31, 2014
NVL2 function The NVL2 function returns one of two values based on whether a specified expression evaluates to NULL or NOT NULL. October 16, 2014
MEDIAN window function The MEDIAN window function calculates the median value for the range of values in a window or partition. October 16, 2014
ON ALL TABLES IN SCHEMA schema_name clause for GRANT and REVOKE commands The GRANT and REVOKE commands have been updated with an ON ALL TABLES IN SCHEMA schema_name clause. This clause allows you to use a single command to change privileges for all tables in a schema. October 16, 2014
IF EXISTS clause for DROP SCHEMA, DROP TABLE, DROP USER, and DROP VIEW commands The DROP SCHEMA, DROP TABLE, DROP USER, and DROP VIEW commands have been updated with an IF EXISTS clause. This clause causes the command to make no changes and return a message rather than terminating with an error if the specified object doesn’t exist. October 16, 2014
IF NOT EXISTS clause for CREATE SCHEMA and CREATE TABLE commands The CREATE SCHEMA and CREATE TABLE commands have been updated with an IF NOT EXISTS clause. This clause causes the command to make no changes and return a message rather than terminating with an error if the specified object already exists. October 16, 2014
COPY support for UTF-16 encoding The COPY command now supports loading from data files that use UTF-16 encoding as well as UTF-8 encoding. For more information, see ENCODING. September 29, 2014
New workload management tutorial Tutorial: Configuring manual workload management (WLM) queues walks you through the process of configuring Workload Management (WLM) queues to improve query processing and resource allocation. September 25, 2014
AES 128-bit encryption The COPY command now supports AES 128-bit encryption as well as AES 256-bit encryption when loading from data files encrypted using Amazon S3 client-side encryption. For more information, see Loading encrypted data files from Amazon S3. September 29, 2014
PG_LAST_UNLOAD_COUNT function The PG_LAST_UNLOAD_COUNT function returns the number of rows that were processed in the most recent UNLOAD operation. For more information, see PG_LAST_UNLOAD_COUNT. September 15, 2014
New troubleshooting queries section Query troubleshooting provides a quick reference for identifying and addressing some of the most common and most serious issues you are likely to encounter with Amazon Redshift queries. July 7, 2014
New loading data tutorial Tutorial: Loading data from Amazon S3 walks you through the process of loading data into your Amazon Redshift database tables from data files in an Amazon S3 bucket, from beginning to end. July 1, 2014
PERCENTILE_CONT window function PERCENTILE_CONT window function is an inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into the given percentile value with respect to the sort specification. June 30, 2014
PERCENTILE_DISC window function PERCENTILE_DISC window function is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. June 30, 2014
GREATEST and LEAST functions The GREATEST and LEAST functions functions return the largest or smallest value from a list of expressions. June 30, 2014
Cross-region COPY The COPY command supports loading data from an Amazon S3 bucket or Amazon DynamoDB table that is located in a different region than the Amazon Redshift cluster. For more information, see REGION in the COPY command reference. June 30, 2014
Best Practices expanded Amazon Redshift best practices has been expanded, reorganized, and moved to the top of the navigation hierarchy to make it more discoverable. May 28, 2014
UNLOAD to a single file The UNLOAD command can optionally unload table data serially to a single file on Amazon S3 by adding the PARALLEL OFF option. If the size of the data is greater than the maximum file size of 6.2 GB, UNLOAD creates additional files. May 6, 2014
REGEXP functions The REGEXP_COUNT, REGEXP_INSTR, and REGEXP_REPLACE functions manipulate strings based on regular expression pattern matching. May 6, 2014
COPY from Amazon EMR

The COPY command supports loading data directly from Amazon EMR clusters. For more information, see Loading data from Amazon EMR.

April 18, 2014
WLM concurrency limit increase

You can now configure workload management (WLM) to run up to 50 queries concurrently in user-defined query queues. This increase gives users more flexibility for managing system performance by modifying WLM configurations. For more information, see Implementing manual WLM

April 18, 2014
New configuration parameter to manage cursor size

The max_cursor_result_set_size configuration parameter defines the maximum size of data, in megabytes, that can be returned per cursor result set of a larger query. This parameter value also affects the number of concurrent cursors for the cluster, enabling you to configure a value that increases or decreases the number of cursors for your cluster.

For more information, see DECLARE in this guide and Configure Maximum Size of a Cursor Result Set in the Amazon Redshift Management Guide.

March 28, 2014
COPY from JSON format The COPY command supports loading data in JSON format from data files on Amazon S3 and from remote hosts using SSH. For more information, see COPY from JSON format usage notes. March 25, 2014
New system table STL_PLAN_INFO The STL_PLAN_INFO table supplements the EXPLAIN command as another way to look at query plans. March 25, 2014
New function REGEXP_SUBSTR The REGEXP_SUBSTR function returns the characters extracted from a string by searching for a regular expression pattern. March 25, 2014
New columns for STL_COMMIT_STATS The STL_COMMIT_STATS table has two new columns: numxids and oldestxid. March 6, 2014
COPY from SSH support for gzip and lzop The COPY command supports gzip and lzop compression when loading data through an SSH connection. February 13, 2014
New functions The ROW_NUMBER window function returns the number of the current row. The STRTOL function converts a string expression of a number of the specified base to the equivalent integer value. PG_CANCEL_BACKEND and PG_TERMINATE_BACKEND enable users to cancel queries and session connections. The LAST_DAY function has been added for Oracle compatibility. February 13, 2014
New system table The STL_COMMIT_STATS system table provides metrics related to commit performance, including the timing of the various stages of commit and the number of blocks committed. February 13, 2014
FETCH with single-node clusters When using a cursor on a single-node cluster, the maximum number of rows that can be fetched using the FETCH command is 1000. FETCH FORWARD ALL is not supported for single-node clusters. February 13, 2014
DS_DIST_ALL_INNER redistribution strategy DS_DIST_ALL_INNER in the Explain plan output indicates that the entire inner table was redistributed to a single slice because the outer table uses DISTSTYLE ALL. For more information, see Join type examples and Evaluating the query plan. January 13, 2014
New system tables for queries Amazon Redshift has added new system tables that customers can use to evaluate query execution for tuning and troubleshooting. For more information, see SVL_COMPILE, STL_SCAN, STL_RETURN, STL_SAVE STL_ALERT_EVENT_LOG. January 13, 2014
Single-node cursors Cursors are now supported for single-node clusters. A single-node cluster can have two cursors open at a time, with a maximum result set of 32 GB. On a single-node cluster, we recommend setting the ODBC Cache Size parameter to 1,000. For more information, see DECLARE. December 13, 2013
ALL distribution style ALL distribution can dramatically shorter execution times for certain types of queries. When a table uses ALL distribution style, a copy of the table is distributed to every node. Because the table is effectively collocated with every other table, no redistribution is needed during query execution. ALL distribution is not appropriate for all tables because it increases storage requirements and load time. For more information, see Data distribution for query optimization. November 11, 2013
COPY from remote hosts In addition to loading tables from data files on Amazon S3 and from Amazon DynamoDB tables, the COPY command can load text data from Amazon EMR clusters, Amazon EC2 instances, and other remote hosts by using SSH connections. Amazon Redshift uses multiple simultaneous SSH connections to read and load data in parallel. For more information, see Loading data from remote hosts. November 11, 2013
WLM memory percent used You can balance workload by designating a specific percentage of memory for each queue in your workload management (WLM) configuration. For more information, see Implementing manual WLM. November 11, 2013
APPROXIMATE COUNT(DISTINCT) Queries that use APPROXIMATE COUNT(DISTINCT) execute much faster, with a relative error of about 2%. The APPROXIMATE COUNT(DISTINCT) function uses a HyperLogLog algorithm. For more information, see the COUNT function. November 11, 2013
New SQL functions to retrieve recent query details Four new SQL functions retrieve details about recent queries and COPY commands. The new functions make it easier to query the system log tables, and in many cases provide necessary details without needing to access the system tables. For more information, see PG_BACKEND_PID, PG_LAST_COPY_ID, PG_LAST_COPY_COUNT, PG_LAST_QUERY_ID. November 1, 2013
MANIFEST option for UNLOAD The MANIFEST option for the UNLOAD command complements the MANIFEST option for the COPY command. Using the MANIFEST option with UNLOAD automatically creates a manifest file that explicitly lists the data files that were created on Amazon S3 by the unload operation. You can then use the same manifest file with a COPY command to load the data. For more information, see Unloading data to Amazon S3 and UNLOAD examples. November 1, 2013
MANIFEST option for COPY You can use the MANIFEST option with the COPY command to explicitly list the data files that will be loaded from Amazon S3. October 18, 2013
System tables for troubleshooting queries Added documentation for system tables that are used to troubleshoot queries. The STL views for logging section now contains documentation for the following system tables: STL_AGGR, STL_BCAST, STL_DIST, STL_DELETE, STL_HASH, STL_HASHJOIN, STL_INSERT, STL_LIMIT, STL_MERGE, STL_MERGEJOIN, STL_NESTLOOP, STL_PARSE, STL_PROJECT, STL_SCAN, STL_SORT, STL_UNIQUE, STL_WINDOW. October 3, 2013
CONVERT_TIMEZONE function The CONVERT_TIMEZONE function converts a timestamp from one time zone to another, with the option to automatically adjust for daylight savings time. October 3, 2013
SPLIT_PART function The SPLIT_PART function splits a string on the specified delimiter and returns the part at the specified position. October 3, 2013
STL_USERLOG system table STL_USERLOG records details for changes that occur when a database user is created, altered, or deleted. October 3, 2013
LZO column encoding and LZOP file compression. LZO column compression encoding combines a very high compression ratio with good performance. COPY from Amazon S3 supports loading from files compressed using LZOP compression. September 19, 2013
JSON, regular expressions, and cursors Added support for parsing JSON strings, pattern matching using regular expressions, and using cursors to retrieve large data sets over an ODBC connection. For more information, see JSON functions, Pattern-matching conditions, and DECLARE. September 10, 2013
ACCEPTINVCHAR option for COPY You can successfully load data that contains invalid UTF-8 characters by specifying the ACCEPTINVCHAR option with the COPY command. August 29, 2013
CSV option for COPY The COPY command now supports loading from CSV formatted input files. August 9, 2013
CRC32 The CRC32 function performs cyclic redundancy checks. August 9, 2013
WLM wildcards Workload management (WLM) supports wildcards for adding user groups and query groups to queues. For more information, see Wildcards. August 1, 2013
WLM timeout To limit the amount of time that queries in a given WLM queue are permitted to use, you can set the WLM timeout value for each queue. For more information, see WLM timeout. August 1, 2013
New COPY options 'auto' and 'epochsecs' The COPY command performs automatic recognition of date and time formats. New time formats, 'epochsecs' and 'epochmillisecs' enable COPY to load data in epoch format. July 25, 2013
CONVERT_TIMEZONE function The CONVERT_TIMEZONE function converts a timestamp from one timezone to another. July 25, 2013
FUNC_SHA1 function The FUNC_SHA1 function converts a string using the SHA1 algorithm. July 15, 2013
max_execution_time To limit the amount of time queries are permitted to use, you can set the max_execution_time parameter as part of the WLM configuration. For more information, see Modifying the WLM configuration. July 22, 2013
Four-byte UTF-8 characters The VARCHAR data type now supports four-byte UTF-8 characters. Five-byte or longer UTF-8 characters are not supported. For more information, see Storage and ranges. July 18, 2013
SVL_QERROR The SVL_QERROR system view has been deprecated. July 12, 2013
Revised Document History The Document History page now shows the date the documentation was updated. July 12, 2013
STL_UNLOAD_LOG STL_UNLOAD_LOG records the details for an unload operation. July 5, 2013
JDBC fetch size parameter To avoid client-side out of memory errors when retrieving large data sets using JDBC, you can enable your client to fetch data in batches by setting the JDBC fetch size parameter. For more information, see Setting the JDBC fetch size parameter. June 27, 2013
UNLOAD encrypted files UNLOAD now supports unloading table data to encrypted files on Amazon S3. May 22, 2013
Temporary credentials COPY and UNLOAD now support the use of temporary credentials. April 11, 2013
Added clarifications Clarified and expanded discussions of Designing Tables and Loading Data. February 14, 2013
Added best practices Added Amazon Redshift best practices for designing tables and Amazon Redshift best practices for loading data. February 14, 2013
Clarified password constraints Clarified password constraints for CREATE USER and ALTER USER, various minor revisions. February 14, 2013

New guide

This is the first release of the Amazon Redshift Developer Guide. February 14, 2013