Appendix

See [here](/docs/cloud/reference/shared-merge-tree#consistency) for further details on enabling these settings.

> Use of sequential consistency will place a greater load on ClickHouse Keeper. The result can
> mean slower inserts and reads. SharedMergeTree, used in ClickHouse Cloud as the main table engine, sequential consistency [incurs less overhead and will scale better](/docs/cloud/reference/shared-merge-tree#consistency). OSS you should use this approach cautiously and measure Keeper load.

## Transactional (ACID) support[​](#transactional-acid-support "Direct link to Transactional (ACID) support")

Users migrating from PostgreSQL may be used to its robust support for ACID (Atomicity, Consistency, Isolation, Durability) properties, making it a reliable choice for transactional databases. Atomicity in PostgreSQL ensures that each transaction is treated as a single unit, which either completely succeeds or is entirely rolled back, preventing partial updates. Consistency is maintained by enforcing constraints, triggers, and rules that guarantee that all database transactions lead to a valid state. Isolation levels, from Read Committed to Serializable, are supported in PostgreSQL, allowing fine-tuned control over the visibility of changes made by concurrent transactions. Lastly, Durability is achieved through write-ahead logging (WAL), ensuring that once a transaction is committed, it remains so even in the event of a system failure.

These properties are common for OLTP databases that act as a source of truth.

While powerful, this comes with inherent limitations and makes PB scales challenging. ClickHouse compromises on these properties in order to provide fast analytical queries at scale while sustaining high write throughput.

ClickHouse provides ACID properties under [limited configurations](/docs/guides/developer/transactional) - most simply when using a non-replicated instance of the MergeTree table engine with one partition. You shouldn't expect these properties outside of these cases and ensure these aren't a requirement.

## Compression[​](#compression "Direct link to Compression")

ClickHouse's column-oriented storage means compression will often be significantly better when compared to Postgres. The following illustrated when comparing the storage requirement for all Stack Overflow tables in both databases:

```
SELECT
    schemaname,
    tablename,
    pg_total_relation_size(schemaname || '.' || tablename) AS total_size_bytes,
    pg_total_relation_size(schemaname || '.' || tablename) / (1024 * 1024 * 1024) AS total_size_gb
FROM
    pg_tables s
WHERE
    schemaname = 'public';
```

```
SELECT
        `table`,
        formatReadableSize(sum(data_compressed_bytes)) AS compressed_size
FROM system.parts
WHERE (database = 'stackoverflow') AND active
GROUP BY `table`
```

```
┌─table───────┬─compressed_size─┐
│ posts       │ 25.17 GiB       │
│ users       │ 846.57 MiB      │
│ badges      │ 513.13 MiB      │
│ comments    │ 7.11 GiB        │
│ votes       │ 1.28 GiB        │
│ posthistory │ 40.44 GiB       │
│ postlinks   │ 79.22 MiB       │
└─────────────┴─────────────────┘
```

Further details on optimizing and measuring compression can be found [here](/docs/data-compression/compression-in-clickhouse).

## Data type mappings[​](#data-type-mappings "Direct link to Data type mappings")

The following table shows the equivalent ClickHouse data types for Postgres.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Postgres Data Type ClickHouse Type|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `DATE` [Date](/docs/sql-reference/data-types/date)| `TIMESTAMP` [DateTime](/docs/sql-reference/data-types/datetime)| `REAL` [Float32](/docs/sql-reference/data-types/float)| `DOUBLE` [Float64](/docs/sql-reference/data-types/float)| `DECIMAL, NUMERIC` [Decimal](/docs/sql-reference/data-types/decimal)| `SMALLINT` [Int16](/docs/sql-reference/data-types/int-uint)| `INTEGER` [Int32](/docs/sql-reference/data-types/int-uint)| `BIGINT` [Int64](/docs/sql-reference/data-types/int-uint)| `SERIAL` [UInt32](/docs/sql-reference/data-types/int-uint)| `BIGSERIAL` [UInt64](/docs/sql-reference/data-types/int-uint)| `TEXT, CHAR, BPCHAR` [String](/docs/sql-reference/data-types/string)| `INTEGER` Nullable([Int32](/docs/sql-reference/data-types/int-uint))| `ARRAY` [Array](/docs/sql-reference/data-types/array)| `FLOAT4` [Float32](/docs/sql-reference/data-types/float)| `BOOLEAN` [Bool](/docs/sql-reference/data-types/boolean)| `VARCHAR` [String](/docs/sql-reference/data-types/string)| `BIT` [String](/docs/sql-reference/data-types/string)| `BIT VARYING` [String](/docs/sql-reference/data-types/string)| `BYTEA` [String](/docs/sql-reference/data-types/string)| `NUMERIC` [Decimal](/docs/sql-reference/data-types/decimal)| `GEOGRAPHY` [Point](/docs/sql-reference/data-types/geo#point), [Ring](/docs/sql-reference/data-types/geo#ring), [Polygon](/docs/sql-reference/data-types/geo#polygon), [MultiPolygon](/docs/sql-reference/data-types/geo#multipolygon)| `GEOMETRY` [Point](/docs/sql-reference/data-types/geo#point), [Ring](/docs/sql-reference/data-types/geo#ring), [Polygon](/docs/sql-reference/data-types/geo#polygon), [MultiPolygon](/docs/sql-reference/data-types/geo#multipolygon)| `INET` [IPv4](/docs/sql-reference/data-types/ipv4), [IPv6](/docs/sql-reference/data-types/ipv6)| `MACADDR` [String](/docs/sql-reference/data-types/string)| `CIDR` [String](/docs/sql-reference/data-types/string)| `HSTORE` [Map(K, V)](/docs/sql-reference/data-types/map), [Map](/docs/sql-reference/data-types/map)(K,[Variant](/docs/sql-reference/data-types/variant))| `UUID` [UUID](/docs/sql-reference/data-types/uuid)| `ARRAY<T>` [ARRAY(T)](/docs/sql-reference/data-types/array)| `JSON` [String](/docs/sql-reference/data-types/string), [Variant](/docs/sql-reference/data-types/variant), [Nested](/docs/sql-reference/data-types/nested-data-structures/nested#nestedname1-type1-name2-type2-), [Tuple](/docs/sql-reference/data-types/tuple)| `JSONB` [String](/docs/sql-reference/data-types/string) | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

- [Postgres vs ClickHouse: Equivalent and different concepts](#postgres-vs-clickhouse-equivalent-and-different-concepts)
  - [Shards vs replicas](#shards-vs-replicas)- [Eventual consistency](#eventual-consistency)- [User implications](#user-implications)- [Recommendations](#recommendations)- [Consistent routing](#consistent-routing)- [ClickHouse Cloud](#clickhouse-cloud)- [ClickHouse OSS](#clickhouse-oss)- [Sequential consistency](#sequential-consistency)- [Transactional (ACID) support](#transactional-acid-support)- [Compression](#compression)- [Data type mappings](#data-type-mappings)

Was this page helpful?