Vector databases have surged in popularity, especially with the rise of AI and semantic search applications. Rather than building a database from scratch, we can extend an existing, robust system like PostgreSQL. Thanks to its well-documented extension API and strong ecosystem—exemplified by projects like pgvector—PostgreSQL is an ideal foundation for adding vector caapbilities.

At a minimum, a vector database must:

• Store fixed-length numeric vectors alongside other data.
• Support efficient similarity searches using Approximate Nearest Neighbor (ANN) algorithms.

In SQL terms, this translates to defining a new data type and an operator for similarity comparison. For example:

-- Define a table with a 3-dimensional vector column
CREATE TABLE items (id bigserial PRIMARY KEY, vec float4[3]);

-- Insert sample vectors
INSERT INTO items (vec) VALUES ('{1,2,3}'), ('{4,5,6}');

-- Find the 5 most similar vectors to [3,1,2] using cosine distance
SELECT * FROM items ORDER BY vec <=> '{3,1,2}' LIMIT 5;

To enable this behavior in PostgreSQL, we need to:

1. Implement a custom vector data type that enforces fixed dimensionality.
2. Define a binary operator (<=>) that computes similarity (e.g., cosine distance or Euclidean distance) between two vectors.

Development Setup

We’ll use Rust and the pgrx framework, which simplifies PostgreSQL extension development by providing safe abstractions over the C-based extension API.

First, install Rust via rustup, then add the cargo-pgrx subcommand:

$ cargo install --locked cargo-pgrx
$ cargo pgrx --version

Next, initialize a PostgreSQL instance managed by pgrx. We’ll use PostgreSQL 17:

$ cargo pgrx init --pg17 download

This command downlodas and compiles PostgreSQL 17 from source, so ensure your system meets the build dependencies.

Scaffolding the Extension

Create a new extension project:

$ cargo pgrx new pg_vector_ext
$ cd pg_vector_ext

The generated structure includes:

• Cargo.toml: Rust manifest
• pg_vector_ext.control: Extension metadata
• src/lib.rs: Main extension logic

Edit Cargo.toml to set the default feature to pg17:

[features]
default = ["pg17"]
pg12 = ["pgrx/pg12"]
pg13 = ["pgrx/pg13"]
pg14 = ["pgrx/pg14"]
pg15 = ["pgrx/pg15"]
pg16 = ["pgrx/pg16"]
pg17 = ["pgrx/pg17"]

The initial src/lib.rs contains a simple test function:

use pgrx::prelude::*;

::pgrx::pg_module_magic!();

#[pg_extern]
fn hello_pg_vector_ext() -> &'static str {
    "Hello, pg_vector_ext"
}

Build and launch the extension in a temporary PostgreSQL instance:

$ cargo pgrx run

Inside the psql prompt, create the extension and test the function:

pg_vector_ext=# CREATE EXTENSION pg_vector_ext;
CREATE EXTENSION
pg_vector_ext=# SELECT hello_pg_vector_ext();
 hello_pg_vector_ext
----------------------
 Hello, pg_vector_ext
(1 row)

This confirms our development environment is ready. The next step is to define the vector type and implement storage and comparison logic.