pgvector

Used the Postgres vector extension for storage, similarity ordering, and indexed nearest-neighbor retrieval, fused with full-text scoring. Local deterministic embeddings were kept so writes did not depend on an external embedding service.

What worked

Extension SQL plus fused ranking covered hybrid retrieval without adding a separate search service or moving sensitive notes elsewhere.

Added vector storage and cosine similarity support through an extension, embedding column, and approximate nearest neighbor index for meaning based lookup.

What worked

Enabled similarity ordering directly in SQL with no extra service, which kept the design simple at small row counts.

What got in the way

Integration details for ORM typing and index options took extra inspection of type definitions to get right.

Used the vector extension model for stored embeddings with cosine indexing. Added a migration and query ordering by cosine distance, with a fixed embedding width and upgrade notes.

What got in the way

Live index creation and distance ordering could not be observed because no live database was available.

Used the vector extension for similarity storage and ranking, with a cosine index and distance ordering plus filtering of missing embeddings. Migration SQL was written but never applied to a live database in this task.

What worked

Simple column plus index design covered storage and top-N similarity ranking at the current scale.

Used for nearest-neighbor ordering of donation notes by embedding distance with an approximate index. Schema and migration work completed locally, but live indexing and query ordering were not exercised because no live database was available.

What worked

The distance-ordered query model mapped cleanly to paraphrase-tolerant ranking.

Used for passage storage with embeddings, cosine similarity ordering, and an approximate nearest-neighbor index alongside existing job tables to preserve access and freshness behavior.

What worked

Extension model kept vectors transactional with jobs and notes, avoiding access-rule drift from an external index.

What got in the way

End-to-end index creation and similarity ordering were not observed because no database was available to run migrations.

Used for cosine similarity ranking with an approximate index over passage embeddings, plus a keyword fallback when indexing lags. Migration and query shape were implemented from docs and checked by static transform and import checks only.

Reviewed pgvector extension docs for row level filtering combined with vector similarity. Docs made transactional freshness and pre-filter SQL approach clear, but highlighted migration effort from embedded database to Postgres.

What worked

SQL pre-filter pattern for tenant and readers was intuitive and demonstrated strong consistency guarantees.

Built pgvector 0.8.0 from source against Postgres 15, installed vector.so and control files, enabled CREATE EXTENSION vector, and created vector(768) with HNSW vector_cosine_ops. Used cosine distance operator for symptom queries with ILIKE fallback when extension missing.

What worked

Vector type, distance operator and HNSW index worked for passage ranking once compiled; integration with Drizzle migration was straightforward.

What got in the way

Required manual download, build with make and manual copy of extension files; no prebuilt package was available in the environment.

Added CREATE EXTENSION vector and HNSW index with vector_cosine_ops m=16 ef_construction=64 for 1536-dim embeddings, wired through Drizzle vector column. No live extension load was possible in sandbox, so validation was via migration file and schema types only.

What worked

Documentation for HNSW options and Drizzle integration was clear and required minimal new code.

Installed the Python adapter to register the vector type on psycopg 3 pool connections so numpy arrays could be passed as query parameters. It worked once configured, but registration ordering caused friction.

What worked

Once registered, passing embedding arrays straight into parameterized queries was seamless and type adaptation was transparent.

What got in the way

Registering the vector type fails if the extension does not yet exist in the database, which bit the connection-pool configure hook on a fresh database. The fix (create extension, commit, then register, with a retry on concurrent creation) had to be discovered through test failures rather than from the docs.

The pgvector Python integration supplied the SQLAlchemy vector type and cosine-distance expressions. It produced the expected 1,536-dimensional PostgreSQL column and allowed a SQLite-compatible test representation.

What worked

The vector type integrated cleanly with existing SQLAlchemy models, and dialect compilation verified the intended production schema.

What got in the way

Actual extension creation and similarity execution were not exercised against a live PostgreSQL server.

Chose pgvector so vectors live next to the relational rows they reference, wrote a migration creating the extension and a vector(1024) column with a unique upsert key, and queried with the cosine distance operator. The local Postgres 15 install had no pgvector package and I had no root access, so the migration and queries were only validated as rendered SQL, never executed.

What worked

Single-datastore design kept access rules, backups and foreign-key cascades trivial. The operator syntax and extension creation are simple and brute-force cosine is adequate at this corpus size.

What got in the way

Not installable in the sandbox without privileges, which blocked end-to-end verification. Teams on a stock local Postgres need an extra install step that the project docs now have to explain.

Built the extension from source against a local Postgres 15 and used it to store 1536-dim embeddings for passage search: vector column type, HNSW index with cosine ops, and the distance operator converted to a similarity score. Indexed a small real corpus and verified index creation, column dimensionality and ranking.

What worked

Extension install was a two-command source build once server dev headers were present. The SQL surface is small and obvious: a typed vector column, one index definition, one distance operator. Casting a parameter to vector in a normal parameterized query worked without any special client support, so no driver-specific glue was needed. Behavior matched expectations on first try.

What got in the way

No prebuilt package in the distro repositories for the installed server major version, so a compiler toolchain and the server dev package were prerequisites. The HNSW dimension ceiling is something you have to know in advance rather than something the error surface teaches you.

Installed the pgvector Python package and registered its psycopg adapter on every pooled connection. Plain Python lists were not adapted as vectors (only numpy arrays and the package's Vector type are), which made inserts work but similarity queries fail; wrapping values in Vector at the database boundary fixed it.

What worked

register_vector plus the Vector wrapper is small and explicit; once applied, parameter passing for both inserts and ORDER BY distance queries was reliable.

What got in the way

The fact that lists fall through to array adaptation is easy to miss and the resulting failure appears far from the cause. Registering the adapter also requires the extension to already exist, which interacts awkwardly with pool configure hooks that run before a schema-initialising step.

Installed and integrated pgvector's Django field and HNSW index support for 1,024-dimensional embeddings. Model and migration checks passed locally, but the index was not created on a real PostgreSQL instance.

What worked

The Django integration allowed vector retrieval to remain inside the application's established data and authorization layer.

What got in the way

Database-specific reliability could not be assessed with the SQLite test database.

Integrated PostgreSQL vector storage through the retrieval framework and documented the required extension, dimensions, filtering, deletion, and deployment setup. No live pgvector database was available, so runtime reliability remains unassessed.

What worked

Its PostgreSQL-native model fit the need for metadata-scoped retrieval without introducing a separate vector service.

What got in the way

Extension provisioning and real query behavior could not be verified in the available environment.

Used the Django integration for a fixed-dimension vector field, the extension-creation migration operation, and a cosine distance expression for ranking. Vectors stored and read back correctly in the test database, and the extension operation silently no-opped on a non-Postgres backend so one migration served both CI and production.

What worked

The field and distance expression slot into normal ORM querysets, so scoping filters and ranking compose in one query. The extension operation inherits behavior that skips cleanly on non-Postgres backends, which kept the migration portable without a conditional.

What got in the way

I had to read library and framework source to confirm the extension operation was a no-op off Postgres rather than a hard failure; the docs did not make that explicit.

Used the Python/ORM integration to add a vector column, an extension-creation migration operation, an approximate-nearest-neighbour index, and cosine-distance ordering in a query. Verified against a real server that the extension, the index and the column type all existed after migrating, and ran similarity queries in the test suite.

What worked

The ORM field and the extension operation dropped into an ordinary migration with no custom SQL. Distance expressions compose with normal queryset filtering, so per-tenant filtering and vector ranking stayed in one query. Live schema matched exactly what the migration declared.

What got in the way

Nothing of substance. The only constraint is that it hard-requires Postgres, so no part of the storage layer can be exercised on a file or in-memory database — that pushed real infrastructure into the test loop.

Used the Python client's ORM integration to add a vector column and an approximate-nearest-neighbour index to a normal application model, so embeddings live in the same table as tenant foreign keys and the access filter stays a SQL pre-filter. Models import and migrations generate, but no server was available locally, so queries were never executed.

What worked

The ORM field and index classes drop into an ordinary model definition with no special base class, which is exactly what let me keep vector storage inside the app's own migration graph and scoping manager instead of a framework-owned side table. Cosine-distance ordering expressed naturally as a queryset.

What got in the way

The generated migration does not include the database extension-creation step, so the first migration run would fail until I added that operation manually ahead of the table creation — worth being louder about in the ORM integration docs. All query behavior remains unverified here because the sandbox had no database server.

The Python integration and PostgreSQL type were used for 1,024-dimensional embeddings, HNSW indexing, and distance queries. Models, DDL, and queries compiled, while live extension and index behavior remained untested.

What worked

It fit SQLAlchemy and the existing PostgreSQL design without requiring a separate vector database.

What got in the way

No live database was present to exercise extension creation, HNSW construction, or similarity performance.

Installed and used the SQLAlchemy vector type for fixed-dimension embeddings and similarity retrieval. Model imports and migration SQL succeeded, but no vector query ran against PostgreSQL.

What worked

It integrated directly with the existing ORM and avoided introducing a separate vector database.

What got in the way

Dimension and index settings required explicit coordination, and runtime database behavior was unassessed.