Quickstart for Managed Postgres

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/cloud/managed-postgres/quickstart.md)

[Private preview](https://clickhouse.com/cloud/postgres)

ClickHouse Managed Postgres is enterprise-grade Postgres backed by NVMe storage, delivering up to 10x faster performance for disk-bound workloads compared to network-attached storage like EBS. This quickstart is divided into two parts:

- **Part 1:** Get started with NVMe Postgres and experience its performance
- **Part 2:** Unlock real-time analytics by integrating with ClickHouse

Managed Postgres is currently available on AWS in several regions and is free during private preview.

**In this quickstart, you will:**

- Create a Managed Postgres instance with NVMe-powered performance
- Load 1 million sample events and see NVMe speed in action
- Run queries and experience low-latency performance
- Replicate data to ClickHouse for real-time analytics
- Query ClickHouse directly from Postgres using `pg_clickhouse`

## Part 1: Get Started with NVMe Postgres[​](#part-1 "Direct link to Part 1: Get Started with NVMe Postgres")

### Create a database[​](#create-postgres-database "Direct link to Create a database")

To create a new Managed Postgres service, click on the **New service** button in the service list of the Cloud Console. You should then be able to select Postgres as the database type.

Enter a name for your database instance and click on **Create service**. You will be taken to the overview page.

Your Managed Postgres instance will be provisioned and ready for use in 3-5 minutes.

### Connect to your database[​](#connect "Direct link to Connect to your database")

In the sidebar on the left, you will see a [**Connect** button](/docs/cloud/managed-postgres/connection). Click on it to view your connection details and connection strings in multiple formats.

Copy the `psql` connection string and connect to your database. You can also use any Postgres-compatible client such as DBeaver, or any application library.

### Experience NVMe performance[​](#nvme-performance "Direct link to Experience NVMe performance")

Let's see NVMe-powered performance in action. First, enable timing in psql to measure query execution:

```
\timing
```

Create two sample tables for events and users:

```
CREATE TABLE events (
   event_id SERIAL PRIMARY KEY,
   event_name VARCHAR(255) NOT NULL,
   event_type VARCHAR(100),
   event_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
   event_data JSONB,
   user_id INT,
   user_ip INET,
   is_active BOOLEAN DEFAULT TRUE,
   created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
   updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
   user_id SERIAL PRIMARY KEY,
   name VARCHAR(100),
   country VARCHAR(50),
   platform VARCHAR(50)
);
```

Now, insert 1 million events and watch the NVMe speed:

```
INSERT INTO events (event_name, event_type, event_timestamp, event_data, user_id, user_ip)
SELECT
   'Event ' || gs::text AS event_name,
   CASE
       WHEN random() < 0.5 THEN 'click'
       WHEN random() < 0.75 THEN 'view'
       WHEN random() < 0.9 THEN 'purchase'
       WHEN random() < 0.98 THEN 'signup'
       ELSE 'logout'
   END AS event_type,
   NOW() - INTERVAL '1 day' * (gs % 365) AS event_timestamp,
   jsonb_build_object('key', 'value' || gs::text, 'additional_info', 'info_' || (gs % 100)::text) AS event_data,
   GREATEST(1, LEAST(1000, FLOOR(POWER(random(), 2) * 1000) + 1)) AS user_id,
   ('192.168.1.' || ((gs % 254) + 1))::inet AS user_ip
FROM
   generate_series(1, 1000000) gs;
```

```
INSERT 0 1000000
Time: 3596.542 ms (00:03.597)
```

NVMe Performance

1 million rows with JSONB data inserted in under 4 seconds. On traditional cloud databases using network-attached storage like EBS, this same operation typically takes 2-3x longer due to network round-trip latency and IOPS throttling. NVMe storage eliminates these bottlenecks by keeping storage physically attached to the compute.

Performance varies based on instance size, current load, and data characteristics.

Insert 1,000 users:

```
INSERT INTO users (name, country, platform)
SELECT
    first_names[first_idx] || ' ' || last_names[last_idx] AS name,
    CASE
        WHEN random() < 0.25 THEN 'India'
        WHEN random() < 0.5 THEN 'USA'
        WHEN random() < 0.7 THEN 'Germany'
        WHEN random() < 0.85 THEN 'China'
        ELSE 'Other'
    END AS country,
    CASE
        WHEN random() < 0.2 THEN 'iOS'
        WHEN random() < 0.4 THEN 'Android'
        WHEN random() < 0.6 THEN 'Web'
        WHEN random() < 0.75 THEN 'Windows'
        WHEN random() < 0.9 THEN 'MacOS'
        ELSE 'Linux'
    END AS platform
FROM
    generate_series(1, 1000) AS seq
    CROSS JOIN LATERAL (
        SELECT
            array['Alice', 'Bob', 'Charlie', 'Diana', 'Eve', 'Frank', 'Grace', 'Hank', 'Ivy', 'Jack', 'Liam', 'Olivia', 'Noah', 'Emma', 'Sophia', 'Benjamin', 'Isabella', 'Lucas', 'Mia', 'Amelia', 'Aarav', 'Riya', 'Arjun', 'Ananya', 'Wei', 'Li', 'Huan', 'Mei', 'Hans', 'Klaus', 'Greta', 'Sofia'] AS first_names,
            array['Smith', 'Johnson', 'Williams', 'Brown', 'Jones', 'Garcia', 'Miller', 'Davis', 'Martinez', 'Taylor', 'Anderson', 'Thomas', 'Jackson', 'White', 'Harris', 'Martin', 'Thompson', 'Moore', 'Lee', 'Perez', 'Sharma', 'Patel', 'Gupta', 'Reddy', 'Zhang', 'Wang', 'Chen', 'Liu', 'Schmidt', 'Müller', 'Weber', 'Fischer'] AS last_names,
            1 + (seq % 32) AS first_idx,
            1 + ((seq / 32)::int % 32) AS last_idx
    ) AS names;
```

### Run queries on your data[​](#run-queries "Direct link to Run queries on your data")

Now let's run some queries to see how fast Postgres responds with NVMe storage.

**Aggregate 1 million events by type:**

```
SELECT event_type, COUNT(*) as count 
FROM events 
GROUP BY event_type 
ORDER BY count DESC;
```

```
 event_type | count  
------------+--------
 click      | 499523
 view       | 375644
 purchase   | 112473
 signup     |  12117
 logout     |    243
(5 rows)

Time: 114.883 ms
```

**Query with JSONB filtering and date range:**

```
SELECT COUNT(*) 
FROM events 
WHERE event_timestamp > NOW() - INTERVAL '30 days'
  AND event_data->>'additional_info' LIKE 'info_5%';
```

```
 count 
-------
  9042
(1 row)

Time: 109.294 ms
```

**Join events with users:**

```
SELECT u.country, COUNT(*) as events, AVG(LENGTH(e.event_data::text))::int as avg_json_size
FROM events e
JOIN users u ON e.user_id = u.user_id
GROUP BY u.country
ORDER BY events DESC;
```

```
 country | events | avg_json_size 
---------+--------+---------------
 USA     | 383748 |            52
 India   | 255990 |            52
 Germany | 223781 |            52
 China   | 127754 |            52
 Other   |   8727 |            52
(5 rows)

Time: 224.670 ms
```

Your Postgres is ready

At this point, you have a fully functional, high-performance Postgres database ready for your transactional workloads.

Continue to Part 2 to see how native ClickHouse integration can supercharge your analytics.

---

## Part 2: Add Real-Time Analytics with ClickHouse[​](#part-2 "Direct link to Part 2: Add Real-Time Analytics with ClickHouse")

While Postgres excels at transactional workloads (OLTP), ClickHouse is purpose-built for analytical queries (OLAP) on large datasets. By integrating the two, you get the best of both worlds:

- **Postgres** for your application's transactional data (inserts, updates, point lookups)
- **ClickHouse** for sub-second analytics on billions of rows

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