4 ms·

Vectors are the new JSON in PostgreSQL

71 points by lawrencechen 3y ago

MWil 3y ago
Someone wants to get started in AI/ML today and they have beginner-level understanding of Python/Javascript. Without any further context, but a desire to learn AI/ML and building on what they know should that person next look to: 1) learn PostgreSQL, pgvector, and whenever the "new" comes 2) learn PyTorch, TensorFlow in Python 3) learn TensorFlow.js Presume hobby-level interest, not production-safe best practices - so I guess there is that additional context
- gorbypark 3y ago
  Most likely #2 would be the best bet for both hobby level and above. You could do some fun stuff with Postgres and pgvector, but you'll be "limited" to creating some embeddings with an existing model, storing them in pgvector and again using that to add context to an existing model (more or less). #3 would limit you to running existing models in a browser. Only #2 would allow you to actually understand and create new meaningful models.
- hot_gril 3y ago
  #2. Databases like Postgres are central to backend applications, but for AI the real action is happening on those tensor frameworks. Honestly I can't see pgvector becoming a mainstream way of running inference. I used pg cube for that on one project a while ago. Yeah it worked, but even ignoring the performance issues, the only reason we considered it was because of our weird use case. We were also doing other funky stuff like large sparse matrix math using just float8 cols, with parallelism (by splitting one query into ~32).
- esafak 3y ago
  Read papers about the problem you wish you to apply ML to, and implement them in pyTorch. I would get a book too. You could read the first three sections of this, for starters: https://probml.github.io/pml-book/book1.html https://probml.github.io/pml-book/book1.html
j_crick 3y ago
Years go by, and I, a simple webdev basement dweller, just can’t stop looking at Postgres and think once in a while: “man, what can’t this thing do?” (Of course it can’t do a lot of things, but the stuff that’s baked into it already or can be enabled by some extensions is simply amazing)
- catchnear4321 3y ago
  your backend developers were so preoccupied with whether or not they could, they didn't stop to think if they should.
  - j_crick 3y ago
    But then once in a while you have this one client with a task that can be solved easily with something like postgis, and you rejoice, and praise gods and developers and things, and drink beer happily ever after to their health, and add tasty sauces to your dinner potatoes.
    - catchnear4321 3y ago
      with great extensions come great responsibility.
williamstein 3y ago
I think one of the most important and interesting questions regarding using pgvector is performance, and in particular, how it compares to Qdrant, Weaviate, etc. This post hints at that where it says "I presented a lightning talk called Vectors are the new JSON where I shared use-cases and some upcoming challenges with improving PostgreSQL and pgvector performance for querying vector data. Some problems to tackle (many of which are in progress!) involve adding more parallelism to pgvector, adding support for indexing for vectors with more than 2,000 dimensions, and leverage hardware acceleration where possible to speed up calculations.". But the post doesn't give any numbers. I tried to read the linked lightning talk, but it's on https://www.slideshare.net/ https://www.slideshare.net/, which is a really weird website now -- every few slides it tried to force me to watch a 30 second commercial!?
- hot_gril 3y ago
  I'm a big fan of Postgres, and my app backends are usually very Postgres-heavy. But yeah, I don't get why you'd want to use Postgres for AI inference unless there's some performance reason.
  - riku_iki 3y ago
    it would be much easier to write query: select infer_class_from_text_using_ml(some_text) from some_doc compared to building infra with different services orchestrations.
    - hot_gril 3y ago
      I'm assuming the cost/effort of getting whatever customized Postgres instance supports efficient (i.e not using the CPU alone) ML inference is more than orchestrating something else.
      - riku_iki 3y ago
        someone has opportunity to prepackage and even sell such product (postgres has BSD license, so you can totally close sources and sell as your own product). Also, I think there are many cases when smaller models can totally run on CPU.
lgas 3y ago
I don't understand why everyone keeps saying that no one is going to want to use PostgreSQL for inference... of course not, no one is suggesting that. But they are going to want to store embeddings of their data and be able to query those embeddings by similarity. Just like they do today in the various existing vector databases.
archibaldJ 3y ago
what about tensors? do anyone know any pointers on that? Working with tensors instead of vectors
- cactusfrog 3y ago
  According to thr article postgres supports multidimensional vectors
louis8799 3y ago
Problem is not all managed postgre services support pgvector

71 points by lawrencechen 3y ago

MWil 3y ago

Someone wants to get started in AI/ML today and they have beginner-level understanding of Python/Javascript. Without any further context, but a desire to learn AI/ML and building on what they know should that person next look to: 1) learn PostgreSQL, pgvector, and whenever the "new" comes 2) learn PyTorch, TensorFlow in Python 3) learn TensorFlow.js Presume hobby-level interest, not production-safe best practices - so I guess there is that additional context

gorbypark 3y ago
Most likely #2 would be the best bet for both hobby level and above. You could do some fun stuff with Postgres and pgvector, but you'll be "limited" to creating some embeddings with an existing model, storing them in pgvector and again using that to add context to an existing model (more or less). #3 would limit you to running existing models in a browser. Only #2 would allow you to actually understand and create new meaningful models.
hot_gril 3y ago
#2. Databases like Postgres are central to backend applications, but for AI the real action is happening on those tensor frameworks. Honestly I can't see pgvector becoming a mainstream way of running inference. I used pg cube for that on one project a while ago. Yeah it worked, but even ignoring the performance issues, the only reason we considered it was because of our weird use case. We were also doing other funky stuff like large sparse matrix math using just float8 cols, with parallelism (by splitting one query into ~32).
esafak 3y ago
Read papers about the problem you wish you to apply ML to, and implement them in pyTorch. I would get a book too. You could read the first three sections of this, for starters: https://probml.github.io/pml-book/book1.html https://probml.github.io/pml-book/book1.html

j_crick 3y ago

Years go by, and I, a simple webdev basement dweller, just can’t stop looking at Postgres and think once in a while: “man, what can’t this thing do?” (Of course it can’t do a lot of things, but the stuff that’s baked into it already or can be enabled by some extensions is simply amazing)

catchnear4321 3y ago
your backend developers were so preoccupied with whether or not they could, they didn't stop to think if they should.
- j_crick 3y ago
  But then once in a while you have this one client with a task that can be solved easily with something like postgis, and you rejoice, and praise gods and developers and things, and drink beer happily ever after to their health, and add tasty sauces to your dinner potatoes.
  - catchnear4321 3y ago
    with great extensions come great responsibility.

williamstein 3y ago

I think one of the most important and interesting questions regarding using pgvector is performance, and in particular, how it compares to Qdrant, Weaviate, etc. This post hints at that where it says "I presented a lightning talk called Vectors are the new JSON where I shared use-cases and some upcoming challenges with improving PostgreSQL and pgvector performance for querying vector data. Some problems to tackle (many of which are in progress!) involve adding more parallelism to pgvector, adding support for indexing for vectors with more than 2,000 dimensions, and leverage hardware acceleration where possible to speed up calculations.". But the post doesn't give any numbers. I tried to read the linked lightning talk, but it's on https://www.slideshare.net/ https://www.slideshare.net/, which is a really weird website now -- every few slides it tried to force me to watch a 30 second commercial!?

hot_gril 3y ago
I'm a big fan of Postgres, and my app backends are usually very Postgres-heavy. But yeah, I don't get why you'd want to use Postgres for AI inference unless there's some performance reason.
- riku_iki 3y ago
  it would be much easier to write query: select infer_class_from_text_using_ml(some_text) from some_doc compared to building infra with different services orchestrations.
  - hot_gril 3y ago
    I'm assuming the cost/effort of getting whatever customized Postgres instance supports efficient (i.e not using the CPU alone) ML inference is more than orchestrating something else.
    - riku_iki 3y ago
      someone has opportunity to prepackage and even sell such product (postgres has BSD license, so you can totally close sources and sell as your own product). Also, I think there are many cases when smaller models can totally run on CPU.

lgas 3y ago

I don't understand why everyone keeps saying that no one is going to want to use PostgreSQL for inference... of course not, no one is suggesting that. But they are going to want to store embeddings of their data and be able to query those embeddings by similarity. Just like they do today in the various existing vector databases.

archibaldJ 3y ago

what about tensors? do anyone know any pointers on that? Working with tensors instead of vectors

cactusfrog 3y ago
According to thr article postgres supports multidimensional vectors

louis8799 3y ago

Problem is not all managed postgre services support pgvector