{"id":37080,"date":"2025-02-09T20:19:22","date_gmt":"2025-02-09T19:19:22","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=37080"},"modified":"2025-03-20T16:25:30","modified_gmt":"2025-03-20T15:25:30","slug":"pgvector-a-guide-for-dba-part1-lab-demo","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pgvector-a-guide-for-dba-part1-lab-demo\/","title":{"rendered":"pgvector, a guide for DBA – Part1: LAB DEMO"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

The first release of pgvector occurred in April 2021. Since then a lot has changed and is still changing. Even though we could say that the level of maturity of AI stacks and usage is currently really amazing, things are still changing fast and will probably continue to do so for a while. This is quite exciting because as a DBA consultant, I am no longer getting questions about theory on vectors, but more about how we can leverage this PostgreSQL extension and deploy it in production data hubs or enterprise applications.
We have reached a level of simplicity of use and maturity of the AI\/ML tools that implementing it is no longer reserved for huge organizations. Anyone can build its own stack or use a cloud provider at a fairly decent price. Since I am going to give some talks on that subject throughout the year, I thought it was useful to make a small series of blog posts and share some knowledge on how a “traditional” PostgreSQL user can extend his SQL knowledge and solve problems that would have been out of reach otherwise.
In this first part, I will talk about the basics, how it works and how you can play with it in a simple LAB DEMO. In the next parts we will dig a bit deeper into performance tuning, scalability, index tuning and internals of vector workflows in our beloved open source RDBMS.

As a reminder here is the Git repo<\/a><\/strong> with the instructions and all the links for the other parts of this series.
Part 2 : pgvector, a guide for DBA \u2013 Part2 Indexes<\/a>
Part 3 : pgvector, a guide for DBA \u2013 Part3 AI Agent and workflows<\/a>
Part 4 : pgvector, a guide for DBA – Part 4 AI Workflows Best practices ( not released yet )<\/p>\n\n\n\n

Why ?<\/h2>\n\n\n\n

Before jumping into the LAB DEMO, let’s talk about why you would use pgvector. If you’re not familiar with this, we could say simply that pgvector is an extension of PostgreSQL that adds a vector data type, with facilities (indexes, operators, …) to store embeddings alongside standard known relational data.
Embeddings are mathematical representations of other objects (like text, images, videos,… etc) into multi-dimensional vectors. The more dimensions your vector has, the more you will be able to encapsulate “meanings” into it. This comes with a cost of complexity of the mathematical operations hence, more compute and token consumption. Embeddings are still kinda magic to me, the simplicity and the elegance of putting words meaning into numbers blows my mind. If you don’t know how it works then it’s magic \ud83d\ude42<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

Here are a few use cases of vectors: similarity search, semantic text search, hybrid search, recommendation system, geospatial + vector search, RAG search, …
I would suggest looking at the PostgreSQLconf.eu Youtube Channel<\/a> and particularly this talk<\/a> from Varun Dhawan which I had the chance to see live in Athens last October.
<\/p>\n\n\n\n

LAB DEMO<\/h2>\n\n\n\n
\n\n\n\n

Architecture<\/h3>\n\n\n\n

We have initially 2 sample databases, dvdrental and netflix_shows. The idea is to merge both databases into one the be able to query tables from both. This can be associated to our standard DWH process.<\/p>\n\n\n\n

Then we are going to create new fields on the film and the netflix_shows tables to host our embeddings (this is possible because of the pgvector extension).
Embeddings are mathematical representations of the movies descriptions in our case.
We are going to use a Python script to connect to OpenAI embedding generation model to inserts those in our tables. Then with another script, we will query those embedding and look for similarities.
The aim here is to propose to DVDRental’s users, Netflix shows they might like based on their rental activity. Similar processes can be found on numerous modern applications in order to propose something that would fit your taste or mood. This practical case can help you see the underlying complexity or in this case the simplicity of extending PostgreSQL. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n


<\/p>\n\n\n\n

\n\n\n\n

Installation steps
<\/h3>\n\n\n\n

Here are the steps to build your environment : <\/strong><\/p>\n\n\n\n

<\/p>\n\n\n\n