AI Engineer Summit 2023

Supabase Vector: The Postgres Vector database

Paul Copplestone· Co-founder & CEO, Supabase16:05

Loading slides…

Read the talk

Supabase Vector: The Postgres Vector database

A community contribution helped turn Supabase’s broken documentation search into Clippy. The broader story is how vector search combines with Postgres indexes, partitions, access rules, and operational data.

From a talk by Paul Copplestone

Vector search inside the application database

An application that stores users, files, and embeddings needs more than a similarity-search endpoint. It also needs identities, storage metadata, and access rules. Paul Copplestone begins with the backend around those requirements: each Supabase project receives a full Postgres database, and users managed through its authentication service live inside that database.

The surrounding services connect to the same foundation:

  • Compute. Deno-powered edge functions run application logic and can be triggered from the database.
  • File storage. Large files live outside Postgres, but their directory metadata lives inside it, where access rules can use it.

Realtime adds subscriptions to database changes and live interactions such as cursor movements. Embedding storage follows through pgvector, a Postgres extension. Vector search becomes another capability of the application database, alongside the services an application already needs.

The completed Supabase stack: Database, Authentication, Edge Functions, Storage, Realtime, and Vector.Open full source frame
The completed opening stack includes Database, Authentication, Edge Functions, Storage, Realtime, and Vector.

This also reflects Supabase’s approach to open source. Copplestone describes operating openly since 2020, using MIT, Apache 2, and Postgres licenses, and supporting existing communities. pgvector was an existing project to adopt and contribute to, rather than a component Supabase needed to invent.

0:450:58
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

0:45 · section reference included

A contribution becomes documentation search

The adoption story starts with an email from Greg asking what it would take to add pgvector to Supabase. He had already done the implementation work and supplied a pull request. After a call, Copplestone followed up the next day: the extension was merged and expected to reach production that week. The same message raised an immediate application problem—Supabase’s documentation search was broken—and asked whether Greg wanted to help.

Roughly two weeks later, Supabase released Clippy, a deliberate nod to Microsoft’s assistant. Copplestone recalls that the team did not know of another documentation interface doing this at the time. They then packaged the example as a reusable template, so other developers could build similar search interfaces for their own documentation. The contribution had become both a working feature and a starting point for other applications.

2:322:43
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

2:32 · section reference included

Adoption and the contributor feedback loop

Copplestone points to Mozilla’s MDN implementation as a prominent example of this documentation-search approach spreading. Supabase’s growth chart covers newly launched databases on the hosted platform, excluding independently operated open-source deployments. He describes pgvector as one tailwind behind that growth, with Supabase fitting into the Vercel, Netlify, and Jamstack ecosystem. At the time of the talk, Copplestone reported about 12,000 new hosted databases per week and estimated that 10–15% used pgvector. Those figures describe database creation, rather than a count of distinct AI applications.

One application reportedly reached one million users in ten days after taking three days to build. For Copplestone, this was a surprisingly literal expression of the ambition to build over a weekend and then scale to millions.

The conference application itself used Supabase. Greg, meanwhile, had joined the company and led the preceding day’s workshop, completing the arc from community contribution to product development. Copplestone thanks him to applause before introducing the story’s speed bump: a public challenge to pgvector’s performance.

3:434:01
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

3:43 · section reference included

An index change alters the benchmark

A tweet cited in the talk claimed that pgvector was 20 times slower than Qdrant and 18% worse at finding relevant documents. The displayed throughput chart measured queries per second, with higher values better, and its Postgres entry used IVFFlat. The talk does not establish the workload or what the critic’s 18% figure measures.

Copplestone first separates the extension’s authorship from Supabase’s involvement. Andrew Kane developed pgvector years before Supabase adopted it; Supabase was a contributor. The response to the criticism was further contribution: Kane, Supabase, AWS, and other collaborators worked on adding HNSW. The talk describes roughly one month of development.

The replacement chart uses Postgres HNSW, drawing cheers from the audience. Copplestone immediately qualifies the comparison: he is not trying to attack Qdrant, and isolated benchmarks do not establish a universal database ranking. Within this comparison, however, changing the index improved both throughput and reported accuracy. In the follow-up comparison, Postgres HNSW and Qdrant both had a reported accuracy value of 0.99. The presentation does not define that accuracy measure.

5:375:53
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

5:37 · section reference included

Compare performance under a stated budget

The next comparison addresses an obvious possibility: perhaps better results simply came from spending more on compute. Supabase’s October 2023 pgvector–Pinecone study supplies the workload behind this part of the talk. The study uses one million 1,536-dimensional dbpedia embeddings, inner-product distance, and clients in the same region.

Copplestone notes the difficulty of benchmarking Pinecone and assessing its accuracy. The chart compares six Pinecone p1.x2 replicas at $480 with an eight-core, 32 GB Postgres HNSW instance at $410. For that workload, the slide reports 780 queries per second at accuracy 0.99 for Postgres HNSW, versus 185 queries per second at accuracy 0.98 for Pinecone.

The talk’s historical comparison names both the configuration and the compute budget.Open full source frame
The talk’s historical comparison names both the configuration and the compute budget.

This approximately matches spending; it does not establish identical hardware or resource allocations. The chart makes a specific cost-and-performance argument under the study’s workload. Its prices and results belong to that historical vendor comparison, rather than a current purchasing recommendation.

7:227:31
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

7:22 · section reference included

From trash photos to good and bad cats

The architectural argument becomes more concrete through a conversation with Joseph, Roboflow’s CEO. An application let users photograph trash in San Francisco, upload the images into an embedding store, and track trends across the city. Like the PaintWTF example discussed earlier at the conference, it faced unwanted, potentially unsafe uploads. Those images consume storage, add work to the search index, and may become visible to users.

Copplestone’s small Postgres proof of concept used partitions to separate wanted and unwanted records. Trash makes for a dull demonstration, so he substitutes cats: good cats and bad cats. Each uploaded embedding is compared with a canonical image, his space cat. The proposed rule sends scores greater than 0.8 to good cats and everything else to bad cats.

The comparison lives in a Postgres function named is_cat. Copplestone generates an embedding for the space-cat image and places that reference vector inside the function. An incoming embedding becomes the argument; a floating-point similarity becomes the result. Despite its name, is_cat returns a score, not a Boolean. The single reference image makes the storage mechanism easy to follow, without establishing a reliable moderation classifier.

8:118:22
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

8:11 · section reference included

Store similarity when a record arrives

The initial table stores three useful pieces of information: the image embedding, the image URL, and the computed similarity. A trigger attaches the computation to insertion, so each incoming row receives its score as part of being stored. This first step concerns an ordinary table; partition routing comes afterward.

The slide calls the trigger check_is_cat and the table cats. It sketches assigning is_cat(new.embedding) to new.is_cat before insertion. Its inline trigger body is abbreviated: PostgreSQL expresses this operation through a separate trigger function.

The slide sketches computing a cat-similarity score before inserting the row.Open full source frame
The slide sketches computing a cat-similarity score before inserting the row.

For an unpartitioned table named cat_uploads, with columns embedding and similarity and an existing is_cat comparison function, the pattern can be expressed in SQL:

sql

CREATE FUNCTION set_cat_similarity()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.similarity := is_cat(NEW.embedding);
  RETURN NEW;
END;
$$;

CREATE TRIGGER score_cat_before_insert
BEFORE INSERT ON cat_uploads
FOR EACH ROW
EXECUTE FUNCTION set_cat_similarity();

NEW represents the row about to be inserted. Assigning its similarity field adds the comparison result to that row before storage. After a batch of uploads, the result is a table of embeddings and image URLs with their corresponding scores—the information needed to separate the records.

10:3110:42
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

10:31 · section reference included

Index accepted records and discard rejected records together

Partitions turn the stored scores into an operational distinction. Each partition can have its own index and storage lifecycle, allowing the proposed layout to treat the two groups differently:

OperationGood cats: acceptedBad cats: rejected
Vector indexingCreate and maintain a search indexLeave these records unindexed
StorageKeep accepted records grouped togetherKeep rejected records separately until cleanup
CleanupRetain searchable recordsDrop and recreate the rejected partition

Rejected uploads still consume storage until cleanup. The useful separation is that they need not enlarge the accepted records’ vector index, and they can be removed together.

The later table definition partitions by range on the stored similarity column, named is_cat in that example. The good-cats partition has bounds from 0.8 to 1; a default partition receives everything outside that range. A small amount of SQL expresses the layout, but its exact bounds determine where records land.

Two PostgreSQL 16 partitioning rules matter when implementing this design:

  • Range boundaries. Lower bounds are inclusive and upper bounds exclusive. The range [0.8, 1) accepts exactly 0.8 but sends exactly 1 to the default partition. This differs from the earlier spoken greater-than rule, so the intended treatment of both boundary scores needs an explicit decision.
  • Routing order. A BEFORE ROW INSERT trigger cannot change the destination partition. Calculate the similarity key before submitting the row to the partitioned table; the earlier trigger pattern for an unpartitioned table cannot simply be used to redirect incoming rows.

The demonstration leaves open whether this is the best solution to the original image problem. Its appeal is that the necessary primitives—functions, triggers, partitions, independent indexes, and bulk cleanup—are already available in Postgres. The vector operation can participate in ordinary database operations.

11:3611:51
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

11:36 · section reference included

Access control and fewer round trips

The broader argument rests on Postgres’s accumulated engineering and extensibility. The talk invokes roughly three decades of database development: an AI application can reuse those primitives instead of building them around a separate embedding store. pgvector itself illustrates the extension mechanism. The talk recalls integrating pgvector into Supabase in approximately two days. It could become a database capability without first becoming part of Postgres core.

For retrieval-augmented generation over user data, row-level security supplies another useful primitive. Declarative policies on database tables can express which rows a user may access, separating one user’s records from another’s. For roles subject to those policies, enforcement remains in the database even if an application or API check fails. That provides defense in depth where the retrieved data actually lives.

Keeping embeddings beside operational data also changes the retrieval path. A query can fetch matching records and their application data from the same database, potentially using one round trip instead of coordinating multiple services. An isolated vector-search benchmark can miss this benefit because it measures the search operation rather than the whole application request.

The talk speculates that vector functionality might eventually enter Postgres core, while treating that prospect as uncertain. It also leaves room for specialized vector databases, particularly where they offer capabilities such as bringing models closer to stored data. The narrower argument for Postgres concerns storing embeddings, indexing them, and fetching them alongside the rest of an application’s data.

12:5713:12
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

12:57 · section reference included

Citus sharding and the next scale target

The closing development target is enterprise use involving billions of vectors. Supabase was exploring sharding with Citus, another Postgres extension, to distribute data across nodes. The talk reports linear transaction scaling as Citus nodes were added, without specifying the workload or node counts. This describes an observation during development toward larger deployments, rather than a demonstrated guarantee for arbitrary billion-vector workloads.

Supabase was discussing the work with Microsoft’s Citus team and seeking design partners, especially organizations already storing billions of embeddings. For developers starting with a database, the talk’s entry point is database.new.

14:5915:14
Suggest correction

This note stays in this page until you copy or download it. Nothing is submitted; reloading clears the draft.

14:59 · section reference included

Resources

From the talk

Transcript download
Full transcript

Loading transcript…