feature request Add PGVector to database models

[dillon-co]

Describe the problem you're trying to solve

Adding PGVector to the database allows for vector search on all the database models. This is super useful if you want to add any LLM or ML features to the app. This makes any natural language questions return super accurate data really fast and allows for easy implementing of tools like langchain or crewAI

Cool features that this will make easy to implement

• natural language search that can get pretty complex to code e.g. "what were people saying when bitcoin was at $20k?"
• Trends and summaries / sentiment analysis e.g. "People seem bullish always, but lately more bullish than usual.", "People have been concerned how Ross Ulbricht getting pardoned will effect the price"
• Easy personalized news feeds, e.g. "Based on your activity, here's some posts you might like"
• Buy/Sell signals e.g. "People seem bullish and the price is down. Might be a good time to buy"
• Hivemind chat. Ask a question about BTC, will give you an answer that is essentially the mean / sum of all chats on stacker news - (This is a little harder than the other ones, but infinitely easier with vectors)
• More ML models are useable. Vectors are array's of numbers that represent the object that's embedded. You you can run cool ML models like linear regression, SVM, and clustering on these.
• currated post reccomendation notification emails. e.g. "Based on your activity, here's some SN posts you might like"

Describe the solution you'd like

Add PGVector extension to postgresql database (there are other options like pinecone but I believe this is the most common)

Add embedding to post and item

Describe alternatives you've considered

No response

Additional context

What Is Vector Search?

[Soxasora]

Oh well we do have OpenSearch in the stack that does essentially the same thing, afaik it's much better than PGVector at handling large data, which of course should be a requirement in regard to actual and future SN growth

[huumn]

In general, I prefer to issues to be about features/enhancement and fixes, ie use case driven, rather than about technologies in search of a use case. Also, this is another level of indirection, because it seems you mean use (1) embeddings via (2) pgvector.

What's the most compelling use case for storing embeddings? That would probably be worth an issue.

[huumn]

Oh well we do have OpenSearch in the stack that does essentially the same thing, afaik it's much better than PGVector at handling large data, which of course should be a requirement in regard to actual and future SN growth

This is true. If we have a pure search/embedding application for posts/comments, OpenSearch is already doing this for us.

There might be very good reason to have embedding be used in other contexts where we are already doing postgres queries, but what those things are aren't immediately obvious to me.

[dillon-co]

Yeah opensearch is more robust but a little harder to use for custom ML models. But i guess there's not too much ML development at SN right now lol.
Embeddings would make the tg bot easier to add features to and open up the ability to ask natural language questions. That was the main thing.
In an sms bot app I've been building I have embeddings for events so people can ask "Is anything happening tomorrow?" And I just pass that in as a prompt and it'll respond super quickly. I thought that might be really cool to add to the SN tg bot. That was the main thing I was thinking

[ekzyis]

I'd love to hear more about the SN TG bot you're referring to

[huumn]

The telegram bot? I think a third party maintains that.

If you want to open an issue detailing that use case, all the requirements (beyond adding a postgres extensions ... like how/which models, what we're storing, etc), then we can figure out a bounty for it.