October 2024 Summaries

Pgai Vectorizer is an open-source tool that automates embedding creation in PostgreSQL, allowing developers to modify their RAG chunking and formatting strategies like they would adjust a database index using simple SQL. This approach enables side-by-side comparisons of different approaches and ensures zero downtime for AI features during testing. Pgai Vectorizer supports multiple chunking functions, allowing the flexibility to easily experiment and test to find the best chunking strategy for your RAG application. The tool also tracks which chunking and formatting strategies were used for each embedding, facilitating A/B testing and gradual rollouts of new strategies.

The text discusses the challenges faced by engineering teams when using vector databases for building AI applications. It highlights that while everything works smoothly for simple applications and proofs of concept, taking these systems into production reveals flawed abstractions with vector databases and the way they are used today. The main issue is that vector databases treat embeddings as independent data, divorced from the source data from which embeddings are created, rather than what they truly are: derived data. This results in unnecessary complexity for developers who have to manage multiple databases and synchronize them manually. The solution proposed by the author is treating embeddings more like database indexes through a new abstraction called "vectorizer". This approach automatically keeps embeddings in sync with their source data, eliminating the maintenance costs that plague current implementations. The author also introduces an open-source tool called pgai Vectorizer, which implements this vectorizer abstraction in PostgreSQL and works with other extensions for vector search like pgvector and pgvectorscale. The article concludes by encouraging developers to try out pgai Vectorizer as it can simplify their AI workflows significantly.

OpenAI's text-embedding-3 model family offers improved performance and cost savings, but many teams struggle to upgrade due to the complexity of re-embedding data. Pgai Vectorizer is an open-source tool that automates embedding creation in PostgreSQL using SQL commands, allowing for easy testing and deployment of new OpenAI embedding models. It enables side-by-side comparisons of model performance, automatic synchronization of embeddings as source data changes, and gradual rollouts of new models without disrupting production systems. Pgai Vectorizer is available in Early Access for both cloud-hosted and self-hosted deployment options.

Pgai Vectorizer is a tool that automates the creation and management of vector embeddings within PostgreSQL using a single SQL command. It simplifies embedding workflows, allowing developers to manage them with just one SQL statement. The tool abstracts embeddings into a declarative feature, enabling users to define embedding models, dimensions, chunking, formatting, indexing options, and more. Pgai Vectorizer also automatically updates the embeddings when source data changes, ensuring that data and embeddings remain in sync. This streamlined process reduces operational overhead and allows developers to focus on building innovative applications rather than managing infrastructure.

Vector databases have become essential components of modern AI and machine learning applications due to their ability to store data organized by embedding representation, optimizing them for semantic search and AI applications. The power of vector embeddings has driven the development of specialized vector databases designed to store data organized by embedding representation, enabling more efficient and meaningful data retrieval. Choosing the right vector database can be challenging given today's wide range of options. Key factors to consider include query rate, partition-ability, secondary filtering needs, system of record considerations, data changes and synchronization, handling structured data, serverless vs. dedicated databases, general-purpose vs. specialized vector databases, open-source vs. closed-source vector databases, performance, security and reliability, developer experience, and observability. Understanding your application's needs, query patterns, and system requirements is crucial in choosing the best vector database for your specific use case. Consider factors such as retrieval-augmented generation (RAG) for chatbots, semantic search for product catalogs, recommendation systems, data augmentation or classification, image recognition and analysis, fraud detection and anomaly detection, personalized content recommendations, natural language understanding (NLU) for voice assistants, intelligent document retrieval, real-time event detection, medical data analysis, customer support chatbots, sentiment analysis, and voice command recognition. Different applications use vector databases in distinct ways, and selecting the right one involves understanding factors like query rates, partitioning ability, filtering needs, and data synchronization. Evaluation criteria for making a sound vector database choice include query rate, partition-ability, secondary filtering needs, system of record considerations, data changes and synchronization, handling structured data, serverless vs. dedicated vector databases, general-purpose vs. specialized vector databases, open-source vs. closed-source vector databases, performance, security and reliability, developer experience, and observability.

AWS Lambda, a serverless computing service by Amazon Web Services, has gained popularity due to its scalability, cost efficiency, and ease of management. Combining AWS Lambda with TimescaleDB, an open-source time-series database built on PostgreSQL, offers a robust solution for managing IoT data pipelines. This integration allows developers to focus on application logic rather than infrastructure management. To set up the integration, create a Lambda function, install necessary libraries, use Lambda layers for dependency management, and store sensitive information in environment variables. Best practices include efficient data processing techniques, monitoring and logging with AWS CloudWatch, and security considerations such as using IAM roles and encrypting sensitive data.

In this tutorial, we will build an image search engine using OpenAI's CLIP model and PostgreSQL with the pgvector extension. We will use a sample dataset of images from Flickr30k and store their embeddings in a PostgreSQL table. Then, we will create a React application that allows users to input a textual query and retrieve image results from our server. To get started, you need the following: 1. A PostgreSQL database with the pgvector extension installed. 2. The OpenAI CLIP model for generating embeddings. 3. A dataset of images (e.g., Flickr30k). 4. Node.js and npm installed on your machine. 5. React, Express, and Axios libraries installed in your project. First, let's set up the PostgreSQL database with the pgvector extension: 1. Install pgvector extension: It ensures that the pgvector extension is installed in the database by executing `CREATE EXTENSION IF NOT EXISTS vector`. 2. Check table existence: This function executes a query to check if the Search_table exists in the public schema. If it does not exist, this function creates it. 3. Create table: If the table does not exist, it executes a query to create the Search_table with the following columns: - id: a primary key with auto-increment - path: a text field for storing the image path - embedding: a vector field with 512 dimensions for storing image embeddings 4. Error handling: It catches and logs any errors that occur during the process. 5. Release connection: The client.release() method is used to return a database client back to the connection pool after it has been used. Since this code will only be called once, we can invoke the function directly in database.js. We can do that when inserting the data. Data insertion This section will discuss the dataset used for the image application and how to insert it into our database. Flickr30k The Flickr30k dataset is a well-known benchmark for sentence-based image descriptions. It contains 31,783 images of people engaging in everyday activities and events. It is widely used for evaluating models that generate sentence-based portrayals of images. The dataset is available on Kaggle and can be easily downloaded. As this is an extensive image dataset, this demo is based on a sample of 100 images. Insertion logic The following code is a part of database.js: ```javascript import pgvector from 'pgvector/pg'; export async function insertInTable(client, filePaths) { // Load processor and vision model await client.connect(); await pgvector.registerTypes(client); try { for (const filePath of filePaths) { try { // Compute embeddings const vision_embedding = await visionEmbeddingGenerator(filePath); console.log(`Embeddings for ${filePath}:`, [pgvector.toSql(Array.from(vision_embedding))]); await client.query('INSERT INTO Search_table (path, embedding) VALUES ($1, $2)', [ filePath, pgvector.toSql(Array.from(vision_embedding)), ]); } catch (err) { console.error(`Error processing ${filePath}:`, err); } } } finally { await client.end(); } } ``` The insertInTable function connects to a PostgreSQL database and iterates over a list of image file paths. For each path, it computes image embeddings using the visionEmbeddingGenerator function and inserts these embeddings, along with the file path, into the Search_table table. It handles errors that occur while processing each image and ensures that the database connection is closed properly once all insertions are complete. This approach maintains robust error handling and efficient database management throughout the insertion process. Let's include a function in utils.js to list the files in our dataset directory. We will use this in database.js to insert the images into the database. Here’s the utility function: ```javascript import fs from 'fs'; import path from 'path'; export function getFilePaths(directory) { try { const files = fs.readdirSync(directory); const filePaths = files.map(file => path.join(directory, file)); return filePaths; } catch (err) { console.error('Error reading directory:', err); return []; } } ``` Now we can import it in database.js and execute the insertion: ```javascript import {getFilePaths} from './utils.js' import pkg from 'pg'; const { Pool } = pkg; function main() { const client = new Pool({ user: '<Your user>', host: '<Your host>', database: '<Your db>', password: '<Your Password>', port: <Your Port>, ssl: { rejectUnauthorized: false, }, }); const tableCreated = await createTableIfNotExists(client); if (tableCreated) { insertInTable(client, getFilePaths('dataset')) } } main() ``` Note: The preceding code remains unchanged in the file. Now that this process is complete, we have inserted the images and their embeddings in the table, which will be retrieved depending on the query. Building the Image Search Application Search API In this section, we will develop a POST route /search using Express.js that accepts a textual query from the user, transforms it into embeddings, and performs a database search. CLIP, a neural network model, combines image and text embeddings into a unified output space, allowing for direct comparisons between the two modalities within a single model. ```javascript app.post('/search', async (req, res) => { try { // Load tokenizer and text model await client.connect(); // Compute text embeddings const text_emb = await textEmbeddingGenerator(req.query['searchText']) const queryTextEmbedding = [pgvector.toSql(Array.from(text_emb))] console.log(queryTextEmbedding) // Perform similarity search const result = await client.query(` SELECT path FROM Search_table ORDER BY embedding <-> $1 LIMIT 5`, queryTextEmbedding); res.json(result.rows); console.log(result.rows) } catch (error) { console.error('Error performing search', error); res.status(500).send('Error performing search'); } finally{ client.end(); } }); ``` The app.post('/search') route processes POST requests to perform an image search based on a textual query. When a request is received, the code first connects to the PostgreSQL database. It then generates embeddings for the search text using the `textEmbeddingGenerator` function. These embeddings are converted into a format compatible with PostgreSQL using pgvector.toSql. The route then executes a similarity search against the Search_table table in the database, ordering results based on their similarity to the query embeddings using the <-> operator. It limits the results to the top five matches. The matching image paths are returned as a JSON response. If an error occurs during this process, a 500 status code is sent, and the database connection is closed in the finally block. After running the server using node index.js, we can check our endpoint using Postman, which is a platform that helps developers build and use APIs. If that seems a hassle, we can simply use wget or curl. Here’s how we can make a POST request with curl: ```bash curl -X POST "http://localhost:3000/search" -d "searchText=old man" ``` If you are using Postman, you will need a desktop version. After logging in and creating a workspace, let’s request our API: 1. Add a query parameter with the key searchText and the value old man. 2. Configure the request method as POST. 3. Set the URL to http://localhost:3000 where the server is listening. Here are the paths retrieved from the database after semantic search: Let's verify one of the images from the paths to ensure that the retrieved images match the query. Now, our server is ready to search, given the query. Let’s complete it with our client side. Final Touches In this section, we will create a React application that a client will use to interact with the Search API. Here’s how you can create the client side: The first step is to create a component file named SearchBar.js, which will take the user's input. Let’s write some code in it. ```javascript import React, { useState } from 'react'; import Timescale from './assets/1.jpeg'; # A Icon saved in the assets const SearchBar = () => { const [searchText, setSearchText] = useState(''); const [clicked, setClicked] = useState(false); return ( <div className="container"> <div className="titleContainer"> <img src={Timescale} alt="logo" className="logo" /> <h1 className="title">Timescale Image Search Engine</h1> </div> <div className="searchContainer"> <input type="text" value={searchText} onChange={(e) => setSearchText(e.target.value)} placeholder="Search..." className="input" /> <button onClick={() => setClicked(true)} className="button"> Search </button> </div> </div> ); }; export default SearchBar; ``` This React component, SearchBar, allows users to input a search query and retrieve image results from a server. It manages the search text, results, loading state, and any errors encountered during the search. Let’s fill in with the useEffect hook to query the Search API. ```javascript const [results, setResults] = useState([]); const [error, setError] = useState(null); useEffect(() => { const performSearch = async () => { try { const response = await axios.post('http://localhost:3000/search', { searchText }); setResults(response.data); } catch (err) { setError(err); } finally { setLoading(false); } }; if (clicked) { setClicked(false); performSearch(); } }, [clicked]); ``` This code snippet uses React's useState and useEffect hooks to manage search results and errors. When the clicked state changes, useEffect triggers an asynchronous search function that sends a POST request to http://localhost:3000/search with the search text. Successful responses update the `results` state, and any errors update the error state. The clicked state is reset to prevent repeated searches. Now, let’s look at the complete SearchBar component. Please note that additional components and custom hooks have been created to handle dynamic image imports. However, due to the scope of the article, we will skip the explanation. If you want, you can explore this further in our GitHub repository. Here’s the complete component: ```javascript #SearchBar.js import React, { useEffect, useState } from 'react'; import Timescale from './assets/1.jpeg'; import axios from 'axios'; import Image from './Image'; const SearchBar = () => { const [searchText, setSearchText] = useState(''); const [results, setResults] = useState([]); const [error, setError] = useState(null); const [clicked, setClicked] = useState(false); useEffect(() => { const performSearch = async () => { try { const response = await axios.post('http://localhost:3000/search', { searchText }); setResults(response.data); } catch (err) { setError(err); } finally { setLoading(false); } }; if (clicked) { setClicked(false); performSearch(); } }, [clicked]); return ( <div style={styles.container}> <div style={styles.titleContainer}> <img src={Timescale} alt="logo" style={styles.logo} /> <h1 style={styles.title}>Timescale Image Search Engine</h1> </div> <div style={styles.searchContainer}> <input type="text" value={searchText} onChange={(e) => setSearchText(e.target.value)} placeholder="Search..." style={styles.input} /> <button onClick={() => setClicked(true)} style={styles.button}> Search </button> </div> <div style={styles.resultsContainer}> {results.length > 0 && ( <ul style={styles.resultsList}> {results.map((item, index) => ( <li key={index} style={styles.resultItem}> <Image fileName={item.path} alt={searchText} /> </li> ))} </ul> )} </div> </div> ); }; const styles = { container: { display: 'flex', flexDirection: 'column', alignItems: 'center', justifyContent: 'center', backgroundColor: 'black', textAlign: 'center', padding: '20px', }, titleContainer: { display: 'flex', alignItems: 'center', marginBottom: '20px', }, logo: { width: '80px', height: '80px', marginRight: '10px', }, title: { fontSize: '48px', color: '#F5FF80', }, searchContainer: { display: 'flex', alignItems: 'center', justifyContent: 'center', width: '100%', marginBottom: '20px', }, input: { padding: '15px', borderRadius: '5px', border: '1px solid #F5FF80', marginRight: '10px', width: '50%', }, button: { padding: '15px 15px', borderRadius: '5px', border: 'none', backgroundColor: '#F5FF80', color: 'black', cursor: 'pointer', }, resultsContainer: { width: '100%', textAlign: 'center', // Center align the results container }, resultsList: { listStyleType: 'none', padding: 0, margin: 0, display: 'flex', flexWrap: 'wrap', justifyContent: 'center', }, resultItem: { margin: '10px', color: '#F5FF80', textAlign: 'center', }, }; export default SearchBar; ``` The SearchBar.js component is also responsible for displaying images on the page. After retrieving the image paths from the database, it selects the corresponding assets and displays them. To dynamically add image imports in React, we have created the useImage effect and the Image component. # useImage.js import { useEffect, useState } from 'react' const useImage = (fileName) => { const [loading, setLoading] = useState(true) const [error, setError] = useState(null) const [image, setImage] = useState(null) useEffect(() => { const fetchImage = async () => { const path = fileName.replace(/\\/g, '/'); try { const response = await import(`./assets/${path}`) // change relative path to suit your needs setImage(response.default) } catch (err) { setError(err) } finally { setLoading(false) } } fetchImage() }, [fileName]) return { loading, error, image, } } export default useImage Note: An assets folder is created within the src directory, which contains the image dataset. Let’s create a component to display the image, as you can see in the SearchBar.js: #Image.js import useImage from "./useImage" const Image = ({ fileName, alt }) => { const { loading, error, image } = useImage(fileName) console.log(error) return ( <> <img src={image} alt={alt} /> </> ) } export default Image The Image component takes the file name as a prop and uses the useImage hook to fetch the image. It returns an img element with the fetched image source, loading state, error state, and alternative text. Now that we have completed our client-side application, let's test it by running the server using node index.js and opening the SearchBar component in a web browser. When you input a search query and click on the "Search" button, the application should send a POST request to the /search endpoint with the search text as payload. The server should then perform a similarity search against the Search_table table in the PostgreSQL database and return the top five matching image paths as JSON response. Finally, the client-side application should display these images on the page using the Image component. In conclusion, we have built an image search engine using OpenAI's CLIP model and PostgreSQL with the pgvector extension. We used a sample dataset of images from Flickr30k and stored their embeddings in a PostgreSQL table. Then, we created a React application that allows users to input a textual query and retrieve image results from our server. This application demonstrates how to use advanced machine learning models like CLIP for semantic search applications and how to efficiently store and query high-dimensional vectors using PostgreSQL with the pgvector extension.

Data parsing, the process of converting data from unstructured formats into structured ones, is essential for developers facing diverse data sources like PDFs, emails, and web pages. This tutorial explores the use of open-source tools like Unstructured and pgai to facilitate this process. Unstructured is an open-source library that excels at extracting and structuring information from various document types, while pgai, a PostgreSQL extension, integrates AI capabilities for operations like text embedding directly within the database. The guide outlines how to set up a command-line utility using these tools to import documents into a PostgreSQL database, where data can be stored and queried in a structured format. By leveraging the OpenAI API, the pgai extension allows for the creation of text embeddings, enabling semantic search capabilities across document types. The tutorial includes practical instructions for setting up the environment, importing data, and querying the database, thus providing a comprehensive pipeline for transforming unstructured data into actionable insights.

PostgreSQL database migration is a complex task, especially when dealing with time-series data. To simplify this process, Timescale introduced the live migration tool, which has been further improved for enhanced functionality and performance. The team expanded migration paths, added support for generated columns, table-data filtering, and resume capability, and made several reliability improvements. Performance optimizations include batch processing for single-row inserts, PostgreSQL pipeline mode, and parallel processing capabilities. These enhancements aim to make database migration faster, more reliable, and less disruptive to ongoing operations.

This article discusses how to handle multi-tenancy in retrieval-augmented generation (RAG) applications built with PostgreSQL and the pgvector extension. Multi-tenancy is a design approach where multiple users or organizations share a single instance of software, ensuring data isolation and security. It offers benefits such as scalability, customization, compliance, efficient updates, cost-effectiveness, and consistent performance but also presents challenges like increased security threats, codebase complexity, backup and restoration issues, limited customization options, and potential global problems affecting all tenants simultaneously. PostgreSQL, enhanced with the pgvector extension, provides a robust solution for implementing multi-tenant RAG apps due to its built-in full-text search capabilities, JSON support, vector extensions, row-level security, scalability, ACID compliance, and extensibility. The article also highlights Timescale Cloud's open-source AI stack, which includes pgvector, pgvectorscale, and pgai, as a way to easily build and scale RAG, search, and agents applications. To implement RAG with PostgreSQL, the workflow involves ingesting and chunking data, converting text into vector embeddings using an embedding model, and storing these vectors in PostgreSQL using pgvector. When a user query is received, the system retrieves relevant information from the vector database based on similarity search, combines it with additional context, and generates a response using a large language model (LLM). The article presents four levels of multi-tenancy implementation in PostgreSQL: table-level separation, schema-level separation, logical database separation, and database service separation. Each level has its pros and cons, making them suitable for distinct use case scenarios based on shared resources, data separation, customization, scalability, and costs. By carefully considering the optimal use cases and aligning them with an application's needs, developers can create a scalable, secure, and performant RAG system in PostgreSQL.

The text discusses the importance of using PostgreSQL replicas to protect data from various risks such as hardware failures, natural disasters, or human errors. It explains how Timescale supports high-availability (HA) replicas and introduces multiple HA typologies for different use cases. The article also delves into the technical details of database replication in PostgreSQL and how Timescale uses it to ensure data safety. Additionally, it provides benchmarking results comparing the performance impact of various replica configurations.

The platform team at Timescale has replaced StatefulSets with their own Kubernetes custom resource and operator called PatroniSets to manage customer database pods and volumes, improving stability and minimizing disruptions. They chose this approach over managing the pods and volumes directly in their main operator or using StatefulSets in "OnDelete" mode. The new system is PostgreSQL/Patroni-aware, has a declarative Custom Resource (CR), and minimizes downtime by performing actions in order to reduce customer disruption. Since its introduction, PatroniSets have provided significant improvements in stability and availability guarantees for Timescale's customers.

Timescale has introduced new tooling to help users onboard easily from proof of concept to production. The data migration wizards allow for quick and easy import of CSV and Parquet files, while the PostgreSQL migration tools offer a variety of options for data migration. Additionally, there are wizards for setting up TimescaleDB core features like hypertables, compression, and continuous aggregates. An Actions hub provides curated recommendations to guide users through their Timescale journey. The company aims to make developers' lives easier by allowing them to focus on their application rather than their database.

The text discusses updates to Postgres that make it more user-friendly and reliable. These include integration of PopSQL into the Timescale Console, offering two distinct modes for managing databases; UI tooling to simplify moving from proof of concept to production; PatroniSets for flexible management of large-scale databases; flexible replication options for high availability needs; and improvements to live migration tools. These updates aim to make Postgres more powerful and easier to use, making Timescale the go-to database solution for all workloads.

The Timescale Console has integrated PopSQL's capabilities into its Ops and Data modes, aiming to streamline PostgreSQL workflows for cloud-based development. These new features include real-time collaboration, autocomplete, schema exploration, version history, query variables, charts, dynamic SQL dashboards, notebooks, effortless SQL sharing and organization, and the ability to connect with multiple databases. The integration of PopSQL into Timescale is expected to continue evolving, with more features planned for the future.

Pondhouse Data built a content recommendation system using pgai and pgvectorscale for an SEO-related internal link building project. The company used the tools to create summaries and embeddings from texts, search for similar content, and filter results based on metadata. Key takeaways include the seamless integration of AI functionalities with PostgreSQL, enabling users to leverage the full capabilities of the database while incorporating AI features.