nbs/tsv_python_getting_started_tutorial.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tutorial: Timescale Vector (PostgreSQL) Python Client Library"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook shows how to use the PostgreSQL as vector database via the [Python Vector python client library](https://github.com/timescale/python-vector). You'll learn how to use the client for (1) semantic search, (2) time-based vector search, (3) and how to create indexes to speed up queries.\n",
    "\n",
    "Follow along by downloading the [Jupyter notebook version of this tutorial here](https://github.com/timescale/python-vector/blob/main/nbs/tsv_python_getting_started_tutorial.ipynb).\n",
    "\n",
    "Sample dataset: We'll analyze a gitlog dataset (git commit messages) and use the vector embeddings of the commit messages to find relevant commit messages to a given query question. Each git commit entry has a timestamp associated with it, as well as natural language message and other metadata (e.g author, commit hash etc). \n",
    "\n",
    "## What is Timescale Vector?\n",
    "**[Timescale Vector](https://www.timescale.com/ai) is PostgreSQL++ for AI applications.**\n",
    "\n",
    "Timescale Vector enables you to efficiently store and query millions of vector embeddings in `PostgreSQL`.\n",
    "\n",
    "- Enhances `pgvector` with faster and more accurate similarity search on 100M+ vectors via `DiskANN` inspired indexing algorithm.\n",
    "- Enables fast time-based vector search via automatic time-based partitioning and indexing.\n",
    "- Provides a familiar SQL interface for querying vector embeddings and relational data.\n",
    "\n",
    "Timescale Vector is cloud PostgreSQL for AI that scales with you from POC to production:\n",
    "\n",
    "- Simplifies operations by enabling you to store relational metadata, vector embeddings, and time-series data in a single database.\n",
    "- Benefits from rock-solid PostgreSQL foundation with enterprise-grade feature liked streaming backups and replication, high-availability and row-level security.\n",
    "- Enables a worry-free experience with enterprise-grade security and compliance.\n",
    "\n",
    "## How to access Timescale Vector\n",
    "Timescale Vector is available on [Timescale](https://www.timescale.com/ai), the cloud PostgreSQL platform. (There is no self-hosted version at this time.)\n",
    "\n",
    "Good news! You get a 90-day free trial for Timescale Vector:\n",
    "\n",
    "- To get started, [signup](https://console.cloud.timescale.com/signup?utm_campaign=vectorlaunch&utm_source=github&utm_medium=direct) to Timescale, create a new database and follow this notebook!\n",
    "- See the [Timescale Vector explainer blog](https://www.timescale.com/blog/how-we-made-postgresql-the-best-vector-database/?utm_campaign=vectorlaunch&utm_source=github&utm_medium=direct) for more details and performance benchmarks."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0. Setup"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Download the [Jupyter notebook version of this tutorial](https://github.com/timescale/python-vector/blob/main/nbs/tsv_python_getting_started_tutorial.ipynb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install needed packages\n",
    "!pip install timescale-vector\n",
    "!pip install openai\n",
    "!pip install tiktoken\n",
    "!pip install python-dotenv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import needed packages\n",
    "import os\n",
    "from dotenv import load_dotenv, find_dotenv\n",
    "import timescale_vector\n",
    "from timescale_vector import client\n",
    "import openai\n",
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "import numpy as np\n",
    "import json\n",
    "import tiktoken\n",
    "import ast\n",
    "import math\n",
    "import uuid\n",
    "from datetime import datetime\n",
    "from datetime import timedelta\n",
    "from typing import List, Tuple"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll use OpenAI's embedding models so let's load our OpenAI API keys from a `.env` file.\n",
    "\n",
    "If you do not have an OpenAI API Key, signup for an OpenAI Developer Account and create an API Key. See [OpenAI's developer platform](https://platform.openai.com/overview) for more information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run export OPENAI_API_KEY=sk-YOUR_OPENAI_API_KEY...\n",
    "# Get openAI api key by reading local .env file\n",
    "\n",
    "_ = load_dotenv(find_dotenv())\n",
    "openai.api_key  = os.environ['OPENAI_API_KEY']"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next let's load our Timescale service URL which we'll use to connect to our cloud PostgreSQL database hosted on Timescale.\n",
    "\n",
    "Launch a PostgreSQL database on [Timescale](https://console.cloud.timescale.com/signup?utm_campaign=vectorlaunch&utm_source=github&utm_medium=direct) and download the `.env` file after your service is created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the service url by reading local .env file\n",
    "# The .env file should contain a line starting with `TIMESCALE_SERVICE_URL=postgresql://`\n",
    "_ = load_dotenv(find_dotenv())\n",
    "TIMESCALE_SERVICE_URL = os.environ[\"TIMESCALE_SERVICE_URL\"]\n",
    "\n",
    "# OR set it explicitly\n",
    "# TIMESCALE_SERVICE_URL = \"postgres://tsdbadmin:<password>@<id>.tsdb.cloud.timescale.com:<port>/tsdb?sslmode=require\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Create a table to store the vectors and metadata\n",
    "\n",
    "First, we'll define a table name, which will be the name of our table in the PostgreSQL database. \n",
    "\n",
    "We set the `time_partition_interval` argument in the client creation function to enable automatic time-based partitioning of the table. This will partition the table into time-based chunks (in this case each containing data for 7 days) and create indexes on the time-based chunks to speed up time-based queries.\n",
    "\n",
    "Each partition will consist of data for the specified length of time. We'll use 7 days for simplicity, but you can pick whatever value make sense for your use case -- for example if you query recent vectors frequently you might want to use a smaller time delta like 1 day, or if you query vectors over a decade long time period then you might want to use a larger time delta like 6 months or 1 year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#| hide\n",
    "import asyncpg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#| hide\n",
    "con = await asyncpg.connect(TIMESCALE_SERVICE_URL)\n",
    "await con.execute(\"DROP TABLE IF EXISTS commit_history;\")\n",
    "await con.execute(\"DROP EXTENSION IF EXISTS vector CASCADE\")\n",
    "await con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Table information\n",
    "TABLE_NAME = \"commit_history\"\n",
    "EMBEDDING_DIMENSIONS = 1536\n",
    "TIME_PARTITION_INTERVAL = timedelta(days=7)\n",
    "\n",
    "# Create client object\n",
    "vec = client.Async(TIMESCALE_SERVICE_URL, \n",
    "                   TABLE_NAME,  \n",
    "                   EMBEDDING_DIMENSIONS, \n",
    "                   time_partition_interval=TIME_PARTITION_INTERVAL)\n",
    "\n",
    "# create table\n",
    "await vec.create_tables()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `create_tables()` function will create a table with the following schema:\n",
    "\n",
    "id | metadata | contents | embedding\n",
    "\n",
    "- `id` is the UUID which uniquely identifies each vector.\n",
    "- `metadata` is a JSONB column which stores the metadata associated with each vector. \n",
    "- `contents` is the text column which stores the content we want vectorized (in this case the commit message).\n",
    "- `embedding` is the vector column which stores the vector embedding representation of the content."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Load in dataset, create vector embeddings, and prepare data for ingestion\n",
    "\n",
    "### Load sample dataset\n",
    "First, you'll need to [download the sample dataset](https://s3.amazonaws.com/assets.timescale.com/ai/commit_history.csv) and place it in the same directory as this notebook.\n",
    "\n",
    "You can use following command:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the file using curl and save it as commit_history.csv\n",
    "# Note: Execute this command in your terminal, in the same directory as the notebook\n",
    "# curl -O https://s3.amazonaws.com/assets.timescale.com/ai/commit_history.csv"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then, we'll load in the gitlog dataset CSV file into a pandas dataframe.\n",
    "\n",
    "Note: Since this is a demo, we will only work with the first 1000 records. In practice, you can load as many records as you want."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the path to the dataset file relative to this notebook\n",
    "file_path = Path(\"commit_history.csv\")\n",
    "\n",
    "# Read the CSV file into a DataFrame\n",
    "df = pd.read_csv(file_path)\n",
    "\n",
    "# Light data cleaning on CSV\n",
    "df.dropna(inplace=True)\n",
    "df = df.astype(str)\n",
    "df = df[:1000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>commit</th>\n",
       "      <th>author</th>\n",
       "      <th>date</th>\n",
       "      <th>change summary</th>\n",
       "      <th>change details</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>44e41c12ab25e36c202f58e068ced262eadc8d16</td>\n",
       "      <td>Lakshmi Narayanan Sreethar&lt;lakshmi@timescale.com&gt;</td>\n",
       "      <td>Tue Sep 5 21:03:21 2023 +0530</td>\n",
       "      <td>Fix segfault in set_integer_now_func</td>\n",
       "      <td>When an invalid function oid is passed to set_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>e66a40038e3c84fb1a68da67ad71caf75c64a027</td>\n",
       "      <td>Bharathy&lt;satish.8483@gmail.com&gt;</td>\n",
       "      <td>Sat Sep 2 09:24:31 2023 +0530</td>\n",
       "      <td>Fix server crash on UPDATE of compressed chunk</td>\n",
       "      <td>UPDATE query with system attributes in WHERE c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>c6a930897e9f9e9878db031cc7fb6ea79d721a74</td>\n",
       "      <td>Jan Nidzwetzki&lt;jan@timescale.com&gt;</td>\n",
       "      <td>Tue Aug 29 21:13:51 2023 +0200</td>\n",
       "      <td>Use Debian Bookworm for 32-bit tests</td>\n",
       "      <td>So far, we have used Debian Buster (10) for ou...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8e941b80ae1b0e0b6affe5431454cdc637628d99</td>\n",
       "      <td>Lakshmi Narayanan Sreethar&lt;lakshmi@timescale.com&gt;</td>\n",
       "      <td>Mon Aug 28 23:19:22 2023 +0530</td>\n",
       "      <td>Fix incorrect row count in EXPLAIN ANALYZE INS...</td>\n",
       "      <td>INSERT ... ON CONFLICT statements record few m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>caada43454e25d3098744fa6b675ac7d07390550</td>\n",
       "      <td>Lakshmi Narayanan Sreethar&lt;lakshmi@timescale.com&gt;</td>\n",
       "      <td>Tue May 30 20:32:29 2023 +0530</td>\n",
       "      <td>PG16: Fix concurrent update issues with MERGE.</td>\n",
       "      <td>PG16 commit postgres/postgres@9321c79c fixes a...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      commit  \\\n",
       "0   44e41c12ab25e36c202f58e068ced262eadc8d16   \n",
       "1   e66a40038e3c84fb1a68da67ad71caf75c64a027   \n",
       "2   c6a930897e9f9e9878db031cc7fb6ea79d721a74   \n",
       "3   8e941b80ae1b0e0b6affe5431454cdc637628d99   \n",
       "4   caada43454e25d3098744fa6b675ac7d07390550   \n",
       "\n",
       "                                              author  \\\n",
       "0  Lakshmi Narayanan Sreethar<lakshmi@timescale.com>   \n",
       "1                    Bharathy<satish.8483@gmail.com>   \n",
       "2                  Jan Nidzwetzki<jan@timescale.com>   \n",
       "3  Lakshmi Narayanan Sreethar<lakshmi@timescale.com>   \n",
       "4  Lakshmi Narayanan Sreethar<lakshmi@timescale.com>   \n",
       "\n",
       "                             date  \\\n",
       "0   Tue Sep 5 21:03:21 2023 +0530   \n",
       "1   Sat Sep 2 09:24:31 2023 +0530   \n",
       "2  Tue Aug 29 21:13:51 2023 +0200   \n",
       "3  Mon Aug 28 23:19:22 2023 +0530   \n",
       "4  Tue May 30 20:32:29 2023 +0530   \n",
       "\n",
       "                                      change summary  \\\n",
       "0               Fix segfault in set_integer_now_func   \n",
       "1     Fix server crash on UPDATE of compressed chunk   \n",
       "2               Use Debian Bookworm for 32-bit tests   \n",
       "3  Fix incorrect row count in EXPLAIN ANALYZE INS...   \n",
       "4     PG16: Fix concurrent update issues with MERGE.   \n",
       "\n",
       "                                      change details  \n",
       "0  When an invalid function oid is passed to set_...  \n",
       "1  UPDATE query with system attributes in WHERE c...  \n",
       "2  So far, we have used Debian Buster (10) for ou...  \n",
       "3  INSERT ... ON CONFLICT statements record few m...  \n",
       "4  PG16 commit postgres/postgres@9321c79c fixes a...  "
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Take a look at the data in the csv (optional)\n",
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create vector embeddings\n",
    "\n",
    "Next we'll create vector embeddings of the commit messages using the OpenAI API. We'll use the `text-embedding-ada-002` model to create the embeddings [(Learn more here).](https://platform.openai.com/docs/guides/embeddings/what-are-embeddings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create embeddings for each commit message\n",
    "\n",
    "embedding_list = []\n",
    "content_list = []\n",
    "\n",
    "openai_client = openai.Client()\n",
    "\n",
    "# Helper function: get embeddings for a text\n",
    "def get_embeddings(text):\n",
    "    response = openai_client.embeddings.create(\n",
    "        model=\"text-embedding-ada-002\",\n",
    "        input = text.replace(\"\\n\",\" \")\n",
    "    )\n",
    "    embedding = response.data[0].embedding\n",
    "    return embedding\n",
    "\n",
    "for index, row in df.iterrows():\n",
    "    #construct text we want to embed\n",
    "    text = row['author'] + \" \"+ row['date'] + \" \" +row['commit']+ \" \" + row['change summary'] + \" \"+ row['change details']\n",
    "    content_list.append(text)\n",
    "    embedding = get_embeddings(text)\n",
    "    embedding_list.append(embedding)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Append embddings and content to dataframe\n",
    "df['content'] = content_list\n",
    "df['embedding'] = embedding_list"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare data for ingestion\n",
    "\n",
    "Next, we'll create a uuid for each git log entry.\n",
    "\n",
    "We'll define a helper funciton `create_uuid()` to create a uuid for commit message and associated vector embedding based on its timestamp. \n",
    "\n",
    "In the helper function, we'll use the timescale vector client library's `uuid_from_time()` method to take a date and create a uuid with a datetime portion that reflects the date string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create uuids for each message\n",
    "uuid_list = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# helper function to take in a date string in the past and return a uuid v1\n",
    "def create_uuid(date_string: str):\n",
    "    if date_string is None:\n",
    "        return None\n",
    "    time_format = '%a %b %d %H:%M:%S %Y %z'\n",
    "    datetime_obj = datetime.strptime(date_string, time_format)\n",
    "    uuid = client.uuid_from_time(datetime_obj)\n",
    "    return str(uuid)\n",
    "\n",
    "for index, row in df.iterrows():\n",
    "    uuid = create_uuid(row['date'])\n",
    "    uuid_list.append(uuid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add uuids to dataframe\n",
    "df['uuid'] = uuid_list"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's create a json of metadata for each entry in our dataset.\n",
    "\n",
    "We'll again use some helper functions to ensure we have data in the right format for easy filtering."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Helper functions\n",
    "# Helper function to split name and email given an author string consisting of Name Lastname <email>\n",
    "def split_name(input_string: str) -> Tuple[str, str]:\n",
    "    if input_string is None:\n",
    "        return None, None\n",
    "    start = input_string.find(\"<\")\n",
    "    end = input_string.find(\">\")\n",
    "    name = input_string[:start].strip()\n",
    "    return name\n",
    "\n",
    "def create_date(input_string: str) -> datetime:\n",
    "    if input_string is None:\n",
    "        return None\n",
    "    # Define a dictionary to map month abbreviations to their numerical equivalents\n",
    "    month_dict = {\n",
    "        \"Jan\": \"01\",\n",
    "        \"Feb\": \"02\",\n",
    "        \"Mar\": \"03\",\n",
    "        \"Apr\": \"04\",\n",
    "        \"May\": \"05\",\n",
    "        \"Jun\": \"06\",\n",
    "        \"Jul\": \"07\",\n",
    "        \"Aug\": \"08\",\n",
    "        \"Sep\": \"09\",\n",
    "        \"Oct\": \"10\",\n",
    "        \"Nov\": \"11\",\n",
    "        \"Dec\": \"12\",\n",
    "    }\n",
    "\n",
    "    # Split the input string into its components\n",
    "    components = input_string.split()\n",
    "    # Extract relevant information\n",
    "    day = components[2]\n",
    "    month = month_dict[components[1]]\n",
    "    year = components[4]\n",
    "    time = components[3]\n",
    "    timezone_offset_minutes = int(components[5])  # Convert the offset to minutes\n",
    "    timezone_hours = timezone_offset_minutes // 60  # Calculate the hours\n",
    "    timezone_minutes = timezone_offset_minutes % 60  # Calculate the remaining minutes\n",
    "    # Create a formatted string for the timestamptz in PostgreSQL format\n",
    "    timestamp_tz_str = f\"{year}-{month}-{day} {time}+{timezone_hours:02}{timezone_minutes:02}\"\n",
    "    return timestamp_tz_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata_list = []\n",
    "\n",
    "for index, row in df.iterrows():\n",
    "    metadata = {\n",
    "        \"author\": split_name(row['author']),\n",
    "        \"date\": create_date(row['date']),\n",
    "        \"commit\": row['commit'],\n",
    "    }\n",
    "    metadata_list.append(metadata)\n",
    "\n",
    "# Add metadata to dataframe\n",
    "df['metadata'] = metadata_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'author': 'Lakshmi Narayanan Sreethar', 'date': '2023-09-5 21:03:21+0850', 'commit': '44e41c12ab25e36c202f58e068ced262eadc8d16'}\n"
     ]
    }
   ],
   "source": [
    "print(metadata_list[0])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've finished loading in our dataset and preparing it for storage in Timescale Vector. \n",
    "\n",
    "Note that we've explained each step of the data preparation process in detail for the purposes of this tutorial. In practice, you can create embeddings, uuids and metadata in a single step."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Add data to Timescale Vector\n",
    "\n",
    "Next, we'll add our commit history data to the `commit_history` table in Timescale Vector.\n",
    "\n",
    "We'll prepare a list of tuples to add to the table. Each tuple will contain the following data:\n",
    "\n",
    "`(id, metadata, contents, embedding)`\n",
    "\n",
    "to match the schema of our table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remind ourselves of the data in the dataframe\n",
    "# df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert dataframe to array of tuples\n",
    "records = []\n",
    "for index, row in df.iterrows():\n",
    "    record = (row['uuid'], row['metadata'], row['content'], row['embedding'])\n",
    "    records.append(record)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lastly, we batch upsert the data into the table using the `.upsert()` method passing in our list of tuples we prepared above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# batch upsert vectors into table\n",
    "await vec.upsert(records)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Similarity search with Timescale Vector\n",
    "\n",
    "### Simple similarity search\n",
    "Let's see an example of performing a simple similarity search using Timescale Vector, where we'll find the most similar commit messages to a given query by performing a nearest neighbor search."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define search query and query_embedding\n",
    "query_string = \"What's new with continuous aggregates?\"\n",
    "query_embedding = get_embeddings(query_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# search table for similar vectors to query_embedding\n",
    "records = await vec.search(query_embedding)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The response from the `.search()` method returns a list `Records` objects. Each `Record` object contains the following attributes: `id`, `metadata`, `contents`, `embedding`, `distance`.\n",
    "\n",
    "The results are sorted by distance. The first result is the most similar to our query.\n",
    "\n",
    "Let's inspect them below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record id=UUID('18331d00-fc57-11ec-a166-06cee12dbc78') metadata={'date': '2022-07-5 13:39:14+0320', 'author': 'Fabrízio de Royes Mello', 'commit': ' e34218ce2963358a500f6bc315aace0fad29c450'} contents=\"Fabrízio de Royes Mello<fabriziomello@gmail.com> Tue Jul 5 13:39:14 2022 +0200  e34218ce2963358a500f6bc315aace0fad29c450 Migrate Continuous Aggregates to the new format Timescale 2.7 released a new version of Continuous Aggregate (#4269) that store the final aggregation state instead of the byte array of the partial aggregate state, offering multiple opportunities of optimizations as well a more compact form.  When upgrading to Timescale 2.7, new created Continuous Aggregates are using the new format, but existing Continuous Aggregates keep using the format they were defined with.  Created a procedure to upgrade existing Continuous Aggregates from the old format to the new format, by calling a simple procedure:  test=# CALL cagg_migrate('conditions_summary_daily');  Closes #4424 \" embedding=array([-0.02072006, -0.00232497, -0.00290987, ..., -0.00420762,\n",
       "        -0.00879542, -0.02118798], dtype=float32) distance=0.15402132505614874>,\n",
       " <Record id=UUID('18331d00-fc57-11ec-8f40-352ea14812b8') metadata={'date': '2022-07-5 13:39:14+0320', 'author': 'Fabrízio de Royes Mello', 'commit': ' e34218ce2963358a500f6bc315aace0fad29c450'} contents=\"Fabrízio de Royes Mello<fabriziomello@gmail.com> Tue Jul 5 13:39:14 2022 +0200  e34218ce2963358a500f6bc315aace0fad29c450 Migrate Continuous Aggregates to the new format Timescale 2.7 released a new version of Continuous Aggregate (#4269) that store the final aggregation state instead of the byte array of the partial aggregate state, offering multiple opportunities of optimizations as well a more compact form.  When upgrading to Timescale 2.7, new created Continuous Aggregates are using the new format, but existing Continuous Aggregates keep using the format they were defined with.  Created a procedure to upgrade existing Continuous Aggregates from the old format to the new format, by calling a simple procedure:  test=# CALL cagg_migrate('conditions_summary_daily');  Closes #4424 \" embedding=array([-0.02072006, -0.00232497, -0.00290987, ..., -0.00420762,\n",
       "        -0.00879542, -0.02118798], dtype=float32) distance=0.15402132505614874>,\n",
       " <Record id=UUID('c98d1c00-6c13-11ed-b2e5-ba3746d2d4a5') metadata={'date': '2022-11-24 13:19:36+-500', 'author': 'Fabrízio de Royes Mello', 'commit': ' 35c91204987ccb0161d745af1a39b7eb91bc65a5'} contents='Fabrízio de Royes Mello<fabriziomello@gmail.com> Thu Nov 24 13:19:36 2022 -0300  35c91204987ccb0161d745af1a39b7eb91bc65a5 Add Hierarchical Continuous Aggregates validations Commit 3749953e introduce Hierarchical Continuous Aggregates (aka Continuous Aggregate on top of another Continuous Aggregate) but it lacks of some basic validations.  Validations added during the creation of a Hierarchical Continuous Aggregate:  * Forbid create a continuous aggregate with fixed-width bucket on top of   a continuous aggregate with variable-width bucket.  * Forbid incompatible bucket widths:   - should not be equal;   - bucket width of the new continuous aggregate should be greater than     the source continuous aggregate;   - bucket width of the new continuous aggregate should be multiple of     the source continuous aggregate. ' embedding=array([-0.03262706, -0.0018098 , -0.01641467, ...,  0.00157952,\n",
       "        -0.01413165, -0.01476743], dtype=float32) distance=0.15454035563716317>,\n",
       " <Record id=UUID('c98d1c00-6c13-11ed-9626-aeca4bbf6c5d') metadata={'date': '2022-11-24 13:19:36+-500', 'author': 'Fabrízio de Royes Mello', 'commit': ' 35c91204987ccb0161d745af1a39b7eb91bc65a5'} contents='Fabrízio de Royes Mello<fabriziomello@gmail.com> Thu Nov 24 13:19:36 2022 -0300  35c91204987ccb0161d745af1a39b7eb91bc65a5 Add Hierarchical Continuous Aggregates validations Commit 3749953e introduce Hierarchical Continuous Aggregates (aka Continuous Aggregate on top of another Continuous Aggregate) but it lacks of some basic validations.  Validations added during the creation of a Hierarchical Continuous Aggregate:  * Forbid create a continuous aggregate with fixed-width bucket on top of   a continuous aggregate with variable-width bucket.  * Forbid incompatible bucket widths:   - should not be equal;   - bucket width of the new continuous aggregate should be greater than     the source continuous aggregate;   - bucket width of the new continuous aggregate should be multiple of     the source continuous aggregate. ' embedding=array([-0.03262706, -0.0018098 , -0.01641467, ...,  0.00157952,\n",
       "        -0.01413165, -0.01476743], dtype=float32) distance=0.15454035563716317>,\n",
       " <Record id=UUID('2144db80-88bd-11ec-8cea-ce147abb2c4b') metadata={'date': '2022-02-8 09:57:23+0140', 'author': 'Erik Nordström', 'commit': ' 5af9f45488d51027804cac16362811f71a89bb64'} contents='Erik Nordström<erik@timescale.com> Tue Feb 8 09:57:23 2022 +0100  5af9f45488d51027804cac16362811f71a89bb64 Add extra telemetry for continuous aggregates Add the following telemetry fields for continuous aggregates:  * The number of continuous aggregates created on distributed   hypertables * The number of continuous aggregates using real-time aggregation ' embedding=array([-0.02287812, -0.02124397,  0.01628467, ..., -0.01387608,\n",
       "        -0.01325794, -0.01354214], dtype=float32) distance=0.15761212923621704>,\n",
       " <Record id=UUID('2144db80-88bd-11ec-a0f8-155e201a9074') metadata={'date': '2022-02-8 09:57:23+0140', 'author': 'Erik Nordström', 'commit': ' 5af9f45488d51027804cac16362811f71a89bb64'} contents='Erik Nordström<erik@timescale.com> Tue Feb 8 09:57:23 2022 +0100  5af9f45488d51027804cac16362811f71a89bb64 Add extra telemetry for continuous aggregates Add the following telemetry fields for continuous aggregates:  * The number of continuous aggregates created on distributed   hypertables * The number of continuous aggregates using real-time aggregation ' embedding=array([-0.02287812, -0.02124397,  0.01628467, ..., -0.01387608,\n",
       "        -0.01325794, -0.01354214], dtype=float32) distance=0.15761212923621704>,\n",
       " <Record id=UUID('dddb6100-d17a-11ec-8fe6-3457c1f43f23') metadata={'date': '2022-05-11 19:36:58+-500', 'author': 'Fabrízio de Royes Mello', 'commit': ' f266f5cf564fcc5509b91493a39eb201c6f5914a'} contents=\"Fabrízio de Royes Mello<fabriziomello@gmail.com> Wed May 11 19:36:58 2022 -0300  f266f5cf564fcc5509b91493a39eb201c6f5914a Continuous Aggregates finals form Following work started by #4294 to improve performance of Continuous Aggregates by removing the re-aggregation in the user view.  This PR get rid of `partialize_agg` and `finalize_agg` aggregate functions and store the finalized aggregated (plain) data in the materialization hypertable.  Because we're not storing partials anymore and removed the re-aggregation, now is be possible to create indexes on aggregated columns in the materialization hypertable in order to improve the performance even more.  Also removed restrictions on types of aggregates users can perform with Continuous Aggregates: * aggregates with DISTINCT * aggregates with FILTER * aggregates with FILTER in HAVING clause * aggregates without combine function * ordered-set aggregates * hypothetical-set aggregates  By default new Continuous Aggregates will be created using this new format, but the previous version (with partials) will be supported.  Users can create the previous style by setting to `false` the storage paramater named `timescaledb.finalized` during the creation of the Continuous Aggregate.  Fixes #4233 \" embedding=array([-0.03077092,  0.0143465 , -0.01135488, ..., -0.00501059,\n",
       "        -0.01490651, -0.02304872], dtype=float32) distance=0.1637590571138441>,\n",
       " <Record id=UUID('dddb6100-d17a-11ec-825d-6556061133f2') metadata={'date': '2022-05-11 19:36:58+-500', 'author': 'Fabrízio de Royes Mello', 'commit': ' f266f5cf564fcc5509b91493a39eb201c6f5914a'} contents=\"Fabrízio de Royes Mello<fabriziomello@gmail.com> Wed May 11 19:36:58 2022 -0300  f266f5cf564fcc5509b91493a39eb201c6f5914a Continuous Aggregates finals form Following work started by #4294 to improve performance of Continuous Aggregates by removing the re-aggregation in the user view.  This PR get rid of `partialize_agg` and `finalize_agg` aggregate functions and store the finalized aggregated (plain) data in the materialization hypertable.  Because we're not storing partials anymore and removed the re-aggregation, now is be possible to create indexes on aggregated columns in the materialization hypertable in order to improve the performance even more.  Also removed restrictions on types of aggregates users can perform with Continuous Aggregates: * aggregates with DISTINCT * aggregates with FILTER * aggregates with FILTER in HAVING clause * aggregates without combine function * ordered-set aggregates * hypothetical-set aggregates  By default new Continuous Aggregates will be created using this new format, but the previous version (with partials) will be supported.  Users can create the previous style by setting to `false` the storage paramater named `timescaledb.finalized` during the creation of the Continuous Aggregate.  Fixes #4233 \" embedding=array([-0.03077092,  0.0143465 , -0.01135488, ..., -0.00501059,\n",
       "        -0.01490651, -0.02304872], dtype=float32) distance=0.1637590571138441>,\n",
       " <Record id=UUID('0df31a00-44f7-11ed-82a8-18143619d1eb') metadata={'date': '2022-10-5 18:45:40+-500', 'author': 'Fabrízio de Royes Mello', 'commit': ' 3749953e9704e45df8f621607989ada0714ce28d'} contents='Fabrízio de Royes Mello<fabriziomello@gmail.com> Wed Oct 5 18:45:40 2022 -0300  3749953e9704e45df8f621607989ada0714ce28d Hierarchical Continuous Aggregates Enable users create Hierarchical Continuous Aggregates (aka Continuous Aggregates on top of another Continuous Aggregates).  With this PR users can create levels of aggregation granularity in Continuous Aggregates making the refresh process even faster.  A problem with this feature can be in upper levels we can end up with the \"average of averages\". But to get the \"real average\" we can rely on \"stats_aggs\" TimescaleDB Toolkit function that calculate and store the partials that can be finalized with other toolkit functions like \"average\" and \"sum\".  Closes #1400 ' embedding=array([-0.02771199,  0.01358744,  0.00311197, ..., -0.00547272,\n",
       "        -0.01917629, -0.03033948], dtype=float32) distance=0.16440806282766374>,\n",
       " <Record id=UUID('0df31a00-44f7-11ed-99ea-57328005937d') metadata={'date': '2022-10-5 18:45:40+-500', 'author': 'Fabrízio de Royes Mello', 'commit': ' 3749953e9704e45df8f621607989ada0714ce28d'} contents='Fabrízio de Royes Mello<fabriziomello@gmail.com> Wed Oct 5 18:45:40 2022 -0300  3749953e9704e45df8f621607989ada0714ce28d Hierarchical Continuous Aggregates Enable users create Hierarchical Continuous Aggregates (aka Continuous Aggregates on top of another Continuous Aggregates).  With this PR users can create levels of aggregation granularity in Continuous Aggregates making the refresh process even faster.  A problem with this feature can be in upper levels we can end up with the \"average of averages\". But to get the \"real average\" we can rely on \"stats_aggs\" TimescaleDB Toolkit function that calculate and store the partials that can be finalized with other toolkit functions like \"average\" and \"sum\".  Closes #1400 ' embedding=array([-0.02771199,  0.01358744,  0.00311197, ..., -0.00547272,\n",
       "        -0.01917629, -0.03033948], dtype=float32) distance=0.16440806282766374>]"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "records"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at how to access the fields of a record object. The client provides helper methods to easily access the fields of a record object.\n",
    "\n",
    "We'll use them inspect the content of the results to see that they are indeed relevant to the query we searched for. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------------------------------------------------\n",
      "Fabrízio de Royes Mello<fabriziomello@gmail.com> Tue Jul 5 13:39:14 2022 +0200  e34218ce2963358a500f6bc315aace0fad29c450 Migrate Continuous Aggregates to the new format Timescale 2.7 released a new version of Continuous Aggregate (#4269) that store the final aggregation state instead of the byte array of the partial aggregate state, offering multiple opportunities of optimizations as well a more compact form.  When upgrading to Timescale 2.7, new created Continuous Aggregates are using the new format, but existing Continuous Aggregates keep using the format they were defined with.  Created a procedure to upgrade existing Continuous Aggregates from the old format to the new format, by calling a simple procedure:  test=# CALL cagg_migrate('conditions_summary_daily');  Closes #4424 \n",
      "--------------------------------------------------------------------------------\n",
      "Fabrízio de Royes Mello<fabriziomello@gmail.com> Tue Jul 5 13:39:14 2022 +0200  e34218ce2963358a500f6bc315aace0fad29c450 Migrate Continuous Aggregates to the new format Timescale 2.7 released a new version of Continuous Aggregate (#4269) that store the final aggregation state instead of the byte array of the partial aggregate state, offering multiple opportunities of optimizations as well a more compact form.  When upgrading to Timescale 2.7, new created Continuous Aggregates are using the new format, but existing Continuous Aggregates keep using the format they were defined with.  Created a procedure to upgrade existing Continuous Aggregates from the old format to the new format, by calling a simple procedure:  test=# CALL cagg_migrate('conditions_summary_daily');  Closes #4424 \n",
      "--------------------------------------------------------------------------------\n",
      "Fabrízio de Royes Mello<fabriziomello@gmail.com> Thu Nov 24 13:19:36 2022 -0300  35c91204987ccb0161d745af1a39b7eb91bc65a5 Add Hierarchical Continuous Aggregates validations Commit 3749953e introduce Hierarchical Continuous Aggregates (aka Continuous Aggregate on top of another Continuous Aggregate) but it lacks of some basic validations.  Validations added during the creation of a Hierarchical Continuous Aggregate:  * Forbid create a continuous aggregate with fixed-width bucket on top of   a continuous aggregate with variable-width bucket.  * Forbid incompatible bucket widths:   - should not be equal;   - bucket width of the new continuous aggregate should be greater than     the source continuous aggregate;   - bucket width of the new continuous aggregate should be multiple of     the source continuous aggregate. \n",
      "--------------------------------------------------------------------------------\n",
      "Fabrízio de Royes Mello<fabriziomello@gmail.com> Thu Nov 24 13:19:36 2022 -0300  35c91204987ccb0161d745af1a39b7eb91bc65a5 Add Hierarchical Continuous Aggregates validations Commit 3749953e introduce Hierarchical Continuous Aggregates (aka Continuous Aggregate on top of another Continuous Aggregate) but it lacks of some basic validations.  Validations added during the creation of a Hierarchical Continuous Aggregate:  * Forbid create a continuous aggregate with fixed-width bucket on top of   a continuous aggregate with variable-width bucket.  * Forbid incompatible bucket widths:   - should not be equal;   - bucket width of the new continuous aggregate should be greater than     the source continuous aggregate;   - bucket width of the new continuous aggregate should be multiple of     the source continuous aggregate. \n",
      "--------------------------------------------------------------------------------\n",
      "Erik Nordström<erik@timescale.com> Tue Feb 8 09:57:23 2022 +0100  5af9f45488d51027804cac16362811f71a89bb64 Add extra telemetry for continuous aggregates Add the following telemetry fields for continuous aggregates:  * The number of continuous aggregates created on distributed   hypertables * The number of continuous aggregates using real-time aggregation \n",
      "--------------------------------------------------------------------------------\n",
      "Erik Nordström<erik@timescale.com> Tue Feb 8 09:57:23 2022 +0100  5af9f45488d51027804cac16362811f71a89bb64 Add extra telemetry for continuous aggregates Add the following telemetry fields for continuous aggregates:  * The number of continuous aggregates created on distributed   hypertables * The number of continuous aggregates using real-time aggregation \n",
      "--------------------------------------------------------------------------------\n",
      "Fabrízio de Royes Mello<fabriziomello@gmail.com> Wed May 11 19:36:58 2022 -0300  f266f5cf564fcc5509b91493a39eb201c6f5914a Continuous Aggregates finals form Following work started by #4294 to improve performance of Continuous Aggregates by removing the re-aggregation in the user view.  This PR get rid of `partialize_agg` and `finalize_agg` aggregate functions and store the finalized aggregated (plain) data in the materialization hypertable.  Because we're not storing partials anymore and removed the re-aggregation, now is be possible to create indexes on aggregated columns in the materialization hypertable in order to improve the performance even more.  Also removed restrictions on types of aggregates users can perform with Continuous Aggregates: * aggregates with DISTINCT * aggregates with FILTER * aggregates with FILTER in HAVING clause * aggregates without combine function * ordered-set aggregates * hypothetical-set aggregates  By default new Continuous Aggregates will be created using this new format, but the previous version (with partials) will be supported.  Users can create the previous style by setting to `false` the storage paramater named `timescaledb.finalized` during the creation of the Continuous Aggregate.  Fixes #4233 \n",
      "--------------------------------------------------------------------------------\n",
      "Fabrízio de Royes Mello<fabriziomello@gmail.com> Wed May 11 19:36:58 2022 -0300  f266f5cf564fcc5509b91493a39eb201c6f5914a Continuous Aggregates finals form Following work started by #4294 to improve performance of Continuous Aggregates by removing the re-aggregation in the user view.  This PR get rid of `partialize_agg` and `finalize_agg` aggregate functions and store the finalized aggregated (plain) data in the materialization hypertable.  Because we're not storing partials anymore and removed the re-aggregation, now is be possible to create indexes on aggregated columns in the materialization hypertable in order to improve the performance even more.  Also removed restrictions on types of aggregates users can perform with Continuous Aggregates: * aggregates with DISTINCT * aggregates with FILTER * aggregates with FILTER in HAVING clause * aggregates without combine function * ordered-set aggregates * hypothetical-set aggregates  By default new Continuous Aggregates will be created using this new format, but the previous version (with partials) will be supported.  Users can create the previous style by setting to `false` the storage paramater named `timescaledb.finalized` during the creation of the Continuous Aggregate.  Fixes #4233 \n",
      "--------------------------------------------------------------------------------\n",
      "Fabrízio de Royes Mello<fabriziomello@gmail.com> Wed Oct 5 18:45:40 2022 -0300  3749953e9704e45df8f621607989ada0714ce28d Hierarchical Continuous Aggregates Enable users create Hierarchical Continuous Aggregates (aka Continuous Aggregates on top of another Continuous Aggregates).  With this PR users can create levels of aggregation granularity in Continuous Aggregates making the refresh process even faster.  A problem with this feature can be in upper levels we can end up with the \"average of averages\". But to get the \"real average\" we can rely on \"stats_aggs\" TimescaleDB Toolkit function that calculate and store the partials that can be finalized with other toolkit functions like \"average\" and \"sum\".  Closes #1400 \n",
      "--------------------------------------------------------------------------------\n",
      "Fabrízio de Royes Mello<fabriziomello@gmail.com> Wed Oct 5 18:45:40 2022 -0300  3749953e9704e45df8f621607989ada0714ce28d Hierarchical Continuous Aggregates Enable users create Hierarchical Continuous Aggregates (aka Continuous Aggregates on top of another Continuous Aggregates).  With this PR users can create levels of aggregation granularity in Continuous Aggregates making the refresh process even faster.  A problem with this feature can be in upper levels we can end up with the \"average of averages\". But to get the \"real average\" we can rely on \"stats_aggs\" TimescaleDB Toolkit function that calculate and store the partials that can be finalized with other toolkit functions like \"average\" and \"sum\".  Closes #1400 \n"
     ]
    }
   ],
   "source": [
    "for result in records:\n",
    "    print(\"-\" * 80)\n",
    "    print(result[client.SEARCH_RESULT_CONTENTS_IDX])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's how to access each field of a record object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "result = records[0]\n",
    "\n",
    "record_id = result[client.SEARCH_RESULT_ID_IDX]\n",
    "record_metadata = result[client.SEARCH_RESULT_METADATA_IDX]\n",
    "record_embedding = result[client.SEARCH_RESULT_EMBEDDING_IDX]\n",
    "record_distance = result[client.SEARCH_RESULT_DISTANCE_IDX]\n",
    "record_contents = result[client.SEARCH_RESULT_CONTENTS_IDX]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------------------------------------------------\n",
      "18331d00-fc57-11ec-a166-06cee12dbc78\n",
      "{'date': '2022-07-5 13:39:14+0320', 'author': 'Fabrízio de Royes Mello', 'commit': ' e34218ce2963358a500f6bc315aace0fad29c450'}\n",
      "Fabrízio de Royes Mello<fabriziomello@gmail.com> Tue Jul 5 13:39:14 2022 +0200  e34218ce2963358a500f6bc315aace0fad29c450 Migrate Continuous Aggregates to the new format Timescale 2.7 released a new version of Continuous Aggregate (#4269) that store the final aggregation state instead of the byte array of the partial aggregate state, offering multiple opportunities of optimizations as well a more compact form.  When upgrading to Timescale 2.7, new created Continuous Aggregates are using the new format, but existing Continuous Aggregates keep using the format they were defined with.  Created a procedure to upgrade existing Continuous Aggregates from the old format to the new format, by calling a simple procedure:  test=# CALL cagg_migrate('conditions_summary_daily');  Closes #4424 \n",
      "0.15402132505614874\n",
      "--------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "print(\"-\" * 80)\n",
    "print(record_id)\n",
    "print(record_metadata)\n",
    "print(record_contents)\n",
    "print(record_distance)\n",
    "print(\"-\" * 80)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Similarity search with metadata filtering\n",
    "\n",
    "Timecale Vector also supports filtering results by metadata. Let's see an example of this, where we'll specify the number of results to return using the `limit` argument and filter the results by the `author` metadata field."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "records_filtered = await vec.search(query_embedding, limit=3, filter={\"author\": \"Rafia Sabih\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------------------------------------------------\n",
      "Rafia Sabih<rafia.sabih@gmail.com> Wed Feb 8 11:54:28 2023 +0100  98218c1d079231a9aa469b37ddd0ed39e77c2adb Enable joins for heirarchical continuous aggregates The joins could be between a continuous aggregate and hypertable, continuous aggregate and a regular Postgres table, and continuous aggregate and a regular Postgres view. \n",
      "--------------------------------------------------------------------------------\n",
      "Rafia Sabih<rafia.sabih@gmail.com> Wed Feb 8 11:54:28 2023 +0100  98218c1d079231a9aa469b37ddd0ed39e77c2adb Enable joins for heirarchical continuous aggregates The joins could be between a continuous aggregate and hypertable, continuous aggregate and a regular Postgres table, and continuous aggregate and a regular Postgres view. \n",
      "--------------------------------------------------------------------------------\n",
      "Rafia Sabih<rafia.sabih@gmail.com> Thu Apr 27 15:01:38 2023 +0200  d9849325d0d0f81a13db1e41aa56f8b567945e72 Improve test suite Add more regression tests for Continuous aggregates with joins. \n"
     ]
    }
   ],
   "source": [
    "for result in records_filtered:\n",
    "    print(\"-\" * 80)\n",
    "    print(result[client.SEARCH_RESULT_CONTENTS_IDX])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can from the output above that we only get results back from the author we filtered for!"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Using ANN search indexes to speed up queries\n",
    "\n",
    "You can speed up similarity queries by creating an index on the embedding column. We recommend doing this if you have large number (10k+) vectors in your table.\n",
    "\n",
    "Timescale Vector supports the following indexes:\n",
    "- timescale_vector_index: a disk-ann inspired graph index for fast similarity search (default).\n",
    "- pgvector's HNSW index: a hierarchical navigable small world graph index for fast similarity search.\n",
    "- pgvector's IVFFLAT index: an inverted file index for fast similarity search.\n",
    "\n",
    "To learn more about Timescale Vector's new DiskANN inspired index, see the [Timescale Vector explainer blog](https://www.timescale.com/blog/how-we-made-postgresql-the-best-vector-database/?utm_campaign=vectorlaunch&utm_source=github&utm_medium=direct) for more details and performance benchmarks.\n",
    "\n",
    "Important note: In PostgreSQL, each table can only have one index on a particular column. So if you'd like to test the performance of different index types, you can do so either by (1) creating multiple tables with different indexes, (2) creating multiple vector columns in the same table and creating different indexes on each column, or (3) by dropping and recreating the index on the same column and comparing results.\n",
    "\n",
    "Let's look at how to create each type of index, starting with the StreamingDiskANN index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a timescale vector (DiskANN) search index on the embedding column\n",
    "await vec.create_embedding_index(client.DiskAnnIndex())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Timescale Vector also supports HNSW and ivfflat indexes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "await vec.drop_embedding_index()\n",
    "\n",
    "# Create HNSW search index on the embedding column\n",
    "await vec.create_embedding_index(client.HNSWIndex())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "await vec.drop_embedding_index()\n",
    "\n",
    "# Create IVFFLAT search index on the embedding column\n",
    "await vec.create_embedding_index(client.IvfflatIndex())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In general we recommend using the timescale vector or HNSW index for most use cases, as they are most useful for high dimension and large datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "await vec.drop_embedding_index()\n",
    "await vec.create_embedding_index(client.DiskAnnIndex())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Similarity search with time filtering\n",
    "\n",
    "We'll use Timescale Vector to find similar commits to a given query within a time range.\n",
    "\n",
    "A key use case for Timescale Vector is efficient time-based vector search. Timescale Vector enables this by automatically partitioning vectors (and associated metadata) by time. This allows you to efficiently query vectors by both similarity to a query vector and time.\n",
    "\n",
    "Time-based vector search functionality is helpful for applications like:\n",
    "- Storing and retrieving LLM response history (e.g. chatbots)\n",
    "- Finding the most recent embeddings that are similar to a query vector (e.g recent news).\n",
    "- Constraining similarity search to a relevant time range (e.g asking time-based questions about a knowledge base)\n",
    "\n",
    "Let's look at how to run similarity searches with time range filters using the client.\n",
    "\n",
    "- The first step to using time filtering with Timescale Vector is to create a table with the `time_partition_interval` argument set to the desired time interval. This will automatically partition the table into time-based chunks to speed up queries. We completed this step in Part 1 above.\n",
    "\n",
    "- Next, we ensure the `id` of our row is a `uuid` with a datetime portion that reflects the date and time we want to associated with the embedding. We completed this step in Part 2 above, where we used the `uuid_from_time()` method provided by the Timescale Vector library.\n",
    "\n",
    "- Finally, we can run similarity searches with time range filters using the client. We'll illustrate this below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define search query\n",
    "query_string = \"What's new with TimescaleDB functions?\"\n",
    "query_embedding = get_embeddings(query_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Time filter variables for query\n",
    "start_date = datetime(2023, 8, 1, 22, 10, 35) # Start date = 1 August 2023, 22:10:35\n",
    "end_date = datetime(2023, 8, 30, 22, 10, 35) # End date = 30 August 2023, 22:10:35\n",
    "td = timedelta(days=7) # Time delta = 7 days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Method 1: Filter within a provided start date and end date.\n",
    "records_time_filtered = await vec.search(query_embedding,\n",
    "                        limit=3, \n",
    "                        uuid_time_filter=client.UUIDTimeRange(start_date, end_date))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------------------------------------------------------------------------\n",
      "Sven Klemm<sven@timescale.com> Tue Aug 29 18:13:24 2023 +0200  e4facda540286b0affba47ccc63959fefe2a7b26 Add compatibility layer for _timescaledb_internal functions With timescaledb 2.12 all the functions present in _timescaledb_internal were moved into the _timescaledb_functions schema to improve schema security. This patch adds a compatibility layer so external callers of these internal functions will not break and allow for more flexibility when migrating. \n",
      "--------------------------------------------------------------------------------\n",
      "Sven Klemm<sven@timescale.com> Tue Aug 29 18:13:24 2023 +0200  e4facda540286b0affba47ccc63959fefe2a7b26 Add compatibility layer for _timescaledb_internal functions With timescaledb 2.12 all the functions present in _timescaledb_internal were moved into the _timescaledb_functions schema to improve schema security. This patch adds a compatibility layer so external callers of these internal functions will not break and allow for more flexibility when migrating. \n",
      "--------------------------------------------------------------------------------\n",
      "Dmitry Simonenko<dmitry@timescale.com> Thu Aug 3 14:30:23 2023 +0300  7aeed663b9c0f337b530fd6cad47704a51a9b2ec Feature flags for TimescaleDB features This PR adds several GUCs which allow to enable/disable major timescaledb features:  - enable_hypertable_create - enable_hypertable_compression - enable_cagg_create - enable_policy_create \n"
     ]
    }
   ],
   "source": [
    "for result in records_time_filtered:\n",
    "    print(\"-\" * 80)\n",
    "    print(result[client.SEARCH_RESULT_CONTENTS_IDX])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only vectors within the specified time range are returned. These queries are very efficient as they only need to search the relevant partitions."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Resources and next steps\n",
    "\n",
    "To continue your learning journey check out the following resources:\n",
    "\n",
    "- [Timescale Vector AI tutorials and guides](https://www.timescale.com/ai)\n",
    "- [Timescale Vector explainer blog and performance benchmarks](https://www.timescale.com/blog/how-we-made-postgresql-the-best-vector-database/?utm_campaign=vectorlaunch&utm_source=github&utm_medium=direct) for more details and performance benchmarks.\n",
    "- [Using Timescale Vector with LangChain](https://python.langchain.com/docs/integrations/vectorstores/timescalevector) \n",
    "- [Using Timescale Vector with LlamaIndex](https://gpt-index.readthedocs.io/en/stable/examples/vector_stores/Timescalevector.html) \n",
    "\n",
    "\n",
    "And finally, if you haven't already, remember to claim your 90 days free of Timescale Vector by [signing up here](https://console.cloud.timescale.com/signup?utm_campaign=vectorlaunch&utm_source=github&utm_medium=direct)."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python3",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}