clickhouse-io

Use the MergeTree engine with a time-based partition key such as toYYYYMM(date) and an ORDER BY clause placing frequently filtered columns first. Choose the smallest appropriate data types and LowCardinality for repeated strings to improve compression and query speed.

Filter on indexed columns first, use ClickHouse-specific functions like quantile and uniq instead of generic equivalents, and pre-aggregate with AggregatingMergeTree or materialized views. Check system.query_log to identify queries exceeding duration thresholds.

ReplacingMergeTree deduplicates rows with the same sorting key during background merges, making it suitable for data arriving from multiple sources. Standard MergeTree keeps all inserted rows and is the default choice for append-only analytical data.

Yes, use an ETL pattern that extracts rows from PostgreSQL, transforms them into the target schema, and batch inserts into ClickHouse. For ongoing sync, listen to PostgreSQL notifications and forward changes as change data capture events.

ClickHouse is optimized for bulk writes, and each small insert creates a separate data part that triggers expensive background merges. Batch rows into a single INSERT statement or use streaming inserts for continuous ingestion.

Avoid ClickHouse for transactional workloads requiring frequent single-row updates or deletes, since it is designed for OLAP rather than OLTP. Also avoid SELECT *, the FINAL modifier, and heavy JOIN usage, which degrade analytical performance.