# Supabase Vector: The Postgres Vector database

Paul Copplestone, Supabase

AI Engineer Summit 2023

[Talk page](https://ai.engineer/talks/MDxEXKkxf2Q-supabase-vector-postgres-vector-database) · [Video](https://www.youtube.com/watch?v=MDxEXKkxf2Q)

## 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.

### Vector search inside the application database

[0:45](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=45s)

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**](https://github.com/pgvector/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.](https://ai.engineer/api/talk-essay-media/e5aa99a9f4c62dc03e3a8a68f6e53976f95efcba5880ca3509d8da1ccfe5fcef)

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.

### A contribution becomes documentation search

[2:32](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=152s)

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.

### Adoption and the contributor feedback loop

[3:43](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=223s)

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.

### An index change alters the benchmark

[5:37](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=337s)

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.

### Compare performance under a stated budget

[7:22](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=442s)

The next comparison addresses an obvious possibility: perhaps better results simply came from spending more on compute. Supabase’s [October 2023 pgvector–Pinecone study](https://supabase.com/blog/pgvector-vs-pinecone) 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.](https://ai.engineer/api/talk-essay-media/5cb0b1a40fa5adbc365a601b9f11262d0fda2440ac4c7804069d858e1b2a57f3)

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.

### From trash photos to good and bad cats

[8:11](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=491s)

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.

### Store similarity when a record arrives

[10:31](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=631s)

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.](https://ai.engineer/api/talk-essay-media/6d316b3071ff255070211400e43d2e7ca7b8196d64321cd0a9a58fd7fa12b09e)

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.

### Index accepted records and discard rejected records together

[11:36](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=696s)

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:

| Operation | Good cats: accepted | Bad cats: rejected |
| --- | --- | --- |
| Vector indexing | Create and maintain a search index | Leave these records unindexed |
| Storage | Keep accepted records grouped together | Keep rejected records separately until cleanup |
| Cleanup | Retain searchable records | Drop 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](https://www.postgresql.org/docs/16/ddl-partitioning.html) 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.**

### Access control and fewer round trips

[12:57](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=777s)

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.

### Citus sharding and the next scale target

[14:59](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=899s)

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`.

## Resources

- [pgvector source and usage](https://github.com/pgvector/pgvector) — The independent Postgres extension at the center of the talk; current instructions include later improvements.
- [The 2023 Pinecone comparison](https://supabase.com/blog/pgvector-vs-pinecone) — The benchmark's datasets, configuration and historical compute budgets, needed to interpret the reported comparison.
- [Postgres 16 partitioning rules](https://www.postgresql.org/docs/16/ddl-partitioning.html) — The authoritative routing and boundary rules to check before implementing the talk's cat-partition concept.

## Slides

### Build in a Weekend, Scale to Millions

Supabase describes itself as a backend as a service: each project gets a full Postgres database and authentication. The slide names Y Combinator, Felicis, and Coatue as backers.

[0:42](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=42s) [Supabase tagline above logos for Y Combinator, Felicis, and Coatue.](https://assets.ai.engineer/sites/aiecode2025/sha256/68f362b439a3fbb1c46bafe8d8a2edb613236f75f6d7554b02b27856bc3624f2.webp)

supabase

Build in a weekend.
Scale to millions.

Backed by
Y Combinator
Felicis
COATUE

### GitHub Stars

Supabase’s GitHub stars rise sharply from 2020, approaching Redis’s leading line. MongoDB’s stars grow more gradually.

[2:11](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=131s) [Line chart comparing GitHub star growth for several projects, including Redis, Supabase, and MongoDB.](https://assets.ai.engineer/sites/aiecode2025/sha256/cb0346252119a36012ef96b735e15d295c42c72bf72e25c1b9d269ee55442067.webp)

GitHub stars

Y-axis ticks: 10.0k, 20.0k, 30.0k, 40.0k, 50.0k, 60.0k
Timeline: 2010, 2012, 2014, 2016, 2018, 2020, 2022

Redis
Supabase
MongoDB

### It started with an email

Supabase’s support for pgvector began with an email from Greg.

[2:31](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=151s) [The words “It started with an email,” with “email” highlighted in green.](https://assets.ai.engineer/sites/aiecode2025/sha256/c2fabdfc455004a088e681d8b588eaff6986b7316a980d8f17afb4d5ca602636.webp)

It started with an
email

### A request to add pgvector

Greg Richardson asked the team to accept pgvector, a Postgres extension for vector operations. He had already done the work and pointed to his pull request on GitHub.

[2:40](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=160s) [An email from Greg Richardson asks the team to accept the pgvector Postgres extension and says the work is already in a GitHub pull request.](https://assets.ai.engineer/sites/aiecode2025/sha256/9e5102551c340e4766d6f32b5417601916063848d29c87d16ff4585e5237bcac.webp)

15 Jan 2023, 08:37
Greg Richardson

I am sending this email to see what it would take for your team to accept a Postgres extension called pgvector. It is a simple yet powerful extension to support vector operations.

I have already done the work. You can find my pull request on GitHub.

### The extension is merged

After merging the extension, Paul Copplestone asked Greg to help with broken docs search. Two weeks later, they released Clippy as a docs search interface.

[3:01](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=181s) [Paul Copplestone replies that the extension is merged and asks Greg for help with documentation search.](https://assets.ai.engineer/sites/aiecode2025/sha256/28ff99b6bbfd119e939011ad62d3ed846207dd90beda4eb3a75f081e7a8ffa24.webp)

16 Jan 2023, 01:29
Paul Copplestone

hey Greg, the extension is merged so it should be landing in prod this week :)

btw, our docs search is currently a bit broken. is this something that you'd be interested in helping with?

### MDN AI Help announcement

Mozilla brought an AI documentation search interface to MDN as AI Help, developed with Supabase.

[3:45](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=225s) [Mozilla announces MDN AI Help, developed in collaboration with Supabase, alongside a QR code and an OpenAI graphic.](https://assets.ai.engineer/sites/aiecode2025/sha256/53a27128f96c8aca6e022bab83942fb710b0a774e81bbc04481c348e8768163f.webp)

moz://a

We're thrilled to announce the latest addition to MDN: AI Help. Developed in collaboration with our partners at Supabase.

### New databases per week on Supabase

New databases on Supabase.com climb to 12,000 per week by August 2023. The speaker calls pgvector a growth tailwind and estimates 10–15% of new databases use it. The chart excludes open-source databases.

[3:50](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=230s) [Bar chart showing weekly new databases rising to 12,000 by August 2023, with the addition of pgvector marked.](https://assets.ai.engineer/sites/aiecode2025/sha256/486c5fb9b24d71cd54a5b473821968080b8a38f2dd1b5d62747ce3e829a13ba9.webp)

New databases per week
on supabase.com

12,000
new databases
every week

pgvector
added

Timeline: AUG 20, FEB 21, AUG 21, FEB 22, AUG 22, FEB 23, AUG 23

### ClippyGPT documentation search

A projected video introduces ClippyGPT, Supabase’s documentation search. The speaker credits Greg, who now works at Supabase, with helping drive its growth.

[5:25](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=325s) [Conference stage with a projected YouTube page for a video about building Supabase documentation search.](https://assets.ai.engineer/sites/aiecode2025/sha256/f5c46d0fd829cd5a24a9214001ce73fbb82ac140407284b19eb94109ca81c49b.webp)

ClippyGPT - How I Built Supabase’s OpenAI Doc Search (Embeddings)

Supabase hired me to build ClippyGPT, their next-generation doc search where we can ask

Interface text:
YouTube
Search
Sign in
Rabbit Hole Syndrome
17.9K subscribers
Subscribe
4.8K
Share
Save
157K views 8 months ago

AI Engineer SUMMIT
Presented by AUTO GPT

### Initial pgvector benchmark

The chart shows Postgres IVFFlat at 23 QPS and Qdrant at 355 QPS, with reported accuracy of 0.82 and 0.99. The speaker disputes the accompanying post’s characterization of Supabase.

[5:35](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=335s) [A chart compares Postgres IVFFlat at 23 queries per second and 0.82 accuracy with Qdrant at 355 and 0.99; a post criticizes pgvector performance.](https://assets.ai.engineer/sites/aiecode2025/sha256/00470dbd929b0dd964619dd7fa886297714b32fb789d1bf5978194863a6a61b2.webp)

Y-axis ticks: 50, 100, 150, 200, 250, 300, 350, 400, 450, 500
QPS

Postgres (ivfflat)
23 (Accuracy = 0.82)

Qdrant
355 (Accuracy = 0.99)

Nirant
@NirantK

Why you should never use pgvector (e.g. @supabase Vector Store) for production:

😮 pgvector is 20x slower than a decent vector DB (e.g. @qdrant_engine)
🤯 And it's a full 18% worse in finding relevant docs for you

And this can happen at as little as 10K documents when chunked!

Interface text:
10:38 AM · Jun 28, 2023 · 206.2K Views
32  51  209  165

### Postgres vector ecosystem

The diagram places Andrew Kane at the foundation of the Postgres vector ecosystem. The speaker credits him with developing pgvector years before Supabase became a contributor.

[6:17](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=377s) [A hand-drawn structure labeled Postgres Vector ecosystem appears beside a portrait.](https://assets.ai.engineer/sites/aiecode2025/sha256/4b2e5cfb42fca90b62d8de9ae5e04c185a14281562ec27c18f9c3834d20fc10d.webp)

Postgres Vector ecosystem

### pgvector HNSW timeline

Supabase appears as the first cloud service, followed by AWS. After the June 28 ivfflat benchmarks, HNSW work ran from July 12 to its August 28 shipment.

[6:25](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=385s) [A 2023 timeline marks Supabase and AWS adoption, the June IVFFlat benchmark, HNSW development starting July 12, and shipment August 28.](https://assets.ai.engineer/sites/aiecode2025/sha256/2fea3a8b02dd41843921600dca906d4b9e5438212127836385495bd333ad84fd.webp)

pgvector HNSW Timeline

Oriole
aws

First cloud service
- Supabase

AWS

June 28:
ivfflat benchmarks

Jul 12:
HNSW Started

Aug 28:
HNSW Shipped

Timeline: 2023, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT

### Apples-for-apples benchmark

In this benchmark, Postgres HNSW reaches 780 queries per second at 0.99 accuracy, versus Pinecone’s 185 at 0.98. The compared configurations cost $410 and $480, respectively.

[7:26](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=446s) [Bar chart comparing Pinecone at 185 queries per second with Postgres HNSW at 780; the slide claims Supabase is four times faster for $70 less.](https://assets.ai.engineer/sites/aiecode2025/sha256/c2ce201fc863814f86804d68c06b7172f054c742d6f698bd02065f9fa42b3a6d.webp)

Apples-for-apples

Supabase is 4x faster than Pinecone for $70 less

Pinecone (p1.x2)
6 Replicas: $480
185 (Accuracy = 0.98)

Postgres (HNSW)
8-core / 32GB: $410
780 (Accuracy = 0.99)

Y-axis ticks: 100, 200, 300, 400, 500, 600, 700, 800, 900

### Why I’m bullish on Postgres + pgvector

The speaker introduces a San Francisco trash-photo app as an example of Postgres’s power: users take pictures and upload them to an embedding store.

[8:12](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=492s) [Title slide reading Why I’m bullish on Postgres + pgvector.](https://assets.ai.engineer/sites/aiecode2025/sha256/2ed3daad957e4738473e622d4ac8def1158891859b43d1918050bc7455bbadcb.webp)

Why I’m bullish on
Postgres + pgvector

### Trash photos and embeddings

Users photograph trash in San Francisco and upload the images to an embedding store to measure trash trends across the city.

[8:33](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=513s) [A street litter photo beside a diagram of users taking photos and exchanging data with an embeddings store.](https://assets.ai.engineer/sites/aiecode2025/sha256/92802e22d5dc5ef6ace76b0aa0d48720e04af7382a7b426ca206d44187396481.webp)

Users

EMBEDDINGS

roboflow

Street photo text:
NO PARKING
[illegible]
16th St

### Problem: NSFW images

In the trash photo app, NSFW uploads can increase storage costs, slow indexes, and expose users to unsafe content.

[8:52](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=532s) [Title slide reading Problem: NSFW images.](https://assets.ai.engineer/sites/aiecode2025/sha256/e06125ca2ccc766c7f15aa5468d163a114cb76366102ec7c1b2763670b5f49fc.webp)

Problem:
NSFW images

### Unsafe images and embeddings

NSFW images take up embedding storage, can slow the index, and may be visible to users in the app. The diagram depicts unwanted content as trash.

[9:07](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=547s) [Users connect to an embedding store, with trash images illustrating unwanted content and notes about storage, indexes, and frontend exposure.](https://assets.ai.engineer/sites/aiecode2025/sha256/3468e441abf4612e0f844c28ec095278cb90a0ef3ce71ea430ec01175de0c0c7.webp)

Users

FILLS EMBEDDING STORE
FILLS INDEXES
NOT SAFE CONTENT ON FRONTEND

EMBEDDINGS

BIOHAZARD

### Solution: Partitions

The speaker proposes Postgres partitions in a proof of concept to address unwanted content in an embedding store.

[9:22](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=562s) [Title slide reading “Solution: Partitions.”](https://assets.ai.engineer/sites/aiecode2025/sha256/643a84adfad6aaf1dfe541968e9d1919fa30da3135cda60f23af3d464fd440a5.webp)

Solution:
Partitions

### Good cats and bad cats

The proposed example segments good cats and bad cats, with users interacting with Supabase.

[9:30](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=570s) [Users connect to Supabase, flanked by images labeled good cats and bad cats.](https://assets.ai.engineer/sites/aiecode2025/sha256/02cf9ac8f99105a5eaef335f60df24b9515f8e3fd6970a6a08a4de17464776e8.webp)

Users

Good Cats
[illegible]
Supabase
Bad Cats

### Cat classification partitions

The is_cat function compares uploaded embeddings with a canonical space cat. The diagram routes similarity scores ≥ 0.8 to good_cats and lower scores to bad_cats.

[9:33](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=573s) [Users connect to a cats table above an is_cat function and a space-cat image.](https://assets.ai.engineer/sites/aiecode2025/sha256/3fbca8503106abbb75df3acda7e1bb5774c21a00063c373eb3e2c1799a643821.webp)

Users
cats
is_cat()?

[9:40](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=580s) [Diagram routes cat images to good_cats at similarity at least 0.8 and bad_cats below 0.8.](https://assets.ai.engineer/sites/aiecode2025/sha256/a67995de54eeba9dfa35af6192def4bb2c441a66f998adf251e40d08b7ce35db.webp)

Users
cats
is_cat()?
≥ 0.8
< 0.8
good_cats
bad_cats

### Cat similarity function

The is_cat function takes an embedding and returns its similarity to a reference vector generated from the space cat image.

[10:11](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=611s) [SQL defines an is_cat function taking a 512-dimensional embedding and returning a float; a long vector continues below the frame.](https://assets.ai.engineer/sites/aiecode2025/sha256/306679ab3194c1d17aab604737808a17991559a182866eb869b748123602c9f7.webp)

create or replace function is_cat (
  query_embedding vector(512)
)
returns float
language plpgsql
as $$
declare
    similarity float;
begin
  select
    1 - ('[0.6766267,-0.13940334,0.024032876,-0.2754531,-0.43185407,-0.003094729,0.2062534,0.09651277,-0.08379026,-0.21812694,-0.28191417,0.13613117,-0.14712599,0.1752473,-0.054559633,-0.06314936,0.089452505,0.076852374,0.02847454,-0.18313752,-0.65038216,-0.25926137,-0.12001791,-0.23211911,-0.5207088,0.7965287,0.09181243,0.17753276,0.44296664,0.13885911,0.56711817,0.2660721,0.05656992,-0.1484654,0.06839454,-0.18251067,0.33777782,-0.04513141,0.22421142,0.98220295,0.15568295,-0.12540177,0.4308219,-0.00962471,-0.03518875,0.051784325,-0.09328693,0.4332377,-0.2258462,0.27584067,-0.06391773,-0.10202806,0.026407752,0.30909413,-0.020372506,-0.10779952,-0.02518718,0.15774767,0.051823735,-0.45768338,-0.08068304,-0.4580621,0.08070726,-0.102466375,0.42523766,0.1631747,0.6030871,0.115232676,-0.6540226,-0.31775096,-0.[illegible]

### Cats table schema

The cats table stores each image URL and 512-dimensional embedding alongside an is_cat similarity score, used to compare the image with a canonical cat.

[10:30](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=630s) [SQL creates a cats table with an ID, URL, 512-dimensional embedding and similarity score.](https://assets.ai.engineer/sites/aiecode2025/sha256/47190a472f9c790c95a1b9dca8ee930032dbda495eb63f1a7f7748763e9a1bf3.webp)

create table cats (
  id serial primary key,
  url text,
  embedding vector(512),
  is_cat float            -- GOOD CAT OR BAD CAT?
);

### Stored cat similarity scores

After images are uploaded, the table stores their URLs and embeddings alongside an is_cat similarity score calculated by the insert trigger.

[11:26](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=686s) [Database table with IDs, image URLs, embedding vectors and cat similarity scores.](https://assets.ai.engineer/sites/aiecode2025/sha256/508fbb9781b8c15df39d7e1ce15cd06ce8ff40d49af5aa5ae481fdc00d41cc56.webp)

id int8 | url text | embedding vector | is_cat float8
1 | https://tlemwzomooprktlwktao[illegible] | [0.36281908,-0.7956709,-0.4255185,[illegible] | 0.733513961369426
2 | https://tlemwzomooprktlwktao[illegible] | [0.36281908,-0.7956709,-0.4255185,[illegible] | 0.733513961369426
3 | https://tlemwzomooprktlwktao[illegible] | [0.36281908,-0.7956709,-0.4255185,[illegible] | 0.733513961369426
4 | https://tlemwzomooprktlwktao[illegible] | [0.38314694,-1.2180047,-0.045842767,[illegible] | 0.741948792518196
5 | https://tlemwzomooprktlwktao[illegible] | [0.6766267,-0.13940334,0.024032876,[illegible] | 0.700709355283664
6 | https://tlemwzomooprktlwktao[illegible] | [0.112703875,-0.6645471,-0.12748712,[illegible] | 1
7 | https://tlemwzomooprktlwktao[illegible] | [0.6766267,-0.13940334,0.024032876,[illegible] | 1
8 | https://tlemwzomooprktlwktao[illegible] | [0.38314694,-1.2180047,-0.045842767,[illegible] | 0.826075403639059
9 | https://tlemwzomooprktlwktao[illegible] | [0.112703875,-0.6645471,-0.12748712,[illegible] | 0.700709355283664

## Full transcript

[0:00](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=0s) [upbeat music] Hey, everyone.

[0:15](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=15s) Um, so, yeah, I'm Copple, the CEO and co-founder of Supabase. Um, also, thank you for having me, especially to Swyx and Ben. When Swyx asks you to come to a conference, you don't say, "Yes," you say, uh, "Definitely."

[0:30](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=30s) And this is the first time we've ever sponsored a, a conference at all, so, um, it's good to be here. So first of all, um, very apt that, uh, apparently this section of talks is, uh, Scale to Millions in a Weekend.

[0:45](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=45s) It's very apt because it's actually our tagline. Um, so what is Supabase? Um, we are a backend as a service. What does that mean? We give you a, a full Postgres database.

[0:58](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=58s) Every time you launch a database, uh, a project within Supabase, you get the database and, uh, we also provide you with authentication.

[1:09](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=69s) All of the users when you use our auth service are also stored inside that database. We give you edge functions for, uh, compute. These are powered by Deno. You can also trigger them from the database.

[1:20](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=80s) So, uh, hopefully you see where this is going. Uh, we give you large file storage. These do not get stored in your database, but the directory structure does get stored in your database, so you can write access rules, things like that.

[1:36](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=96s) We have a real-time system. This is actually the genesis of, uh, Supabase. I won't talk about it in here, but, um, you can use this to listen to changes coming out of your database, your Postgres database.

[1:47](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=107s) You can also use it to build live, uh, like cursor movements, things like this. And then most importantly for this talk, uh, we have a vector offering. This is for storing embeddings.

[1:59](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=119s) This is powered by pgvector, uh, and that's the topic of this talk. I wanna sort of make the case for pgvector.

[2:08](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=128s) So first of all, I wanted to show-- Oh, and yeah, finally, we're open source. So we've been operating since twenty-twenty. Everything we do is MIT l- licensed Apache 2 or Postgres.

[2:19](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=139s) We try to support existing communities wherever we can, and we try to coexist with them, and that's largely why we support pgvector. It is an existing tool. We contribute to, to it.

[2:32](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=152s) So I wanted to show a little bit about how the sausage is made in an open source company. And for pgvector, this started with just an email from Greg.

[2:43](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=163s) He said, "I'm sending this email to see what it would take for your team to accept a Postgres extension called pgvector. It's a simple yet powerful extension to support vector operations.

[2:55](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=175s) I've already done the work. You can find my, my pull request on GitHub." So I jumped on a call with Greg, and afterwards, um, I sent him an email the next day.

[3:06](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=186s) "Hey, Greg. The extension is merged, so, uh, it should be landing in prod this week. By the way, our docs search is currently a bit broken. Uh, is this something you'd be interested in helping with?"

[3:19](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=199s) Then fast-forward two weeks, and we released Clippy, uh, which is, of course, a throwback to, uh, Microsoft Clippy, the OG AI, AI assistant. Uh, I think we were the first to do this within docs.

[3:33](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=213s) We certainly didn't know of anyone else doing this as a doc search interface. Um, so we built an example, a template around it where you can do this within your own docs, and others followed suit.

[3:43](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=223s) Um, notably, Mozilla released this for MDN, one of the most popular dev, uh, docs, uh, on the internet, along with, uh, many other AI applications. So this is a chart of all the new databases, uh, being launched on Supabase dot com, our platform.

[4:01](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=241s) Um, it doesn't include the open source, uh, databases. So you can see where pgvector was added. It is one of the tailwinds that, um, a- accelerated the growth of new databases on our platform.

[4:13](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=253s) And since then, we've kind of become part of the AI stack for a lot of builders, especially. Uh, we work very well with, uh, Vercel, Netlify, the Jamstack crowd, and, uh, now we're launching around twelve thousand databases a week, and so this, uh, around maybe ten to fifteen percent of them are using pgvector in one way or

[4:33](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=273s) another. So thousands of AI applications being launched every week.

[4:38](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=278s) Um, also, um, some of these apps kind of fit that tagline, "Build in a weekend. Scale to millions." We've literally had apps-- We had one that scaled to a million users in ten days.

[4:48](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=288s) I know they built it in three days. Um, so a, a lot of, uh, really bizarre things that we've seen since, uh, since pgvector was launched.

[4:58](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=298s) Uh, also the app you're using today, if you're using it, uh, is powered by, uh, Supabase. So thank you, Simon, for using that inside the application. And then finally, just to wrap up that story arc, um, Greg, who, uh, emailed us at the start of the year, now works at Supabase.

[5:14](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=314s) If you attended the workshop yesterday, he actually, uh, uh, was the one leading that. [audience cheering]

[5:22](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=322s) Nice. Thanks, Greg. [audience applauding] Also responsible for a lot of the growth in Supabase, so we owe him a lot. Um, but every good story has a few speed bumps, and for pgvector that started with a tweet.

[5:37](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=337s) Um, this is one. Uh, it says, "Why you should never use pgvector, Supabase Vector Store, uh, for production. pgvector is twenty times slower than a decent vector database, Qdrant, and it's a full eighteen percent worse in finding relevant docs for you."

[5:53](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=353s) So in this chart, uh, higher is better. Um, it's the queries per second. Just making sure you all know And, uh, Postgres, the IVFFlat index is not doing well here.

[6:06](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=366s) Um, and first of all, we feel this is a unfair mischaracterization of Supabase because pgvector is actually owned by Andrew Kane, a single sole contributor who was contribu- uh, who developed this many years before Supabase came along.

[6:22](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=382s) Nonetheless, uh, we are contributors, and so, uh, when Andrew saw the tweet, um, he decided, well, HNSW, let's just add it. And, uh, we got to work with the Aurelion team and the AWS team, and it took about one month to, to build an HNSW.

[6:40](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=400s) What were the results? Uh, this is the same chart, but we just use, uh, Postgres HNSW. [audience cheering]

[6:55](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=415s) First of all, I'm not a big fan of benchmarks because it seems like I'm ragging on Qdrant here. I'm not. Unfortunately, th- they were used in the, um, in the tweet, so we had to benchmark against them.

[7:07](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=427s) Also, they're very isolated. But what you can see most importantly is that the queries per second increased and also the accuracy in- increased. They're both for Qdrant and HNSW, uh, zero point nine nine.

[7:22](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=442s) Um, also, um, you might be thinking, well, you can just throw compute at it. Maybe that's what they're doing. Um, this one actually is a blog post we released today.

[7:31](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=451s) You can read it. That's the QR code for it. This is an apples for apples comparison, uh, between Pinecone and Postgres for the same compute. We basically take all the same dollar value.

[7:43](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=463s) So it's very hard to benchmark Pinecone, but, uh, and to find accuracy. But we're measuring the, um, queries per second for Pinecone using six replicas, which cost four hundred and eighty dollars versus one of our own, uh, database systems, which is four hundred and ten.

[8:00](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=480s) So we give them a bit of extra compute and the queries per second and accuracy are, um, obviously different on the chart. Um,

[8:11](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=491s) so why am I bullish about Postgres and pgvector for this particular thing? I was chatting to Joseph, actually the CEO of Roboflow, a few months ago, and I like to tell this example.

[8:22](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=502s) Uh, it's related actually to the paint one, but a slightly different, uh, application. I like to tell it because it highlights the power of Postgres. So, um, he told me about this app where the users could take photos of trash within San Francisco and then they would upload it to an embedding store and, um, they would kind

[8:41](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=521s) of measure the trends with, of trash throughout San Francisco. You could think of this the same as, um, the PaintWTF, the, the example that he just used. Um, the problem, of course, with all of these ones is not safe for work, uh, images.

[8:58](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=538s) So, uh, why is that a problem? Uh, first of all, it fills up your embedding store. You have to store the data. It's gonna cost you more. Uh, your index is gonna slow down if you're indexing this content, and users can see this data inside the app.

[9:17](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=557s) So I thought about this for an hour, and I did a little proof of concept for him, um, just using Postgres. Uh, the solution that I thought of was partitions.

[9:26](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=566s) Now, trash is very boring, so I'm gonna use cats in this example. Uh, we're gonna segment good cats and bad cats.

[9:34](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=574s) Um, so we'll start with a basic table where we're gonna store all of our cats. We're gonna store the embeddings inside them. Then, um, when an up-- when an embedding is uploaded, uh, we're going to call a function called is_cat.

[9:47](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=587s) And here I'm going to, um, I'm gonna compare it to a canonical cat, in this case, my, uh, space cat.

[9:54](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=594s) Then if the similarity is greater than zero point eight, uh, I'll store it in a good cats partition, and everything else can just go into a bad cats partition.

[10:04](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=604s) Um, so to do this, I just took my space cat and I generated a vector of, uh, of that, and then I literally just stuffed it inside a Postgres function called is_cat.

[10:15](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=615s) Uh, the way that this works, it takes in an embedding that's the f- uh, line three, and then it's going to return, uh, l- uh, a, uh float, or a similarity basically.

[10:26](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=626s) And all it's gonna do is compare the distance to this canonical cat.

[10:31](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=631s) Uh, I'm gonna create a table to store all of my embeddings. Uh, that's line five, the embeddings, the URL of the image. And then finally on line six, we're gonna determine the similarity.

[10:42](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=642s) If it, is it a good cat or a bad cat? Um, then finally, Postgres has this thing called triggers, which are very cool. What we can do is an attach a trigger to a table.

[10:55](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=655s) So, uh, first of all, line two, we're gonna create the trigger. Line three, we're gonna do it before the insert onto this table. And then the most important one is line six.

[11:05](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=665s) And this trigger, uh, for every time you upload a cat, um, we're going to run that function that we just saw, compare it, and then store in, uh, the table, uh, the similarity.

[11:17](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=677s) NEW here is actually kind of a special value for Postgres. Inside the trigger is for the values that you're about to insert.

[11:25](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=685s) And then finally, what does the data look like after uploading a bunch of images? You can see here that we're storing, uh, all of our embeddings, the URLs for them, and then on the right-hand side, that, uh, similarity.

[11:36](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=696s) And now we can use that essentially to, uh, create a segment. So, uh, we just need to split the data. And the nice thing about, uh, partitions in Postgres, uh, they've got kind of all the properties of a regular table and each one individually.

[11:51](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=711s) So we can create an index only on the good cats and then to clean up the, a- as our bad cats are getting uploaded, if we ever wanna clean them up, we just drop the partition and recreate it.

[12:02](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=722s) And the way that they work on disk is all the data is stored, um, grouped together, so good cats will, uh, be, uh, fast, kept fast. Bad cats will, uh, will be dropped

[12:15](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=735s) So what does that look like in code? In Postgres code, it's really just 14, 13, 14 lines of code. Uh, here, um, just adding on line seven, you can see the path- partition that I create, and I'm going to do it by a range.

[12:28](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=748s) Here, um, uh, is_cat is the column that I'm going to partition by. Then on line nine, I create good cats, and line 11 is where I actually determine the values between 0.8 and one, and then on, uh, line 13, everything else is gonna fall into the default partition.

[12:48](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=768s) So honestly, I don't even know if this is the right way to solve the problem, but I just think it's cool that I could just do that, and it's all built into Postgres.

[12:57](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=777s) So that's really why I'm bullish on Postgres. Uh, I mean, it's so extensible. It's got 30 years of engineering. It's got pretty much everything that you, all the primitives that you might need to get out of your way while you are building an AI application.

[13:12](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=792s) It's also extensible. pgvector itself is not built into Postgres. It's just an extension. So for us to add it, we just scouted around the community, or Greg did for, in this case, and then we merged it in as an extension, and it was running basically within two days.

[13:30](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=810s) Some other things worth highlighting, if you're doing RAG especially, um, Postgres has row-level security, which I think is very cool. Um, this allows you to write declarative rules on your tables inside your Postgres database.

[13:43](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=823s) And so if you're storing user data and you want to split it up by different users, you can actually write those rules. Uh, it's also a defense in, in depth.

[13:52](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=832s) So if it gets through maybe your API security, you can go directly into, uh, your database. The security is still there.

[14:00](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=840s) Um, something that's often not captured in benchmarks, a single round trip versus multiple round trips. So if you store your embeddings next to your operational data, then you do a single fetch to your database.

[14:15](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=855s) And then finally, uh, we're still early. Uh, pgvector is currently a, a extension. I can foresee it's probably going to get merged into pg core eventually. I'm not too sure.

[14:30](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=870s) Um, people often ask me, "Is there still space for a, uh, a specialized vector database?" Yes, I think there are, uh, for many other things that databases won't do.

[14:41](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=881s) Um, maybe a lot of, uh, putting models closer to the database, uh, could be one of those things. But for this particular use case where you're actually just storing embeddings, indexing them, fetching them out, I think then, yeah, uh, Postgres is, is definitely, uh, going to be, uh, moving down that direction.

[14:59](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=899s) What's next for Supabase Vector? Um, pretty simply, we have been really focused on more enterprise use cases or, uh, largely, uh, how do you store billions of vectors. Um, this is another area that needs development.

[15:14](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=914s) So we've been working on sharding with Citus, another Postgres extension, and it allows you to split your, um, your data between different nodes. And we've s- found that the transactions scale in a linear fashion as you add nodes.

[15:29](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=929s) So, um, in this case, we're going to develop this. We've been chatting to the Citus team at Microsoft. If you want to be a design partner on this, then, um, we'd love to work with you on it, and especially if you're already storing billions of embeddings.

[15:44](https://www.youtube.com/watch?v=MDxEXKkxf2Q&t=944s) And if you wanna get started, just go to database.new, and, uh, we also have apparently now our swag has finally arrived. So if you want some free credits and swag, come see us at the booth, and, uh, happy building. [audience cheering] [upbeat music]
