Technology1 min read

pg_clickhouse 0.11.1: Now with pgvector, UPDATE, DELETE pushdown

By · Published by Everything Blog

In short

The pg_clickhouse extension now maps ClickHouse float arrays directly to PostgreSQL vector columns via explicit foreign tables. Users can run native vector operations, insertions, and queries through PostgreSQL while delegating heavy similarity calculations directly to the remote engine. Additionally, version 0.11.1 implements full pushdown support for UPDATE, DELETE, and TRUNCATE statements alongside routine bug fixes.

Key points

  • Users can directly insert vector array values and query them through standard PostgreSQL…: Users can directly insert vector array values and query them through standard PostgreSQL interfaces.
  • Delegating vector operators and functions allows queries to leverage native ClickHouse ve…: Delegating vector operators and functions allows queries to leverage native ClickHouse vector similarity indexes.
  • General maintenance improvements and bug fixes are bundled into the 0.11.1 release of the…: General maintenance improvements and bug fixes are bundled into the 0.11.1 release of the extension.

Sources checked by our analysis pipeline: 1

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.

pgvector type mapping

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

CALL clickhouse_perform('ch_server', $$

CREATE TABLE items (

id Int32,

category String,Array(Float32)

v = MergeTree ORDER BY (id);

) ENGINE $$);

pg_clickhouse v0.11.1 lets us map the v

column to the vector

type:

CREATE EXTENSION vector;

CREATE FOREIGN TABLE items (

int NOT NULL,

id NOT NULL,

category text 4) NOT NULL

v vector( ) SERVER ch_server;

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:

INSERT INTO items VALUES (0, 'a', '[0,0,0,0]');

INSERT INTO items

SELECT i, (ARRAY['a', 'b', 'c'])[i % 3 + 1],

'[%s,%s,%s,%s]', sin(i), cos(i), sin(i * 2), cos(i * 3))::vector

format(FROM generate_series(1, 100) i;

And of course select them:

SELECT * FROM…

Technology