Quickstart for Managed Postgres

This section shows you how to replicate your Postgres data to ClickHouse and query it seamlessly.

### Setup ClickHouse integration[​](#setup-integrate-clickhouse "Direct link to Setup ClickHouse integration")

Now that we have tables and data in Postgres, let's replicate the tables to ClickHouse for analytics. We start by clicking on **ClickHouse integration** in the sidebar. Then you can click on **Replicate data in ClickHouse**.

In the form that follows, you can enter a name for your integration and select an existing ClickHouse instance to replicate to. If you don't have a ClickHouse instance yet, you can create one directly from this form.

Important

Make sure the ClickHouse service you select is Running before proceeding.

Click on **Next**, to be taken to the table picker. Here all you need to do is:

- Select a ClickHouse database to replicate to.
- Expand the **public** schema and select the users and events table we created earlier.
- Click on **Replicate data to ClickHouse**.

The replication process will start, and you will be taken to the integration overview page. Being the first integration, it can take 2-3 minutes to setup the initial infrastructure. In the meantime let's check out the new **pg\_clickhouse** extension.

### Query ClickHouse from Postgres[​](#pg-clickhouse-extension "Direct link to Query ClickHouse from Postgres")

The `pg_clickhouse` extension lets you query ClickHouse data directly from Postgres using standard SQL. This means your application can use Postgres as a unified query layer for both transactional and analytical data. See the [full documentation](/docs/integrations/pg_clickhouse) for details.

Enable the extension:

```
CREATE EXTENSION pg_clickhouse;
```

Then, create a foreign server connection to ClickHouse. Use the `http` driver with port `8443` for secure connections:

```
CREATE SERVER ch FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'http', host '<clickhouse_cloud_host>', dbname '<database_name>', port '8443');
```

Replace `<clickhouse_cloud_host>` with your ClickHouse hostname and `<database_name>` with the database you selected during replication setup. You can find the hostname in your ClickHouse service by clicking **Connect** in the sidebar.

Now, we map the Postgres user to the ClickHouse service's credentials:

```
CREATE USER MAPPING FOR CURRENT_USER SERVER ch 
OPTIONS (user 'default', password '<clickhouse_password>');
```

Now import the ClickHouse tables into a Postgres schema:

```
CREATE SCHEMA organization;
IMPORT FOREIGN SCHEMA "<database_name>" FROM SERVER ch INTO organization;
```

Replace `<database_name>` with the same database name you used when creating the server.

You can now see all the ClickHouse tables in your Postgres client:

```
\det+ organization.*
```

### See your analytics in action[​](#analytics-after-integration "Direct link to See your analytics in action")

Let's check back on the integration page. You should see that the initial replication is complete. Click on the integration name to view details.

Click on the service name to open the ClickHouse console and see your replicated tables.

### Compare Postgres vs ClickHouse performance[​](#performance-comparison "Direct link to Compare Postgres vs ClickHouse performance")

Now let's run some analytical queries and compare performance between Postgres and ClickHouse. Note that replicated tables use the naming convention `public_<table_name>`.

**Query 1: Top users by activity**

This query finds the most active users with multiple aggregations:

```
-- Via ClickHouse
SELECT 
    user_id,
    COUNT(*) as total_events,
    COUNT(DISTINCT event_type) as unique_event_types,
    SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) as purchases,
    MIN(event_timestamp) as first_event,
    MAX(event_timestamp) as last_event
FROM organization.public_events
GROUP BY user_id
ORDER BY total_events DESC
LIMIT 10;
```

```
 user_id | total_events | unique_event_types | purchases |        first_event         |         last_event         
---------+--------------+--------------------+-----------+----------------------------+----------------------------
       1 |        31439 |                  5 |      3551 | 2025-01-22 22:40:45.612281 | 2026-01-21 22:40:45.612281
       2 |        13235 |                  4 |      1492 | 2025-01-22 22:40:45.612281 | 2026-01-21 22:40:45.612281
...
(10 rows)

Time: 163.898 ms   -- ClickHouse
Time: 554.621 ms   -- Same query on Postgres
```

**Query 2: User engagement by country and platform**

This query joins events with users and computes engagement metrics:

```
-- Via ClickHouse
SELECT 
    u.country,
    u.platform,
    COUNT(DISTINCT e.user_id) as users,
    COUNT(*) as total_events,
    ROUND(COUNT(*)::numeric / COUNT(DISTINCT e.user_id), 2) as events_per_user,
    SUM(CASE WHEN e.event_type = 'purchase' THEN 1 ELSE 0 END) as purchases
FROM organization.public_events e
JOIN organization.public_users u ON e.user_id = u.user_id
GROUP BY u.country, u.platform
ORDER BY total_events DESC
LIMIT 10;
```

```
 country | platform | users | total_events | events_per_user | purchases 
---------+----------+-------+--------------+-----------------+-----------
 USA     | Android  |   115 |       109977 |             956 |     12388
 USA     | Web      |   108 |       105057 |             972 |     11847
 USA     | iOS      |    83 |        84594 |            1019 |      9565
 Germany | Android  |    85 |        77966 |             917 |      8852
 India   | Android  |    80 |        68095 |             851 |      7724
...
(10 rows)

Time: 170.353 ms   -- ClickHouse
Time: 1245.560 ms  -- Same query on Postgres
```

**Performance comparison:**

|  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Query Postgres (NVMe) ClickHouse (via pg\_clickhouse) Speedup|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | Top users (5 aggregations) 555 ms 164 ms 3.4x|  |  |  |  | | --- | --- | --- | --- | | User engagement (JOIN + aggregations) 1,246 ms 170 ms 7.3x | | | | | | | | | | | |

When to use ClickHouse

Even on this 1M row dataset, ClickHouse delivers 3-7x faster performance on complex analytical queries with JOINs and multiple aggregations. The difference becomes even more dramatic at larger scales (100M+ rows), where ClickHouse's columnar storage and vectorized execution can deliver 10-100x speedups.

Query times vary based on instance size, network latency between services, data characteristics, and current load.

## Cleanup[​](#cleanup "Direct link to Cleanup")

To delete the resources created in this quickstart:

1. First, delete the ClickPipe integration from the ClickHouse service
2. Then, delete the Managed Postgres instance from the Cloud Console

- [Part 1: Get Started with NVMe Postgres](#part-1)
  - [Create a database](#create-postgres-database)- [Connect to your database](#connect)- [Experience NVMe performance](#nvme-performance)- [Run queries on your data](#run-queries)- [Part 2: Add Real-Time Analytics with ClickHouse](#part-2)
    - [Setup ClickHouse integration](#setup-integrate-clickhouse)- [Query ClickHouse from Postgres](#pg-clickhouse-extension)- [See your analytics in action](#analytics-after-integration)- [Compare Postgres vs ClickHouse performance](#performance-comparison)- [Cleanup](#cleanup)

Was this page helpful?