Redshift vs PostgreSQL

Quick verdict

Amazon Redshift is AWS's managed data warehouse. AWS's documentation says it "is based on PostgreSQL", but also that its storage and query execution engine "are completely different from the PostgreSQL implementation": data is stored by column with compression encodings, queries run in parallel across compute nodes coordinated by a leader node, and features suited to OLTP, such as secondary indexes and efficient single-row changes, were left out. It comes as provisioned clusters (AWS now recommends its Graviton-based RG node type, generally available since May 2026, with RA3 as the current generation) or as Redshift Serverless, billed by capacity used.

PostgreSQL is the open source object-relational database server, released under the PostgreSQL License. It is a general-purpose engine with a strong transactional focus: enforced constraints, MVCC, many index types, triggers, a rich type system (arrays, jsonb, ranges, UUID) and an extension ecosystem. postgresql.org lists 18.6 as the current release. On AWS it is available as Amazon RDS for PostgreSQL and Aurora PostgreSQL.

Side by side

Key differences

Same roots, different engine

Redshift's PostgreSQL heritage shows in its SQL syntax and PG-prefixed catalog tables. AWS previously recommended PostgreSQL JDBC and psqlODBC drivers for it, and now recommends its own Amazon Redshift drivers instead. That is where the similarity ends. AWS states plainly that you should not "assume that the semantics of elements that Amazon Redshift and PostgreSQL have in common are identical", and its documentation keeps three lists of what Redshift does not support. The most important for anyone moving from PostgreSQL:

• Features: indexes, triggers, sequences, table partitioning (range and list), tablespaces, inheritance, exclusion and check constraints, collations, full text search, table functions, SQL/MED, and the psql client (AWS supports its own RSQL client instead). Primary key, unique and foreign key constraints can be declared but are not enforced.
• Data types: arrays, BYTEA, BIT, composite, enumerated, range, network address, SERIAL/BIGSERIAL, MONEY, UUID, XML, HSTORE and PostgreSQL's JSON.
• Functions: among others STRING_AGG(), ARRAY_AGG(), GENERATE_SERIES(), FORMAT(), REGEXP_MATCHES(), IS DISTINCT FROM, ROLLBACK TO SAVEPOINT and the array, range, text search and XML function families. AWS notes that some unsupported functions do not raise an error when they run only on the leader node, which is not a sign that they are supported.

Other behaviour differs quietly: Redshift's default VACUUM is a full vacuum that also re-sorts rows, ALTER TABLE ... ADD COLUMN adds one column per statement, and trailing spaces in VARCHAR values are ignored in comparisons. AWS also announced that Python UDFs are no longer supported after 30 June 2026, with enforcement in phases; SQL UDFs and Lambda UDFs remain.

Physical design: distribution and sort keys instead of indexes

In PostgreSQL you design indexes for the queries you run. In Redshift there are no indexes; you choose how rows are distributed across nodes and how they are sorted on disk, or let Redshift choose with AUTO. Auto-numbering also changes: Redshift has IDENTITY(seed, step) columns instead of sequences and SERIAL.

-- Amazon Redshift
CREATE TABLE sales (
    sale_id     BIGINT IDENTITY(1,1),
    customer_id INT NOT NULL,
    sale_date   DATE NOT NULL,
    amount      DECIMAL(12,2),
    PRIMARY KEY (sale_id)          -- informational only, not enforced
)
DISTSTYLE KEY DISTKEY (customer_id)
SORTKEY (sale_date);

-- PostgreSQL
CREATE TABLE sales (
    sale_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id int NOT NULL REFERENCES customers (customer_id),
    sale_date   date NOT NULL,
    amount      numeric(12,2)
);
CREATE INDEX ON sales (sale_date);

Because Redshift does not enforce the primary key, loading the same batch twice leaves duplicate rows. AWS also warns that the planner assumes declared keys are valid, so if the data violates them some queries can return incorrect results (a SELECT DISTINCT can return duplicates, for example). Redshift does enforce NOT NULL. Deduplication becomes part of your load process, typically with MERGE, which Redshift supports (including a REMOVE DUPLICATES simplified mode). PostgreSQL rejects the duplicate at insert time, or handles it with INSERT ... ON CONFLICT, which Redshift does not have.

Dialect gaps you will hit when porting queries

String aggregation is the classic example. PostgreSQL's STRING_AGG is on Redshift's unsupported list; Redshift uses LISTAGG:

-- PostgreSQL
SELECT region, STRING_AGG(city, ', ' ORDER BY city) AS cities
FROM stores GROUP BY region;

-- Amazon Redshift
SELECT region, LISTAGG(city, ', ') WITHIN GROUP (ORDER BY city) AS cities
FROM stores GROUP BY region;

JSON is the second. PostgreSQL's json/jsonb operators do not exist in Redshift; semi-structured data goes into a SUPER column, loaded with JSON_PARSE or COPY, and is navigated with PartiQL dot and bracket notation. Date series built with generate_series(), regular-expression splitting and array functions all need rewriting as well. In our view, expect a port of PostgreSQL reporting SQL to Redshift to be a review of every query, not a connection-string change.

Getting PostgreSQL data into Redshift: zero-ETL and federated queries

AWS provides two documented paths that avoid building your own pipeline:

• Zero-ETL integrations replicate data continuously from a source into a Redshift provisioned cluster or Serverless workgroup after an initial load. Supported sources listed in October 2026 are Aurora MySQL, Aurora PostgreSQL, RDS for MySQL, RDS for PostgreSQL, RDS for Oracle, Oracle Database@AWS, DynamoDB, several SaaS applications, and self-managed MySQL, PostgreSQL, SQL Server and Oracle. Regional support varies by source.
• Federated queries let Redshift query live tables in RDS or Aurora PostgreSQL (9.6 or later) and RDS or Aurora MySQL (5.6 or later) through an external schema, pushing predicates down to the source. They are read-only, do not work with concurrency scaling, and add load (and, on Aurora, I/O charges) to the operational database.

Neither path is free of trade-offs. Zero-ETL moves data continuously, so you pay for storage and compute on both sides; federated queries hit your production database each time. For occasional joins of live operational data with warehouse data, federated queries are simpler; for dashboards over the full history, replicate.

When you do not need Redshift yet

The decision is often less "Redshift or PostgreSQL" than "do we need a warehouse at all". If reporting fits on a PostgreSQL read replica, and partitioning, BRIN indexes and materialised views keep queries acceptable, a second system adds cost and a new dialect for little gain. Columnar options in between include PostgreSQL extensions, an embedded engine such as DuckDB reading PostgreSQL, or a dedicated columnar server such as ClickHouse; see DuckDB vs PostgreSQL and ClickHouse vs PostgreSQL. The general trade-offs are covered in data warehouse vs database.

Redshift becomes the better fit when analytics joins data from several systems, history grows into many terabytes, many analysts or BI tools query at once, or reporting load is affecting the application. If you are choosing between warehouses rather than whether to have one, see BigQuery vs Redshift and Snowflake vs PostgreSQL.

Pricing and licensing

Amazon Redshift has two billing models. Provisioned clusters are billed per node-hour (on demand, or lower with reserved instances), with Redshift Managed Storage billed separately per GB-month on RG and RA3 nodes. Redshift Serverless is billed in RPU-hours per second with a 60-second minimum; one RPU provides 16 GB of memory, the default base capacity is 128 RPUs, and base capacity can be set from 4 RPUs (in listed regions) up to 512, or 1,024 in some regions. Querying data in Amazon S3 with Redshift Spectrum is billed per TB scanned; the pricing page states Spectrum is not required on RG nodes, which include a built-in data lake query engine. Zero-ETL integrations may add charges on the source and target services.

As one example, the Redshift pricing page lists Serverless compute at USD 0.375 per RPU-hour in US East (N. Virginia) in October 2026, plus managed storage at USD 0.024 per GB-month in the same region. Use the AWS Pricing Calculator for node-based clusters and other regions. New Serverless users are offered a USD 300 credit that expires after 90 days.

PostgreSQL is free under the PostgreSQL License. Managed PostgreSQL on AWS (RDS or Aurora) is billed by instance or capacity, storage, I/O and backup according to AWS's price lists.

Pricing checked on the vendors' official pages on 7 October 2026. Prices change; confirm before buying.

Where each one leads

Limitations

When to choose each

When neither is right

• You are not on AWS, or want a serverless warehouse billed per query: see BigQuery vs Redshift and BigQuery vs PostgreSQL.
• You need analytics on files or inside a pipeline with no server at all: see DuckDB vs PostgreSQL.
• You need real-time analytics on event or log data with an open source engine: see ClickHouse vs PostgreSQL.
• You want a multi-cloud warehouse: see Snowflake vs PostgreSQL.

Final recommendation

Frequently asked questions

Sources

• Amazon Redshift: Amazon Redshift and PostgreSQL
• Amazon Redshift: Unsupported PostgreSQL features
• Amazon Redshift: Unsupported PostgreSQL data types
• Amazon Redshift: Unsupported PostgreSQL functions
• Amazon Redshift: Features implemented differently
• Amazon Redshift: Table constraints
• Amazon Redshift: Isolation levels
• Amazon Redshift: SUPER type
• Amazon Redshift: MERGE
• Amazon Redshift: Zero-ETL integrations
• Amazon Redshift: Federated queries
• Amazon Redshift: Federated query considerations
• Amazon Redshift Serverless: Compute capacity
• Amazon Redshift pricing
• AWS What's New: Amazon Redshift RG instances (May 2026)
• PostgreSQL home page (current releases)
• PostgreSQL License

Checked October 2026.

How we research comparisons: our editorial method.