Working with AI data stored in Postgres tables
Suggest editsThe examples on this page are about working with AI data stored in columns in the Postgres table.
To see how to use AI data stored in S3-compatible object storage, skip to working with AI data in S3.
Begin by creating a Postgres table for some test AI data:
Working with auto embedding
Next, you are going to create a retriever with the just created products table as the source using the pgai.create_pg_retriever
function which has this syntax:
- The
retriever_name
is used to identify and reference the retriever; set it toproduct_embeddings_auto
for this example. - The
schema_name
is the schema where the source table is located; set this topublic
. - The
primary_key
is the primary key column of the source table. - The
model_name
is the name of the embeddings encoder model for similarity data; set it toall-MiniLM-L6-v2
to use the open encoder model for text data from HuggingFace. - The
data_type
is the type of data in the source table, which could be eitherimg
ortext
. Set it totext
. - The
source_table
is the name of the source table. The source table created previously, isproducts
so set it to that. - The
columns
is an array of columns to use for the similarity search by the retriever. Set this toARRAY['product_name', 'description']
to use the product_name and description columns. - The
auto_embedding
is a boolean value to set a trigger for auto embeddings. Set it to TRUE so that any future insert, update or delete to the source table shall automatically generate, update or delete also the corresponding embedding.
This gives the following SQL command:
You have now created a retriever for the products table. The next step is to insert some AI data records into the products table.
Since you set auto_embedding
to true, the retriever shall automatically generate all embeddings in real-time for each inserted record:
Now you can use the retriever, by specifying the retriever name, to perform a similarity retrieval of the top K most relevant, in this case most similar, AI data items. You can do this by running the pgai.retrieve
function with the required parameters:
- The
query
is the text to use to retrieve the top similar data. Set it toI like it
. - The
top_k
is the number of top similar data items to retrieve. Set this to 5 - The
retriever_name
is the name of the retriever. The retriever's name isproduct_embeddings_auto
.
This gives the following SQL command:
Working without auto embedding
You can now create a retriever without auto embedding. This means that the application has control over when the embeddings computation occurs. It also means that the computation is a bulk operation. For demonstration you can simply create a second retriever for the same products table that you just previously created the first retriever for, but setting auto_embedding
to false.
The AI records are already in the table though. As this second retriever is newly created, it won't have created any embeddings. Running pgai.retrieve
using the retriever now doesn't return any results:
You need to run a bulk generation of embeddings before performing any retrieval. You can do this using the pgai.refresh_retriever
function:
The retriever_name
is the name of the retriever. Our retriever's name is product_embeddings_bulk
. So the SQL command is:
You can now run that retrieve operation using the second retriever and get the same results as with the first retriever:
The next step is to see what happens if when you add more AI data records:
The new data is automatically picked up in the retrieval from the first retriever with auto embeddings:
The second retriever without auto embedding doesn't reflect the new data. It can only do so when once there has been another explicit call to pgai.refresh_retriever
. Until then, the results don't change:
If you now call pgai.refresh_retriever()
again, the embeddings computation uses the new data to refresh the embeddings:
And the new data shows up in the results of the query when you call the pgai.retrieve
function again:
You used the two different retrievers for the same source data just to demonstrate the workings of auto embedding compared to explicit refresh_retriever()
.
In practice you may want to combine auto embedding and refresh_retriever() in a single retriever to conduct an initial embedding of data that existed before you created the retriever and then rely on auto embedding for any future data that's ingested, updated, or deleted.
You should consider relying on pgai.refresh_retriever
, and not using auto embedding, if you typically ingest a lot of AI data at once as a batch.
- On this page
- Working with auto embedding
Could this page be better? Report a problem or suggest an addition!