Best Postgres extensions for analytics in 2026: pg_clickhouse, pg_duckdb, pg_lake and more

The best Postgres extension for analytics depends on one question: where should the analytical compute run? pg_duckdb and pg_lake run a DuckDB engine on the same machine as Postgres, which makes them quick to adopt. pg_clickhouse pushes supported query operations down to a separate ClickHouse server or cluster, so the heavy scans and aggregations run on hardware that scales independently of your transactional database. TimescaleDB, Citus columnar and AlloyDB's columnar engine change how Postgres stores data instead.

We build ClickHouse and maintain pg_clickhouse, so we have a stake in this comparison. We've tried to describe every option the way its own maintainers do, cite their docs for each claim, and say where each one is the better fit.

TL;DR

• For heavy aggregations and Parquet files on a server with spare capacity: pg_duckdb. In our tests it sped up high-cardinality GROUP BY and distinct counts on heap tables 2.5–3x, but was slower than plain Postgres on simple scans.
• For writing Iceberg tables from Postgres and querying a data lake with SQL: pg_lake.
• For production analytics that must not slow down your transactional workload, or that will outgrow one machine: pg_clickhouse with data replicated into ClickHouse. In our tests it changed OLTP throughput by less than 2% under analytical load, against drops of 18–58% for the options that run on the Postgres host.

Four ways to add analytics to Postgres #

Every extension in this list falls into one of four architectures. The architecture matters more than any single feature, because it decides which resources your analytical queries consume.

The first three rows share a property: however fast the engine is, it runs on the box that's also serving your application. That's a reasonable trade at small and medium scale, and it's why these extensions are easy to like. It becomes the constraint when analytical load grows or when transaction latency matters. The fourth row moves the work elsewhere, at the cost of running a second system and keeping its data in sync.

How we evaluated them #

We compared each option on the criteria that decide whether it works in production:

• Where compute runs. Same process, same host, or a separate system.
• What data it accelerates. Existing heap tables, a separate columnar copy, or files in object storage.
• Freshness. Whether analytical queries see committed rows immediately or after replication.
• Write and transaction semantics. What you can INSERT, UPDATE or DELETE, and what can share a transaction.
• Maturity. Release cadence as of October 2026, open issues, and documented limitations.
• Availability. Self-hosted install path and which managed Postgres services offer it.

We also ran the three main extensions side by side on the same hardware; see what we measured.

Comparison table #

1. pg_clickhouse #

pg_clickhouse is an Apache-2.0 Postgres extension that lets you query ClickHouse tables from Postgres and pushes as much of each query as it can down to ClickHouse. It's the option for teams whose analytics have outgrown what the Postgres server can do on the side, or whose transactional latency can't absorb analytical scans.

How it runs #

pg_clickhouse is a foreign data wrapper. You create a server that points at ClickHouse, map a user, and import the ClickHouse tables as foreign tables:

1CREATE EXTENSION pg_clickhouse;
2
3CREATE SERVER ch_srv FOREIGN DATA WRAPPER clickhouse_fdw
4  OPTIONS (driver 'binary', host 'clickhouse.internal', dbname 'analytics');
5
6CREATE USER MAPPING FOR CURRENT_USER SERVER ch_srv OPTIONS (user 'default');
7
8CREATE SCHEMA analytics;
9IMPORT FOREIGN SCHEMA analytics FROM SERVER ch_srv INTO analytics;

When you query a foreign table, the planner works out which parts of the query ClickHouse can execute and sends them as ClickHouse SQL. As of v0.11.0, that covers filters, GROUP BY and a long list of aggregates (for example, percentile_cont maps to ClickHouse's quantile functions, and FILTER clauses map to -If combinators), ORDER BY and LIMIT, joins between tables on the same ClickHouse server, window functions, many scalar functions, and, with ClickHouse 25.8 or later, scalar, EXISTS and IN subqueries. EXPLAIN (VERBOSE) prints the remote SQL, so you can check what was pushed down.

Anything that can't be pushed down runs in Postgres on rows fetched from ClickHouse. The most common case is a join between a foreign table and a local heap table: pg_clickhouse fetches the ClickHouse side and Postgres performs the join. That works, but it moves rows over the network and puts the join work back on Postgres.

Performance #

On TPC-H at scale factor 1, the pg_clickhouse README reports that 16 of the 22 queries push down fully as of v0.10 (14 as a single foreign scan and 2 as several), up from 12 in earlier versions. Fully pushed-down queries run far faster than the same queries on Postgres heap tables: Q1 drops from 4,483 ms to 59 ms and Q17 from 2,107 ms to 37 ms. The same table shows the cost of partial pushdown. Q18 and Q21, which aren't fully pushed down, are slower through pg_clickhouse than on plain Postgres (7,228 ms vs 5,230 ms, and 4,470 ms vs 1,145 ms). That benchmark ran on a single laptop with ClickHouse on the same machine.

On ClickBench, which we maintain, the May 2026 pg_clickhouse result on a c6a.4xlarge (ClickHouse on the same host, a release before v0.10) took 33.6 s in total across the hot runs, against 33.4 s for native ClickHouse measured the same day. Once a query is pushed down, ClickHouse does almost all of the work.

Getting data into ClickHouse #

pg_clickhouse doesn't replicate data. It queries whatever is in ClickHouse, so most teams pair it with change data capture (CDC). ClickHouse Managed Postgres offers two built-in ways to keep ClickHouse in sync:

• ClickPipes is a fully managed pipeline. You pick tables, it runs the initial load and then streams changes. The sync interval (60 seconds by default), batch size and snapshot parallelism are all configurable. It's the same ClickPipes Postgres CDC that works with other Postgres providers, so it suits teams that want one managed pipeline for every source.
• WalShadow reads Postgres's physical WAL instead of logical decoding and delivers sub-second replication lag, with initial load and schema changes handled for you. The docs recommend it "when you need the lowest latency and the least replication overhead". It's open source and needs Postgres 16 or newer.

Self-managed setups can use ClickPipes, PeerDB or any other CDC tool. pg_clickhouse itself works with any Postgres 14+ and any ClickHouse 23.3+.

That gives you one Postgres endpoint for both workloads: transactions hit heap tables, analytical queries hit foreign tables backed by ClickHouse. Applications keep their Postgres driver. Pointing existing queries at the replicated tables usually takes a search_path change or a schema prefix, not a rewrite.

Trade-offs #

• You operate (or buy) a second system, and analytical queries see data after replication lag (sub-second with WalShadow, the sync interval with ClickPipes), not at commit.
• Joins to local Postgres tables and constructs that aren't supported yet fall back to Postgres execution. Check plans with EXPLAIN (VERBOSE).
• Foreign tables support INSERT and COPY FROM, but not UPDATE or DELETE as of v0.11.0.
• It's a young project (first release December 2025), moving quickly; ClickHouse Managed Postgres ships v0.10 as of October 2026.

Choose pg_clickhouse when analytical queries scan tens of millions of rows or more, when they run often enough to affect transactional latency, or when you want analytics to scale independently of your Postgres instance. Skip it when a few reports a day over a few million rows are the whole workload; plain Postgres or a read replica will do.

2. pg_duckdb #

pg_duckdb embeds DuckDB's vectorized columnar engine inside Postgres. It's MIT-licensed, developed in collaboration with MotherDuck, and its maintainers declared it production-ready at v1.0.0 in September 2025. It's the most direct way to run analytical queries on existing Postgres tables through a columnar engine without moving any data.

How it runs #

You add it to shared_preload_libraries, restart, and create the extension. Queries that use DuckDB-only features, such as read_parquet() or DuckDB tables, run through DuckDB automatically. Queries over ordinary Postgres tables run through DuckDB when you set duckdb.force_execution:

1-- postgresql.conf: shared_preload_libraries = 'pg_duckdb'
2CREATE EXTENSION pg_duckdb;
3
4SET duckdb.force_execution = true;
5SELECT order_date, count(*), sum(amount)
6FROM orders
7GROUP BY order_date
8ORDER BY order_date;

DuckDB reads heap tables through a Postgres scan, so it sees committed data immediately and needs no copy. It can also read and write Parquet, CSV, JSON, Iceberg and Delta files on S3, GCS, Azure and R2.

Per the pg_duckdb settings documentation, "any connection that uses DuckDB will have its own DuckDB instance". By default each of those instances may use up to 4 GB of memory (duckdb.max_memory) and one thread per CPU core (duckdb.threads = -1). Three analysts running DuckDB queries at once can each try to use every core on your database server.

This isn't a theoretical risk: we hit it in our own test. On a 4 GB Postgres instance with pg_duckdb's default settings, running the same 5-million-group aggregation a second time in one session pushed its backend past the memory limit, and the kernel killed it. Postgres treats a killed backend as a crash, so it terminated every connection, including the transactional clients, and ran crash recovery. One analytics session took the whole database offline for those clients. We reproduced it on a second machine. Lowering duckdb.max_memory to 2 GB stopped the crashes, but the Postgres container still peaked at about 3.7 GB of its 4 GB. Size duckdb.max_memory against the server's total memory and expected concurrency before enabling it, and keep it off a primary you can't afford to restart.

Performance #

MotherDuck's v1.0 announcement reports up to about 4x faster than Postgres with all indexes on a TPC-H-like suite, and queries that timed out after 10 minutes on Postgres finishing in under 10 seconds when only primary keys exist. The post doesn't state hardware or scale factor.

Our own tests matched that on the query shapes Postgres handles worst: on 50 million heap rows, pg_duckdb ran a 5-million-group aggregation in 5.3 s against 13.8 s, and a distinct count in 3.45 s against 10.2 s. On simple full scans and low-cardinality GROUP BYs it was 1.7–2x slower than plain Postgres warm (about 2.5x cold), because DuckDB still reads row-format heap pages through a Postgres scan. Exported to Parquet, the same queries ran about 5x to 57x faster than pg_duckdb on the heap. See what we measured.

Trade-offs #

• Analytical work runs on the same CPU, memory and disk as your transactions. MotherDuck's own guidance is that "since DuckDB can be very resource-hungry, you'll want controls in place to prevent use on the primary", and it suggests running pg_duckdb on a read replica. PlanetScale's documentation for its pg_duckdb support likewise doesn't recommend running it directly on the database.
• A read replica isolates the primary, but you're still bounded by one machine, and you pay for that machine whether it's busy or not. MotherDuck is the documented way to move compute off the server.
• Without MotherDuck, DuckDB-format tables are temporary only. You can't write to a Postgres table and a DuckDB table in the same transaction.
• Some types don't convert cleanly (no enum, jsonb returns as json, wide numeric needs a lossy opt-in), and DuckDB execution is superuser-only unless you grant duckdb.postgres_role.
• At the time of writing (October 2026), the latest release is v1.1.1, from December 2025. Main has continued to move, with Postgres 19 support among unreleased changes.

Choose pg_duckdb when you want faster aggregations on existing tables, the data fits comfortably on one server, and you can run it on a replica or a server with headroom. Avoid it on a busy primary where transaction latency is the priority.

3. pg_lake #

pg_lake turns Postgres into a client for the data lake. It came out of Crunchy Data Warehouse, which Snowflake acquired in June 2025, and was open-sourced under Apache-2.0 in November 2025. It's the right pick when the goal is Iceberg: writing transactional Iceberg tables from Postgres and querying Parquet, CSV or JSON in object storage with SQL.

How it runs #

pg_lake splits the work across two processes. Postgres, with the pg_lake extensions, handles connections, planning and transactions. A separate multi-threaded process, pgduck_server, runs DuckDB and executes the scans. The two talk over a Unix socket, so, as the pg_lake configuration docs put it, pgduck_server "must run on the same machine as PostgreSQL". The design avoids the threading and memory-safety issues of embedding DuckDB into Postgres's process-per-connection model, and lets every connection share one DuckDB instance and one file cache.

1CREATE EXTENSION pg_lake CASCADE;
2
3CREATE TABLE measurements (
4  station text NOT NULL,
5  measured_at timestamptz NOT NULL,
6  temperature double precision
7) USING iceberg;
8
9SELECT station, count(*), avg(temperature) FROM measurements GROUP BY station;
10
11COPY (SELECT * FROM measurements WHERE station = 'Istanbul')
12  TO 's3://mybucket/exports/istanbul.parquet';

Iceberg tables support INSERT, UPDATE, DELETE and COPY, and their metadata commits inside the Postgres transaction, so a transaction can atomically touch both heap and Iceberg tables. Postgres acts as the Iceberg catalog, which Spark, PyIceberg and other engines can read. When every table in a query is a lake table and every function is supported, the whole query runs in DuckDB.

Trade-offs #

• pg_lake doesn't accelerate heap tables. It accelerates Iceberg tables and files. In a join between a heap table and an Iceberg table, DuckDB scans the Iceberg side and Postgres does the join. To use it for analytics on OLTP data, you copy that data into Iceberg yourself.
• pgduck_server shares the host. By default DuckDB may use 80% of system memory and one thread per core; the performance docs advise limiting it with --memory_limit on servers that also run a busy Postgres workload. In our tests the separate process kept DuckDB's memory out of Postgres, but it used the same cores, and transactional throughput fell 38% in our read-only test and 58% in the read-write test, more than running the same queries on plain Postgres (20% and 41%). It got far more analytical work done for that cost, though.
• There's no prebuilt image or package. Building from source, including DuckDB, took us 13 minutes on a 16-core server and over an hour on a laptop, and pgduck_server took 20–60 seconds to accept queries after starting.
• Iceberg tables have no indexes, unique constraints or foreign keys, and no MERGE or INSERT ... ON CONFLICT. Each statement writes at least one file, so the docs recommend staging single-row inserts in a heap table and flushing them in batches.
• Writes to external Iceberg catalogs are marked experimental.

Choose pg_lake when Iceberg is your analytical storage format, you want Postgres to write lakehouse tables, or you need SQL over files in S3. Look elsewhere when you want to speed up queries over the tables your application already writes to.

4. TimescaleDB Hypercore #

TimescaleDB's Hypercore stores hypertables in a hybrid row and column format: recent data lands in a rowstore and converts to a compressed columnstore as it ages, with full INSERT, UPDATE, DELETE and upsert support. Continuous aggregates add incrementally maintained rollups. It's actively developed (2.31.0, October 2026) and the natural choice for time-series and event data that you're willing to model as hypertables.

It applies to hypertables, not arbitrary tables, so it's a schema decision as much as an extension. The columnstore is part of the TSL-licensed Community Edition, not the Apache-2.0 build, which is what some managed Postgres services ship. Queries run in Postgres, so the resource picture is the same as any Postgres query.

Choose it when your analytical data is time-partitioned and you want columnar compression and rollups without leaving Postgres.

5. Citus columnar #

Citus ships a columnar table access method (CREATE TABLE ... USING columnar) that stores data in compressed stripes, which cuts storage and speeds up scans that touch few columns. It's maintained as part of Citus (14.3.0, October 2026). Columnar tables are append-only per the Citus documentation: no UPDATE or DELETE, no foreign keys, and no parallel scans, and converting a table drops its indexes. It fits archival and log-style tables. On its own it runs on the single Postgres node; combined with Citus distribution, both storage and query work spread across worker nodes.

Choose it when you have large append-only tables and want them smaller and faster to scan, or you already run Citus.

6. AlloyDB columnar engine #

AlloyDB isn't an extension you can install on your own Postgres, but it often comes up in this comparison. Google's AlloyDB keeps selected columns in an in-memory column store (30% of instance memory by default) and uses it transparently when every column a query needs is loaded. It's the closest thing to "Postgres tables, faster, no changes", and Google documents enabling it on the primary, on read pool instances, or both. It ties you to AlloyDB on Google Cloud or AlloyDB Omni, and frequently updated data invalidates the columnar copy.

Projects we left out #

• pg_mooncake mirrored heap tables into Iceberg columnstore tables queried through DuckDB. Mooncake Labs joined Databricks in October 2025, the last GitHub release was v0.1.2 in February 2025, and as of October 2026 its docs site redirects to Databricks. We wouldn't start a new project on it.
• ParadeDB pg_analytics is archived; its README says the extension "has been discontinued".
• Hydra columnar has had no code changes since 2024.
• clickhouse_fdw is archived; its README points users to pg_clickhouse as its continuation.

What we measured #

We ran pg_duckdb, pg_lake and pg_clickhouse side by side on one AWS m9gd.4xlarge (Graviton5, 16 vCPUs, 64 GB, local NVMe) in October 2026 to check two things: how fast each runs typical analytical queries, and what each does to a transactional workload running on the same Postgres instance.

Setup #

• Versions: PostgreSQL 17 throughout; pg_duckdb 1.1.1 (DuckDB 1.4.3) from its official image; pg_clickhouse 0.11 from its official image; pg_lake built from source at commit 1918df6; ClickHouse 26.10.
• Resources: every Postgres instance capped at 4 CPUs and 4 GB, pinned to the same four cores, run one at a time, with shared_buffers=1GB. ClickHouse ran in its own 4 CPU / 4 GB container on different cores, standing in for a separate server. pg_lake's pgduck_server ran on the same four cores as its Postgres instance but in its own 4 GB container, so that path had 8 GB of memory in total.
• Data: a 50-million-row events table (7.6 GB as a heap table) and a 5-million-row users table, generated deterministically.
• How each extension saw the data: pg_duckdb queried the heap tables directly, and separately a one-off Parquet export. pg_lake queried an Iceberg copy in local S3-compatible storage. pg_clickhouse queried a MergeTree copy loaded once (in production, CDC would keep it current).

All five paths returned identical results for all six queries. This is a single-host test, not a formal benchmark: latencies are medians of 10 runs per query, and each contention test ran once per configuration. The scripts and raw output are on GitHub so you can rerun it.

Query latency #

Median of 10 warm runs per query (each path was also run 10 times cold, with caches cleared and processes restarted before every run; the full cold and warm results, with spreads, are in the lab's RESULTS.md):

¹ With duckdb.max_memory lowered to 2 GB and scan parallelism raised to 4. With the default 4 GB per connection, Q4 completed on every fresh connection, but repeating it in one session got the backend killed for exceeding the container's memory limit, and Postgres restarted and dropped every connection.

Three results stood out:

• pg_duckdb on heap tables isn't a general accelerator. It was slower than plain Postgres on the simple scans (Q1, Q2), about level on Q3 and the join (Q6), and faster only where Postgres is weakest: high-cardinality aggregation (Q4, about 2.6x) and distinct counts (Q5, about 3x). Every plan's leaf was a Postgres scan of row-format heap pages, and that scan, not DuckDB's engine, set the pace.
• The same engine over columnar data is fast. On a Parquet copy, pg_duckdb was about 5x to 57x faster than on the heap and within 2x of pg_clickhouse on every query. pg_lake, also DuckDB, matched or beat it on Q1, Q2 and Q4 once its local file cache was warm (it was fastest of all on Q2) but was slower on the distinct count, the filtered query and the join. Those speedups come from changing the storage format, so they depend on keeping a second copy of the data, just like replicating to ClickHouse.
• pg_clickhouse pushed all six queries down to ClickHouse, including the join, count(DISTINCT) and date_trunc, and was the fastest path warm on Q1, Q3 and Q4, and on all six queries cold. On Q5 and Q6 it and pg_duckdb over Parquet were effectively tied, within each other's run-to-run spread.

Effect on transactional throughput #

We ran pgbench (16 clients) against each Postgres instance twice: a read-only test (pgbench -S, 60 seconds) and the default read-write TPC-B test (120 seconds). Each ran alone and with a single session looping Q3 and Q4 through each path. At baseline both tests kept Postgres's four cores busy, so they measure CPU contention. The read-write baseline ran twice on each instance and repeated within 1.5%; the read-only baseline ran once:

pg_duckdb and pg_lake cost transactions about as much as running the same analytics on plain Postgres, or more. pg_lake's separate process kept DuckDB's memory out of the Postgres process (Postgres stayed at about 1 GB, the same as its baseline), but pgduck_server ran on the same cores and took 1.5 to 2.2 of the 4, so throughput fell further. In fairness to both, they turned that CPU into more analytical work than plain Postgres did: 8 and 62 Q4 runs against 4 in the read-write test.

pg_clickhouse left transactional throughput essentially unchanged in both tests while completing the most Q4 runs, because the scans and aggregations ran on ClickHouse's cores, not Postgres's. With pg_duckdb at its default memory setting, the read-write test didn't finish: the second Q4 in the session got its backend killed and Postgres dropped every connection.

Why where the compute runs matters #

An embedded or same-host engine changes how efficiently Postgres executes an analytical query. It doesn't change how many resources the server has. A vectorized engine might finish a scan ten times faster, but while it runs it's using cores, memory bandwidth and I/O that your transactional queries also need, and Postgres has no built-in way to give transactions priority over that work. pg_duckdb and pg_lake both expose memory and thread caps. pg_lake's docs recommend limiting pgduck_server on servers with a busy Postgres workload, and MotherDuck recommends keeping pg_duckdb off the primary.

There are three common ways to handle that:

1. Cap the engine. Set duckdb.max_memory and duckdb.threads, or --memory_limit and DuckDB threads for pgduck_server. This protects transactions by making analytics slower.
2. Run it on a read replica. This isolates the primary, but the replica is still one machine, sized for peak analytical load, and it lags the primary.
3. Run analytics on a separate engine. pg_clickhouse sends the pushed-down work to ClickHouse, which can scale out across nodes and which you size for analytics alone. Postgres keeps planning the query and converting results, but the scans and aggregations happen elsewhere.

The third option costs a second system and replication lag. In exchange, adding analytical capacity no longer means resizing your transactional database.

Choosing between them #

If you need one rule: keep analytics inside Postgres while it has headroom, and move them out once analytical queries start to cost you transactional latency or once one server isn't enough.

If you want the last two without assembling the pieces yourself, ClickHouse Managed Postgres bundles Postgres, CDC into ClickHouse and pg_clickhouse in one ClickHouse Cloud organization. You still choose which tables to replicate and import as foreign tables.