For those who don't know me, let's get corrected and I work at Altinity as a Director
of Services and we do support services and database of the service at Specializing Over
Clickhouse.
About myself, I've been working in open source for the last decade and a half and previously
in life, I was an enterprise space doing enterprise databases.
Most recently and within the last four or five years, I've called two books which I brought
some over here.
One of them is the MySQL cookbook over here.
We have a few copies.
This one should go to the best question and I'll say that one for the best question in the
room.
This one is the latest book on the database design modeling which we touch based on the new technologies
and how it is scalable database operations and options to explore.
This should go to the youngest in the room.
I'll see.
Okay.
Sealing through you.
That's my high-spraaker.
I was going to skip that but I want to ask the question like, what's the one
noticle mild equals two?
How many kilometers?
How do you measure that?
Anyone?
One point eight.
Okay.
Because in land it's one point six.
Thank you.
All right.
I'll go back.
Not the embeddings.
We can have two closer, I guess.
Let me see.
This clicker is not clicking.
All right.
I'll let chunker into this himself actually.
That would be good.
Hi.
My name is Shankar and I've been an engineer throughout my life.
And I was a long-term member of the Oracle Rack database development team, real application clusters.
I was working in distributed logging, distributed recovery and stuff like that.
After that, I have done some work in my SQL at Aurora and in Google.
And yeah, my interests are of course vector search and AI.
I also do a lot of work in my SQL and new recovery performance and really recovery performance improvements.
Yeah.
That's about me.
Awesome.
He's our developer on my vector project.
And he's also the mastermind and architect of it.
I'm with the vector space for the last six months not as experience as him.
And we all learning.
So let's learn that together today.
Just a minute.
Just use this one.
Okay, vectors and dimensions.
So vectors actually as much as also went through is a data point that represents what we point to.
So what we can access to it can they can be in.
We learned in our school systems in the earlier mat was two dimensional functions.
And then there's a three dimensional space where you can actually have.
And then the geo space and then there's also a data types that actually needs to be represented as vectors and within the dimensions.
And.
So vectors once the vectors are coupled and and collected they turn into an embedding and these embeddings can be represented as.
The mathematical expressions and and those can be also processed using the functions that we will.
Demo today.
This is used in in like the AI as the previous talk mentioned and.
It is popular with the AI but before even AI there it was already been used in some other places which I will also touch base on.
So the vector embeddings.
This is an example of an embedding that open AI uses and so this is a Wikipedia article.
And there's a model called embedding ADA zero zero two with the dimensions one five three six.
So there's what they do is they go and train on this using the L of M.
And they have which is also open source and with these dimensions you get certain answer and like.
Mathias said in the previous talk is is approximate.
So the the higher dimensions the higher the data points are those embeddings you get a better result or a larger dataset the result that can be.
The manipulated in the more meaningful.
So also open AI has another model called text embedding three large model with with a higher dimension of 3000.
So you can process that what that means is you need more GPU power which cost more money.
But on the end deep seek popped up and they can also do embeddings up to 8k so 8000 dimensions embeddings are possible with deep seek.
But to process that it will take more GPU power and of course space to do that.
So I think we all are learning about more about embeddings what are the benefits.
Exactly what we try to do in the databases is a search.
So if you all remember in this room a use some sort of a full text search with you know like equals you know matching or other functions in in the.
So basically most of them are covered in this book.
It is very difficult and costly operation to do that within a database not scalable also.
On the large scale eCommerce applications mobile applications you search for a keyword and then you can actually have an answer but it.
You will get an exact answer from those or LTP databases but it will be costly and may not be very efficient.
So the AI based applications use this technique and that is going to be supporting some of the application.
So why do we need vector support in our databases because the traditional search is is not very efficient to get the results.
And again the born of AI machine learning and all that new technology open source and have this.
Need.
So coming back to my SQL that is our subject.
So you have my SQL implementation and you also do some sort of some of you will remember.
Solar search you know doing full text indexing on a different we brand that for replicas for many many years.
I don't know if it's still relevant but I think with this technology is not.
And Phoenix was very popular at one point.
We would run a replica of a replica so it's not impacted by something else run a job and then in the morning you would have an index that you could do a search.
Keywords adapter you know plugs USB type C whatever and but with the support of existing ecosystem we can add some more functionality in my scan that's the idea behind.
Why do we need my scan and my scan is also very popular and using places so it's it can be very good.
The use cases are what he has already mentioned the NLP and LMs and other souls keep that okay.
This is something when I was doing a little bit more deep research why.
There are no vector dedicated databases that will do this job properly.
There are actually they call a vector database.
And some of these are listed I'm no in expert in these but what are the differences between the vector database or a vector store as a provider.
In this case is my SQL MariaDB has tied DB has postgreses with also some of them are a little more specialized in this type of workflows like click house elastic search where you can actually have a more analytics.
Targeted all updates as instead of having an within an all tb system.
These databases if you do a little bit research on the vector database they're actually.
Not a real database but it's a platform they provide an API and software development kit for the database behind it.
You basically you code vector related or machine learning related or AI related application within the database.
So they come with bunch of libraries you don't it's not like a SQL compatible database query and answer type of databases some of them they provided some of them are completely application based.
Where as with the vector support comes to comes to existing databases database platforms is an extension to existing solution.
So you already have your data in this case my SQL and you want to add a some sort of functionality within the my SQL you can get that with our subject of today is the plugin.
So traditional databases use you know we are organized with schema and and we have tables columns data types we do.
We get that result but vector databases is slightly different they go in.
They go in in the data type which is in this case the embeddings are vector within the dimensions we get we get a different approach and we instead of versus rows and columns we have the embeddings in it.
It's kind of a difference between we need to we must remember the difference and then that's how the compression and how we store the data how we index the data it's going to be different and traditional database on those.
And again like if you're basically doing an AI based or or.
This type of application it would be good to go into that direction as focusing on a vector database but if you have a database that has.
Already in in progress and and you have it in production and one and add functionality you can do that and at that I'm going to the microphone to shankar for the vector plugin architecture.
Yeah, you can often.
Okay, thanks I'll can for laying the background and thanks Matthias for also laying the background earlier and let's get in how the my vector plugin is are.
So let's look at the my vector plugin architecture so if you're aware of postgres rate postgres is very extensible you can add new data types you can add new access methods.
So it's very extensible and that's how the PG vector was added in fact and but my sequel is not you can and we add a new storage engine to my sequel.
You can't hack around with the optimizer or even data types and I worked in Oracle so adding a new data type is not easy job.
So the plugin has three main components uses UDF's user defined functions in CNC++ every database provides that.
It uses for the data type it for a type sorry for a to it uses wire binary that's basically a blob where you can just push those floats.
And with nine we have the vector data type in my sequel so the plugin uses the vector data type and the third component for the indexing so it uses an hns w.
vector index and the index supports both online and batch mode builds so online so the third piece of the design is it uses bin logs to update the vector index.
So it's a plugin it's not inside the database it cannot intercept the emails but it uses the bin log as a wall.
So post commit update of the vector index it came up earlier so it provides read committed consistency.
All right and to tie all these things we have stored procedures so again we the plugin cannot change the my sequel syntax it doesn't touch the parser doesn't touch the grammar so it uses stored procedures.
And stored procedures are in fact an easy way to extend the functionality of any database.
Again, from coming from the Oracle day is right lot of Oracle supply thousands of stored procedures so things like queuing advanced queuing and stuff are implemented only using stored procedures.
So for managing the database the plugin provides stored procedures.
Okay so it's completely open source it's the get up repo is there and how do you build or get and build the plugin is straightforward you get clone the repo and like any other plugin my sequel plugin so you configure the make file for the plugin and then just make it.
Output of this will be a shared object and then you take the shared object yes to file and copy it to the my sequel distribution my sequel installation directory and you run this single script SQL script.
This will go and install the plugin and create the UDFs the the wrappers for the UDFs and also install these stored procedures that the plugin uses plugin provides.
Yeah little more so the plugin is supported the same thing works for both my sequel ato if you have a production system currently in my sequel ato you can use it all the demos are on I know what I know.
But it will work the same way for it and just going over this graphic here right so the index can be updated in the batch build or it can be updated online also both ways and batch is a very important use case models change frequently.
So you have a million documents you have a hundred million documents you index it today the model LLM right it learns new things next month a new LLM the new version of the model could be released so you will have to reindex everything.
So batch all the online is important batch is also more important.
Okay and some of the other features of the plugin it uses the agonist w library from the original author thank you to your email code so it uses the same code as the original inventor of agonist w so high speed parallel build of index is supported.
You can specify the recall and search parameters that the theories lays out.
Agonist w is incrementally persistent so it's not a full versus full right on every update the agent is w is incrementally persistent so you have a hundred million documents today index and you add 10 more it is only incrementally persistent of 10 more index updates.
As I mentioned the index is updated by reading the bin law it is crash slave index is crash slave so I have been working in recovery for in many multiple databases so it is crash slave.
If the my SQL crashes comes back up it is recovered the next is recovered and why a plugin there is a theory behind if I can't I'm not listed the paper but.
Domain indexes are better always as an external index things like spatial things like image index or stuff right this is not a relational databases for more structured data but.
This kind of indexes are typically called as external domain indexes and they are better of stored outside the database and.
That's why you are the plugin avoids right amplification.
Okay, let's jump to the demo.
Let me do that once again.
Okay.
Yeah, you will have to enable bin log on the primary and the replic also.
Okay, you said that the index sets up after we don't make several individually because that's not set.
Yeah.
But the flexibility provided is there as I can mention about solar right so you can so you can have a production system which is the primary and.
But you don't want vector search to impact that so you can have only this index set up on the replic also so you have a high memory and high CPU instance.
So you can set it up on the replic also only on the replic.
Okay, so let's look at a quick demo quick demo but I want to just remind.
It's a plugin so it needs to be compiled so I think something need to keep in mind so you can just.
If you have a like a binary that's running right now if you add the plugin to plugin directory if you restart it won't work so you still need to download the source.
Add the plugin compile and then run it's basically just a kind of quick reminder.
Okay, so let's look at the demo.
So I'll stand here.
So we have three demos the first demo is it has a bit of vectors where you see visually or in text one but beyond that as Mattias mentioned right you don't need to really see the vectors there like to high dimensions.
So let's look at this demo.
Okay, so this is kind of the data dictionary for the my vector and we are going to create a table.
Which has words and their amenities.
This is from this Stanford NLP data set so they publish a data set where 400k words and their 50 dimension vectors are published.
So the demo is based on that.
So the same set of files are part of the GitHub.
So this is how you create a table with a vector column.
So this is an annotation.
Again, annotations are quite old in the database.
And yeah, so you say my vector and a various parameters H&SW.
So it also provides KNN index also if you want to do brute force search and the various H&SW related parameters.
So in H&SW, this would translate into bar binary in I know it would translate to the Oracle vector tag.
So I'm going to create the table.
Yeah, just to show that this became the vector column and it uses the comments as data dictionary.
So next is we will insert 400k.
Yeah, this is where we see a vector actually beyond this demo there's no more seeing the vectors.
So this is the Stanford NLP data set.
You see the word secretary and that is the 50 dimension vector for secretary.
And we use my vector construct.
You can use the Oracle supplied function also.
And there are 400k rows.
It just shows a brief snippet.
So we load this 400k rows.
Yeah, this is how the vector for book looks and we use the Oracle function vector to string.
So now let's do some distance.
So distance came repeatedly today in our thing.
Okay, the next is from using the same function.
So the Oracle function is in scientific notation.
Whereas the my vector function prints it in decimal.
So let's do some distance.
So this is the distance between book and paper.
Again, absolute distance is not matter.
It's all relative that matter.
So this is the distance between book and novel.
Eight.
Because using the my vector distance function from the plugin.
The distance between book and car.
So we moved away.
We went further away from book.
This is the distance between book and library.
So we come back.
And these are the 20 words closest to book.
And yeah, the SQL query went away.
So it will come back.
Yeah, so this is exactly brute force gain in search.
So you use the SQL order by and compute the distance between each vector and your search vector.
And you get this.
So the key point to realize is it came up.
It is brute force could be good if you have power and your data set is manageable.
But my SQL does not have parallel query.
So this has to be executed in this single thread.
So this is how you build the vector index using the stored procedure.
Done.
Five seconds for 400k and 50 dimension.
And this is how you search.
Again, you can see the vector in this example, but beyond this point you.
So select word from words where my vector is approximate number search.
And you give the vector column and a couple of other parameters.
And then the search vector.
So this is the search vector.
So this is the search vector for hardwood.
So these are the 10 words related to hardwood.
And yeah, the same one using brute force.
So this completes one part of the demo.
So let's look at a Wikipedia related one.
Open AI stuff.
So I'll just stand here.
So we are using this as our demo.
So the idea is a query created data set from Wikipedia.
And I'm using two tables to store the data.
So the data is stored in one table.
So this is where you would document and stuff would be.
So there is an ID column and a title column for the Wikipedia article.
And the vector is stored separately in another table.
And as I said, no point in seeing the vector.
One, five, three, six.
And it takes 6KB of this piece each vector.
So 1 million documents or articles.
And yeah.
So on a 48 CPU, it's a green machine from OBS.
And not nothing high powered.
I took two minutes to index 1 million vectors of 15, 3, six-dimensional.
Yeah.
So how do you search?
All right.
It came up in the earlier talk.
So you have your search vector as search query.
Tell me some famous ancient structures.
And then you contact Open AI.
So you need to get the embedding for this query, search query,
and then generate the SQL query.
So the embedding goes here.
So I run Python to get this SQL.
So this is how the query looks like the final SQL query,
just a snippet.
Select article ID from DPIDware.
This is the one, five, three, six-dimensional vector.
Yeah.
So we search for famous ancient structures.
These are the top 10 matches.
So now you'll have to visualize how this applies to your data set.
Could be patent filings.
Could be chat logs, personas.
And the one more syntax is supported.
So this time we will query some good marketing strategies.
I have just a couple of minutes.
So that's about it.
There was a running one more demo.
So this is about online update to the vector index.
And this is a more business oriented thing.
So we are going to search through a clinical trials dataset.
Open, nothing, nothing private.
And we're going to use the MP net model.
So it is a sentence, transformer, free open source model.
We are going to load around 283K documents,
containing descriptions of clinical trials.
Each divector is dimension 768.
This is kind of the example table where I'm showing the how the role looks like.
So there is a description of the study or the clinical trial.
This is something about very close wings.
And the vector is 768 dimension.
On the part of the vector is shown.
Just a couple of minutes.
So I'm going to run.
So insert 282606 rows once, first and then insert 1,000 rows.
So the table is empty initially.
And we build the index first time.
So that's a table name, CL trials online.
Nothing much, 0 rows.
So load 400, sorry, 282, sorry, I think I forgot the script name.
282606 rows.
So the index is updated in the background from the binlogs.
And this is life.
So at this point right, within one second,
243K rows have gone into the vector index.
And by the next time I do that, the index is updated.
282606 rows.
Just one more minute.
Yeah. And now I'll insert 1,000 individual rows.
Just to prove that the vector index is incrementally updated.
Sorry for that.
Yeah. So we reached 283606.
And just a couple of applications.
Again, the same concept as the earlier one.
Now we are using the MP net model from sentence transformers, all MP net base 2.
This is the query.
jogging after knee replacement surgery.
Show me some effects of jogging.
Can I jog after knee?
This could be your application searching through your dataset.
Yeah.
9 number 10 is the query.
So run the Python script.
Yeah. So these are the top 10 matches from the dataset.
So we were searching for knee surgery and jogging.
Yeah. So return to physical activities after total soon.
So.
Yeah, that's all I had any questions.
That's it.
Yes.
How do you generate the L1 answer for this one?
Good question.
So this what I showed is only vector search and similarity search.
So RAD is built on top of that.
So now you give these 10 articles and their contents to your LLM.
And say can you answer as an healthcare expert?
Can you please answer this question?
How does knee surgery and jogging and you please use these?
So you pass all these documents to them.
The LLM will generate.
So what I showed is only vector search, not rag.
Do we have time for question?
Yeah, how are we can take off layer?
One more question.
Is there any question that you have when you run the same query twice?
Is there any question there?
Yeah.
The current implementation is a in memory vector index.
And that is cash.
And the other thing, yeah, based on the buffer pool, my SQL buffer pool being.
Actual table rules, yeah.
That would be based on the my SQL buffer pool.
So when you run the first query, you have to take the same two seconds.
And then you run the second query.
You have to take rest.
You have time to take the first query.
The first query if the vector index is not been loaded in memory.
Yeah, that query will take time.
But the once the vector index is in memory.
The next query is would be super first.
Yeah.
So it would not pass.
That is the question.
Oh, yeah.
Thank you.
I just want one quick thing.
Thank you.
Who wants to reveal his age?
And who's the youngest in the room?
Right, draw it.
That one flight.
Okay.
You can come find me over here.
Can you get it?
Oh, yeah.
It's not that too.
It's a flight of books.
Yeah.