How to Migrate from ParadeDB to ClickHouse in PostgreSQL

Why migrate from ParadeDB to ClickHouse?

ClickHouse offers column-oriented storage and massive parallel reads, making large analytical queries faster than ParadeDB’s row-oriented Postgres base.

Which ParadeDB objects can be migrated directly?

Regular tables, materialized views, and indexes migrate with CSV export. Extensions, triggers, and functions require manual rewrites or are unsupported.

How do I export data efficiently?

Use the \COPY or COPY command with CSV format and HEADER. Disable compression to avoid CPU overhead during extraction.

Export example

\COPY Customers TO '/tmp/customers.csv' WITH (FORMAT CSV, HEADER);
\COPY Orders TO '/tmp/orders.csv' WITH (FORMAT CSV, HEADER);

How do I map ParadeDB types to ClickHouse?

INTEGER → Int32, BIGINT → Int64, TEXT/VARCHAR → String, TIMESTAMP → DateTime64(3), NUMERIC → Decimal(18,4). No direct array or JSON support—use String or nested tables.

How do I create equivalent tables in ClickHouse?

CREATE TABLE Customers (
id UInt32,
name String,
email String,
created_at DateTime64(3)
) ENGINE = MergeTree
ORDER BY id;

CREATE TABLE Orders (
id UInt32,
customer_id UInt32,
order_date DateTime64(3),
total_amount Decimal(18,4)
) ENGINE = MergeTree
ORDER BY (customer_id, order_date);

How do I bulk-load the exported CSV?

Use clickhouse-client --query with FORMAT CSV piped from cat for each table.

cat /tmp/customers.csv | clickhouse-client --query="INSERT INTO Customers FORMAT CSVWithNames"

How can I verify the migration?

Compare row counts and aggregate checksums.

-- Postgres
SELECT COUNT(*) FROM Customers;
-- ClickHouse
SELECT COUNT(*) FROM Customers;

What is the recommended cutover sequence?

1) Lock ParadeDB writes. 2) Export final delta. 3) Load into ClickHouse. 4) Validate. 5) Redirect application reads.

What are common mistakes?

Skipping type mapping validations and ignoring time zone differences cause silent data issues. Always check destination column definitions.