Support for pgvector

[liemng]

Feature Request

Is your feature request related to a problem? Please describe

Support for pgvector.

Describe the solution you'd like

Support for pgvector similar to Hibernate: https://docs.jboss.org/hibernate/orm/6.4/userguide/html_single/Hibernate_User_Guide.html#vector-module

[mp911de]

How exactly you envision support for PG Vector with a library that requires you to provide SQL?

[davecramer]

@mp911de how do you support custom types today? The only "support" that is possible in pgjdbc is to allow sending and receiving the vector in binary. Is that possible ? Pretty significant performance possible with binary mode.

[mp911de]

We have a Codec API that can be extended by the driver or any external Codec

r2dbc-postgresql/src/main/java/io/r2dbc/postgresql/codec/BuiltinDynamicCodecs.java

Lines 55 to 65 in 543f5cc

	public Codec<?> createCodec(ByteBufAllocator byteBufAllocator, int oid) {
	switch (this) {
	case HSTORE:
	return new HStoreCodec(byteBufAllocator, oid);
	case POSTGIS_GEOMETRY:
	return new PostgisGeometryCodec(oid);
	default:
	throw new UnsupportedOperationException(String.format("Codec %s for OID %d not supported", name(), oid));
	}
	}

By parsing type/OID information, one could come up with any kind of wrapper that makes sense.

[davecramer]

Cool, so there are significant savings to be had by sending and receiving vectors in binary!

[liemng]

Yeah, exactly it, @mp911de. It would be nice to have a built-in codec for pgvector, given the popularity of LLMs nowadays.

[mp911de]

Thanks for clarifying. It sounded at first like an object mapper feature. Am 5. Dez. 2023, 18:21 +0100 schrieb Liem Nguyen ***@***.***>:

…

Yeah, exactly it, @mp911de. It would be nice to have a built-in codec for pgvector, given the popularity of LLMs nowadays. — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: ***@***.***>

[davecramer]

FWIW, I didn't implement it in pgjdbc, rather in pg_vector java support. I don't think it matters where it's implemented.

[mp911de]

That's in place now, including support for 1-dimensional vector arrays (array of vectors).

[mp911de]

Please give the new Vector codec a try, it's available from 1.1.0 and 1.0.3 snapshots via oss sonatype.

[davecramer]

cool!

[liemng]

Working as designed! @mp911de you rock! When is 1.0.3 release planned for?

[mp911de]

I would want to wait for today's Reactor release before shipping a new release.

[chrischall]

Apologies for the comment here vs. a bug report, but PostgreSQL 8.2 and below to not have pg_catalog.pg_type.typarray:
https://www.postgresql.org/docs/8.2/catalog-pg-type.html

AWS Redshift is based on PostgreSQL 8.0.2:
https://docs.aws.amazon.com/redshift/latest/dg/r_VERSION.html

As such, this driver can no longer be used to connect to AWS Redshift.

[mp911de]

Please downgrade to 1.0.2 for the time being. I filed #621 to fix the regression.