Most migrations to DuckDB start as a cost reduction exercise and quickly become an architectural question. DuckDB is not a drop-in replacement for either Postgres or Redshift. It is a different class of database with different performance characteristics, different consistency guarantees, and a different operational model. The migration works, and the outcome is almost always cheaper and faster for analytical workloads. But the teams that do it badly spend six weeks debugging query result discrepancies and connection handling edge cases that were entirely predictable.

When DuckDB Is and Is Not the Correct Answer

Before starting a migration, the use case has to match what DuckDB actually is. It is an in-process, single-node, columnar analytical database. It is not a server, it does not handle concurrent writes from multiple processes, and it is not a transactional store.

Diagnostic Baseline: Before You Migrate

A migration without a baseline has no success criteria. Run these assessments against the source system before writing a single line of migration code.

Query inventory

1. Export query logs from Postgres (pg_stat_statements) or Redshift (SVL_QLOG) for the last 30 days. Count unique query patterns, not individual executions.
2. Classify by read/write ratio. Queries that are 95%+ reads are migration candidates. Mixed workloads need a decision about whether the write path stays in Postgres.
3. Identify queries that use Postgres-specific functions with no DuckDB equivalent: pg_advisory_lock, LISTEN/NOTIFY, row-level security policies, and partitioning via inheritance rather than declarative partitioning.
4. Flag queries that depend on Redshift-specific features: distribution keys, sort keys, COPY from S3 with IAM roles, Redshift Spectrum, materialised views with incremental refresh.

Schema complexity assessment

1. Count tables with triggers. DuckDB does not support triggers. Any trigger-dependent logic must be moved to the application layer or the ETL pipeline.
2. Count stored procedures and functions. DuckDB supports macros and scalar functions but not PL/pgSQL procedures. Complex stored procedures must be rewritten as dbt models, Python scripts, or SQL files.
3. Count foreign key constraints and check constraints. DuckDB supports these in schema definitions but does not enforce foreign keys at write time. Referential integrity must be validated in the pipeline.
4. Identify SERIAL or IDENTITY columns used as auto-increment primary keys. DuckDB supports SEQUENCE objects and DEFAULT nextval() but the syntax differs from Postgres.

Data volume and format

• Total uncompressed data volume in the tables being migrated.
• Row count per table. Tables above 500M rows will need a deliberate partitioning strategy in the target Parquet layout.
• Dominant data types. DuckDB handles standard types (INTEGER, BIGINT, VARCHAR, TIMESTAMP, DECIMAL) without issue. JSONB, PostGIS geometry, and custom Postgres types need explicit handling.

SQL Compatibility Matrix

What works without changes

• Standard SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
• All standard window functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTILE, SUM OVER, AVG OVER
• CTEs and recursive CTEs
• Joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS
• UNION, INTERSECT, EXCEPT
• Standard aggregate functions: COUNT, SUM, AVG, MIN, MAX
• CASE WHEN expressions
• String functions: CONCAT, UPPER, LOWER, TRIM, REPLACE, SUBSTRING, POSITION, LIKE, ILIKE
• Date functions: DATE_TRUNC, DATE_DIFF, EXTRACT, INTERVAL arithmetic
• JSON extraction: json_extract, json_extract_string (syntax differs slightly from Postgres ->> operator)

Postgres-to-DuckDB: syntax changes required

Pattern	Postgres	DuckDB
JSON field access	col->>'key'	json_extract_string(col, '$.key')
Array literal	ARRAY[1, 2, 3]	[1, 2, 3]
Array element access	arr[1] (1-based)	arr[1] (1-based, same)
String format	format('%s', val)	printf('%s', val) or format('%s', val)
Epoch extraction	EXTRACT(EPOCH FROM ts)	EPOCH(ts)
Type cast	val::INT	val::INT (same) or CAST(val AS INT)
Regex match	col ~ 'pattern'	regexp_matches(col, 'pattern')
NULL comparison	IS DISTINCT FROM	IS DISTINCT FROM (same)
Generate series	generate_series(1, 10)	range(1, 11) or recursive CTE
String split to rows	string_to_table(str, ',')	string_split(str, ',') returns array, then UNNEST

Redshift-to-DuckDB: what does not exist

Redshift feature	DuckDB equivalent
DISTKEY / DISTSTYLE	Not applicable. DuckDB is single-node.
SORTKEY / COMPOUND SORTKEY	Not applicable. Use Parquet partitioning and file ordering instead.
COPY FROM S3 with IAM	read_parquet('s3://...') with DuckDB httpfs extension.
Redshift Spectrum	Native in DuckDB. read_parquet() queries S3 directly.
WLM query queues	Not applicable. Use PRAGMA threads to control concurrency.
LISTAGG(col, delim)	STRING_AGG(col, delim)
DATEDIFF(unit, a, b)	DATE_DIFF('day', a, b)
NVL(a, b)	COALESCE(a, b)
GETDATE()	CURRENT_DATE or NOW()
APPROXIMATE COUNT DISTINCT	approx_count_distinct(col)

Step-by-Step Migration Sequence

Phase 1: Environment and tooling

1. Install DuckDB. For pipeline use, the Python package is the standard entry point: pip install duckdb. For interactive use, the CLI binary is available at duckdb.org.
2. Install extensions required for your migration: INSTALL httpfs; LOAD httpfs; for S3 access, INSTALL postgres; LOAD postgres; for direct Postgres attachment.
3. Set resource limits before running large queries: PRAGMA memory_limit='8GB'; and PRAGMA threads=4;. DuckDB will use all available CPU and memory by default.

Phase 2: Schema migration

1. Export DDL from Postgres using pg_dump --schema-only. Review each table definition against the compatibility notes above before running in DuckDB.
2. Remove or comment out: triggers, stored procedures, row-level security policies, table partitioning via inheritance (replace with Parquet partition layout), and extension-specific types (PostGIS, hstore).
3. Replace SERIAL and BIGSERIAL with INTEGER DEFAULT nextval('seq_name') using a DuckDB SEQUENCE, or with UBIGINT DEFAULT nextval('...') for large tables.
4. Test CREATE TABLE statements in DuckDB before migrating data. Catch type compatibility issues at the schema layer, not after a 48-hour data export.

Phase 3: Data migration

-- Option 1: Direct Postgres attachment (for databases on the same host or accessible network)
INSTALL postgres; LOAD postgres;
ATTACH 'host=localhost dbname=source_db user=postgres' AS src (TYPE POSTGRES);

-- Migrate a single table to Parquet
COPY (SELECT * FROM src.my_table) TO 'my_table.parquet' (FORMAT PARQUET);

-- Option 2: Export to CSV from Postgres, import to DuckDB
-- On Postgres: \COPY table TO 'table.csv' WITH CSV HEADER
-- In DuckDB:
CREATE TABLE my_table AS
SELECT * FROM read_csv_auto('table.csv');

1. Migrate large tables in date-partitioned batches. A single 500M row table exported as one Parquet file is valid but creates a suboptimal read pattern for time-filtered queries. Partition by year or year/month during migration.
2. After migration, run row count and SUM checks on every table against the source. Row counts alone are not sufficient: duplicate detection requires at minimum a COUNT DISTINCT on the primary key.
3. Run a sample of business-critical queries against both systems on the same data and compare results. Specific areas to check: NULL handling in aggregations (DuckDB and Postgres differ in edge cases), integer division behaviour, and timestamp timezone handling.

Phase 4: Query migration

1. Translate each query category from the diagnostic inventory. Start with simple SELECT queries, then window function queries, then complex CTEs. Stored procedures last.
2. Use DuckDB's QUALIFY clause to replace CTEs used solely for window function filtering: SELECT id, ROW_NUMBER() OVER (ORDER BY val DESC) AS rn FROM t QUALIFY rn = 1;
3. Replace Redshift LISTAGG with STRING_AGG. Replace DATEDIFF with DATE_DIFF. Replace NVL with COALESCE. These are mechanical substitutions.
4. Re-test query results against the source system after translation. Pay particular attention to floating-point aggregations, date arithmetic, and queries that previously relied on Redshift sort key ordering.

Failure Modes

These are the issues that appear repeatedly across migrations. They are predictable, which means they are preventable.

Production Setup Reference

-- Standard session setup for production analytical workloads
PRAGMA threads=8;                              -- Limit to half available cores
PRAGMA memory_limit='16GB';                    -- Leave headroom for OS
PRAGMA temp_directory='/fast/nvme/tmp';        -- Fast disk for spill

-- Extension setup for S3 access
INSTALL httpfs;
LOAD httpfs;
SET s3_region='me-south-1';                    -- Set to your S3 region
SET s3_access_key_id='...';                    -- Or use IAM role via instance profile
SET s3_secret_access_key='...';

-- Querying Parquet files directly from S3 (replacing Redshift Spectrum)
SELECT
    DATE_TRUNC('month', event_date) AS month,
    COUNT(*) AS events,
    COUNT(DISTINCT user_id) AS unique_users
FROM read_parquet('s3://your-bucket/events/year=*/month=*/*.parquet')
WHERE event_date >= '2026-01-01'
GROUP BY 1 ORDER BY 1;

-- Writing partitioned Parquet output (replacing INSERT INTO Redshift)
COPY (
    SELECT * FROM transformed_data
) TO 'output/' (FORMAT PARQUET, PARTITION_BY (year, month));

-- Profile a slow query before optimising
EXPLAIN ANALYZE
SELECT user_id, SUM(amount) FROM transactions
WHERE created_at >= '2026-01-01'
GROUP BY user_id;

Summary

A successful migration is a migration that was scoped correctly from the start. DuckDB handles the analytical query layer. It does not replace transactional databases, multi-user query services, or systems that require concurrent writes. Run the diagnostic baseline honestly, translate the schema before the data, test results against the source system, and expect to spend time on JSON operators, timezone handling, and memory configuration. The migration is worth doing. The organisations that run it properly come out with a stack that costs less, runs faster on analytical workloads, and has no infrastructure dependencies they do not control.

Nauman Shahid builds zero-dependency data infrastructure for organisations in the UAE and Gulf region. Companion guide: the Zero-Dependency Data Architecture Blueprint at data.nauman.cc/zero-dependency-architecture/. Diagnostic engagements: www.mindflex.tech. Vendor lock-in audit: audit.nauman.cc.