Borys Neselovskyi: Analytics and AI with PostgreSQL - Mission Possible!

This talk focuses on the capabilities of the PostgreSQL database, especially in relation to analytics and artificial intelligence (AI). The speaker, who has a background in Oracle technologies, discusses how EDB is enhancing PostgreSQL to support AI and analytics applications. The talk highlights features such as the EDB lakehouse architecture, which allows for real-time analytics on large datasets using cost-effective storage solutions like object stores. The speaker also demonstrates the use of PostgreSQL's extensions, specifically for analytics and AI model integration, including creating vectorized data from various sources. By leveraging these advancements, PostgreSQL becomes a powerful tool for building applications that can efficiently analyze data and support AI functionalities.

Hello everyone and nice to meet you in very beautiful city villainous. I like really and enjoy my stays in villains. And today we will talk about the Postgress database opensource database uh and uh about some new areas with Postgress uh can play very very important role about me very old man as already said uh several years uh Oracle uh experience I was Oracle ace worked with different

Oracle technologies databases exadata middleware and so on And five years ago I uh switched to the open source to posgress and uh since this time I'm helping my customer to adopt open source strategy and uh to uh make it possible to run posgress in very good way. Good. But coming back to AI and analytics uh yeah in 3 years some uh studies uh uh say that uh

one of $7 in IT industry will be uh spent for AI and now we you know EDB I am working for EDB and uh we expanded the Postgres capability abilities to make it uh possible uh to make AI programs uh very very easier uh based on Postgress database and as well to make analytics and today we will talk about these two topics in a deep dive more

dive. Okay. Uh as you know posgress is very very uh uh good database with several capabilities and you can build several applications. Yeah, Postgress is very extensible database and you can install extensions for oil. You can create your Python programs or Java programs, Ruby programs with Postgress. make just special uh programs uh applications uh use time series data connect you to different other database to relational non-

relational uh to lakehouse everywhere everything is possible with posgress and uh yeah but the postgress is very well uh known as database for uh transactional workload yeah oil front end back end for uh web applications. Postgress is very well because you can use parallel processing. You can use mater uh several indexes types of indexes to make your application really ready uh for mission critical workloads. But uh

in in lakehouse for example data warehousing or AI yeah postgress was not yeah very good with it. Um and I worked several years ago for uh uh some big company in Germany who uh have several exodatas Oracle big machines. Yeah, very expensive machines and uh we have a data warehouse based on this exad datas and main uh uh goal or main uh uh administrative task was to

tune the queries. Yeah, because even of exad data we have the bet running queries and uh yeah it was not uh very easy job I would say. Uh we at EDB we created uh two transactional uh workloads. Postgress is very very good. Uh two new areas uh analytics and AI and let me uh talk about the analytics. At first the idea was uh to create some solution

uh to make it possible to make realtime analysics from very big amount of data. Uh what will be done very performant and it should be cost effective. uh and uh we created EDB lakehouse and this lakehouse uh consist uh is architecture is very very similar to datab bricks. We have separation of compute engine. Compute engine is running on postgress and we bought the company uh CEO with

leading uh analytics and dream based off postgress and the data the lakehouse data is stored on object store. You don't need to have very expensive database storage. You have object store as free minio or something else. And we will organize your data in open data format. It means the data will be organized in pocket files, iceberg rest uh format. Uh the data will be uh column organized

and compressed and the posress will play as uh uh yeah engine front end application to run uh the queries. We created the extension PGAA and uh u in the postcrist database you can run the first statement select PGA create storage location it will create the prepared layhouse uh uh storage uh area in uh u in S3 storage. You can create it in S3 storage or even in

the local file system. And then you can say create table uh for example table name using PGAA. It will uh uh move or replicate the data of the table customer posress customer to S3 storage and then you can run your queries uh on S3 storage and it will be uh very very performant. By this solution you don't need to care about SQL tuning, query tuning because uh

the the parket and open data format is really good sweets and uh the organization of data in column and compress it can bring you really very good results. I will show you how it works. I will compare uh traditional uh BI uh BI dashboard building with analytics one. But uh you have data for example in the tables in Postgress tables, Oracle tables or or you and you

have table in yeah buckets in S3 buckets. How can you replicate this data? Because we can uh with create table using PGAA we can replicate data one time but if we have uh ch that data is changed and we need to replicate it in real time we can use uh postgress distributed it's a solution of EDB for replication and you can make it replicate in real time

if your uh source table is changed it will be replicated to S3 to lakehouse storage. You can create some table in the uh lakehouse storage using PGD replicate. It will create the portion of the table in S3 storage in analytics lakehouse storage. And uh you can set your uh preferred uh select uh uh uh uh engine. Yeah, you can say okay I prefer analytics engine or even

database and you it will run with analytics engine or with transactional uh database engine and you can make the tiered tables if you know okay I need this table uh uh content only for my analytics I will not change it you can relocate it from database storage to analytic storage and then the data will be there you can make it partition wise for example or for whole

table. Yeah, it's working very well and how it works uh you have uh in the bottom open data ecosystem. It's mean that the data will be structured with open data format uh with uh uh iceberg uh format and uh you can just access the this data with different tools. Here you can use data bricks and snowflake spark but evenb posgress analytic accelerator the new one if you

are using posgress if you are using this you can just install it and use it yeah and then you will be able uh to access the data to to to make it different data sources to be uh uh able to analyze big amounts of data and now I would like to show you uh building of BI dashboard. On the left side is traditional Postgress with the same

resources. On the right size is analytic and as you can see it will be started in the same time and in analytic already everything uh yeah created and in the postgress uh traditional postgress it will take uh uh some time to do it and uh now uh you can see that this is test running on the real system with the same uh hardware with the RAM CPUs

but analytics is really really uh quicker. Okay, let me go to the next slide and I would like to show you how it works. I u it's wrong. It's wrong but this is okay. I need to go to my user. Can you good see it or it's too small? Okay. But I cannot see because the resolution is not very good. I need to change my glasses. Okay.

Um I am going uh to my demo. Demo is running in my VM uh I have just normal Postgress Postgress version 16 and uh I installed two uh extensions. PGAA is analytics extension and PGFS this extension can help you to bind your Postgress database with some object storage. Yeah, you can use it just to have some data in object storage and accesses uh from Postgress database. Okay.

Now I have uh some schemas in my database and I have storage locations. It means what for the table for example customer what is posgress table I have one uh storage location uh here and this it will be on the s free storage on the object storage. Okay. Now I will just uh show you that my table line 10 has 6 million of uh rows and I

will run one analytic uh query here. And you will see it will be done in yeah sometimes is everything is running on my VM. Yeah it could be uh it could be done uh in and VM is in AWS maybe now network issues. Okay 12 seconds. And now I will show you the execution plan of this query and you will see that uh the uh engine is

C4 is analytics engine. And if we are looking for the data source, we will see here the pocket files. It's running on the S3 storage. Uh and you will uh have very good Okay. Uh if you have question uh please interrupt me and uh yeah we can do it interactively. Good. This is about analytics. Uh now we will tell talk about AI. Uh artificial intelligence is very

interesting and with Postgress you can really build uh the AI applications. At first I would like to uh to show you how uh is working by us by by by by humans. Here we have the brain and in our brain we have a big amount of uh uh of neuron cells here called as neurons and neurons are playing together with other neurons through the neurons network and

form from electrical impulses and and the information it will be became the information. Yeah. And uh we have our brain. We are using brain for ideas, association uh associations. And we have our memory. Yeah. And with help of our memory, we can just uh uh say okay this experience was bad. I I can improve it. We can improve our behavior and we can uh take some objectives,

goals, consciousness. uh it's working uh uh by us and with uh AI uh AI models uh are simulating this uh u neural uh networks. Yeah, our brain is uh large uh language models and our memory, our experiences, our data. If we have uh bad quality data, yeah, yeah, maybe we cannot make the good decision and the consciousness are playing from front AI apps objective goal coordination and

how it works. Uh yeah, you know all of us using chat GPT or maybe big or other things uh open AI uh you can just go to chatgpt you can uh ask the question or you can use the chat GPT models or or open AI models in your own environment. uh you can prepare your data, you can train the model if the answers are not uh very

good. You can yeah you can train it, you can create your generic uh model and then you need second uh second application for prom generic model. Yeah. And uh after you creating all of this you can generate text you can uh make similarity to uh uh search for images text you can make translation and several other apps. But what is about your private knowledge? Uh for private

knowledge you have to prepare your data. You have to to make this data readable from the uh models. You have to tune this data and you can create your own custom model and with this custom model and with data preparation you can use custom uh prompt model uh and uh yeah you will create uh your the whole stack and if you have uh chatbot for example if

you want to to to build the chatbot with uh retrieval argumented uh generation You can prepare the data in form of embeddings of vector and uh yeah you can create your own prom generic model or use uh one of the open source and very important is uh in uh the chat boards uh uh to have the data. Yeah, the data is very very very important for you

and uh in uh rack models we store the data. It could be the tables, relational uh tables, it could be uh some files, yeah, some sensor data, PDFs, images and you have to vectorize it data. Vectorized is mean uh the data will be prepared to use by some LLM model and uh will be uh converted in some yeah uh numbers yeah in in vector informations. Um if

you have the new data you have to refresh your vector database to be up to date and then you can submit chat message. uh your model will uh generate the informations and will send this to decoder only LLM and this LLM this front end LM model will uh send the response to the customer it's working uh and this is uh how work how it works retrieval generation

let me show what is possible Uh with Postgress I can create I created uh can you good see it? Okay I created two programs. The first upload files Python uh will uh download the PDF file make it in chunks because it could be very big. uh loaded in the database as the vector data and prepare to using uh of the LLM model. I will I am using

in this example uh Olama MX B AI embit large its model for generations and uh yeah I will uh download it to the database make it retrievable make a vector version of the data and you can see this program is about 79 uh lines. The second our front end LLM uh uh program who will ask uh uh take the uh questions from the customer and provide the

answers is going about uh Olama as well and I am using the same model to generate the answer and uh I have the program with about uh 170 rows and I need to create it uh everything. Yeah, I need to load Python. I need to load Oama. I have to to make it retrievable. I need to to think about performance and everything. And now uh I can

show you the database. My database is very uh uh normal Postgress database. And you can see that I have uh ver version 16 and I installed it PG vector. Yeah. PG vector is very very uh uh famous uh extension and I installed Python because I am using Python in the database and uh now I don't have any tables at the moment but I will start my first

file upload file PU with streaml and this is the application uh and uh I have to connect to my local uh and I can uh choose some uh data set name and I will uh upload to the database uh the Lithuania handbook. Yeah, informations about Lithuania. Now upload in this time uh the program will create the vectorized uh table with this PDF file. it will create it

in chunks if the PDF file is very very big and after some time I will have uh this PDF file in my database as retriever I will retrieve it I wrote uh Python script yeah I have to maintain it and but it's uh everything is possible yeah I think after after two three seconds we will be ready and uh we will see here that our Python file

will create one table with the name EDB Lithuania and this is our our yeah now I think now it's ready and we have now the data and this is really the vector table I can show you the data uh after some yeah this is the numbers this is the vector data and uh we have here several chunks in the table several uh uh cho chapters and now

data is already there. Now I will start my second application and this is really this uh chatbot application. Uh it's about run rec and uh the application uh will connect uh to the database and uh application. Yeah, see that I have one data set. Yeah, I can create several kinds of data, several data sets. Uh I can combine several PDFs. Yeah, I can create images uh uh

portion of vector data. But now I am able to ask the questions. Yeah, it's a chatbot but based only on my uh PDF file. I can uh ask how big is population and chatbot will generate me response according to the information that I saved as vector in my database. Yeah, you can see it. I can ask uh uh which industries I invest Lithuania. I make the error.

Yeah, because I will see if the chatbot can understand the human errors as well. It take times because maybe uh the program should go uh through uh several vector data. Yeah. Our performance is very important in this case. Yeah. But now I have some answer. Yeah. I have my chatbot. Yeah. And this is good. Yeah. Now we have chatbot and we can create it and it's very

very easy. [Music] But I have some questions, guys. Okay. Yeah. Need to make it again. Exactly. Okay, we have it now. It's good. Yeah. Because the guys can ask the questions. Yeah. And get the answers. Yeah. They love it. And you will get more users, more use cases, more data. it will be uh uh start to using routinely and it will be mission critical in your uh

uh company. Are you really ready for this? What's about your data control? What is about your performance? Yeah. What about uh 15 minutes? Nice. Thank you. Uh yeah, all of these questions are very very important for you guys. Yeah. Or for me too. And we have several AI applications. Yeah. L chain, llama index, they are very very good but they are working with your data as connected

data source and it means that you have to to manage your uh storage repositories, databases, access performance, backup, high availability because they cannot help you with this. They can just generate the response according to your data but the whole stack is managed should be managed by you. Yeah is a data you're stupid. Therefore uh we have the new extension extension uh is calling as AIDB and this

extension can help you the whole process to make it in the database from document preparation uh embedding creation vector indexing uh argumented uh uh response generation and everything you can do only with one uh Postgress extension. It will be done in the posgress database because now with vector database with PG vector we can do only this vector storage and index and vector search but all of these

points uh with prompt and contents windows me uh data preparation data uh uh uh vectorizing and several other things are not uh possible but with our extension it will be done uh really very very uh uh quick easy and uh I have here some examples how to create retriever and how to use retriever. can just run uh the questions human questions in the SQL form if you

have retriever and uh I would like to show you how it works because we created our Python programs and we uh did it uh to find okay yeah this one exactly okay let's me uh start the demo I have the postress normal postress database and I have only one extension and I will create a and pgfs extensions and now I have uh aid pgfs and vector extension

will be installed automatically. Uh I will create just normal uh table with restaurant menu with dish name description price and star rating of the table and I will insert 49 rows of the data. As you can see, we can see different several dishes and all is good. Uh, and I have already in my database some models. Yeah. T5 par clip here to for text similarity search images

uh uh search. You can create your own uh programs models. You can load uh uh the models to the database. And now I created the retriever. this retriever it means I will create the table vector table of my restaurant menu. Copy uh column to vectorize this description and uh yeah I created this retriever. You can create different retrievers. Yeah you can use different models different uh columns

of the table to retrieve. And uh now we have two tables. We have a restaurant menu normal classic postcress table and dish retriever vector is vectorized uh copy table. Uh now I will load the data into retriever with this bulk embedding and to make it uh refreshable. If I will uh for example add some uh rows to restaurant menu it should be automatically refresh my retriever. I

can uh uh run uh set retriever out of processing and as you can see now we have in retriever on my table 49 rows. I will add 10 rows more to to my restaurant menu. And now we have 50 uh 59 rows in both tables. Now I am able to use uh uh my database as the u uh chatbot. For example, I can ask in the form

of SQL, I would like a spicy uh meat dish. It should have a very good rating and should cost not more than €20. Please provide me eight best results. And I will got some results uh uh without writing Python code or even I would like uh a Italian dish. Yeah. and I will have four best results. As you can see, um with the AIDB we can save

really uh yeah we can write only with one SQL command to uh create retriever. We can uh use it and simplify our code. Yeah, in this example we will uh uh make a similarity search of the tables. Yeah, with 70 lines of code or even we can ask from retriever about the images or text similarity search about 55 lines of code or even one uh with retriever.

As you can see that you can be uh very quick. Yeah, you can uh reduce your complexity of your code. You have uh uh all uh options to control who can access database. Uh uh you can make performance tuning on the database level. uh you can use your backup and recovery uh high availability uh architectures in posgress to make it uh really high available and it could

be very interesting maybe for you and uh uh it. Do we have time 3 minutes? Yeah. Oh no. Uh what about the road map? I will uh talk about road map very very uh um yeah in short term uh the first thing we are planning to uh to use green plan to modernize green plan I don't know in in analytics area it's very very uh uh good

solution but the problem of the green plan was it was both from uh VMware and now by broadcom stopped the development and we have several customers of green plan and EDB now took over uh the responsible of this and will modernize green plan. Uh green plan is running on posgress. With green plan you can run pabytes uh uh uh real analytics data on pabytes because you can

scale your uh compute units and uh with some coordinators and uh we will uh get it to the market again green plan but on the new versions of postgress in AI part we will introduce uh GPU acceleration it will be uh possible to use GPUs as Well, and uh yeah, I think uh at this moment I am ready. Yeah. Do you have Thank you very much. Uh

do you have questions? Please. Yeah. So if a normal table has got a vector table associated with it, will there be any performance overheads or transactions on the normal? Yeah, it could it it could be because this is the replication. Yeah, you replicate uh the data. It could have some some impact of your CPUs. Yeah. Or memory. It's always similar to high availability. You know, you have

replication. It have very small impact of your uh uh tables because the data will be catched and replicate but not too big. I think Yeah. Ah, okay. Only with CDB postgress edition, unfortunately. Yes. Okay. I can see it here. Yeah. Cool. Have we got any other questions? Feel free to shout, shout scream. Anyone? You've obviously done a very thorough job. That's fantastic. Um, fantastic, Boris. Well, if

if if I imagine that you're going to be kind of around. Um, so if there is anything any other questions or any deeper chats that anyone wants, then I imagine they can find you find you around for the next few days. Awesome. Fantastic. Lovely. All right, round of applause for Boris. Thank you, guys.