ClickHouse vs PostgreSQL

Quick verdict

ClickHouse describes itself as a real-time analytics database management system. It stores data by column and its main table engine family, MergeTree, is designed for high ingest rates and large data volumes. It is open source under the Apache License 2.0 and can be self-hosted, or used as ClickHouse Cloud, the managed service run by ClickHouse, Inc. The latest release in the ClickHouse changelog is 26.9 (September 2026).

PostgreSQL is an open source object-relational database server with full ACID transactions, multiversion concurrency control (MVCC), a wide range of index types, streaming and logical replication, and an extension system that lets third parties add data types, index methods and storage. It is released under the PostgreSQL License. postgresql.org lists 18.6 as the current release; PostgreSQL 19 is in beta.

Side by side

Key differences

MergeTree versus row storage

In ClickHouse, each INSERT creates a data part, sorted by the table's primary key, and background processes merge parts together. The primary key determines sort order and builds a sparse index over granules of 8,192 rows; ClickHouse's documentation states that it does not require a unique primary key, so rows with the same key can be inserted. PARTITION BY lets queries skip whole partitions, and TTL rules can delete or move data by age.

-- ClickHouse
CREATE TABLE events (
    event_date Date,
    user_id    UInt64,
    event_type LowCardinality(String),
    value      Float64
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, event_date, user_id);

-- PostgreSQL
CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_date date NOT NULL,
    user_id    bigint NOT NULL,
    event_type text NOT NULL,
    value      double precision
);
CREATE INDEX ON events (event_type, event_date);

PostgreSQL stores whole rows and enforces primary keys, unique constraints and foreign keys. That suits workloads that read and change individual records. In our view, the choice of ORDER BY key in ClickHouse plays a role similar to index design in PostgreSQL, and it is one of the main design decisions for a ClickHouse table.

The UPDATE and DELETE model

This is the biggest practical difference for anyone coming from PostgreSQL. ClickHouse offers several mechanisms, and its documentation explains when to use each:

• Lightweight DELETE (DELETE FROM ... WHERE) marks rows as deleted in a hidden _row_exists column; they disappear from query results and are physically removed during later merges. It works on the MergeTree family. The docs warn that deleting large volumes this way can slow down SELECT queries.
• Lightweight UPDATE (UPDATE ... SET ... WHERE) is documented as a beta feature. It writes patch parts containing only the changed rows and columns, which queries apply immediately. The table must have the enable_block_number_column and enable_block_offset_column settings, columns in the primary or partition key cannot be updated, and the docs describe it as designed for updating up to about 10% of a table.
• Mutations (ALTER TABLE ... UPDATE / DELETE) rewrite the affected data parts and suit larger, partition-aligned changes.
• Engine-based patterns such as ReplacingMergeTree (insert a new version of a row and deduplicate on merge) and CollapsingMergeTree model changes as inserts.

-- ClickHouse: table prepared for lightweight updates
CREATE TABLE orders (
    order_id UInt64,
    status   String,
    amount   Decimal(10,2)
) ENGINE = MergeTree
ORDER BY order_id
SETTINGS enable_block_number_column = 1, enable_block_offset_column = 1;

UPDATE orders SET status = 'shipped' WHERE order_id = 1001;  -- beta
DELETE FROM orders WHERE status = 'cancelled';

-- PostgreSQL: ordinary transactional changes
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1001;
DELETE FROM orders WHERE status = 'cancelled';
COMMIT;

PostgreSQL's UPDATE, DELETE and MERGE are ordinary row-level operations that take part in multi-statement transactions and can be rolled back. If your application changes individual rows frequently, that model is far simpler to work with.

Transactions and consistency

ClickHouse documents that an INSERT into a single partition of a single MergeTree table is atomic when the rows are inserted as one block: either all rows are confirmed or none are. Multi-statement transactions with commit and rollback are documented as experimental and are not supported in ClickHouse Cloud. Replication between replicas is asynchronous, so recently inserted data may not appear on every replica immediately.

PostgreSQL offers full multi-statement ACID transactions with Read Committed (the default), Repeatable Read and Serializable isolation, plus enforced constraints. For money, inventory, bookings or anything where a partial change is unacceptable, this is the deciding factor.

JOIN behaviour

ClickHouse supports all standard SQL JOIN types and adds ANY, ASOF, SEMI and ANTI joins; ASOF JOIN matches each row to the closest value rather than an exact one, which is useful for time-series data. By default ClickHouse uses a hash join that builds a hash table of the right-hand table in memory, and the join_algorithm setting can select other algorithms, including falling back to a merge join when memory limits are reached. The JOIN documentation also states that there is no optimisation of the order of execution of a JOIN in relation to other stages of the query, so how a query is written matters more than in PostgreSQL. Analytical schemas in ClickHouse are therefore often more denormalised.

-- ClickHouse: match each trade to the latest quote at or before it
SELECT t.symbol, t.ts, t.price, q.bid
FROM trades AS t
ASOF JOIN quotes AS q
  ON t.symbol = q.symbol AND t.ts >= q.ts;

PostgreSQL's cost-based planner chooses join order and algorithm (nested loop, hash or merge join) using table statistics, which suits normalised schemas with many joins. It has no ASOF JOIN; the same result needs a lateral subquery.

-- PostgreSQL equivalent with LATERAL
SELECT t.symbol, t.ts, t.price, q.bid
FROM trades AS t
CROSS JOIN LATERAL (
  SELECT bid FROM quotes
  WHERE quotes.symbol = t.symbol AND quotes.ts <= t.ts
  ORDER BY quotes.ts DESC
  LIMIT 1
) AS q;

When PostgreSQL extensions are enough

Before adding a second database, check whether a PostgreSQL extension covers your analytical needs:

• TimescaleDB, maintained by Tiger Data (the company formerly called Timescale), packages time-series analytics as a Postgres extension: hypertables partition data by time, a columnstore compresses older data, and continuous aggregates refresh pre-aggregated results in the background. Its repository is under two licences, Apache 2.0 and the Timescale License (TSL), so check which features fall under which licence before you rely on them.
• Citus turns PostgreSQL into a distributed database by sharding tables across a cluster of nodes and parallelising queries, and includes a columnar access method. It is licensed under AGPL-3.0; its repository lists Citus 14 with PostgreSQL 18 support.

In our view, these are a reasonable choice when you want one database, your team already knows PostgreSQL, and transactional and analytical data need to stay together. ClickHouse becomes the stronger candidate when analytics is the main workload, data volumes are large and growing, and the data is mostly append-only. ClickHouse also publishes pg_clickhouse, a Postgres extension that pushes queries down to ClickHouse, which comes with its managed Postgres service.

Pricing and licensing

ClickHouse open source is free under the Apache License 2.0. ClickHouse Cloud is billed pay-as-you-go for compute (per compute unit-hour) and storage (per TB-month), with rates that vary by plan, cloud provider (AWS, GCP or Azure) and region. Plans listed on clickhouse.com/pricing in October 2026 are Basic (up to 1 TB storage, single availability zone), Scale (unlimited storage, compute-storage separation, two or more availability zones) and Enterprise (adds SAML SSO, private regions and HIPAA and PCI compliance). As one example, the pricing page lists the Enterprise plan on AWS US East 1 at USD 0.39030 per compute unit-hour and USD 25.30 per TB per month of storage; use the vendor's calculator for other plans and regions. New accounts get a 30-day trial with USD 300 in credits and no credit card. Bring Your Own Cloud (BYOC) runs ClickHouse Cloud in your own AWS, GCP or Azure account on custom-quoted pricing.

ClickHouse also runs ClickHouse Managed Postgres, documented as a public beta on AWS and a private preview on GCP in October 2026, priced per instance-hour with compute and storage combined. We do not compare it in detail here because of its beta status.

PostgreSQL is free under the PostgreSQL License, with no paid edition from the PostgreSQL Global Development Group. Managed PostgreSQL and commercial support are priced by each provider.

Pricing checked on the vendors' official pages on 7 October 2026. Prices change; confirm before buying.

Where each one leads

Limitations

When to choose each

When neither is right

• You only need analytics on files or a single machine, with no server: an embedded engine is simpler; see DuckDB vs PostgreSQL and DuckDB vs SQLite.
• You are choosing a transactional relational server, not an analytics engine: see MySQL vs PostgreSQL.
• Your data is document-shaped and schemas change often: see MongoDB vs PostgreSQL.
• Your organisation already runs a cloud data warehouse for analytics; adding ClickHouse may duplicate it, so compare against what you have first.

Final recommendation

Frequently asked questions

Sources

• ClickHouse GitHub repository (licence)
• ClickHouse changelog
• ClickHouse: MergeTree table engine
• ClickHouse: Lightweight DELETE
• ClickHouse: Lightweight UPDATE
• ClickHouse: Updating data overview
• ClickHouse: Transactional (ACID) support
• ClickHouse: JOIN clause
• ClickHouse: Data replication
• ClickHouse Cloud pricing
• ClickHouse Managed Postgres overview
• PostgreSQL home page (current releases)
• PostgreSQL: Transaction isolation
• PostgreSQL License
• TimescaleDB repository
• Citus repository

Checked October 2026.

How we research comparisons: our editorial method.