Migrate PostgreSQL queries to ClickHouse

What a PostgreSQL to ClickHouse migration changes in your queries

Moving analytical queries from PostgreSQL to self-managed ClickHouse is mostly a redesign of tables and queries, not a line-by-line port. The work centers on ordering keys instead of indexes, denormalized data for wide reporting reads, and append-style handling of rows that change after they are written. Teams that expect their existing SQL to run unchanged usually find that the reporting queries slow down or return the wrong shape of result until the tables are rebuilt around how ClickHouse stores and reads data. The effort depends on three things: how many dashboards and reports must move, how normalized the current schema is, and how much data must be checked against the source before anyone trusts the new results. ClickHouse publishes a three-part PostgreSQL migration guide, and its part on rewriting queries is the documented starting point for the query-level changes.

Before estimating the work, inventory the PostgreSQL queries that drive the slow dashboards and group them by pattern: simple aggregations, multi-table joins, window functions and queries that depend on updated rows. Ask which of these must stay in PostgreSQL, since transactional reads and small lookups often remain there, and which move to ClickHouse. Confirm with your own team which tables are append-only and which receive updates or deletes, because that decides whether a ReplacingMergeTree table, a materialized view or a periodic rebuild fits each one. Also confirm the data types and time zones the reports depend on, because implicit casts and type differences are a common source of silent result changes.

ClickHouse server is open source under the Apache License 2.0, so the software carries no license fee, and the migration runs in cloud accounts, data centers or restricted networks that you control. That also means the people doing the work must be sized into your plan, whether they are your engineers, a partner or both. Plan for a validation phase where PostgreSQL and ClickHouse answer the same questions over the same data window, and keep that comparison as evidence for the cutover decision. Agree with any partner in writing which queries, tables and reconciliation checks are in scope, because those choices, more than the SQL dialect itself, determine how long the migration takes.

Which PostgreSQL features map cleanly to ClickHouse and which need redesign

This table pairs common PostgreSQL concepts with their ClickHouse counterparts and notes how each one moves across. Rows marked as redesign need a new data model or query pattern rather than a direct translation. Confirm function and type support against the ClickHouse version you plan to run before you commit to a design.

PostgreSQL concept	ClickHouse counterpart	Migration note
B-tree indexes	ORDER BY sorting key	Redesign. The sorting key sets physical row order and drives pruning, so choose it from your most common filters and time ranges, not from a copy of your primary key.
UPDATE and DELETE	ReplacingMergeTree and mutations	Redesign. Updates are usually modeled as new row versions merged later, and deletes need a marker or a mutation. Expect results to change until merges complete unless you query with FINAL.
Normalized joins	Denormalized wide tables	Redesign. Flatten frequently joined dimensions into the fact table where the data allows, and keep joins for small, rarely changing lookups.
Materialized views	Incremental materialized views	Redesign. A ClickHouse materialized view runs on inserts into its source table and writes to a target table, so it does not refresh on a schedule like a PostgreSQL view.
Serial and UUID keys	Surrogate keys and UUID columns	Translate with care. ClickHouse has no sequence-driven primary key enforcement, so decide whether keys are generated upstream, and confirm UUID generation functions for your version.
Transactional multi-statement writes	Per-batch inserts and eventual consistency	Redesign. Multi-table atomic writes are not a default ClickHouse pattern, so design around idempotent batches and reconcile counts after loads.
Standard SQL functions and types	ClickHouse functions and data types	Translate with verification. Many functions differ in name or behavior, so confirm each one used by your queries against the version in your pre-production environment.

Documented import paths for moving PostgreSQL data

Each path moves PostgreSQL data into ClickHouse in a different way, and each suits a different stage of the work. Query rewrites can only be tested against loaded data, so pick the path that matches your initial copy, your need for ongoing changes and your limits on load against the source.

Initial data copy from the migration guide

The data migration part of ClickHouse's PostgreSQL migration guide covers the bulk copy of source tables into ClickHouse. This copy is the starting point, because every rewritten query and ordering key choice has to be tested against loaded rows. Check how PostgreSQL types such as numeric, timestamp and array columns map to ClickHouse types, since rewritten queries depend on those types. Confirm who owns the export, where the files or streams land, and whether the copy must be repeated at cutover.

PostgreSQL integration for on-demand reads

The PostgreSQL integration lets a ClickHouse server read source tables directly, which suits side-by-side validation queries against the same rows. It reads on demand and does not track changes, so a table read today can differ from the same table read tomorrow. Each query also adds load to the source. Confirm the network path from ClickHouse nodes to the PostgreSQL port, a read-only role with the narrowest grants, and the hours when validation scans may run.

PeerDB change data capture

PeerDB takes an initial snapshot of PostgreSQL tables and then streams changes into ClickHouse, which is how updates and deletes reach the ReplacingMergeTree targets described on this page. The design, covered on its own page, depends on logical replication slots. A stalled consumer keeps WAL on the source, which can fill the primary's disk. Confirm the server's logical replication settings, who approves slot creation, and how slot lag is monitored and alerted on.

clickhouse-local on an extraction host

clickhouse-local runs on a separate host, reads rows from PostgreSQL or exported files, and pushes them into the cluster. The cluster then needs no route to the source network. It is a batch tool, so it does not follow changes or resume a half-finished export on its own. Confirm the host's network paths to both sides, where credentials are stored, and an agreed window for large scans that could slow production queries.

Hot standby for large source scans

For large copies and validation counts, PostgreSQL hot standby servers can answer read-only queries, which moves scan load off the primary. Standby data trails the primary by the replication delay, so counts taken there can differ from live figures. Record the replay position with each extract. Confirm whether a standby exists, what replication delay the business accepts, and how many sessions the copy may open, with connection pooling through PgBouncer where needed.

How ordering keys replace PostgreSQL indexes

ClickHouse does not find rows through a B-tree index that points at individual tuples the way PostgreSQL does. The ORDER BY key of a MergeTree table decides how each data part is physically sorted, and ClickHouse keeps a sparse primary index over that sort order, so a query that filters on the leading key columns can skip most granules without reading them. When you port a PostgreSQL analytical query, the first design task is therefore not to recreate each index, but to decide which columns the most important queries filter and group by, and to order the table so those columns come first. Write down the top ten to twenty queries by business importance and frequency, list their WHERE and GROUP BY columns, and rank the columns by how often they appear. That ranking, not the table's primary key in PostgreSQL, is the starting point for the ORDER BY clause.

Leading columns should have low to moderate cardinality and appear in equality or range filters across most queries. A high-cardinality column such as a user ID or request ID placed first spreads sorted data across many granules, so the sparse index prunes little and the leading column's value distribution makes compression and merges less effective. A column that is rarely filtered, even if it is selective, adds cost to every insert and rarely saves a read when it is first. A common pattern is a time or tenant column followed by a small number of dimension columns, but the right order depends on your own query mix. Test two or three candidate orderings against a copy of representative data and compare EXPLAIN indexes = 1 output and bytes read before committing. Your team should confirm the exact behavior for the ClickHouse version you run.

Skip indexes and projections are later steps, not substitutes for a poor ordering key. A skip index helps when a filter on a non-leading column is selective within the granules the key already selects, and a projection stores an alternative sort order or pre-aggregation for a second query pattern. Add either only after measurements show a specific slow query that the ordering key cannot serve. Detailed tuning of these structures, along with partitions and inserts, belongs to the ClickHouse performance tuning and schema design group, which is where this section's decisions get worked out in full.

Rewriting JOINs for ClickHouse by query situation

In PostgreSQL, a fact table often references a small dimension such as customers, products or regions, and every report joins to it. In ClickHouse, the usual first move is to copy the few attributes that analysts filter and group by into the fact table at load time, so the query scans one table. When the attributes change often, a dictionary can serve current values by key without a join. The ClickHouse PostgreSQL data modeling guide covers the modeling trade-offs in more detail.

Denormalize stable attributes: Copy columns such as region or product category into fact rows during ingestion when they rarely change and reports need them as they were at event time.
Use a dictionary for changing attributes: Dictionaries serve key lookups from memory and suit dimensions refreshed on a schedule. Confirm refresh behavior and memory use for your version and dimension size.
Place the smaller table on the right: If a join remains, put the smaller table on the right side and confirm the join algorithm and memory limits in your version's settings.
Accept storage and freshness costs: Denormalized values repeat across every fact row, which adds storage before compression. A change to a dimension value does not reach history unless you backfill the affected rows.

Test a period in which dimension values changed, and confirm that denormalized results match what PostgreSQL returns for the same point in time.

Handling updated rows with ReplacingMergeTree and what it does not do

ClickHouse is built for inserts, so PostgreSQL UPDATE statements have no direct equivalent. The usual pattern is to insert a new version of the row and let a ReplacingMergeTree table keep only the latest one. You define the sorting key as the business identity of the row, add a version column such as an updated_at timestamp or a monotonically increasing sequence number, and ClickHouse keeps the row with the highest version when background merges combine parts with the same sorting key. Until those merges run, duplicates are physically present and visible to any query that reads the table. Plan for that window explicitly. Confirm with your own tests how long merges take on your data volumes, and do not treat the table as deduplicated at query time by default.

Queries have to account for those duplicates. Using FINAL on the table returns deduplicated rows at read time, but it adds work on every read, so benchmark it on representative queries before adopting it as the default. For aggregations, an alternative is to group by the sorting key and select the argument of the maximum version, which gives the same answer without forcing a full merge pass. Deletes need a deliberate design too: a ReplacingMergeTree can carry an is_deleted flag, and the reporting queries must filter on it. Physical removal then depends on your merge and cleanup strategy, which you should document and test. Note also that a PostgreSQL transaction that updates several related rows atomically has no single equivalent here, because ClickHouse does not provide multi-statement transactions for this pattern. Readers should design for eventual consistency across tables, or keep related facts in one wide row. For how change data capture feeds these tables from PostgreSQL, including initial snapshots, replication slots and delete handling, see the PeerDB sibling page on replicating PostgreSQL to ClickHouse with PeerDB CDC.

The migration sequence from query inventory to decommissioning PostgreSQL reports

This sequence moves each PostgreSQL analytical query onto ClickHouse with proven results, runs both systems in parallel, and retires the PostgreSQL copy only after restore tests pass.

Inventory every analytical query and owner
Collect every query, report, dashboard definition and scheduled job that reads analytical data from PostgreSQL, including the SQL text, run frequency, latency expectations and the team that owns each one. Pull this from application code, pg_stat_statements where enabled, BI tool metadata and cron files, since reports often live outside the main codebase. Done when each item has a named owner and a status, and the list has been reviewed by the people who receive the output. Ask your team to confirm which items are truly analytical and which are transactional, because only the analytical set moves.
Classify queries by rewrite effort
Sort each inventoried query into three groups: those that map directly to ClickHouse SQL, those that need denormalized target tables to avoid expensive joins, and those that need a full rewrite because they depend on PostgreSQL-specific features, row-level updates or index-driven access patterns. The ClickHouse PostgreSQL migration guide covers rewriting queries and data modeling in detail, so use it as the reference for each group. Done when every query sits in exactly one group, the rewrite-heavy group has an estimate agreed by its owners, and the denormalization candidates are linked to the tables they would replace.
Design target tables for ordering keys and updates
For each target table, choose an ORDER BY key from the filters and grouping columns the queries actually use, not from the PostgreSQL primary key or indexes. Decide the update pattern for each source table: append-only events can use plain MergeTree, while rows that PostgreSQL updates in place usually need ReplacingMergeTree with a version column, and reads may need FINAL or argMax-style logic. Document partitioning and deletion handling. Done when a reviewed schema file exists for every target table, with the ordering key, engine and update rule written next to the queries that justify them.
Load a representative dataset
Load a copy of real data into a pre-production ClickHouse cluster using the import path chosen for your environment, such as a batch export, the PostgreSQL table functions, or a change data capture tool. Include enough history that the queries exercise the same date ranges, cardinality and skew as production, because a small sample can hide slow ordering keys and wrong join results. Done when row counts per table match the source at the load cut-off point, and the load job can be rerun to produce the same result.
Rewrite queries and compare results
Port each query to ClickHouse SQL and run it alongside the original PostgreSQL query against the same logical data. Compare row counts, aggregate totals, distinct counts, null handling, time zone and rounding behavior, and sort order where it matters. Record every difference and its cause, and treat any unexplained mismatch as a blocker. Done when each query has a saved comparison result that its owner has signed off, and the validation checklist for the page has been run against every query in the rewrite group.
Run both systems in parallel
Keep PostgreSQL and ClickHouse fed with the same data for an agreed period, and compare outputs on a schedule that reflects real reporting cycles, including month-end and any periodic jobs. Track mismatches, latency and load on both systems, and fix the cause in the rewrite or the load path rather than masking it. Done when the agreed period has passed with no unexplained differences, and the owners have confirmed that the ClickHouse results are fit for their decisions.
Cut over dashboards and applications
Point each dashboard, API and scheduled report to ClickHouse one group at a time, starting with the lowest-risk consumers. Keep the PostgreSQL query path available as a documented fallback until the cutover for that consumer is stable. Confirm access controls in ClickHouse, using the access control workflow with users, roles and row policies, so that each consumer sees only the data it saw before. Done when every consumer reads from ClickHouse, the fallback has been tested, and the owners confirm that the outputs match.
Retire the PostgreSQL analytical copy after restore tests
Before removing the PostgreSQL analytical tables, make sure the ClickHouse data is protected: run BACKUP to a separate storage target, restore it to a spare cluster, and check row counts and sample queries against the expected results. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed, and recommends regular restore tests on spare clusters. Replication does not protect against human error or corruption, so the restore test is the evidence that retirement is safe. Done when a restore test has passed, the runbook is updated, and the decommissioning decision is recorded with its owner.

A checklist for proving ClickHouse results match PostgreSQL

Rewritten queries and schemas only earn trust when they return the same answers as the PostgreSQL originals. The six checks below run in order, from load completeness to performance, and each one leaves evidence that a named owner signs off before traffic moves. DeployOpen can prepare and run these checks, but compliance evidence is judged by your auditors.

Row counts per table and partition

After each load, compare row counts per table and per time partition between PostgreSQL and ClickHouse. Count from the same cut-off timestamp, and account for rows still arriving on the source during the copy. Keep the query text, the timestamp, and both result sets as evidence. The data owner signs off that every partition matches or that each gap has a documented reason. A mismatch in one month usually points to a load window or time zone problem, so check those first.

Aggregate checksums by dimension

Compare sums, averages and distinct counts for key measures, grouped by the main business dimensions such as account, region and product. Run the same grouping on both systems and diff the output row by row, not just the grand total, because offsetting errors can hide in a total. Keep the diff report and the grouping definitions. The finance or analytics owner of each measure signs off, since they know which totals must match exactly and which may differ by rounding.

Sampled row-level edge cases

Pull a sample of rows that exercise edge cases: NULL values in filtered columns, deleted and updated records, time zone boundaries, and values where rounding changes the result. Compare each sampled row across both systems and record the outcome. Keep the sample selection logic so the sample can be regenerated. The domain owner signs off on the edge case list, because only they know which business rules a rewritten filter or JOIN might break.

Duplicate handling after merges

Rows in a ReplacingMergeTree table are deduplicated only when background merges run or when a query uses FINAL, so counts can differ depending on timing. Compare query results taken with FINAL against the PostgreSQL source of truth, and confirm that the version column picks the latest row. Keep the query results and the merge timing observed. The data platform owner signs off, and should confirm the chosen approach matches how the business reads current state.

Latency and resource use under load

Run the rewritten queries at the concurrency you expect in production, and record latency percentiles, memory use and CPU alongside the PostgreSQL baseline. Include materialized view refresh cost in the same run, since it competes for the same resources. Keep the test plan, the hardware profile, and the results. The platform owner and the people who run the reports sign off that response times meet their needs. Ask your team which queries users run most often, and test those first.

Evidence and sign-off record

Gather the outputs from the five checks above into one record with the version of the schema, the load date, the query versions and the named approvers. Store it with the migration change record so it can be reproduced later. DeployOpen can prepare and run these checks, but compliance evidence is judged by your auditors, who decide whether it is sufficient. Confirm early which format your auditors expect.

What drives the cost and duration of a PostgreSQL to ClickHouse rewrite

This table lists the factors that change how much work a PostgreSQL to ClickHouse rewrite takes and what a quote should cover. No figures are given here because a quote follows from a written scope. Read each row as a question to answer before scoping, since most of the effort sits in query rewrites, validation and the parallel run.

Driver	Effect on effort and duration	What to ask for in a quote
Number and complexity of analytical queries	Each query with window functions, correlated subqueries or multi-step aggregations needs its own rewrite and result comparison. Simple aggregations are mostly mechanical.	A query inventory with complexity tiers and a named count of queries in each tier
Tables that need denormalization	Every join path the ClickHouse model flattens adds schema design, backfill logic and testing. Wide fact tables take longer to shape than narrow ones.	A list of source tables, the target table for each, and which joins become precomputed columns
Dashboards and applications reading the output	Each consumer that depends on exact column names, types or timing adds regression checks and coordination with its owners.	An inventory of consumers, their query patterns and who signs off on changes
Update and delete patterns in the source	Rows updated in place need ReplacingMergeTree or a similar strategy, with tests for duplicates and deletes that arrive out of order. Append-only sources are simpler.	A description of how often rows change or are deleted, and the expected behavior of reads during merges
Volume of historical data to load	Larger histories lengthen the initial load, backfill runs and reconciliation. Sizing also affects ordering key and partition choices.	The row counts and date ranges to migrate, with the load method and where its checkpoints are kept
Number of validation rounds	Result comparison against PostgreSQL usually repeats after each fix. Each round covers sample queries, row counts, aggregates and edge cases such as nulls and time zones.	The number of validation rounds included in the fixed scope and the acceptance criteria for each
Parallel run and its length	Running both systems side by side means two data pipelines, two sets of queries and a comparison process. The run length is set by the business, not by the migration itself.	Whether a parallel run is included, which systems run in it, and the exit criteria for stopping it
Access to source-side business logic owners	Unclear rules for status codes, currency, soft deletes or effective dates cause rework. Delays often come from waiting for answers, not from writing code.	Named source-side owners, their availability windows and a decision log for business rules
One-time versus recurring work	Schema design, query rewrites, backfills and validation are one-time. Ongoing pipeline monitoring, schema changes and version upgrades recur after cutover.	Separate line items for one-time build work and any recurring operations or retainer support

What usually goes wrong in PostgreSQL to ClickHouse rewrites and how to prevent it

Most failed PostgreSQL to ClickHouse rewrites break in predictable places: the table design, the deduplication model, the joins, the hidden rules and the validation. Each card below names one of these failures and the step in the migration sequence that prevents it.

Ordering keys chosen from intuition

Teams often copy the PostgreSQL primary key into the ClickHouse ordering key, or pick columns because they look important. ClickHouse prunes data by the ordering key, so the key should follow the filters and groupings your dashboards and reports actually use. Before loading data, collect the top query shapes from PostgreSQL logs and test the proposed key against them on a representative sample. Confirm the choice with a query plan review, not with a guess.

Stale rows before merges run

ReplacingMergeTree removes duplicate rows only when background merges run, so a query can read both the old and the new version of an updated row. Teams that treat the table as already deduplicated return inflated counts and wrong latest-state values. The fix is to query with FINAL where correctness matters, or to design reads around a version column and an explicit latest-row filter. Test this with updates and deletes in a staging copy, and compare counts before and after a forced merge.

Joins that fail at production size

A JOIN that returns in seconds against a few thousand test rows can exhaust memory or slow sharply against billions of rows. Right-side table size, join algorithm choice and denormalization decisions all change the result. Rewrite the most expensive PostgreSQL joins first, often by denormalizing dimension attributes into the fact table or by using a materialized view, and load a production-scale sample to measure memory use and run time before cutover.

Rules hidden in triggers and application code

PostgreSQL triggers, check constraints, default values and application-level validation often enforce business rules that never appear in the schema dump. When these move to ClickHouse, the rules disappear silently, and reports drift from the source. Inventory every trigger, function, constraint and ORM hook before the migration, then decide for each whether it moves into an insert pipeline, a materialized view, a query filter or an upstream application change.

Validation that only compares totals

Matching row counts or a grand total between PostgreSQL and ClickHouse can hide wrong values, missing nulls, shifted timestamps and wrong joins that cancel out. Validation should compare per-day, per-key and per-report results, sample individual rows across the full history, and check the edge cases such as deleted records and late-arriving events. Keep a written checklist of reports and sign-off owners so each rewritten query is accepted against the business output it replaces.

Backups never restored in a test

A ClickHouse backup configuration that has never been restored is an unverified assumption. Replication protects against node loss but not against an accidental delete or a bad migration step, so the rewritten schema and data need their own restore path. ClickHouse documentation warns that a backup never test-restored will probably not restore properly when needed. Schedule a restore to a spare cluster before cutover, run the validation checklist on the restored data, and repeat the test on a regular cadence.

Questions teams ask before rewriting PostgreSQL queries for ClickHouse

How much does a PostgreSQL to ClickHouse query rewrite cost?

The cost depends on how many queries and tables move, how far the data model changes, and how much validation the business expects before cutover. No public figure applies to every workload, so a quote should follow a written scope. The main components are analysis of the existing query estate, schema design for ordering keys and denormalized tables, rewriting of joins and aggregations, building materialized views or ReplacingMergeTree target tables for updated rows, and result validation against PostgreSQL. Costs recur only where the team keeps changing queries or runs the platform. Ask any provider to split the quote into inventory, design, rewrite, validation and handover, and to state which items assume your staff are available. Queries that depend on PostgreSQL-specific features, such as complex stored procedures or triggers, usually raise the cost the most.

How long does it take to rewrite PostgreSQL queries for ClickHouse?

Duration is driven by the number of queries in scope, how many of them need a changed data model, and how thorough the result validation must be. A small set of dashboards over one fact table takes far less time than a reporting estate spanning many schemas and business owners. Teams usually lose time in three places: agreeing which results count as correct, waiting for source owners to explain business rules, and finding that a query needs a different table layout than the one planned. Ask for a timeline built from a query inventory, with validation gates for each query group and a clear point where parallel running ends. No duration in this answer is a promise, and any timeline should be confirmed against your own query count and sign-off process.

Can our own team rewrite PostgreSQL queries for ClickHouse without a partner?

Yes, a capable internal team can do the work if it has ClickHouse experience and data modeling skills, and if it has time to run both systems in parallel. The hard parts are not syntax. They are choosing ordering keys that match real filter patterns, deciding when a ReplacingMergeTree table or a materialized view fits, and proving that aggregates match PostgreSQL output. Engineers who know PostgreSQL well still need to learn how ClickHouse merges parts and handles updates and deletes. A partner makes sense when the team lacks that experience, when deadlines are fixed, or when the same people must also run production. Whichever route you choose, write down who owns each query group after cutover.

What does our organization need to provide for the rewrite?

The work needs four inputs from your side before design starts. First, a query inventory: every analytical query, its frequency, its owner and the dashboard or report it feeds. Second, named source owners who can confirm business rules, such as how cancelled orders or corrected records should count. Third, a representative dataset, ideally a recent copy or a sample that includes skewed tenants, late-arriving rows and deletes, because these shape the data model. Fourth, a test ClickHouse cluster that matches the planned production layout closely enough to measure query behavior. Without these, the team will design against assumptions and find the gaps during validation. Confirm access arrangements for the PostgreSQL replica and the test environment early, since they often take longer than the rewrite itself.

Can PostgreSQL remain the system of record after we add ClickHouse?

Usually yes. Many teams keep PostgreSQL as the transactional system of record and feed ClickHouse for analytics, so application writes, constraints and transactions stay where they already work. The design must then answer how changes reach ClickHouse, how updated and deleted rows are represented, and how freshness is measured against what dashboards show. Change data capture, batch loads or scheduled exports each change the operational burden differently. Confirm with your application owners which data must be current within seconds and which can lag. Also confirm the PostgreSQL side still meets its own availability goals, since PostgreSQL high availability and failover are handled by your PostgreSQL design, not by ClickHouse.

Does ClickHouse run every PostgreSQL query unchanged?

No. ClickHouse uses its own SQL dialect, and many PostgreSQL features, functions, data types and transaction behaviors do not map one to one. Some queries need rewritten joins, different window or date functions, or changed handling of NULLs and types. ClickHouse also works best with denormalized, append-oriented tables, so a query that relies on many normalized joins may need a different layout. Confirm function and type support against the ClickHouse version you will run, because behavior changes between releases, and read the changelog before upgrading. Treat every rewritten query as new code: run it against the same source data in both systems and compare row counts, aggregates and edge cases such as empty groups and duplicate keys before any dashboard switches over.

How do we check that ClickHouse results match PostgreSQL?

Set a validation method before rewriting starts, so results are compared on the same snapshot and the same business rules. A practical checklist covers:

Row counts and key uniqueness per table, including after ReplacingMergeTree deduplication has run with FINAL or an equivalent approach.
Sums, counts and averages for each dashboard metric over agreed date ranges and tenants.
Edge cases such as late rows, deleted records, time zone boundaries and NULL handling.
Performance under realistic concurrency on the test cluster, not only single-query timings.

Record each comparison and the owner who signs it off. Keep PostgreSQL output as the reference until the team accepts the differences, and document any intentional difference so future readers do not treat it as a bug.

How DeployOpen works on this

DeployOpen scopes the rewrite from your real PostgreSQL queries, schemas and result sets, then builds the ClickHouse tables, query versions and validation harness in infrastructure you control. Engagements are fixed-scope, so the written scope fixes which queries, tables and reports are covered before work starts.

How we scope

Inventory the PostgreSQL queries, reports and dashboards that will move, with owners and expected result shapes.
Classify each table by access pattern, update frequency and delete behavior to decide ordering keys and ReplacingMergeTree use.
Record in the written scope which joins, materialized views and downstream consumers are included, and which stay on PostgreSQL.

How we build

Design ClickHouse table definitions, ordering keys and denormalized targets from the agreed query inventory.
Rewrite each in-scope query, documenting every change to joins, functions, null handling and date logic.
Run old and new queries against a pre-production copy with representative data, and keep the comparison scripts as deliverables.

How we hand over

Deliver the query catalog, schema definitions and a validation checklist covering row counts, aggregates and sampled rows.
Document known semantic differences, such as updated-row handling before merges complete, so analysts know what to expect.
Walk your engineers through the materialized views and update patterns so they can change the model without us.

How we support

Confirm with us whether post-handover support, query changes and upgrades will be covered by a retainer or a separate scope.
Plan re-validation whenever the source schema changes or when you move to a newer ClickHouse release, after reading its changelog.
Agree in the quote who owns the ongoing test runs, restore tests and monitoring after the engagement closes.