How to Use Redshift on AWS in PostgreSQL

How do I connect to, load data into, and query Amazon Redshift on AWS using PostgreSQL syntax?

Amazon Redshift is a PostgreSQL-compatible, cloud data-warehouse that you connect to and query with standard SQL.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How do I connect to Amazon Redshift?

Use a PostgreSQL connection string that points to the Redshift endpoint, port 5439, and your database name.

psql "host=example-cluster.abc123.us-east-1.redshift.amazonaws.com \ port=5439 dbname=dev user=reporting password=••••"

Keep the connection user in the public schema read-only unless you need DDL rights. Store credentials in a secrets manager and pass them as environment variables for CI jobs.

How do I bulk-load data from S3?

Redshift’s COPY command ingests files in parallel, making it the fastest way to populate fact tables.

COPY Orders FROM 's3://ecom-data/2024/06/orders.csv' CREDENTIALS 'aws_iam_role=arn:aws:iam::123456789012:role/redshiftCopyRole' FORMAT AS CSV IGNOREHEADER 1 REGION 'us-east-1';

Grant the cluster IAM role s3:GetObject on the bucket. Match file column order to the table to avoid load errors.

How can I query across multiple tables?

Because Redshift speaks PostgreSQL, you write the same joins you already know.

SELECT c.name, COUNT(o.id) AS orders, SUM(o.total_amount) AS lifetime_value FROM Customers c JOIN Orders o ON o.customer_id = c.id GROUP BY c.name ORDER BY lifetime_value DESC LIMIT 20;

Use DISTKEY and SORTKEY on customer_id to minimize data movement for this pattern.

What are best practices for performance?

Assign DISTSTYLE KEY to large fact tables on the most common join column.
Compress columns with ENCODE AUTO or specific encodings found via ANALYZE COMPRESSION.
Run VACUUM and ANALYZE after heavy loads to reclaim space and update statistics.
Keep commits small; Redshift isn’t optimized for many tiny transactions.

Where does Redshift differ from vanilla PostgreSQL?

Redshift omits some features (e.g., ALTER TABLE … ADD CONSTRAINT foreign keys) and adds warehouse-specific ones like COPY, UNLOAD, and spectrum external tables. Always test queries for compatibility before porting code.

Why How to Use Redshift on AWS in PostgreSQL is important

How to Use Redshift on AWS in PostgreSQL Example Usage


-- Show top-selling products this month
SELECT p.name,
       SUM(oi.quantity) AS units_sold,
       SUM(oi.quantity * p.price) AS revenue
FROM   OrderItems oi
JOIN   Products   p  ON p.id = oi.product_id
JOIN   Orders     o  ON o.id = oi.order_id
WHERE  o.order_date >= date_trunc('month', current_date)
GROUP  BY p.name
ORDER  BY revenue DESC
LIMIT 10;

How to Use Redshift on AWS in PostgreSQL Syntax


-- PostgreSQL-style connection string
psql "host=<endpoint> port=5439 dbname=<db> user=<user> password=<pwd>"

-- Bulk load from S3
COPY <table_name>
FROM 's3://<bucket>/<path>/<file>'
CREDENTIALS 'aws_iam_role=<IAM-role-ARN>'
[FORMAT AS] CSV | JSON | PARQUET
IGNOREHEADER <int>
DELIMITER ','
REGION '<aws-region>'
TIMEFORMAT 'auto'

-- Example distribution and sort keys
CREATE TABLE Orders (
    id           BIGINT   IDENTITY(1,1),
    customer_id  BIGINT   NOT NULL,
    order_date   DATE     NOT NULL,
    total_amount NUMERIC(10,2)
)
DISTSTYLE KEY DISTKEY (customer_id)
SORTKEY  (order_date);

Common Mistakes

Ignoring sort keys: Loading wide fact tables without a SORTKEY causes slow scans. Pick the most common filter (e.g., order_date) as SORTKEY to enable zone-map pruning.
Using many small INSERTs: Redshift is columnar and optimized for batch inserts via COPY. Aggregate small files into 100 MB+ objects and load them in parallel for best throughput.

Frequently Asked Questions (FAQs)

Can I use psql or PgAdmin with Redshift?

Yes. Redshift is wire-compatible with PostgreSQL 8.0.2, so any standard PostgreSQL client works as long as you point to the cluster endpoint on port 5439.

How do I unload query results back to S3?

Use the UNLOAD command:

UNLOAD ('SELECT * FROM Orders') TO 's3://ecom-data/unloads/orders_' IAM_ROLE 'arn:aws:iam::123456789012:role/redshiftUnloadRole' FORMAT AS PARQUET;

Is Redshift Serverless different?

Serverless removes cluster sizing; you pay for RPU-seconds. SQL syntax stays the same, but connection strings use <workgroup>.<region>.redshift-serverless.amazonaws.com.