Postgres Analytics Offload: pg_duckdb vs CDC to ClickHouse vs HTAP Extensions

Every Postgres-backed product eventually grows a reporting problem. The schema was designed for point lookups and short transactions, and then someone asks for a weekly cohort query that scans two hundred million rows, groups by six columns, and must finish before the dashboard times out. The query is not wrong. The storage layout is simply optimized for the opposite access pattern, and no amount of work_mem tuning changes that.

This record walks through the decision as an architecture decision record (ADR): when Postgres analytics offload is justified, which of the four realistic options fits, and what each one costs you in operations. The options are a tuned read replica, the pg_duckdb extension, logical replication or change data capture (CDC) into ClickHouse, and columnar or HTAP-style extensions such as Citus columnar and pg_mooncake. I verified extension status against upstream repositories and vendor documentation on the date of writing and flag every item I could not confirm.

What this covers: the limits of row-store Postgres for analytics, the mechanisms behind each option, a decision matrix, SQL and configuration you can adapt, the failure modes that bite in production, and a recommendation by workload shape.

Context and Background

Postgres stores table data as fixed-size 8 KB pages holding whole rows (the heap). A query that needs three columns out of twenty still reads every page that contains a qualifying row, because the columns live side by side inside each tuple. Add row-at-a-time execution in the classic executor, and a scan-heavy aggregate spends most of its time moving bytes it never uses. Indexes rescue selective queries, but a report that touches 10 to 40 percent of a table cannot use them productively; the planner correctly prefers a sequential scan.

Analytical engines invert the layout. A column store keeps each column contiguous, compresses it with codecs that exploit similarity within a column, and processes it in vectors of a thousand or so values per call, which lets the CPU use SIMD instructions and keeps caches warm. The gap is structural, and it is why “OLAP on Postgres” tuning guides eventually run out of road: partitioning, parallel query, BRIN indexes, and materialized views each buy a constant factor, not a change of class.

There are two honest ways to respond. You can move the analytical read path somewhere with a better layout, or you can change the layout inside Postgres itself. The 2026 landscape offers both in several flavours:

Read replica, tuned. Nothing new to install. It isolates reporting load from the primary but does not change the scan cost.
pg_duckdb. An open-source (MIT) Postgres extension, maintained under the DuckDB organization with MotherDuck involvement, that embeds a DuckDB instance in the Postgres process. Its repository lists support for Postgres 14 through 18 (pg_duckdb on GitHub).
CDC to ClickHouse. Stream the write-ahead log (WAL) through logical decoding into a dedicated column store. ClickHouse acquired PeerDB in July 2024 and now runs it as the Postgres CDC connector in ClickPipes, which reached general availability in May 2025 according to ClickHouse’s own year-in-review post.
Columnar and HTAP extensions. Citus columnar (an append-only table access method) and pg_mooncake (a columnstore mirror backed by Iceberg and executed by DuckDB).

If you want the broader engine comparison first, the earlier posts on DuckDB versus ClickHouse for embedded analytics and on Postgres 18 versus TimescaleDB versus ClickHouse for IoT cover the engines in isolation. This post takes a different angle: you already run Postgres, you do not want a second source of truth to babysit unless it is necessary, and you need to know where the line sits.

When offload is actually warranted

Before choosing, check that the problem is real. A useful triage, in order of cost:

Is a missing index, a stale statistic, or a bad join order the real cause? Run EXPLAIN (ANALYZE, BUFFERS) and look at rows removed by filter and buffer hits versus reads.
Is the same aggregate recomputed by many dashboards? A materialized view or a summary table maintained by a job may remove 90 percent of the load.
Does the report interfere with OLTP latency? Then a replica is the minimum fix, regardless of what else you do.
Is the scanned data volume larger than memory, or the query frequency high enough that even a good plan saturates I/O? Now you have a layout problem, and offload is on the table.

Only the fourth case justifies the complexity discussed below. Teams routinely skip steps one and two and adopt a second database to avoid writing a summary table.

The Decision Space: Four Ways to Offload Analytics

The direct answer: choose pg_duckdb on a replica when data is below a few hundred gigabytes, minutes of staleness are acceptable, and you want zero new infrastructure. Choose CDC to ClickHouse when volume, concurrency, or retention outgrow one replica. Choose columnar extensions only for append-heavy tables where in-database simplicity outweighs engine maturity.

Figure 1: Decision flow for Postgres analytics offload. Freshness and data volume are the first two discriminators; update patterns decide whether a columnar mirror is viable.

The flow in Figure 1 is deliberately coarse. Two questions carry most of the weight: how stale can the answer be, and does the working set fit on one machine. Everything else (SQL dialect coverage, team skills, managed-service availability) refines the choice but rarely reverses it.

Option A: Read replica, tuned

A streaming replica gives you an isolated copy with the same row layout. It protects the primary from long scans and, with hot_standby_feedback and max_standby_streaming_delay set deliberately, can run reports for minutes without replay conflicts. Parallel query (max_parallel_workers_per_gather) scales a scan across cores. What it cannot do is change bytes-per-row-read. I treat this as the baseline every other option must beat, and as the foundation under pg_duckdb, because that extension recommends running on a replica. For replica topology and failover, see the earlier PostgreSQL high availability guide with Patroni.

Option B: pg_duckdb, an analytical engine inside the Postgres process

pg_duckdb loads DuckDB into each Postgres backend. When you enable DuckDB execution for a session, supported queries are executed by DuckDB’s vectorized engine, reading Postgres tables directly. The MotherDuck release post for version 1.0 is explicit about what that does and does not give you: the data stays in Postgres’s row-oriented heap, so you get DuckDB’s vectorized execution but not DuckDB’s columnar storage or compression. The same post reports speed-ups of up to roughly 4x on a TPC-H-like benchmark against Postgres with all indexes, and queries that timed out at ten minutes on indexless Postgres finishing in under ten seconds. Treat these as the vendor’s claims on its own workload; the post gives them as ranges and notes that gains shrink where indexes already serve the query.

Beyond heap scans, the extension reads and writes Parquet, CSV, JSON, Iceberg, and Delta Lake files in S3, GCS, Azure, and R2, and can connect to MotherDuck for serverless offload. That second capability matters architecturally: it turns Postgres into a SQL front end over a lake, which is a different use than speeding up heap scans.

Option C: CDC to ClickHouse

Here the analytical store is a separate system. A logical replication slot decodes WAL into row changes, a reader batches them, and ClickHouse applies them into ReplacingMergeTree tables. The pattern is the same as in the Debezium streaming architecture post, with ClickHouse as the sink and PeerDB or ClickPipes as the reader. You get a purpose-built column store, compression, and horizontal scale, and you pay with a second system, eventual consistency, and a replication slot that must be monitored forever.

Option D: Columnar and HTAP extensions in Postgres

Hybrid transactional and analytical processing (HTAP) tries to serve both workloads from one database. In the Postgres ecosystem, that means a columnar table type alongside heap tables. Citus columnar has existed for years and is append-only. pg_mooncake is newer: its README describes a columnstore mirror of regular Postgres tables stored as Iceberg, with ingestion handled by a component called moonlink and queries accelerated by DuckDB via pg_duckdb. I come back to what each can and cannot do below.

How Each Option Works Underneath

Understanding the mechanisms is what lets you predict failure modes, so each gets its own subsection. The diagrams are simplified; the points in the text are the load-bearing ones.

pg_duckdb: planner hooks, per-connection engines, and the replica rule

Figure 2: pg_duckdb inside a Postgres backend. Supported queries are routed to the embedded DuckDB engine, which scans heap pages or object-store files; unsupported queries fall back to the native executor.

Figure 2 shows the shape: a hook intercepts planning, the embedded DuckDB engine executes what it can, and the rest follows the normal path. I could not verify from public documentation exactly which statement shapes fall back, so I will not claim a precise compatibility list. The repository notes one concrete limitation: advanced DuckDB types such as STRUCT, MAP, and UNION require a DuckDB execution context, and the docs recommend duckdb.query() for complex type operations and TEMP tables when you need DuckDB-side tables.

Two operational facts follow from the release post. First, each Postgres connection gets its own DuckDB instance, so memory and thread limits must be sized per connection and per replica, not per cluster. With 20 concurrent reporting sessions and a 4 GB DuckDB memory limit each, you have committed 80 GB, which is a number you want to discover in a design review and not in an out-of-memory kill (illustrative arithmetic, not a measurement). Second, extensions must match between primary and replica, so pg_duckdb has to be installed on the primary even if nothing there should ever use it. The recommended safeguard is to prevent DuckDB execution from being enabled on the primary through role-level settings.

A minimal setup on a replica looks like this:

-- postgresql.conf (replica and primary; library must be present on both)
-- shared_preload_libraries = 'pg_duckdb'

CREATE EXTENSION pg_duckdb;

-- Route this session's eligible queries to DuckDB
SET duckdb.force_execution = true;

-- Reporting role gets DuckDB execution by default; app role does not
ALTER ROLE reporting SET duckdb.force_execution = true;
ALTER ROLE app_rw    SET duckdb.force_execution = false;

-- Example report: heap scan executed by DuckDB's vectorized engine
SELECT date_trunc('week', created_at) AS wk,
       plan, count(*) AS orders, sum(total_cents) / 100.0 AS revenue
FROM   orders
WHERE  created_at >= now() - interval '180 days'
GROUP  BY 1, 2
ORDER  BY 1, 2;

The role-level ALTER ROLE ... SET pattern is ordinary Postgres, and it is the cleanest way to make “reports use DuckDB, the app never does” a property of credentials instead of a convention. Confirm the parameter names against the version you deploy, because the settings documentation is the source of truth and the README gives no defaults.

To use the lake-reading side, you create credentials from SQL and query files directly:

-- Install format support once per database
SELECT duckdb.install_extension('iceberg');

-- S3 credentials (a managed identity is preferable in production)
SELECT duckdb.create_simple_secret('S3', '<key_id>', '<secret>', '<region>');

SELECT r['customer_id'], sum(r['amount'])
FROM   read_parquet('s3://my-bucket/events/2026/10/*.parquet') r
GROUP  BY 1;

The r['column'] syntax is how the extension exposes columns from file-reading functions. This is the part of pg_duckdb that competes with a lakehouse engine, not with ClickHouse, and it is the reason I treat the extension as two products in one box.

CDC to ClickHouse: slots, snapshots, and merge-time deduplication

Figure 3: Postgres to ClickHouse CDC. The slot holds WAL until the reader confirms it; the initial snapshot runs in parallel by CTID ranges and merges with the change stream.

The pipeline in Figure 3 has three phases. The initial snapshot copies existing rows, the stream applies ongoing changes, and a steady state maintains the two within seconds to minutes of each other. ClickHouse’s year-in-review post describes snapshot parallelism by CTID block ranges and reports that partition generation for the initial load on multi-terabyte tables dropped from over seven hours to under a second after switching to CTID block-based partitioning. That is a vendor-reported improvement on its own pipeline; the article does not give a hardware or dataset description, so do not extrapolate it to your tables.

The Postgres side deserves the most respect. The PostgreSQL documentation states plainly that replication slots persist across crashes and know nothing about their consumers: they prevent removal of required WAL and required catalog rows even when nothing is connected, and in extreme cases that can lead to database shutdown to prevent transaction ID wraparound. A stalled CDC reader is therefore a primary-database incident, not an analytics incident. The documentation also notes a logical slot normally emits each change once but, because its position is persisted only at checkpoint, may resend recent changes after a crash, so consumers must be idempotent. Failover support exists (slots created with the failover option can be synchronized to a standby with sync_replication_slots, which requires hot_standby_feedback and a physical slot named in primary_slot_name, among other settings); check the current documentation for your major version before relying on it.

Publication and slot setup for a self-managed reader is short:

-- On the primary
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_slot_wal_keep_size = '100GB';  -- cap the damage from a stalled consumer
-- restart required for wal_level

CREATE PUBLICATION analytics_pub FOR TABLE orders, order_items, customers;

-- Make deletes carry enough key data for non-PK ordering keys downstream
ALTER TABLE orders REPLICA IDENTITY FULL;  -- cost: bigger WAL records; use a unique index identity when possible

SELECT pg_create_logical_replication_slot('analytics_slot', 'pgoutput');

max_slot_wal_keep_size is a real Postgres setting that bounds how much WAL a slot may retain before it is invalidated; I recommend it because losing a slot (and re-snapshotting) is a recoverable event while a full primary disk is not. The 100 GB figure is a placeholder to be sized against your write rate and your recovery objectives.

On the ClickHouse side, ClickHouse’s own data-modeling post explains the mapping. Tables use ReplacingMergeTree with a version column (_peerdb_version); a delete is stored as a newer row version with _peerdb_is_deleted set; background merges keep the latest version per sorting key. Because merge timing is unpredictable, the post’s own two-row example leaves ClickHouse with three more rows than Postgres until merges run, and it recommends FINAL with a _peerdb_is_deleted = 0 filter as the default for queries that must be correct.

-- ClickHouse: shape of a CDC-managed table (column names follow PeerDB's convention)
CREATE TABLE orders
(
    id            Int64,
    tenant_id     Int32,
    created_at    DateTime,
    total_cents   Int64,
    status        LowCardinality(String),
    _peerdb_is_deleted UInt8,
    _peerdb_version    Int64
)
ENGINE = ReplacingMergeTree(_peerdb_version)
PRIMARY KEY id
ORDER BY id;

-- Correct report: dedupe at read time
SELECT toStartOfWeek(created_at) AS wk, status, count() AS orders, sum(total_cents) / 100 AS revenue
FROM orders FINAL
WHERE _peerdb_is_deleted = 0 AND created_at >= now() - INTERVAL 180 DAY
GROUP BY wk, status
ORDER BY wk;

Figure 4 makes the delete semantics concrete, because this is where first-time users get wrong counts.

Figure 4: Row lifecycle in a ReplacingMergeTree CDC target. Every change is an append; only FINAL (or a completed merge) collapses versions, and deletes are tombstone rows.

The same post warns that a custom ordering key interacts with deduplication, because the ordering key is also the deduplication key, and that projections are skipped for queries using FINAL. Its suggested workaround for a different sort order is a refreshable materialized view, which is only as fresh as its last refresh. These are design constraints you accept with the pattern, not defects to be tuned away.

Columnar extensions: what Citus columnar and pg_mooncake actually give you

Citus columnar is a table access method. Its README is clear and conservative: tables are append-only, with no UPDATE or DELETE; there is no logical decoding; no foreign keys; no tuple locks (SELECT ... FOR UPDATE); no serializable isolation; no TOAST; no AFTER ... FOR EACH ROW triggers; and no intra-node parallel scans. Compression is selectable per table (none, pglz, zstd, lz4, lz4hc, with zstd the default when compiled in), data is organized into stripes (default limit 150,000 rows) and chunk groups (default 10,000 rows), and btree and hash indexes are supported while gist, gin, spgist, and brin are not. In a partitioned table mixing row and columnar partitions, UPDATE and DELETE succeed against row partitions and fail against columnar ones.

That profile defines the use case precisely: immutable facts. An events table partitioned by month, with the hot month in heap and closed months converted to columnar, is a coherent design. A mutable orders table is not. The lack of logical decoding also matters: a columnar table cannot feed a CDC pipeline, so you cannot columnarize a table and replicate it onward.

pg_mooncake takes a different route. The MotherDuck announcement describes a native columnstore table type (USING columnstore) storing Parquet files with Iceberg or Delta metadata in object storage, executed by DuckDB, released under MIT and available as a preview on Neon at the time of that post. The current README on GitHub describes a mirror approach instead: you call mooncake.create_table(...) to create a columnstore mirror of an existing Postgres table, stored in Iceberg, with the README promising “sub-second freshness” and requiring wal_level = logical, pg_duckdb in shared_preload_libraries, and duckdb.allow_community_extensions enabled. Two things follow. The project’s design has visibly shifted between those two descriptions, so pin versions and re-read the README before every upgrade. And the announcement’s “up to 1,000x faster” claim comes with no dataset, hardware, query set, or baseline in the text I could fetch, so I treat it as marketing, not evidence. I also could not find a tagged release in the repository snapshot, which is a maturity signal worth weighing for anything customer-facing.

Because the mirror is Iceberg-native, other engines can read it. That is the interesting architectural property: the analytical copy lives in open table format in object storage, and Postgres is only one of several readers. It is also the property that makes this option converge toward the CDC option, with the replication machinery inside an extension instead of in a separate service.

Worked cost model (illustrative)

Numbers below are a thought experiment to show where the time goes, not benchmarks. Assume an orders table of 200 million rows averaging 150 bytes per row in the heap, so about 30 GB before indexes and bloat. A weekly revenue report needs three columns: a timestamp (8 bytes), a status code (2 bytes after dictionary encoding in a column store, but 4 to 16 bytes in the heap), and an amount (8 bytes).

Heap scan: the query must read essentially all 30 GB of pages, because rows are contiguous. At a sustained 1.5 GB/s from a warm NVMe-backed replica, that is about 20 seconds of I/O before CPU cost. If the working set is partly cold, multiply accordingly.
Columnar scan: the same three columns occupy roughly 18 bytes per row uncompressed, 3.6 GB total. With a typical 3x to 5x compression on sorted, repetitive columns the read shrinks to under 1.2 GB, so I/O drops to around a second at the same disk speed. CPU then dominates, and vectorized execution keeps it to a few seconds at multi-core parallelism.
pg_duckdb on the heap: I/O stays at 30 GB because the layout is unchanged; you save the executor overhead, not the byte volume. That is why gains here are real but bounded, consistent with the vendor’s own up-to-about-4x figure against an indexed Postgres.
CDC or Iceberg mirror: you pay the byte reduction, plus the cost of the copy.

The lesson is general: if the report reads a small fraction of columns from a wide table, layout beats engine. If the report reads most columns, the layout advantage shrinks and engine efficiency matters more. Measure your own column-touch ratio from pg_stat_statements and the query text before choosing.

Deeper Analysis: Decision Matrix and Operating Costs

Decision matrix

Criterion	Tuned read replica	pg_duckdb on replica	CDC to ClickHouse	Citus columnar	pg_mooncake mirror
Extra infrastructure	None	None beyond replica	Separate cluster or managed service plus CDC reader	None (extension)	Object storage plus extension components
Data layout benefit	None	None for heap, yes for Parquet or Iceberg	Full column store	Full, for append-only data	Column store copy in Iceberg
Freshness	Seconds (replay lag)	Seconds (replay lag)	Seconds to minutes, pipeline dependent	Immediate within the same database	Claimed sub-second by README (unverified by me)
Handles UPDATE and DELETE	Yes	Yes	Yes, via versioned rows and tombstones	No	Mirrors them per README
Scale ceiling	One machine	One machine per session	Horizontal	One node, no parallel scans	Depends on object store and DuckDB
Query dialect	Postgres	Postgres, with DuckDB functions	ClickHouse SQL	Postgres	Postgres
Failure that hurts most	Replay conflicts	Memory per connection	Slot WAL retention, merge lag	Writes to mutable tables fail	Immature project churn
Operational skill needed	Existing DBA	Existing DBA plus DuckDB tuning	New engine, new on-call	Existing DBA	Existing DBA plus beta-software risk
License	PostgreSQL	MIT	ClickHouse is Apache 2.0 licensed; managed pipes are commercial	Open source (Citus)	MIT

I would add one caveat on the licence row: I did not re-verify the licences for every component of every option, only the ones stated in the repositories I fetched (MIT for pg_duckdb and pg_mooncake). Check ClickHouse and Citus terms for your deployment model.

Freshness is the discriminator people forget to quantify

Ask the report’s consumers what staleness they can tolerate, then write it down as a service-level objective. A finance close dashboard that is read once a day tolerates an hour. An operations dashboard that drives intervention may tolerate 30 seconds. A customer-facing metric inside the product might tolerate five. Each tier points to a different architecture: hourly refresh makes even a nightly Parquet export plus pg_duckdb lake queries defensible; 30 seconds points to CDC; sub-second points to running on the primary with a good index or a precomputed counter, and no analytics offload at all.

Concurrency and tenancy

A single replica with pg_duckdb behaves like a Postgres server with a very hungry executor: every session may use many cores and a large memory budget. Ten dashboard users clicking simultaneously produces ten competing scans. Postgres’s connection pooling, role-level resource settings, and statement timeouts are your governors. The earlier post on PgBouncer, PgCat, and RDS Proxy is relevant, because a transaction-mode pooler reuses backends across clients and therefore interacts with per-connection DuckDB instances and session-level SET duckdb.force_execution. Pin reporting traffic to a dedicated pool with session pooling so the setting means what you think it means.

ClickHouse handles high-concurrency reads better by design, but it too needs quotas and settings profiles per user to stop one runaway query from starving others. The difference is that you scale it out by adding replicas and shards, instead of buying a larger Postgres machine.

Schema evolution

CDC pipelines transport schema changes imperfectly. ClickHouse’s review states that the managed connector supports ADD COLUMN and DROP COLUMN but not CHANGE TYPE, which means an ALTER COLUMN TYPE on the Postgres side needs a planned resync or a new column plus backfill. Because the pre-flight checks the connector runs cover replica identity and primary keys, tables without a primary key are the first thing to fix. In the extension options schema changes are Postgres DDL and just work, within each extension’s limits. This is a quiet advantage of staying in-database that tends to be undervalued until the first painful migration.

Cost shape

Without invented prices, the cost structure is easy to describe. A tuned replica costs one more instance of your current size. pg_duckdb costs the same instance, possibly larger for memory. CDC to ClickHouse adds a cluster or a managed-service bill, a network egress line if cross-cloud, and engineering time for the pipeline: monitoring, resyncs, and schema coordination. Columnar mirrors add object storage and compaction work. The engineering-time line is almost always the largest and the least budgeted. A reasonable rule: if offloading means a new on-call rotation, price it as at least a fraction of one engineer’s year, then ask whether a materialized view would have been enough.

Trade-offs, Gotchas, and What Goes Wrong

Replication slot incidents. The most severe failure in this space is a CDC consumer that stops advancing. WAL accumulates on the primary, disk fills, and the OLTP database stalls. Mitigations: alert on pg_replication_slots lag in bytes, set max_slot_wal_keep_size, keep the reader’s health in the same paging path as the database’s, and rehearse a slot drop and resync. ClickHouse’s post notes that its pipeline now exposes slot growth and commit lag through a Prometheus endpoint, and that consuming the slot during the initial snapshot is still under investigation, so initial loads on write-heavy tables need WAL headroom planned in advance.

Wrong counts from unmerged versions. Reports that omit FINAL and the deleted filter silently overcount. The failure is quiet: numbers look plausible and are wrong by the churn rate of the table. Wrap the table in a view that always applies the dedupe, and give analysts the view, not the base table. Remember that FINAL has a query cost and disables projections.

DuckDB memory in a shared process. An in-process engine shares fate with the Postgres backend. A runaway aggregate can push the replica into swap or trigger the kernel’s out-of-memory killer, which kills a Postgres process and forces crash recovery. Set DuckDB memory and thread limits, set statement_timeout for the reporting role, and run on a replica sized for it. The release post’s own advice, to prevent DuckDB execution on the primary, exists for exactly this reason.

Replay conflicts on replicas. A long scan on a hot standby can be cancelled when replay needs to remove rows the scan still sees. Enabling hot_standby_feedback stops cancellations but delays vacuum on the primary and can inflate bloat. This is a real trade, not a free setting; measure bloat after enabling it.

Extension churn. pg_duckdb and pg_mooncake are young projects, and pg_mooncake pins specific commits of its submodules (moonlink, duckdb_mooncake, pg_duckdb). A managed-Postgres provider may or may not offer them, and upgrades of major Postgres versions can lag the extension. Confirm availability on your provider before designing around it. I could not establish a definitive latest pg_duckdb release number from the pages I fetched; the README references a v1.1.1 container tag, which I note without claiming it is current.

Treating columnar as general purpose. Citus columnar tables reject updates and deletes; a retention job that does DELETE will fail rather than degrade. Partition by time and drop partitions instead.

Two sources of truth. Whatever you choose that copies data, you need reconciliation: row counts and checksums per partition on a schedule. Teams skip this and discover divergence in front of a customer.

Practical Recommendations

Start from the freshness and size answers in Figure 1, and then apply these defaults.

If your analytical data is under a few hundred gigabytes and a delay of seconds to minutes is fine, start with a dedicated replica, pg_duckdb loaded, a reporting role with DuckDB execution on, and strict memory and timeout limits. That path adds no new system. Measure before and after on your five slowest real queries, and do not proceed to anything heavier unless the numbers fail your service-level objective. If many of those queries read files in a lake, the extension also doubles as a way to query Parquet and Iceberg without another engine.

If you have terabytes, many concurrent dashboard users, long retention, or event data that never changes, plan CDC to ClickHouse. Budget for the slot monitoring, the dedupe discipline, and the schema-change process from day one. Use the managed pipe if you want someone else to own the connector, and a self-hosted reader if data residency forbids it, and accept that either way you own the Postgres side of the slot.

If your heavy tables are append-only and time-partitioned, Citus columnar for closed partitions is a pragmatic hybrid, provided you have verified your managed provider supports it. Evaluate pg_mooncake in a non-critical environment, with pinned versions and a rollback to CDC or pg_duckdb, until you see a stable release history.

Checklist before committing:

[ ] Triage done: indexes, statistics, and a summary table ruled out as the fix.
[ ] Freshness SLO written down per report family.
[ ] Column-touch ratio and scanned bytes estimated from real queries.
[ ] Replica or sink sized for per-connection memory, not per-cluster.
[ ] Primary protected: pg_duckdb execution disabled there, max_slot_wal_keep_size set, slot-lag alerts wired to paging.
[ ] Dedupe view defined for any ReplacingMergeTree table.
[ ] Reconciliation job comparing counts and checksums.
[ ] Provider support for every extension confirmed, and extension versions pinned.
[ ] Exit path documented, for example to re-snapshot or drop the slot.

Frequently Asked Questions

Can pg_duckdb replace ClickHouse for Postgres reporting?

For modest datasets, often yes; at scale, no. pg_duckdb speeds execution but leaves data in Postgres’s row-oriented heap, so you do not get columnar storage or compression benefits, and each connection runs its own DuckDB instance on one machine. ClickHouse adds a compressed column layout and horizontal scaling. Use the extension to defer a second system, and move when volume, concurrency, or retention exceed one replica.

Is it safe to run pg_duckdb on my primary database?

It is discouraged. The release guidance notes DuckDB can be resource-hungry, each connection gets its own engine, and the extension must be installed on both primary and replica anyway. The safer pattern is installing it everywhere but enabling DuckDB execution only for a reporting role on a replica. That keeps analytical memory spikes away from your transactional workload.

How stale is data in a Postgres to ClickHouse CDC pipeline?

Typically seconds to minutes, but it depends on write volume, batching, and merge behavior, and I could not find a published latency figure with stated test conditions. Treat freshness as something you measure in your environment and alert on. Remember that logical replication lag and ClickHouse merge lag are separate: rows can arrive quickly yet still need FINAL to read correctly.

Why do my ClickHouse counts not match Postgres?

Because a ReplacingMergeTree table stores every change as a new row version and deduplicates asynchronously during background merges. Until merges finish, updates and deletes appear as extra rows. Query with FINAL and filter on the deleted flag, or read through a view that does both. ClickHouse’s own example shows three more rows than Postgres after an insert, update, and delete.

Does Citus columnar support UPDATE and DELETE?

No. The Citus columnar README states the tables are append-only, with no UPDATE or DELETE, and also lists no logical decoding, no foreign keys, and no tuple locks. It suits immutable event data. In a partitioned table you can keep recent partitions as heap and convert older ones to columnar, then drop whole partitions for retention.

Is pg_mooncake production ready?

I cannot say it is. The repository shows no formal releases in the snapshot I read, the original announcement called it a preview, and the project’s documented design has changed from a native columnstore table type to a Postgres-table mirror stored in Iceberg. Its performance claims lack stated methodology. Evaluate it with pinned versions and an exit path before depending on it.

Postgres Analytics Offload: pg_duckdb vs CDC to ClickHouse vs HTAP Extensions for Reporting Queries (2026)