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.












