Amazon Redshift is based on PostgreSQL. However, there are very important differences. Some of them are outlined below
- Amazon Redshift is designed for OLAP and BI applications which requires processing of data across large datasets with columnar storage. However, Postgres is designed for OLTP applications for high performance.
- Amazon Redshift doesn’t support indexes, instead each table has sort key which determines the order of the rows when the data is loaded. Postgres supports Secondary indexes that are suited for OLTP Processing.
- Some of the common features which are different across Amazon Redshift and PostgreSQL – VACCUM, CREATE TABLE, ALTER TABLE, COPY.
- Some of the unsupported PostgreSQL features in Amazon Redshift – Tablespaces, Constraints – Foreign/Primary keys, Indexes, Triggers, Sequences. More information is available in – https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html
- Some of the unsupported PostgreSQL data types in Amazon Redshift – Arrays, BIT, INTERVAL, SERIAL, char. More information is available in https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-datatypes.html
- Some of the unsupported PostgreSQL functions in Amazon Redshift – https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-functions.html