There is a big movement going on in the Local First circles to give users greater control over their data, greater performance and resilience. They want to do this by building applications that need less access to external servers and even the network at times.

One of the major breakthroughs in recent years which is making this goal more accessible to a lot of applications is the ability to use the power of well established databases within the Browser. Wayback, we saw WebSQL appear in browsers but never gained traction because of licensing and compatibility across all browsers. This was a real shame.

The appearance of IndexedDB was a welcome addition to the space and has served us well. It was designed as a low-level implementation but again, I don’t think it has given the boost we expected due to the ergonomics of using it. It also seemed to lack some of the power many expect from working with longstanding Relational databases. Having said that, there has been a significant rise in its use over the last few years as a storage for in-browser data managers and in-memory query engines.

All this background brings us to a major change which is happening in the browser data storage landscape. With the introduction of WebAssembly we are seeing a number of new options for us to consider. These include well loved relational databases like SQLite and now Postgres in the form of PGLite.

What is a vector database

A Vector Database is a specialised feature of a database which allows efficient storage and indexing of long series of numbers, called vectors. A Vector in this context is referring to a vector space where numbers represent data in a multi-dimensional space. The position of each number within the series represents a property or a feature of the object the vector has been derived from. The database then allows us to do exact and approximate nearest neighbour searches to find records similar to the search term.

This is a lot to wrap your head around at first. It is often useful to understand this while considering a simplified vector representing a set of images. Let's consider the following few images and next to each store a 4-dimensional vector, each representing a potential property of the image. The higher the number stored in each dimension indicates the relevance of that property to the contents of the image.

It's interesting that by considering these numbers, you can probably build up a picture of the original image in your mind.

Now consider that these vectors might have thousands or even millions of dimensions, this allows us to use a Vector Database to store the semantic meanings of an object as well as compare it against others in an efficient manner.

What options are available with SQLite and PGLite

Let's consider two common options for using a relational database with nearest neighbour search on vector embeddings.

PGLite with pgvector

The pgvector project has been going since 2021, is well supported and works on the server as well as in the browser with WebAssembly.

The following shows a simple example of how to query the embeddings in the database.

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));
INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

SQLite with sqlite-vec

The sqlite-vec project has been going since 2024, it is relatively new but has a good set of companies backing it, including Mozilla. It is pre v1 at the time this is being written so one to play with and watch.

Here is a query similar to the above based on the example in the Github Repo.

create virtual table items using vec0(
  embedding float[3]
);

-- KNN style query
select
  rowid,
  distance
from items
where 
  sample_embedding match '[0.890, 0.544, 0.825]'
order by distance
limit 2;
/*
┌───────┬──────────────────┐
│ rowid │     distance     │
├───────┼──────────────────┤
│ 2     │ 2.38687372207642 │
│ 1     │ 2.38978505134583 │
└───────┴──────────────────┘
*/

Taking a simple example of embeddings model which has been generated with 7 vectors, one for each day of the week. The model would calculate these vectors on past data and will result in each vector being a number between 0 and 1 where the closer these vectors are to 1 the more relevant that day is for a user on the system.

select * from lessons

Searching for the user best suited for an event on Wednesday might be done in one of two ways.

1. Selecting the rows ordered by the distance
2. Selecting the distance itself

Of course we could mix these two together, but let's consider them separately for the moment. To get the five closest results for a vector which matches Wednesday the most we can use [0,0,0,1,0,0,0]. pgvector adds a few distance functions, the <-> which gives us L2 distance on this multi-dimensional array of numbers.

SELECT * FROM lessons ORDER BY embedding <-> '[0,0,0,1,0,0,0]' LIMIT 5;

Selecting the distance itself allows us to see the result of the calculation. This is useful if we want to filter out results if they are not close enough.

SELECT l.user, l.embedding <-> '[0,0,0,1,0,0,0]' as distance from lessons l order by distance LIMIT 5;

• "Vector Databases: They are single-purpose DBMSs with indexes to accelerate nearest-neighbor search. RM DBMSs should soon provide native support for these data structures and search methods using their extendable type system that will render such specialized databases unnecessary" - Stonebraker & Pavlo, 2024. https://db.cs.cmu.edu/papers/2024/whatgoesaround-sigmodrec2024.pdf
• https://dev.to/jonbiz/implementing-a-rag-system-inside-an-rdbms-sqlite-and-postgres-with-sqlite-vec-pgvector-4d5h
• Vector Spaces Explained
• Euclidean_distance