Dev48
Language
  • About
  • Services
  • Industries
  • Technologies
  • Articles
  • Contacts
Book a call
    Home/Articles/Pgclickhouse 0111 now with pgvector update delete pushdown
Dev48
info@dev48.org+375 29 320-40-96WhatsApp@isky_by

© 2026 · All rights reserved.

pg_clickhouse 0.11.1: Now with pgvector, UPDATE, DELETE pushdown

Source: ClickHouse•November 19, 2026 at 01:08 PM

pg_clickhouse 0.11.1: Now with pgvector, UPDATE, DELETE pushdown

pg_clickhouse 0.11.1 brings pgvector search and UPDATE, DELETE, and TRUNCATE pushdown, plus closer PostgreSQL compatibility, to ClickHouse Managed Postgres.

Don't let the minor version increment fool you: pg_clickhouse v0.11.1, released last week, delivers a slew of improvements and bug fixes, including UPDATE, DELETE, TRUNCATE, and pgvector search pushdown.

Let's start with the latter: type mapping. Given this ClickHouse table:

lets us map the v column to the vector type:

We use an explicit CREATE FOREIGN TABLE rather than IMPORT FOREIGN SCHEMA to map the ClickHouse Array(Float32) column to a PostgreSQL vector column. It works for ClickHouse Array(Float64) columns, too. Now we can insert vector array values:

And of course select them:

also pushes down a number of operators and functions, including, most simply, =, visible in the "Remote SQL" line from EXPLAIN (VERBOSE):

The results come as no surprise:

Even better, pg_clickhouse pushes down the pgvector distance operators. For example, <-> pushes down to the ClickHouse L2Distance function once again visible in the "Remote SQL" line from EXPLAIN (VERBOSE):

And the results:

The complete list of operators and functions that push down to ClickHouse functions:

Sharp readers will have noticed the "Vector" lines in the plan output:

That Vector Index Not Used line clearly tells us the query used no vector similarity index. But could it use one? Let's add an index and see:

Use the L2 distance operator again:

Et voilà! It now detects that the ClickHouse table has a L2Distance() index that the ClickHouse executor may have used for the query. The results remain unchanged:

pg_clickhouse detects and reports the ClickHouse usage of vector_similarity indexes using the similarity functions that correspond to the supported operators (cosineDistance, L1Distance, etc.)

also introduces support for UPDATE, DELETE, and TRUNCATE statements on MergeTree family tables. It pushes down UPDATE and DELETE statements as lightweight UPDATE or DELETE statements, respectively, and TRUNCATE statements as TRUNCATE statements, naturally.

Let's put them to work on the items table. First, UPDATE requires a couple of ClickHouse settings to be enabled:

And now we can make updates:

Check our work:

Now DELETE:

Is it gone? Yup:

Do be aware that PostgreSQL UPDATE and DELETE, unlike ClickHouse's, do not require WHERE clauses. In the absence of a WHERE clause, pg_clickhouse inserts WHERE true to match the PostgreSQL behavior. But you surely don't want to update or delete every row in a ClickHouse table! As the docs say:

Lightweight updates are designed to update small amounts of rows (up to about 10% of the table).

Lightweight updates are designed to update small amounts of rows (up to about 10% of the table).

Instead of deleting every row, use TRUNCATE:

All gone:

also adds pushdown for the regr_* family of linear regression aggregate functions on ClickHouse 26.9 or later, when ClickHouse added them. These include:

  • regr_avgx
  • regr_avgy
  • regr_count
  • regr_intercept
  • regr_r2
  • regr_slope
  • regr_sxx
  • regr_sxy
  • regr_syy

Example:

The appearance of regr_avgyOrNull() in the previous sample output spoils the news, but to be clear, this release also teaches NULL-returning aggregates to actually return NULL rather than a ClickHouse default value (e.g., 0) when they aggregate no values. Rather than sum, avg, and min, etc., pg_clickhouse now pushes down sumOrNull, avgOrNull, and minOrNull, thus matching Postgres behavior:

JSONB subscript syntax, data['a']['b'], now pushes down to ClickHouse column "dot notation":

On ClickHouse 23.6+, pg_clickhouse now always sets session_timezone to match the PostgreSQL TimeZone, and passes the PostgreSQL TimeZone to DateTime functions, thus ensuring compatibility of timestamp and timestamptz values:

For bare numeric columns, pg_clickhouse now uses Decimal(76, 38), the largest possible ClickHouse decimal size, and raises an error for longer values. It also properly trims trailing zeros from such values, thanks to a community contribution:

Running this query, the output was previously:

But now is:

These changes cover the highlights; peruse the for all the things. And of course pg_clickhouse v0.11.1 is available on all new and existing ClickHouse Managed Postgres servers today. As a minor release all installs benefit from its improvements without any action. But do run ALTER EXTENSION pg_clickhouse UPDATE; to upgrade from v0.10 or earlier.

← All articles

What this article says

More in Data & Analytics

All →
CMMC vs. NIS2: Two paths to the same secure data destination
Collibra

CMMC vs. NIS2: Two paths to the same secure data destination

Smarter Pipelines with Trusted Metadata
Matillion

Smarter Pipelines with Trusted Metadata

What’s New in the Data Productivity Cloud: July 2025
Matillion

What’s New in the Data Productivity Cloud: July 2025

How Chain, Our AI Workspace, Helps Investigators Fight Crime
Chainalysis

How Chain, Our AI Workspace, Helps Investigators Fight Crime

How Datadog resolves evolving OpenTelemetry semantics at query time
Datadog

How Datadog resolves evolving OpenTelemetry semantics at query time

Retain more logs for longer with Flex Logs BYOBucket
Datadog

Retain more logs for longer with Flex Logs BYOBucket

More from ClickHouse

Detect, act, keep the evidence: node reliability across three clouds
ClickHouse

Detect, act, keep the evidence: node reliability across three clouds

Why Front migrated from Elasticsearch to ClickHouse for “pixel-perfect” analytics at scale
ClickHouse

Why Front migrated from Elasticsearch to ClickHouse for “pixel-perfect” analytics at scale

Trunk cuts observability costs by up to 95% and reclaims 20 engineering hours a week with ClickHouse Observability
ClickHouse

Trunk cuts observability costs by up to 95% and reclaims 20 engineering hours a week with ClickHouse Observability

The day the sky emptied: rendering the FAA outage with ClickHouse FORMAT PNG
ClickHouse

The day the sky emptied: rendering the FAA outage with ClickHouse FORMAT PNG

More load is faster: tuning Postgres read replicas
ClickHouse

More load is faster: tuning Postgres read replicas

Bring your Claude Platform organization to ClickHouse Cloud with linked billing
ClickHouse

Bring your Claude Platform organization to ClickHouse Cloud with linked billing