https://community.databricks.com/t5/lakebase-blogs/how-to-perform-semantic-search-in-databricks-lakebase/ba-p/139846 <H1><SPAN>Introduction</SPAN></H1> <P><SPAN>In today’s AI-native world, applications no longer rely on exact keyword matches—they understand meaning. This shift is powered by </SPAN><STRONG>embeddings</STRONG><SPAN>: numerical representations of text that capture semantic similarity. For instance, a user searching for </SPAN><I><SPAN>“moisturizer for dry skin”</SPAN></I><SPAN> should also find products titled </SPAN><I><SPAN>“hydrating face cream”</SPAN></I><SPAN> or </SPAN><I><SPAN>“intense moisture lotion”</SPAN></I><SPAN>. While the words may differ, their meanings are similar—something traditional keyword search would miss. Embeddings help bridge this gap, enabling </SPAN><STRONG>semantic search</STRONG><SPAN>, </SPAN><STRONG>RAG (Retrieval-Augmented Generation)</STRONG><SPAN>, and </SPAN><STRONG>intelligent recommendations</STRONG><SPAN>.</SPAN></P> <P><SPAN>With pgvector— supported on </SPAN><A href="https://www.databricks.com/blog/what-is-a-lakebase" target="_blank" rel="noopener"><SPAN>Databricks Lakebase</SPAN></A><SPAN> (Postgres OLTP database)—you can store, index, and query embeddings natively in SQL, bringing the power of semantic search directly to your data lakehouse.</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Why pgvector on Databricks Lakebase?</SPAN></H1> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Unified Platform</STRONG><SPAN>: No separate vector database needed—everything runs in your existing Postgres instance.&nbsp;</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>OLTP + AI</STRONG><SPAN>: Store product metadata and embeddings together in a single system.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Lakehouse Integration:</STRONG> <SPAN>Unity Catalog tables, which are created from OLAP workloads, can be effortlessly mirrored in Lakebase using&nbsp;</SPAN><A href="https://docs.databricks.com/aws/en/oltp/instances/sync-data/sync-table" target="_blank" rel="noopener">Synced tables</A><SPAN>.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>100% pure Postgres</STRONG><SPAN>: Works elegantly with psycopg, JDBC, and your favorite BI tools</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Scalable Performance</STRONG><SPAN>: IVFFlat and HNSW indexes enable fast vector search at scale</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Unity Catalog Integration:</STRONG><SPAN> Register a Postgres Database as a Catalog in UC. Data can be queried through DBSQL using query federation.</SPAN></LI> </UL> <P><SPAN>This blog explores Postgres' native search features, specifically full-text search (keyword matching) and vector-based search (text embeddings), as an alternative to Databricks' managed </SPAN><A href="https://docs.databricks.com/aws/en/generative-ai/vector-search" target="_blank" rel="noopener"><SPAN>Mosaic AI Vector Search</SPAN></A><SPAN>. While Mosaic AI Vector Search currently offers closer integration with Unity Catalog, this guide focuses on leveraging Postgres' capabilities. The choice between Mosaic AI Vector Search and Postgres' native search depends on your specific use case.</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Semantic Search in Real-World</SPAN></H1> <H2><SPAN>E-commerce Product Search</SPAN></H2> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Semantic Product Discovery</STRONG><SPAN>: Find products even when customers use different terminology. For example, a user searches for </SPAN><I><SPAN>“comfy winter boots for icy sidewalks,”</SPAN></I><SPAN> and the search returns </SPAN><I><SPAN>insulated, slip-resistant boots</SPAN></I><SPAN> even if the product titles only say </SPAN><I><SPAN>“snow boots with rubber sole.”</SPAN></I></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Recommendation Systems</STRONG><SPAN>: Identify similar products based on embeddings. For example, a prospect views a </SPAN><I><SPAN>“14-inch lightweight business laptop,”</SPAN></I><SPAN> and the site recommends similar ultrabooks from other brands based on semantic similarity of specs and reviews, not just matching </SPAN><I><SPAN>“14-inch”</SPAN></I><SPAN> in the title.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Search Analytics</STRONG><SPAN>: Understand what customers are looking for through query analysis. By clustering semantically similar queries like </SPAN><I><SPAN>“work from home chair,”</SPAN></I> <I><SPAN>“ergonomic office seat,”</SPAN></I><SPAN> and </SPAN><I><SPAN>“chair for back pain,”</SPAN></I><SPAN> the retailer sees a strong demand for ergonomic chairs and decides to expand that category.</SPAN></LI> </UL> <H2><SPAN>Content Management</SPAN></H2> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Document Search</STRONG><SPAN>: Find relevant documents based on meaning, not just keywords. To fulfil a GDPR compliance requirement, an employee searches </SPAN><I><SPAN>“how do we handle customer data deletion requests,”</SPAN></I><SPAN> and the system surfaces the </SPAN><I><SPAN>“Data Subject Rights Handling Procedure”</SPAN></I><SPAN> PDF even though it never uses the exact phrase “deletion requests.”</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Knowledge Base</STRONG><SPAN>: Power intelligent FAQ systems and help desk solutions. For example, a business traveller asks, </SPAN><I><SPAN>“Can I use this app when I’m offline on a flight?”</SPAN></I><SPAN> and the help center returns the article </SPAN><I><SPAN>“Using the mobile app without an internet connection,”</SPAN></I><SPAN> even though the query never mentions </SPAN><I><SPAN>“mobile app”</SPAN></I><SPAN> or </SPAN><I><SPAN>“offline mode”</SPAN></I><SPAN> explicitly.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Content Recommendations</STRONG><SPAN>: Suggest related articles or resources. After reading an article called </SPAN><I><SPAN>“How to Save Money on Groceries,”</SPAN></I><SPAN> the site recommends other posts like </SPAN><I><SPAN>“Beginner’s Guide to Budgeting”</SPAN></I><SPAN> and </SPAN><I><SPAN>“Easy Cheap Dinner Recipes,”</SPAN></I><SPAN> because they’re about similar ideas (saving money), even if they don’t share the same words.</SPAN></LI> </UL> <H2><SPAN>Customer Support</SPAN></H2> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Ticket Classification</STRONG><SPAN>: Automatically categorize support tickets using semantic similarity. Tickets like </SPAN><I><SPAN>“charged twice on my card,” “duplicate payment on statement,” </SPAN></I><SPAN>and</SPAN><I><SPAN> “double billing issue” </SPAN></I><SPAN>are all routed to the “Billing &gt; Duplicate Charges” queue automatically.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Answer Retrieval</STRONG><SPAN>: Find relevant answers from knowledge bases. A support agent types </SPAN><I><SPAN>“customer wants to change shipping address after ordering”</SPAN></I><SPAN> into their console, and the system surfaces the internal SOP </SPAN><I><SPAN>“Post-order address change policy”</SPAN></I><SPAN> as the top suggested reply.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Sentiment Analysis</STRONG><SPAN>: Combine with other AI models for comprehensive customer insights. Support messages like “</SPAN><I><SPAN>app keeps crashing,</SPAN></I><SPAN>” </SPAN><I><SPAN>“can’t open the app,”</SPAN></I><SPAN> and </SPAN><I><SPAN>“screen freezes all the time”</SPAN></I><SPAN> get grouped together, and sentiment analysis shows most of them are very angry or frustrated—signaling there’s a serious new bug the team needs to fix quickly.</SPAN></LI> </UL> <P><SPAN>The subsequent steps will familiarize you with Postgres' search functionalities. You will learn to search for products like "All Beauty" using semantically rich phrases such as "sun protection for sensitive skin" or "hydration booster for face."</SPAN></P> <P>&nbsp;</P> <H1><SPAN>Architecture Overview</SPAN></H1> <P><SPAN>Our semantic search solution follows this workflow:</SPAN></P> <OL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Data Ingestion and Enrichment</STRONG><SPAN>: Load product data from external sources (e.g., Amazon product metadata). Create searchable text and generate embeddings using </SPAN><A href="https://docs.databricks.com/aws/en/large-language-models/ai-functions" target="_blank" rel="noopener"><SPAN>Databricks AI functions</SPAN></A><SPAN>.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Synced Tables</STRONG><SPAN>: Sync enriched data to Postgres via Lakehouse Federation</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Search Optimization</STRONG><SPAN>: Create search-optimized tables with both full-text and vector indexes</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Query Execution</STRONG><SPAN>: Perform hybrid (full-text and vector-based) searches combining semantic and keyword matching</SPAN></LI> </OL> <P class="lia-align-center"><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_0-1763668095320.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21856iCFE2B899012CDBD6/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_0-1763668095320.png" alt="uday_satapathy_0-1763668095320.png" /></span></P> <H2><SPAN>Step 1: Data Ingestion and Enrichment in Databricks</SPAN></H2> <P><SPAN>We'll start by loading Amazon product metadata for the category </SPAN><I><SPAN>‘All Beauty’</SPAN></I><SPAN> from the </SPAN><A href="https://amazon-reviews-2023.github.io/" target="_blank" rel="noopener"><SPAN>Amazon Reviews 2023 dataset</SPAN></A><SPAN> and enriching it with searchable text and embeddings.&nbsp;</SPAN></P> <H3><SPAN>Setting Up the Environment</SPAN></H3> <LI-CODE lang="markup">CREATE CATALOG IF NOT EXISTS amazon_reviews_23; CREATE SCHEMA IF NOT EXISTS amazon_reviews_23.datasets; CREATE VOLUME IF NOT EXISTS amazon_reviews_23.datasets.raw; </LI-CODE> <H3><SPAN>Loading and Processing Data</SPAN></H3> <LI-CODE lang="python">from pyspark.sql.functions import from_json, schema_of_json, col, lit, expr, concat_ws import requests # Download Amazon Beauty product metadata all_beauty_prod_meta_link = "https://mcauleylab.ucsd.edu/public_datasets/data/amazon_2023/raw/meta_categories/meta_All_Beauty.jsonl.gz" uc_volume_path = "/Volumes/amazon_reviews_23/datasets/raw/meta_All_Beauty.jsonl.gz" response = requests.get(all_beauty_prod_meta_link, stream=True) response.raise_for_status() with open(uc_volume_path, "wb") as f: for chunk in response.iter_content(chunk_size=8192): if chunk: f.write(chunk)</LI-CODE> <H3><SPAN>Data Transformation and Enrichment</SPAN></H3> <P><SPAN>We clean the Amazon Reviews 2023 dataset by removing duplicate columns and invalid characters from column names. Then, we create a </SPAN><SPAN>search_text</SPAN><SPAN> column that consolidates all relevant textual information for product searches.</SPAN></P> <LI-CODE lang="python">def flatten_and_sanitize(df, parent=""): """Clean up column names and flatten nested structs in the input dataframe""" fields = [] for field in df.schema.fields: name = f"{parent}.{field.name}" if parent else field.name clean_name = name.replace(" ", "_").replace(".", "_").replace(",", "_") \ .replace(";", "_").replace("{", "_").replace("}", "_") \ .replace("(", "_").replace(")", "_").replace("\n", "_") \ .replace("\t", "_").replace("=", "_") if str(field.dataType).startswith("StructType"): fields += flatten_and_sanitize(df.select(col(field.name + ".*")), parent=clean_name) else: fields.append(col(name).alias(clean_name)) return fields def prepare_for_search(df): """Generate a search text column and embedding column from the input dataframe""" df = df.withColumn( "search_text", concat_ws( " ", "parent_asin", "title", "main_category", expr("array_join(categories, ' ')"), expr("array_join(description, ' ')"), expr("array_join(features, ' ')"), "store", "details_UPC", "details_Package_Dimensions" ) ) df = df.withColumn( "embedding", expr("ai_query('databricks-gte-large-en', search_text)") ) return df # Process the raw data raw_df = spark.read.text(uc_volume_path) sample_json = raw_df.limit(1).collect()[0][0] json_schema = schema_of_json(sample_json) parsed_df = raw_df.select( from_json("value", json_schema).alias("data") ).select("data.*") flat_df = (parsed_df.select(flatten_and_sanitize(parsed_df)) .transform(prepare_for_search) ) # Save the enriched data flat_df.write.mode("overwrite").saveAsTable( "amazon_reviews_23.datasets.meta_all_beauty" ) </LI-CODE> <H3><SPAN>Key Features of the Data Processing</SPAN></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Schema Flattening</STRONG><SPAN>: Automatically handles nested JSON structures and sanitizes column names</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Search Text Generation</STRONG><SPAN>: Combines multiple fields (title, categories, description, features) into a single searchable text field</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Embedding Generation</STRONG><SPAN>: Uses Databricks' built-in </SPAN><SPAN>ai_query</SPAN><SPAN> function with the </SPAN><FONT face="courier new,courier"><SPAN>databricks-gte-large-en</SPAN></FONT><SPAN> model to generate 1024-dimensional embeddings</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Data Quality</STRONG><SPAN>: Handles missing values and ensures consistent data types</SPAN></LI> </UL> <H2><SPAN>Step 2: Load the table into Lakebase</SPAN></H2> <P><SPAN>We now possess the </SPAN><I><SPAN>meta_all_beauty</SPAN></I><SPAN> Unity Catalog table. However, for transactional applications, product searches against this dataset would typically occur within an OLTP database. This is where Databricks Lakebase becomes essential.&nbsp;</SPAN></P> <P><SPAN>As a prerequisite, you can follow these steps to </SPAN><A href="https://docs.databricks.com/aws/en/oltp/instances/create/" target="_blank" rel="noopener"><SPAN>create a lakebase instance</SPAN></A><SPAN> and </SPAN><A href="https://docs.databricks.com/aws/en/oltp/instances/register-uc" target="_blank" rel="noopener"><SPAN>register this database as a catalog in UC</SPAN></A><SPAN>. We will take the next few steps to load the data in </SPAN><I><SPAN>meta_all_beauty</SPAN></I><SPAN> into our Postgres database using the concept of </SPAN><A href="https://docs.databricks.com/aws/en/oltp/instances/sync-data/sync-table" target="_blank" rel="noopener"><SPAN>Synced tables</SPAN></A><SPAN>. Synced Tables offer a ‘managed data synchronization’ service from Unity Catalog tables to Postgres tables.</SPAN><SPAN> You can decide ‘how’, and ‘how frequently’, the data replication happens by choosing one of the following modes:&nbsp;</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Snapshot</STRONG><SPAN> – Replaces the entire destination with a fresh full copy of the source table on each run; most efficient for large changes.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Triggered</STRONG><SPAN> – Copies only incremental changes since the last run when manually or programmatically triggered; balances cost and lag.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Continuous</STRONG><SPAN> – Continuously streams changes from the source to the destination in near real time; lowest lag but highest cost.</SPAN></LI> </UL> <H3><SPAN>Various phases of a Synced table - Creation, Provisioning and Online Stages</SPAN></H3> <P><SPAN>Please note that the UI may change as the product evolves.</SPAN></P> <H4><SPAN>Creation of a Synced Table</SPAN></H4> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_1-1763668485591.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21857iEC1B58D52C4343CB/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_1-1763668485591.png" alt="uday_satapathy_1-1763668485591.png" /></span></P> <P>&nbsp;</P> <H4><SPAN>Sync begins</SPAN></H4> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_2-1763668485591.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21858i895C1D6979688C23/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_2-1763668485591.png" alt="uday_satapathy_2-1763668485591.png" /></span></P> <H4><SPAN>Rows being copied</SPAN></H4> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_3-1763668485592.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21859i26AA6A81F522409F/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_3-1763668485592.png" alt="uday_satapathy_3-1763668485592.png" /></span></P> <P>&nbsp;</P> <H4><SPAN>Sync finished and the Postgres target table is online</SPAN></H4> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_4-1763668485592.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21860iEFAF95E9548D930F/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_4-1763668485592.png" alt="uday_satapathy_4-1763668485592.png" /></span></P> <H4><SPAN>Querying the Synced table from the Postgres SQL Editor:</SPAN></H4> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_5-1763668485593.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21861i7DB3B44783C5E068/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_5-1763668485593.png" alt="uday_satapathy_5-1763668485593.png" /></span></P> <H4><SPAN>The DDL for this Synced table in Postgres looks like this:</SPAN></H4> <LI-CODE lang="python">CREATE TABLE product_reviews_analysis_oltp.meta_all_beauty ( average_rating float8 NULL, bought_together text NULL, categories jsonb NULL, description jsonb NULL, "details_Package_Dimensions" text NULL, "details_UPC" text NULL, features jsonb NULL, images jsonb NULL, main_category text NOT NULL, parent_asin text NOT NULL, price text NULL, rating_number int8 NULL, store text NULL, title text NULL, videos jsonb NULL, search_text text NULL, embedding jsonb NULL, CONSTRAINT meta_all_beauty_pkey PRIMARY KEY (parent_asin, main_category) ) PARTITION BY RANGE (parent_asin, main_category);</LI-CODE> <H2><SPAN>Step 3: Postgres Setup for Search</SPAN></H2> <P><SPAN>Now we'll set up the Postgres side with pgvector support and create our search-optimized tables.</SPAN></P> <H3><SPAN>Enable pgvector Extension</SPAN></H3> <LI-CODE lang="markup">CREATE EXTENSION IF NOT EXISTS vector;</LI-CODE> <H3><SPAN>Create the Search-Optimized Table</SPAN></H3> <P><SPAN>Product searches will happen against the </SPAN><I><SPAN>meta_all_beauty_indexed</SPAN></I><SPAN> table. This table is built using the data from the </SPAN><I><SPAN>meta_all_beauty </SPAN></I><SPAN>table (synced table) after adding two columns carrying indexing information for search. These columns are:&nbsp;</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>embedding_pg::VECTOR(1024): Column with PGVector datatype to house embeddings for text.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>search_vector::TSVECTOR: Column with Text Search datatype, optimized for full text search.</SPAN></LI> </UL> <LI-CODE lang="markup">CREATE TABLE product_reviews_analysis_oltp.meta_all_beauty_indexed ( parent_asin TEXT NOT NULL, main_category TEXT NOT NULL, search_text TEXT NOT NULL, embedding_pg VECTOR(1024), search_vector TSVECTOR, PRIMARY KEY (parent_asin, main_category) ); </LI-CODE> <H3><SPAN>Populate the Table with Data</SPAN></H3> <LI-CODE lang="markup">INSERT INTO product_reviews_analysis_oltp.meta_all_beauty_indexed ( parent_asin, main_category, search_text, search_vector, embedding_pg ) SELECT parent_asin, main_category, search_text, to_tsvector('english', search_text), string_to_array(trim(both '[]' from embedding::text), ',')::float8[]::vector FROM product_reviews_analysis_oltp.meta_all_beauty; </LI-CODE> <P><SPAN>In case the synced table is refreshed incrementally (Continuous / Triggered Mode) and the amount of inserts / updates happening in each refresh is much lower than the prior amount of data in the synced table, you could use a ‘merge’ operation (which happens in Postgres through an INSERT + ON CONFLICT pattern) to refresh the search index table. You could add a last_modified_date column in the source tables. For a given primary key, if the incoming row has a last_modified_date greater than that of the existing row, perform an update operation instead of an insert:</SPAN></P> <LI-CODE lang="markup">INSERT INTO product_reviews_analysis_oltp.meta_all_beauty_indexed ( parent_asin, main_category, search_text, search_vector, embedding_pg, last_modified ) SELECT parent_asin, main_category, search_text, to_tsvector('english', search_text), string_to_array(trim(both '[]' from embedding::text), ',')::float8[]::vector, last_modified FROM product_reviews_analysis_oltp.meta_all_beauty WHERE embedding IS NOT NULL -- optional safeguard ON CONFLICT (parent_asin, main_category) DO UPDATE SET search_text = EXCLUDED.search_text, search_vector = EXCLUDED.search_vector, embedding_pg = EXCLUDED.embedding_pg, last_modified = EXCLUDED.last_modified WHERE EXCLUDED.last_modified &gt; product_reviews_analysis_oltp.meta_all_beauty_indexed.last_modified;</LI-CODE> <H3><SPAN>Create Optimized Indexes</SPAN></H3> <LI-CODE lang="markup">-- Full-text search index CREATE INDEX idx_beauty_fts ON product_reviews_analysis_oltp.meta_all_beauty_indexed USING GIN (search_vector); -- Vector similarity search index CREATE INDEX idx_beauty_vector ON product_reviews_analysis_oltp.meta_all_beauty_indexed USING ivfflat (embedding_pg vector_cosine_ops) WITH (lists = 100); -- Update table statistics ANALYZE product_reviews_analysis_oltp.meta_all_beauty_indexed;</LI-CODE> <H3><SPAN>Understanding the Index Types</SPAN></H3> <P><SPAN>Before diving into the queries, let's understand what these indexes do:</SPAN></P> <H4><SPAN>GIN (Generalized Inverted Index)</SPAN></H4> <P><SPAN>Think of GIN as a </SPAN><STRONG>book's index</STRONG><SPAN> but for text search. When you search for "vitamin C serum," GIN quickly finds all documents containing those words without scanning every single record. It's like having a smart librarian who knows exactly which pages contain your search terms.</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Best for</STRONG><SPAN>: Full-text search, exact word matches</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Speed</STRONG><SPAN>: Very fast for keyword searches</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Memory</STRONG><SPAN>: Moderate memory usage</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Example</STRONG><SPAN>: Finding all products that contain "moisturizer" or "anti-aging"</SPAN></LI> </UL> <H4><SPAN>IVFFlat (Inverted File with Flat Compression)</SPAN></H4> <P><SPAN>Imagine you have 1 million product descriptions, and you want to find the 10 most similar ones to a query. IVFFlat is like organizing products into </SPAN><STRONG>100 different buckets</STRONG><SPAN> based on their similarity. When you search, it only looks in the most relevant buckets instead of checking all 1 million products.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="uday_satapathy_6-1763668863979.png" style="width: 716px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21863i4D5BD5481858F01B/image-dimensions/716x478?v=v2" width="716" height="478" role="button" title="uday_satapathy_6-1763668863979.png" alt="uday_satapathy_6-1763668863979.png" /></span></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Best for</STRONG><SPAN>: Vector similarity search, finding "similar" items</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Speed</STRONG><SPAN>: Fast for approximate similarity search</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Memory</STRONG><SPAN>: Low memory usage</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Build time:</STRONG><SPAN> Faster index build time than HNSW.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Rebuild time upon incremental updates:</STRONG><SPAN> Generally, a full-rebuild is needed (slow).</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Example</STRONG><SPAN>: Finding products similar to "vitamin C serum" even if they don't contain those exact words</SPAN></LI> </UL> <H4><SPAN>HNSW (Hierarchical Navigable Small World)</SPAN></H4> <P><SPAN>HNSW is like having a </SPAN><STRONG>smart GPS for similarity search</STRONG><SPAN>. Instead of checking every possible route, it uses a network of connections to quickly navigate to the most similar items. It's more sophisticated than IVFFlat and often faster, but uses more memory. </SPAN><A href="https://docs.databricks.com/aws/en/generative-ai/vector-search" target="_blank" rel="noopener"><SPAN>Mosaic AI Vector Search</SPAN></A><SPAN> uses HNSW indexing.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="uday_satapathy_7-1763668863976.png" style="width: 602px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21862i084FB82D9F9E2327/image-dimensions/602x902?v=v2" width="602" height="902" role="button" title="uday_satapathy_7-1763668863976.png" alt="uday_satapathy_7-1763668863976.png" /></span></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Best for</STRONG><SPAN>: High-performance vector similarity search</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Speed</STRONG><SPAN>: Very fast, often faster than IVFFlat</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Memory</STRONG><SPAN>: Higher memory usage</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Build time:</STRONG><SPAN> Slower index build time than IVFFlat.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Rebuild time upon incremental updates:</STRONG><SPAN> Fast insert into a graph. No global rebuild needed.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Example</STRONG><SPAN>: Finding the most similar products in a large catalog with sub-second response times</SPAN></LI> </UL> <H4><SPAN>When to Use Each Index</SPAN></H4> <TABLE> <TBODY> <TR> <TD> <P><STRONG>Index Type</STRONG></P> </TD> <TD> <P><STRONG>Use Case</STRONG></P> </TD> <TD> <P><STRONG>Speed</STRONG></P> </TD> <TD> <P><STRONG>Memory</STRONG></P> </TD> <TD> <P><STRONG>Best For</STRONG></P> </TD> </TR> <TR> <TD> <P><STRONG>GIN</STRONG></P> </TD> <TD> <P><SPAN>Text search</SPAN></P> </TD> <TD> <P><SPAN>Very Fast</SPAN></P> </TD> <TD> <P><SPAN>Low</SPAN></P> </TD> <TD> <P><SPAN>"Find products with 'vitamin C'"</SPAN></P> </TD> </TR> <TR> <TD> <P><STRONG>IVFFlat</STRONG></P> </TD> <TD> <P><SPAN>Vector similarity</SPAN></P> </TD> <TD> <P><SPAN>Fast</SPAN></P> </TD> <TD> <P><SPAN>Low</SPAN></P> </TD> <TD> <P><SPAN>"Find products similar to this one"</SPAN></P> </TD> </TR> <TR> <TD> <P><STRONG>HNSW</STRONG></P> </TD> <TD> <P><SPAN>Vector similarity</SPAN></P> </TD> <TD> <P><SPAN>Very Fast</SPAN></P> </TD> <TD> <P><SPAN>High</SPAN></P> </TD> <TD> <P><SPAN>"Find similar products in huge catalogs"</SPAN></P> </TD> </TR> </TBODY> </TABLE> <P><SPAN>For most applications, start with GIN for text search and IVFFlat for vector search. Upgrade to HNSW when you need maximum performance and have sufficient memory.</SPAN></P> <H2><SPAN>Step 4: Search Query Examples</SPAN></H2> <P><SPAN>Now let's explore different types of search queries you can perform.</SPAN></P> <H3><SPAN>Full-Text Search Only</SPAN></H3> <P><SPAN>Perfect for exact keyword matches and traditional search functionality:</SPAN></P> <LI-CODE lang="markup">WITH fts_search AS ( SELECT parent_asin, main_category FROM product_reviews_analysis_oltp.meta_all_beauty_indexed WHERE search_vector @@ plainto_tsquery('english', 'vitamin c moisturizer') LIMIT 10 ) SELECT f.parent_asin, f.main_category, m.title, m.price, m.rating_number, m.categories FROM fts_search f JOIN product_reviews_analysis_oltp.meta_all_beauty m ON f.parent_asin = m.parent_asin AND f.main_category = m.main_category;</LI-CODE> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_8-1763669333108.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21864i792A910C27B1326C/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_8-1763669333108.png" alt="uday_satapathy_8-1763669333108.png" /></span></P> <H3><SPAN>Ranked Full-Text Search</SPAN></H3> <P><SPAN>Enhance your search results with relevance scoring. The </SPAN><SPAN>ts_rank_cd()</SPAN><SPAN> function computes a </SPAN><STRONG>ranking score</STRONG><SPAN> indicating the degree to which the </SPAN><SPAN>search_vector</SPAN><SPAN> column aligns with the query 'vitamin c serum'. The </SPAN><SPAN>@@</SPAN><SPAN> operator determines (as a boolean) if a </SPAN><SPAN>tsvector</SPAN><SPAN> matches a </SPAN><SPAN>tsquery</SPAN><SPAN>.</SPAN></P> <LI-CODE lang="markup">WITH fts_ranked AS ( SELECT parent_asin, main_category, ts_rank_cd(search_vector, plainto_tsquery('english', 'vitamin c serum')) AS rank FROM product_reviews_analysis_oltp.meta_all_beauty_indexed WHERE search_vector @@ plainto_tsquery('english', 'vitamin c serum') ORDER BY rank DESC LIMIT 10 ) SELECT r.parent_asin, r.main_category, m.title, m.average_rating, m.price, r.rank FROM fts_ranked r JOIN product_reviews_analysis_oltp.meta_all_beauty m ON r.parent_asin = m.parent_asin AND r.main_category = m.main_category;</LI-CODE> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_9-1763669526405.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21865iAAE116B723E0277A/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_9-1763669526405.png" alt="uday_satapathy_9-1763669526405.png" /></span></P> <H3><SPAN>Vector Similarity Search</SPAN></H3> <P><SPAN>To find semantically similar products, you generate vector embeddings for your search text using the same embedding model that created the vector index. You then query the index to retrieve documents most similar to these generated embeddings. We achieve this by utilizing </SPAN><A href="https://docs.databricks.com/aws/en/machine-learning/model-serving/query-embedding-models?language=SQL" target="_blank" rel="noopener"><SPAN>Databricks' </SPAN><SPAN>ai_query()</SPAN><SPAN> function with the </SPAN><SPAN>databricks-gte-large-en</SPAN><SPAN> foundation model</SPAN></A><SPAN> to generate the embeddings.</SPAN></P> <LI-CODE lang="markup">WITH vector_search AS ( SELECT parent_asin, main_category FROM product_reviews_analysis_oltp.meta_all_beauty_indexed ORDER BY embedding_pg &lt;-&gt; '[0.3544921875,-0.272705078125,...]'::vector LIMIT 10 ) SELECT v.parent_asin, v.main_category, m.title, m.price, m.average_rating, m.store FROM vector_search v JOIN product_reviews_analysis_oltp.meta_all_beauty m ON v.parent_asin = m.parent_asin AND v.main_category = m.main_category;</LI-CODE> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_10-1763669606900.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21866iB56EA47CC164B480/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_10-1763669606900.png" alt="uday_satapathy_10-1763669606900.png" /></span></P> <H3><SPAN>Hybrid Search</SPAN></H3> <P><SPAN>Combine the power of both full-text and vector search for the best results:</SPAN></P> <LI-CODE lang="markup">WITH search_results AS ( SELECT parent_asin, main_category FROM product_reviews_analysis_oltp.meta_all_beauty_indexed WHERE search_vector @@ plainto_tsquery('english', 'vitamin c moisturizer') ORDER BY embedding_pg &lt;-&gt; '[0.3544921875,-0.272705078125,...]'::vector LIMIT 10 ) SELECT r.parent_asin, r.main_category, m.title, m.store, m.average_rating, m.rating_number, m.price, m.categories, m.features, m.description FROM search_results r JOIN product_reviews_analysis_oltp.meta_all_beauty m ON r.parent_asin = m.parent_asin AND r.main_category = m.main_category;</LI-CODE> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="uday_satapathy_11-1763669654141.png" style="width: 999px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/21867i53DA488CC2AAEFFD/image-size/large?v=v2&amp;px=999" role="button" title="uday_satapathy_11-1763669654141.png" alt="uday_satapathy_11-1763669654141.png" /></span></P> <H2><SPAN>Performance Optimization Tips</SPAN></H2> <H3><SPAN>Index Tuning</SPAN></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>IVFFlat Index</STRONG><SPAN>: Adjust the </SPAN><SPAN>lists</SPAN><SPAN> parameter based on your data size. For 1M vectors, use 100-1000 lists</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>HNSW Index</STRONG><SPAN>: For even better performance, consider upgrading to HNSW when available</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>GIN Index</STRONG><SPAN>: Optimize for your typical query patterns</SPAN></LI> </UL> <H3><SPAN>Query Optimization</SPAN></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Use </SPAN><SPAN>LIMIT</SPAN><SPAN> clauses to control result set size</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Combine filters to reduce the search space before vector operations</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Consider using approximate search for very large datasets</SPAN></LI> </UL> <H3><SPAN>Monitoring and Maintenance</SPAN></H3> <LI-CODE lang="markup">-- Check index usage SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE tablename = 'meta_all_beauty_indexed'; -- Monitor query performance EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM product_reviews_analysis_oltp.meta_all_beauty_indexed WHERE search_vector @@ plainto_tsquery('english', 'vitamin c'); </LI-CODE> <H2><SPAN>Best Practices</SPAN></H2> <H3><SPAN>Data Quality</SPAN></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Consistent Text Processing</STRONG><SPAN>: Ensure your search text generation is consistent across all records</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Embedding Quality</STRONG><SPAN>: Use high-quality embedding models and validate results</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Regular Updates</STRONG><SPAN>: Keep embeddings current as your data changes</SPAN></LI> </UL> <H3><SPAN>Security and Privacy</SPAN></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Access Control</STRONG><SPAN>: Implement proper row-level security for sensitive data</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Data Masking</STRONG><SPAN>: Consider masking sensitive information in search results</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Audit Logging</STRONG><SPAN>: Track search queries for compliance and analytics</SPAN></LI> </UL> <H3><SPAN>Scalability</SPAN></H3> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Partitioning</STRONG><SPAN>: Consider partitioning large tables by category or date</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Caching</STRONG><SPAN>: Implement query result caching for frequently accessed data</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Load Balancing</STRONG><SPAN>: Distribute query load across multiple read replicas</SPAN></LI> </UL> <H2><SPAN>Conclusion</SPAN></H2> <P><SPAN>With pgvector on Databricks Lakebase, you can build powerful semantic search capabilities that understand meaning, not just keywords. This unified approach brings together:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Data Engineering</STRONG><SPAN>: Seamless data processing with Spark and Delta Lake</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>AI Integration</STRONG><SPAN>: Built-in embedding generation with Databricks AI functions</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>OLTP Performance</STRONG><SPAN>: Fast, indexed queries with Postgres compatibility</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Scalability</STRONG><SPAN>: Enterprise-grade performance with proper indexing strategies</SPAN></LI> </UL> <P><SPAN>The combination of Databricks' data processing power and pgvector's search capabilities creates a compelling solution for modern AI applications. Whether you're building e-commerce search, content discovery, or intelligent document retrieval, this architecture provides the foundation for sophisticated semantic search at scale.</SPAN></P> <P><SPAN>Start with the examples in this guide, adapt them to your specific use case, and watch as your applications become more intelligent and user-friendly through the power of semantic search.</SPAN></P> <H2><SPAN>Additional Resources</SPAN></H2> <UL> <LI style="font-weight: 400;" aria-level="1"><A href="https://github.com/pgvector/pgvector" target="_blank" rel="noopener"><SPAN>pgvector Documentation</SPAN></A></LI> <LI style="font-weight: 400;" aria-level="1"><A href="https://docs.databricks.com/aws/en/large-language-models/ai-functions" target="_blank" rel="noopener"><SPAN>Databricks AI Functions</SPAN></A></LI> <LI style="font-weight: 400;" aria-level="1"><A href="https://www.postgresql.org/docs/current/textsearch.html" target="_blank" rel="noopener"><SPAN>Postgres Full-Text Search</SPAN></A></LI> <LI style="font-weight: 400;" aria-level="1"><A href="https://docs.databricks.com/aws/en/query-federation/" target="_self"><SPAN>Databricks Lakehouse Federation</SPAN></A></LI> </UL> Thu, 04 Dec 2025 15:58:00 GMT uday_satapathy 2025-12-04T15:58:00Z https://community.databricks.com/t5/lakebase-blogs/how-to-perform-semantic-search-in-databricks-lakebase/ba-p/139846 <P><SPAN class="appsElementsGenerativeaiAstAnimated">In today's AI-native world, applications are moving beyond keyword matching to embrace<SPAN>&nbsp;</SPAN></SPAN><STRONG><SPAN class="appsElementsGenerativeaiAstAnimated">semantic search</SPAN></STRONG><SPAN class="appsElementsGenerativeaiAstAnimated"><SPAN>&nbsp;</SPAN>powered by embeddings. This blog post explores how to leverage<SPAN>&nbsp;</SPAN></SPAN><STRONG><SPAN class="appsElementsGenerativeaiAstAnimated">pgvector on Databricks Lakebase</SPAN></STRONG><SPAN class="appsElementsGenerativeaiAstAnimated"><SPAN>&nbsp;</SPAN>(Postgres OLTP database) to natively store, index, and query these embeddings in SQL. Learn the architecture to unify your product metadata, vector data, and operational workloads in a single, scalable Lakehouse-integrated platform for powerful e-commerce search, intelligent recommendations, and more.</SPAN></P> Thu, 04 Dec 2025 15:58:00 GMT https://community.databricks.com/t5/lakebase-blogs/how-to-perform-semantic-search-in-databricks-lakebase/ba-p/139846 uday_satapathy 2025-12-04T15:58:00Z https://community.databricks.com/t5/lakebase-blogs/how-to-perform-semantic-search-in-databricks-lakebase/bc-p/141178#M12 <P><a href="https://community.databricks.com/t5/user/viewprofilepage/user-id/82106">@uday_satapathy</a>&nbsp;- Quite comprehensive example. Thanks.</P> Thu, 04 Dec 2025 16:10:04 GMT https://community.databricks.com/t5/lakebase-blogs/how-to-perform-semantic-search-in-databricks-lakebase/bc-p/141178#M12 Raman_Unifeye 2025-12-04T16:10:04Z