How to Use ClickHouse Cloud-Native Tables in PostgreSQL

How do I create and query ClickHouse Cloud-native tables from PostgreSQL?

ClickHouse Cloud Native lets you create, load, and query column-oriented tables that scale elastically and integrate with PostgreSQL via FDW or ETL.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does “cloud-native” mean for ClickHouse?

Cloud-native ClickHouse abstracts storage, compute, and networking so that clusters scale automatically, use S3-backed storage, and expose the regular SQL interface without manual server management.

Why connect PostgreSQL to ClickHouse Cloud?

Teams keep transactional data in PostgreSQL while offloading analytics to ClickHouse.Using the clickhouse_fdw extension or batch ETL, you query massive datasets with millisecond latency.

How do I create a table in a ClickHouse Cloud cluster?

Create tables with the ENGINE = MergeTree family (or ReplicatedMergeTree) and point location to cloud object storage.Define ORDER BY for efficient pruning.

Example

CREATE TABLE Orders ( id UInt64, customer_id UInt64, order_date Date, total_amount Decimal(12,2) ) ENGINE = MergeTree ORDER BY id SETTINGS storage_policy = 's3_mirror';

How do I query ClickHouse from PostgreSQL?

Install clickhouse_fdw, create a foreign server that points at the cloud endpoint, then map ClickHouse tables to foreign tables. Use normal SELECT statements.

Example

CREATE EXTENSION IF NOT EXISTS clickhouse_fdw; CREATE SERVER ch_cloud FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(host 'https://.aws.clickhouse.cloud', port '8443');. CREATE FOREIGN TABLE ch_orders ( id bigint, customer_id bigint, order_date date, total_amount numeric ) SERVER ch_cloud OPTIONS(table 'Orders'); SELECT customer_id, SUM(total_amount) FROM ch_orders GROUP BY customer_id;

Best practices for cloud-native ClickHouse

Partition by time for log-like data, use ORDER BY on high-cardinality keys, store large blobs outside ClickHouse, and monitor system.parts to spot merges.

Common mistakes to avoid

Skipping the ORDER BY clause leads to full-table scans and high costs. Forgetting to set a storage policy causes local SSD usage to explode instead of tiering to S3.

When should I prefer ETL over FDW?

Use FDW for low-latency joins on fresh data (<1 GB).Batch-load data with INSERT INTO ... SELECT or tools like Airbyte when you need full historical analytics.

Long-tail questions answered

Can ClickHouse Cloud handle petabyte-scale fact tables?

Yes. Data is chunked into immutable parts on S3 while compute nodes pull only relevant parts, keeping queries fast and costs predictable.

Does ClickHouse Cloud encrypt data at rest?

All objects on S3 are encrypted with AWS KMS keys by default, satisfying SOC 2 and GDPR requirements.

Why How to Use ClickHouse Cloud-Native Tables in PostgreSQL is important

How to Use ClickHouse Cloud-Native Tables in PostgreSQL Example Usage


-- Load the last 7 days of PostgreSQL orders into ClickHouse Cloud
INSERT INTO Orders
SELECT id, customer_id, order_date, total_amount
FROM postgres.public.Orders
WHERE order_date >= current_date - interval '7 days';

How to Use ClickHouse Cloud-Native Tables in PostgreSQL Syntax


CREATE TABLE <name> (
    column_name data_type [compression_codec] [DEFAULT|MATERIALIZED expr],
    ...
) ENGINE = {MergeTree | ReplicatedMergeTree('/clickhouse/tables/{shard}/{table}', '{replica}')} 
[PARTITION BY expr] 
[ORDER BY expr] 
[PRIMARY KEY expr] 
[SAMPLE BY expr] 
[SETTINGS storage_policy='s3_mirror', index_granularity=8192];

Common Mistakes

Ignoring ORDER BY. Without it, ClickHouse cannot prune data parts, causing slow, expensive scans. Always set ORDER BY on commonly filtered columns.
Using local storage default. Storing data only on SSD defeats cloud elasticity. Set storage_policy='s3_mirror' or another object-storage policy.