Supabase Vector: The Postgres Vector database

Talk transcript

1. 0:00

   [upbeat music] Hey, everyone.

2. 0:15

   Um, so, yeah, I'm Coppul, 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."

3. 0:30

   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.

4. 0:45

   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.

5. 0:58

   Every time you launch a database, uh, a project within Supabase, you get the database and, uh, we also provide you with authentication.

6. 1:09

   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.

7. 1:20

   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.

8. 1:36

   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.

9. 1:47

   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.

10. 1:59

    This is powered by pgvector, uh, and that's the topic of this talk. I wanna sort of make the case for pgvector.

11. 2:08

    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.

12. 2:19

    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.

13. 2:32

    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.

14. 2:43

    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.

15. 2:55

    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.

16. 3:06

    "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?"

17. 3:19

    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.

18. 3:33

    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.

19. 3:43

    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.

20. 4:01

    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.

21. 4:13

    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

22. 4:33

    another. So thousands of AI applications being launched every week.

23. 4:38

    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.

24. 4:48

    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.

25. 4:58

    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.

26. 5:14

    If you attended the workshop yesterday, he actually, uh, uh, was the one leading that. [audience cheering]

27. 5:22

    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.

28. 5:37

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

29. 5:53

    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.

30. 6:06

    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.

31. 6:22

    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.

32. 6:40

    What were the results? Uh, this is the same chart, but we just use, uh, Postgres HNSW. [audience cheering]

33. 6:55

    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.

34. 7:07

    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.

35. 7:22

    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.

36. 7:31

    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.

37. 7:43

    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.

38. 8:00

    So we give them a bit of extra compute and the queries per second and accuracy are, um, obviously different on the chart. Um,

39. 8:11

    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.

40. 8:22

    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

41. 8:41

    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.

42. 8:58

    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.

43. 9:17

    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.

44. 9:26

    Now, trash is very boring, so I'm gonna use cats in this example. Uh, we're gonna segment good cats and bad cats.

45. 9:34

    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.

46. 9:47

    And here I'm going to, um, I'm gonna compare it to a canonical cat, in this case, my, uh, space cat.

47. 9:54

    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.

48. 10:04

    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.

49. 10:15

    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, where a similarity basically.

50. 10:26

    And all it's gonna do is compare the distance to this canonical cat.

51. 10:31

    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.

52. 10:42

    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.

53. 10:55

    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.

54. 11:05

    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.

55. 11:17

    New here is actually kind of a special value for Postgres. Inside the trigger is for the values that you're about to insert.

56. 11:25

    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.

57. 11:36

    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.

58. 11:51

    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.

59. 12:02

    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

60. 12:15

    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.

61. 12:28

    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.

62. 12:48

    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.

63. 12:57

    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.

64. 13:12

    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.

65. 13:30

    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.

66. 13:43

    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 at, at depth.

67. 13:52

    So if it gets through maybe your API security, you can go directly into, uh, your database. The security is still there.

68. 14:00

    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.

69. 14:15

    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.

70. 14:30

    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.

71. 14:41

    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.

72. 14:59

    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.

73. 15:14

    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.

74. 15:29

    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.

75. 15:44

    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]